How to view list of hidden parameters

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,
decode
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like ‘\_%’ escape ‘\’
order by
name

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 ?

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’
) REQUEST,
DECODE( A.lmode,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
) LMODE,
DECODE (A.BLOCK,
0, ‘None’ ,
1, ‘BLOCKING’) BLOCK,
B.os_user_name,
C.object_name
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.*

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