OraFAQ Forum: Server Administration » Query hidden parameters

Home » RDBMS Server » Server Administration » Query hidden parameters (Oracle 11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query hidden parameters [message #602377]

Tue, 03 December 2013 13:52

juamd
Messages: 21
Registered: November 2011
Location: Colombia

Junior Member

Hi.

I am not able to get some hidden parameters from oracle, I need this parameters:

_always_semi_join
_b_tree_bitmap_plans
_partition_view_enabled
_gc_defer_time
_no_or_expansion
_optimizer_max_permutations

And I am using this query:

select par.ksppinm name,
val.ksppstvl value,
val.ksppstdf default_value
from x$ksppi par,
x$ksppcv val
where par.indx=val.indx;

But none of them appear

My db is started with spfile and my user has sysdba role.

Thanks.

Report message to a moderator

Re: Query hidden parameters [message #602378 is a reply to message #602377]

Tue, 03 December 2013 14:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You should only ever be using hidden parameters under direction from Oracle Support.
Submit your question via Service Request to MOS.

Report message to a moderator

Re: Query hidden parameters [message #602531 is a reply to message #602377]

Thu, 05 December 2013 06:54

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

I would try something like :

SQL> select * 
           from   x$kspvld_values  
           where  lower(NAME_KSPVLD_VALUES) like '%always_semi_join%';

ADDR           INDX    INST_ID PARNO_KSPVLD_VALUES NAME_KSPVLD_VALUES   ORDINAL_KSPVLD_VALUES VALUE_KSPVLD_VALUES  ISDEFAULT_KSPVLD_VALUES
-------- ---------- ---------- ------------------- -------------------- --------------------- -------------------- ----------------------------------------------------------------
B6A28818        501          1                2114 _always_semi_join                        1 HASH                 FALSE
B6A28818        502          1                2114 _always_semi_join                        2 MERGE                FALSE
B6A28818        503          1                2114 _always_semi_join                        3 NESTED_LOOPS         FALSE
B6A28818        504          1                2114 _always_semi_join                        4 CHOOSE               FALSE
B6A28818        505          1                2114 _always_semi_join                        5 OFF                  FALSE
SQL>

But keep in mind the words from Blackswan. You should be well aware of what you are doing to play around with hidden settings.
And...don't play around in production.

Report message to a moderator

Re: Query hidden parameters [message #602566 is a reply to message #602531]

Thu, 05 December 2013 13:44

juamd
Messages: 21
Registered: November 2011
Location: Colombia

Junior Member

Thanks guys.. that worked and by the way, we need to modify these parameters because of oracle support tells us in a document about siebel's performance.

Report message to a moderator

Previous Topic:	Completed redo scan
Next Topic:	Disabling SQL Tuning Advisor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 20 11:31:33 CDT 2026