Oracle Hidden Parameters

Display all Hidden Parameters
Hidden Parameter

================

with comment 

alter system set "_kgl_large_heap_warning_threshold"=xxxxxxxvalue   comment='SR NO XXXXXXX' scope=both ;

set line 150

 col PARAMETER format a35

 col DESCRIPTION format a60

 col SESSION_VALUE format a15

 col INSTANCE_VALUE format a10

 --spool HIDDEN_parameter.log

 col comment1  for a15

 SELECT a.ksppinm AS parameter,

        -- a.KSPPINM,

        a.ksppdesc AS description,

        b.ksppstvl AS session_value,

        c.ksppstvl AS instance_value,

        b.KSPPSTCMNT AS  comment1

 FROM   x$ksppi a,

        x$ksppcv b,

        x$ksppsv c

 WHERE  a.indx = b.indx

 AND    a.indx = c.indx

 AND    a.ksppinm LIKE '/_kgl_large_heap_assert_threshold' ESCAPE '/' ;

 

 

PARAMETER                           DESCRIPTION                                                  SESSION_VALUE   INSTANCE_V COMMENT1

----------------------------------- ------------------------------------------------------------ --------------- ---------- ---------------

_kgl_large_heap_assert_threshold    maximum heap size before KGL raises an internal error        1572864000      1572864000 By anuj 

ww

set linesize 400 pagesize 300

col "Default Value" for a15 

col "Session Value" for a15 

col "Instance Value" for a15

col comment for a20 

col IS_SESSION_MODIFIABLE for a20

col IS_SYSTEM_MODIFIABLE for a20

col HOSTNAME for a40

col INSTANCE for a25

col Parameter  for a30

SELECT sys_context('userenv','host') hostname,sys_context('userenv', 'instance_name') instance, a.ksppinm "Parameter", 

b.KSPPSTDF "Default Value",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value",

