Home » RDBMS Server » Server Administration » Query hidden parameters (Oracle 11g R2)
Query hidden parameters [message #602377] Tue, 03 December 2013 13:52 Go to next message
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.
Re: Query hidden parameters [message #602378 is a reply to message #602377] Tue, 03 December 2013 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 22506
Registered: January 2009
Senior Member
You should only ever be using hidden parameters under direction from Oracle Support.
Submit your question via Service Request to MOS.
Re: Query hidden parameters [message #602531 is a reply to message #602377] Thu, 05 December 2013 06:54 Go to previous messageGo to next message
martijn
Messages: 267
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.
Re: Query hidden parameters [message #602566 is a reply to message #602531] Thu, 05 December 2013 13:44 Go to previous message
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.
Previous Topic: Completed redo scan
Next Topic: Disabling SQL Tuning Advisor
Goto Forum:
  


Current Time: Sat Jul 26 00:56:05 CDT 2014

Total time taken to generate the page: 5.11001 seconds