If you want to get rid of the content of USER_TAB_MODIFICATIONS or DBA_TAB_MODIFICATIONS then you have to execute dbms_stats.FLUSH_DATABASE_MONITORING_INFO
If that is taking a very long time then you might have hit the oracle bug 5709414
Use Opatch.
Look in the Opatch directory in Oracle Home.
‘opatch query -all’ will allow you to query detailed information about a patch.
‘opatch lsinventory’ will list all patches with the applied date chronologically.
‘opatch lsinventory -detail’ will list all patches and which files were modified.
A collegue of mine had to debug all his code, twice, to try to find why one of his tables wasn’t populated.
Here is why: He asked to execute a script (script didn’t contained a commit statement) in sql*plus at someone of the production team. That guy opened sql*plus, executed the script and closed window (File>Exit) through the menu.
Now if you close sql*plus by using the menu or the X icon on the top right of your window, sql*plus DOES NOT commit when quiting. When you type at the prompt: “exit” or “quit” then you commit and exit sql*plus.
Just keep that in mind and btw the option “autocommit” has noting to to with this problem (on a lot of blogs and forums they thing autocommit will autocommit on exit, NOT!)
I found a pdf that contained the SQL reference manuel of Oracle 7, you remember?
Pure nostalgic document that probably everybody had laying on their desk or drawer.
Here it is: Oracle v7 sql_reference
Because of some problems with the Oracle statistics I did a small research.
I have a custom procedure of gathering stats that i call p_x_stats_table. It will gather stats with and estimation of X% of the records. With X corresponding a percentage witch reflect 100.000 records (or all records of table if amount of records inferiuor then 100.000).
With that procedure i have no problems, it’s more with the “standard” ones. (btw this has been tested with Oracle 10.2.0.3).
The real amount of records of TABLE_X:
COUNT(*)
———-
1507788
SQL>exec p_x_stats_table('TABLE_X'); PL/SQL PROCEDURE successfully completed. Elapsed: 00:04:09.00 NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE ---------- ----------- ----------- 1505339 589 99804 COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM AVG_COL_LEN ---------------------- ------------ ----------- ----------- --------------- ----------- PARTITION_KEY 1 1 99804 FREQUENCY 13 CONTRACT_REFERENCE 1082788 1 99804 NONE 20 TABLE_NAME 18 1 99804 NONE 9 CONTRACT_TYPE 192 10 99804 HEIGHT BALANCED 9 BS_TYPE 9 1 99804 NONE 4 BOOK_CODE 320 27 99804 HEIGHT BALANCED 10 TRADING 2 1 99804 NONE 2 GL_BOOK_TYPE 8 1 99804 NONE 9 START_DATE 6171 254 98753 HEIGHT BALANCED 8
Here instead of taking a pourcentage (based on 100.000 records) we take 5% and a block_sampling at TRUE (with p_x_stats_table block_sampling is always false).
SQL>exec DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_X',NULL,5,TRUE,'FOR ALL COLUMNS SIZE SKEWONLY',3,'GLOBAL',TRUE); PL/SQL PROCEDURE successfully completed. Elapsed: 00:02:21.14 NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE ---------- ----------- ----------- 1540240 586 77012 COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM AVG_COL_LEN ---------------------- ------------ ----------- ----------- --------------- ----------- PARTITION_KEY 1 1 77012 FREQUENCY 13 CONTRACT_REFERENCE 924372 1 77012 NONE 20 TABLE_NAME 10 1 77012 NONE 9 CONTRACT_TYPE 105 10 77012 HEIGHT BALANCED 9 BS_TYPE 5 1 77012 NONE 4 BOOK_CODE 137 28 77012 HEIGHT BALANCED 10 TRADING 2 1 77012 NONE 2 GL_BOOK_TYPE 7 1 77012 NONE 9 START_DATE 5646 254 76954 HEIGHT BALANCED 8
You can see that the num_distinct values are not so optimal (for example column table_name contains in reality 19 distinct values). Now let’s try with DBMS_STATS.AUTO_SAMPLE_SIZE (in my opinion only usable from Oracle 11G onwards, why? you’ll see). Block sampling stays at TRUE in this example.
SQL>exec DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_X',NULL,DBMS_STATS.AUTO_SAMPLE_SIZE,TRUE,'FOR ALL COLUMNS SIZE SKEWONLY',3,'GLOBAL',TRUE); PL/SQL PROCEDURE successfully completed. Elapsed: 00:13:46.92 NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE ---------- ----------- ----------- 1507788 596 1507788 COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM AVG_COL_LEN ---------------------- ------------ ----------- ----------- --------------- ----------- PARTITION_KEY 1 1 6840 FREQUENCY 13 CONTRACT_REFERENCE 1211381 254 1507788 HEIGHT BALANCED 20 TABLE_NAME 4 4 6840 FREQUENCY 10 CONTRACT_TYPE 33 33 6840 FREQUENCY 9 BS_TYPE 3 3 6840 FREQUENCY 4 BOOK_CODE 14 14 6840 FREQUENCY 10 TRADING 1 1 6840 FREQUENCY 2 GL_BOOK_TYPE 1 1 6840 FREQUENCY 9 START_DATE 1744 254 6840 HEIGHT BALANCED 8
WOOOOOW, check those results, this stinks! Bad histogram types, bad number of distinc values. No wonder that some of the production queries have sometimes long execution times. Now with some more research of the skewed data in my table i discover that the main reason of my problems might be the block_sample parameter. Let’s try now to put this parameter to FALSE. And did you noted how long it took to gather wrong stats!?!
sql> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_X',NULL,DBMS_STATS.AUTO_SAMPLE_SIZE,FALSE,'FOR ALL COLUMNS SIZE SKEWONLY',3,'GLOBAL',TRUE); PL/SQL PROCEDURE successfully completed. Elapsed: 00:10:03.51 NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE ---------- ----------- ----------- 1507788 590 1507788 COLUMN_NAME NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM AVG_COL_LEN ---------------------- ------------ ----------- ----------- --------------- ----------- PARTITION_KEY 1 1 5721 FREQUENCY 13 CONTRACT_REFERENCE 1211381 254 1507788 HEIGHT BALANCED 20 TABLE_NAME 16 16 5721 FREQUENCY 9 CONTRACT_TYPE 113 113 5721 FREQUENCY 9 BS_TYPE 9 9 5721 FREQUENCY 4 BOOK_CODE 141 141 5721 FREQUENCY 10 TRADING 2 2 5721 FREQUENCY 2 GL_BOOK_TYPE 8 8 5721 FREQUENCY 9 START_DATE 3021 254 5653 HEIGHT BALANCED 8
Not to bad, slightly faster then block_sample=TRUE (but still 250% slower then p_x_stats_table), much better results then with block_sample=TRUE. The choice of histograms type are slightly bizar. Certain columns don’t need histograms at all (that might explain why the stats took much longer).
So, if you guys are using Oracle 10R2 (or older) try to make yourself your own statistics program (with block_sample=FALSE if you have a table with skewed data and do a sample of around 100.000 records.
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPSTAT_TAB_HISTORY
There is a good article (written by Marc Rittman) that’s explains how to finally get OBIEE and MS cubes work together.
The most important paragraph is:
Getting XML/A working with Microsoft Analysis Services 2000 is a bit tricky as you’ve got to download the Microsoft XML For Analysis SDK from Microsoft Technet, upgrade Analysis Services to Service Pack 2 or higher, and install Internet Information Services, but once you’re up and running you can start up Oracle BI Administration and connect to your cube.
http://www.rittmanmead.com/2008/02/01/obiee-hybrid-olap-reporting-using-ms-analysis-services-oracle/
In Oracle 10gR2 there is a way to make a commit even faster in your PL/SQL code. Hey every microsecond counts.
COMMIT WRITE IMMEDIATE NOWAIT;