to_char(decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE')) IS_SESSION_MODIFIABLE,

to_char(decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE')) IS_SYSTEM_MODIFIABLE,

c.KSPPSTCMNT "comment"

FROM x$ksppi a, x$ksppcv b,  x$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE '/_%&PARAMMETER%' escape '/'

/

==================

set lines 200 pages 100
col parameter          format a50
col description        format a80 word_wrapped
col "session value"    format a10
col "instance value"   format a10
select a.ksppinm    "parameter",
a.ksppdesc          "description",
b.ksppstvl          "session value",
c.ksppstvl          "instance value"
from x$ksppi a,x$ksppcv b,x$ksppsv c
where 1=1 
and a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '/_%' escape '/'
and a.ksppinm like '%optimizer_dsdir_usage_control%' ---- to search value 
order by a.ksppinm;

====

   set lines 300 pagesize 300 col name for a40 col value for a35 col description for a70 col sesmod for a15 col adjusted for a15 col sysmod for a15 col "Default" for a15 col modified for a15 SELECT NAME, VALUE, DECODE(isdefault, 'TRUE', 'Y', 'N') AS "Default", DECODE(isem, 'TRUE', 'Y', 'N') AS sesmod, DECODE(isym, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') AS sysmod, DECODE(imod, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') AS modified, DECODE(iadj, 'TRUE', 'Y', 'N') AS adjusted, description FROM ( --GV$SYSTEM_PARAMETER SELECT x.inst_id AS INSTANCE, x.indx + 1, ksppinm AS NAME, ksppity, ksppstvl AS VALUE, ksppstdf AS isdefault, DECODE(BITAND(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') AS isem, DECODE(BITAND(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE') AS isym, DECODE(BITAND(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') AS imod, DECODE(BITAND(ksppstvf, 2), 2, 'TRUE', 'FALSE') AS iadj, ksppdesc AS description FROM x$ksppi x, x$ksppsv y WHERE x.indx = y.indx AND SUBSTR(ksppinm, 1, 1) != '_' AND x.inst_id = USERENV('Instance')) ORDER BY NAME; 

or 

set lines 400 pagesize 300

col name for a40

col value for a35

col description for a70

col sesmod for a15

col adjusted for a15

col sysmod for a15

col "Default" for a15

col modified for a15

SELECT NAME,

       VALUE,

       DECODE(isdefault, 'TRUE', 'Y', 'N') AS "Default",

       DECODE(isem, 'TRUE', 'Y', 'N') AS sesmod,

       DECODE(isym, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') AS sysmod,

       DECODE(imod, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') AS modified,

       DECODE(iadj, 'TRUE', 'Y', 'N') AS adjusted,

       description

  FROM ( --GV$SYSTEM_PARAMETER

        SELECT x.inst_id AS INSTANCE,

                x.indx + 1,

                ksppinm AS NAME,

                ksppity,

                ksppstvl AS VALUE,

                ksppstdf AS isdefault,

                DECODE(BITAND(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') AS isem,

                DECODE(BITAND(ksppiflg / 65536, 3),

                       1,

                       'IMMEDIATE',

                       2,

                       'DEFERRED',

                       'FALSE') AS isym,

                DECODE(BITAND(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') AS imod,

                DECODE(BITAND(ksppstvf, 2), 2, 'TRUE', 'FALSE') AS iadj,

                ksppdesc AS description

            , y.KSPPSTCMNT "comment"

          FROM x$ksppi x, x$ksppsv y

         WHERE x.indx = y.indx

       --  AND SUBSTR(ksppinm, 1, 1) != '_'

           AND x.inst_id = USERENV('Instance')

AND x.ksppinm LIKE '/_%&PARAMMETER%' escape '/'

)

 ORDER BY NAME; 

======================================================

for hidden!!!!!!!!!!!!

   set lines 300 pagesize 300 col name for a40 col value for a35 col description for a70 col sesmod for a15 col adjusted for a15 col sysmod for a15 col "Default" for a15 col modified for a15 SELECT NAME, VALUE, DECODE(isdefault, 'TRUE', 'Y', 'N') AS "Default", DECODE(isem, 'TRUE', 'Y', 'N') AS sesmod, DECODE(isym, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') AS sysmod, DECODE(imod, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') AS modified, DECODE(iadj, 'TRUE', 'Y', 'N') AS adjusted, description FROM ( --GV$SYSTEM_PARAMETER SELECT x.inst_id AS INSTANCE, x.indx + 1, ksppinm AS NAME, ksppity, ksppstvl AS VALUE, ksppstdf AS isdefault, DECODE(BITAND(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') AS isem, DECODE(BITAND(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE') AS isym, DECODE(BITAND(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') AS imod, DECODE(BITAND(ksppstvf, 2), 2, 'TRUE', 'FALSE') AS iadj, ksppdesc AS description FROM x$ksppi x, x$ksppsv y WHERE x.indx = y.indx AND SUBSTR(ksppinm, 1, 1) = '_' AND x.inst_id = USERENV('Instance')) ORDER BY NAME; 

NAME                                     VALUE                               Default         SESMOD          SYSMOD          MODIFIED        ADJUSTED        DESCRIPTION
---------------------------------------- ----------------------------------- --------------- --------------- --------------- --------------- --------------- ----------------------------------------------------------------------
nls_sort                                                                     Y               Y               N               N               N               NLS linguistic definition name
nls_territory                            AMERICA                             N               Y               N               N               N               NLS territory name
n

=====

set linesize 500
col NAME for a40
col VALUE for a20
col DESCRIPTION for a70 

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '\_shared\_pool\_reserved\_min\_alloc%' escape '\'
order by name;

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '\_shared\_pool\_reserved\_min\_alloc%' escape '\'
order by name;

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '\_small\_table\_threshold%' escape '\'
order by name;

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '\_job\_queue\_interval%' escape '\'
order by name;

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '_sort_multiblock_read%' ;

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '%_bump_highwater_mark_count%';

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '%push_join_union_view%';

select a.ksppinm name,b.ksppstvl value,b.ksppstdf "default",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 '\_kks\_use\_mutex\_pin%' escape '\'
order by name;

Oracle Network tracing

a Network Configuration File: /us/oracle/product/10g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)

#NAMES.DEFAULT_DOMAIN = us.co.uk
#TRACE_LEVEL_CLIENT = SUPPORT
#TRACE_UNIQUE_CLIENT = on
#TRACE_DIRECTORY_CLIENT =/us/oracle/product/10g/network/admin
#TRACE_FILE_CLIENT = sqlnetanuj.log
#TRACE_TIMESTAMP_CLIENT = ON
#TRACE_LEVEL_LISTENER=16
#TRACE_TIMESTAMP_LISTENER=TRUE
#TRACE_DIRECTORY_LISTENER=/us/oracle/product/10g/network/admin
#TRACE_FILELEN_LISTENER=500000
#TRACE_FILENO_LISTENER=10

#TRACE_LEVEL_CLIENT=16
#TRACE_UNIQUE_CLIENT=TRUE
#TRACE_DIRECTORY_CLIENT=/tmp
#TRACE_FILE_CLIENT=client
#TRACE_TIMESTAMP_CLIENT=ON

Oracle on Unix to find top sql

oracle@apt-amd-02:~> cat top.sh

date
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|grep -v grep|cut -c1-15,42-79|sort -rn +2 | head -10 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
sqlplus -s "/ as sysdba" < set pages 0 lines 80 trims on echo off verify off pau off
set linesize 180
set pagesize 2000
select t.* from dba_hist_sqltext ht,table(dbms_xplan.display_awr(ht.sql_id, null, null,'ALL')) t
where ht.sql_id in ( select s.sql_id from v\$process p, v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date