SQL of the Day: How to view progress of DML operations

So, you're running an INSERT/UPDATE/DELETE statement and it's just sitting there processing. Then your boss comes by and says, "When will this be done?". Well now you can tell them.

Run this SQL to show the progress of DML operations:

SELECT SUBSTR(sql_text, 1, 60) "SQL Text",
       rows_processed "Total Rows Processed",
       ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
       TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
       TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec"
  FROM SYS.v_$sqlarea
 WHERE sql_text LIKE 'INSERT INTO some_table%'
   AND open_versions > 0
   AND rows_processed > 0;

D'oh! of the Day: Creating a tablespace with 16k blocksize in a database with 8k blocksize default

So, this should have been a no-brainer.

I'm trying to improve the performance of a DSS database (10gR2 with ASM), so I figure, "Hey, i'll create a new tablespace with a 16k blocksize and reorg 2 big tables into it to see if that helps."

SQL> CREATE SMALLFILE TABLESPACE "DATA_16K"
DATAFILE'+DGROUP1(DATAFILE)' SIZE 1M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K;
ORA-27035: logical block size is invalid

Huh??

Here's the solution (the D'oh! moment): I never created a buffer pool for 16k blocks!

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID|NAME |BLOCK_SIZE|CURRENT_SIZE| BUFFERS
----------|--------------------|----------|------------|----------
3|DEFAULT | 8192| 2560| 317439

SQL> alter system set db_16K_cache_size=2m scope=both;
System altered.

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID|NAME |BLOCK_SIZE|CURRENT_SIZE| BUFFERS
----------|--------------------|----------|------------|----------
3|DEFAULT | 8192| 2528| 313471
7|DEFAULT | 16384| 32| 2014

SQL> CREATE SMALLFILE TABLESPACE "DATA_16K"
DATAFILE'+DGROUP1(DATAFILE)' SIZE 1M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K;

Tablespace created.

Yes, i'm easily amused some days.

Having issues using DBCA with ASM on 10gR2?

So, i'm feeling particularly pleased last week when I finally get ASM up and running last week on a new reporting server. It's an IBM AIX box, with a 600GB disk group (DGROUP1), using the usual "+ASM" instance name. Also note that I used my 10gR2 (10.2.0.1 EE) home as my ASM home, since i'm only using ASM for a single database on this box.

Once i verify that ASM is working and that it's hooked into EM Grid Control, I cd to my Oracle 10gR2 home, setup my environment by using oraenv, and then run dbca.

Everything is going great until I get to the storage page, where I select "ASM" and click Next. Within a couple of seconds, I see this gem of an error message:

"DBCA could not startup the ASM instance configured on this node. To proceed with the database creation using ASM, ASM instance needs to be up and running. Do you want to recreate ASM instance on this node?"

Hmm. Not what I wanted to see, that's for sure. :-(

So it's off to Metalink to see what Oracle has to say. In no short order, I find about 6 separate issues (some with their own accompanying bug numbers) all reporting the exact same issue, and all with existing ASM
instances.

The only note that had a real work-around involved changing some OAS (Oracle Advanced Security) settings as ODBC did not work well with them. That's all well-and-good, but i'm not using OAS. My sqlnet.ora file has one line!

Next, I did what all good DBA's do when faced with this type of situation: go to Google. Unfortunately, I was still unable to find a solution. The OTN forums were not much help either. Oddly enough, some of my searches even resulted in googlewhacks.

The one interesting result I found on Metalink said something like, "I managed to use dbca to setup a database with ASM before, but now that I try it a second time, it does not work!" Interesting. To me, that meant there HAD TO BE a solution here. There's just no way that dbca is unable to create a database with ASM. Common!!

Here's what i'm doing: I have 2 terminal windows open - one was running dbca, the other was my working terminal. The working one being the terminal I used to change listener settings, tnsping, etc.

After 2 hours of a hapless tango between clicking Next> and seeing the same error, then changing settings ... , I finally throw in the towel at 4:30 and close the term window running dbca.

Just for fun, I run dbca from the OTHER term window where i've been continually futzing with settings. I get to Storage, select ASM and click Next... NOW IT WORKS!!! WTF?!

That's why it works for some people sometimes and then stops the next time. We've got some env variable or something set wrong!

SOLUTION: If you get this error, check your settings, open another window, re-run oraenv, keep trying!

As soon as dbca completed, I catpured my 'env' output - here's the items of interest:

• ORACLE_SID=+ASM
• ORACLE_HOME=/orabase/product/ora10g
• LIBPATH=/orabase/product/ora10g/lib:/orabase/product/ora10g/lib32:/usr/lib:/lib
• JAVA_HOME=/usr/java5/bin
  

HowTo: Force re-evaluation of Policy Violations in OEM Grid Control

Don't you hate when you fix a Security/Policy Violation in OEM and it won't re-evaluate it for another 24 hours?

Well there's 2 things you can try:

1. Apply your Monitoring Template to the object (this works especially well for Hosts), this will force a re-evaluation and clear outstanding alerts
2. Use an undocumented parameter for the EM Agent (10.2.0.1+) "emctl clearstate agent". This will cause the Agent to re-evaluate the instances on it's host - note: this can take a couple of hours to run and update in the background!