Search This Blog

Total Pageviews

Thursday 3 November 2011

Oracle Hidden Parameters

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

Oracle DBA

anuj blog Archive