discovering hidden / undocumented functionality

Before you read this I would stress that this is not an invitation to try out this functionality once discovered. But if you ever wondered how the Oracle gurus who frequent the Oracle UK User group scene and the members of the Oaktable etc find this sort of information out here are some pointers:-

• x$ksppi fixed view
  

 select *
  from SYS.X$KSPPI
 where substr(KSPPINM,1,1) = '_';

select a.ksppinm  "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
   from x$ksppi a, x$ksppcv b, x$ksppsv c
 where a.indx = b.indx and a.indx = c.indx
     and substr(ksppinm,1,1)='_'

• 10053 trace
  
  At the top of the trace file a full description of the Optimizer environment including hidden parameters can be found. Jonathan Lewis covers this in his "Cost-Based Oracle Fundamentals" book. If you don't have this check out the white papers section of www.centrexcc.com .
  
  
• performing strings (Unix) on the Oracle executable
  This will reveal the full list of hints that Oracle uses.