Coskan’s Approach to Oracle

February 7, 2007

Effects of Adding Extra Columns To an Index

Filed under: CBO — coskan @ 1:24 pm

Continuing with highlights from the Oracle Cost Based Optimizer Book of Mr Jonathan Lewis ;

Case : One day you discover that users started to add another filter to the where clauses and you decided to add the newly added filter as an extra column to your index.

Mr Lewis says that this decision must be made with attention because of the side affects to old queries.

CBO uses clustering_factor column from the dba_indexes table to calculate the cost of and index. The calculation formula is below ;

Cost =Blevel+ ceil(effective_index_selectivity*Leaf Blocks)+ceil(effective_table_selectivity*clustering_factor)

When you add an extra column to a well clustered index, Cluster Factor can be change dramatically and when the cluster_factor increased, your cost would increase more than you imagine.

So think twice, test twice before adding another column to an index.

To see how costs go crazy Code Listing 2

Refences Used :Oracle Cost Based Optimizer Fundamentals (Jonathan Lewis) pg 104-106

(All my test are done under Oracle Locally Managed Table without ASSM and CPU costing disabled as the writers test environment)

February 6, 2007

How to view list of hidden parameters

Filed under: How To — coskan @ 1:22 pm

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)

/* hidden parameters */

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
sys.x$ksppi a,
sys.x$ksppcv b
a.indx = b.indx
a.ksppinm like ‘\_%’ escape ‘\’
order by

Footnote 27/07/2009: I personally start to use Tanel Poder’s script to search both hidden and normal parameters

Who is locking with what type of lock ?

Filed under: How To — coskan @ 12:54 pm

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.

/*who is locking ? what type of lock ?*/

select A.sid,A.type,DECODE (A.REQUEST,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
DECODE( A.lmode,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
0, ‘None’ ,
from v$lock A, v$locked_object B, dba_objects C
where B.session_id=A.sid and C.object_id = B.object_id
AND OWNER=’OWNER’ and c.object_name=’X;

4- The session was one of the DBA’s session so the session could be killed and i killed the session

alter system kill session ‘sid,serial#’

5-I looked back to blockers table and became sure that the problem is solved.

select holding_session from dba_blockers;

>no rows

null value impact on release 10.1.0.*

Filed under: CBO — coskan @ 9:58 am

Nowadays i am studying on the book of Mr Jonathan Lewis , Oracle CBO Fundamentals.

I saw a good example on a bug like situation On Ch4- S IMPLE B-TREE ACCESS

Mr Lewis has proven wrong behaviour of CBO when null values exist on indexes.

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).

Code listing 1 : Using Nulls On 10.1.0.*

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. “

Refences Used :Oracle Cost Based Optimizer Fundamentals(Jonathan Lewis)

pg 80-81

Theme: Silver is the New Black. Get a free blog at


Get every new post delivered to your Inbox.

Join 203 other followers