February 6, 2007

Hidden parameters sometimes ease the life of DBA but be carefull while using them Oracle usually does not support the usage of hidden parameters without their knowledge and you cant even know the side effects of the parameter.

Here is the query you can use to view the values of hidden parameter. (use it with sys user with sysdba privilages)

Some of our main application processes hanged today (not the database only the processes) while i was at lunch time. When I arrived the company my manager asked me to look at the database for understanding the cause of hang. I simply realised that there is a lock on somewhere which is waiting other sessions.

I followed the following way to understand what was going on for solving the problem.

1- who is blocking select holding_session from dba_blockers;

2- what is he blocking

select sid,serial#,sql_text,username from v$sql s,v$session se where se.sql_id=s.sql_id where sid=’holder';

3- when i looked in the sql_text i understood session is working on table X

after that i looked at what is the type of lock for being sure about the cause of the problem.

When you get statistics of an index with null values Oracle lowers the num_row column of index with number of nulls and the CBO start to use value index selectivity with filters for value of index_selectivity -which has the effect of losing the cost of the leaf_block accesses (can be seen by 10053 trace file).

below is the writers note about the occurance of this case is randomly, but i couldn’t create the case about the wrong behaviour of gather_table_stats package with cascade option.

“The underlying problem is that the call to dbms_stats.gather_table_stats(), with the cascade option set to true, sometimes fails to update the index statistics. Counterintuitively, when dbms_stats gets it wrong, the execution plan comes up with the right cost (because the value of user_indexes.num_rows stays the same as user_tables.num_rows), and when dbms_stats gets it right, the execution plan comes up with the wrong costs because user_indexes.num_rows is (correctly) recorded as being less than user_tables.num_rows. ”