查询进程正在执行的SQL

set linesize 250
set pagesize 200
col event format a25
col sid format 9999
col program format a30
col username format a10
col p2,p3 format 999999
alter session set nls_date_format='yyyymmdd hh24miss';
select a.sid,ername,MAND,event,p1,p2,p3,b.program ,
mand,
SQL_HASH_VALUE from v$session_wait a , v$session b
where a.sid=b.sid and event not like 'SQL%' and event not like 'rdbms ipc%'
and event not like 'pmon%' and event not like 'smon%' and event not like 'job%'
and event not like 'gcs%' and event not like 'ges%' and event not like 'gcs%'
and event not like 'PL/SQL lock timer'
--and a.sid=435
order by SQL_HASH_VALUE,event
/

杀进程
select 'kill -9 ' || spid from v$session_wait a , v$session b ,v$process c
where a.sid=b.sid and c.addr=b.paddr
--and event in ('buffer busy waits','db file scattered read')
and ERNAME in ( 'RESNJ','POINT_QRY')
and b.program like 'JDBC%'


select * from v$process where addr in ( select paddr from v$session where sid in (5811,1155,5528,5235,1013,5737,5474,5593));

NLS_DATE_FORMAT=YYYYMMDDhh24miss

alter session set nls_date_format='yyyymmdd hh24miss';
set linesize 250
set pagesize 200
col event format a30
col sid format 9999
col program format a30
col p2,p3 format 999999
select sid,event,p1raw,p2, program,sql_id ,COMMAND,username
from v$session b
where event not like 'SQL%' and event not like 'rdbms ipc%'
and event not like 'pmon%' and event not like 'smon%' and event not like 'job%'
and event not like 'gcs%' and event not like 'ges%'
and event not like 'Streams%' and event not like 'DIAG%'
--and a.sid=435
order by sql_id,event
/

select spid from v$process where addr=(select paddr from v$session where sid=&1)

select 'kill -9 '||spid from v$process where addr in (select paddr from v$session where event like 'enq: TX%')

select inst_id, sid,event, program,sql_id
from gv$session b
where event not like 'SQL%' and event not like 'rdbms ipc%'
and event not like 'pmon%' and event not like 'smon%' and event not like 'job%'
and event not like 'gcs%' and event not like 'ges%'
and event not like 'Streams%' and event not like 'DIAG%'
--and a.sid=435
order by 1
/

alter session set nls_date_format='yyyymmdd hh24miss';
set linesize 250
set pagesize 200
col event format a30
col sid format 9999
col program format a30
col p2,p3 format 999999
select SAMPLE_TIME,SESSION_ID ,EVENT ,SQL_ID from v$active_session_history where
event is not null and
SAMPLE_TIME>sysdate-1/(24*10)
/

set linesize 250
set pagesize 200
col event format a25
col sid format 9999
col program format a30
col p2,p3 format 999999
select a.inst_id,a.sid,event,p1,p1raw,p2,p2raw,p3,b.program ,mand,SQL_HASH_VALUE
from gv$session_wait a , gv$session b
where a.sid=b.sid and event not like 'SQL%' and event not like 'rdbms ipc%'
and event not like 'pmon%' and event not like 'smon%' and a.inst_id=b.inst_id
and event

not like 'gcs%' and event not like 'ges%'
order by SQL_HASH_VALUE,event
/

select a.event,b.program ,SQL_HASH_VALUE,Count(*) from v$session_wait a , v$session b
where a.sid=b.sid and event not like 'SQL%' and event not like 'rdbms ipc%'
and event not like 'pmon%' and event not like 'smon%'
--and a.sid=435
Group By a.event,b.program,SQL_HASH_VALUE
/

改变lgwr进程的优先级
rtsched -s SCHED_HPUX -p150 -P 9994

select 'alter system kill session '''||sid||','||SERIAL# ||''';' from v$session
where sid in (3336,3015,2496,3074 )

/opt/ignite/bin/print_manifest |more


col COLUMN_NAME format a20
select owner,table_name,NUM_ROWS,BLOCKS,TEMPORARY,PARTITIONED,LAST_ANALYZED from dba_tables where table_name='&tableName';
select INDEX_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='&tableName' and table_owner='';
select index_name,BLEVEL,NUM_ROWS,LEAF_BLOCKS,PARTITIONED from dba_indexes where table_name='&tableName';
select OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY from dba_tab_columns where table_name='&tableName';

在10g查看SQL及执行计划
Select sql_text from v$sqltext where sql_id='&h' order by piece ;
select a.* from table( dbms_xplan.display_cursor( '&sql_id',null,'ADVANCED') ) a ;
select * from table(dbms_xplan.display_awr('8ua7sc771vxn6') );

select *from table(dbms_xplan.display);
############
select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;
select * from table(xplan.display_cursor(null,null,’ALLSTATS LAST‘));
###################


col Operation for a30
col Options for a20
col "Object Name" for a20
col "Optimizer" for a15
select lpad(' ', 2*(level-1))||operation "Operation",
options "Options",
decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name",
substr(optimizer, 1, 6) "Optimizer"
from v$sql_plan a
start with sql_id = '&h'
and id = 0
connect by prior id = a.parent_id
and prior a.address = a.address
and prior a.sql_id = a.sql_id;

在9i查看SQL及执行计划

Select sql_text from v$sqltext where hash_value=(select sql_hash_value from v$session where sid=&1) order by piece ;



Select sql_text from v$sqltext where hash_value='&h' order by piece ;
col Operation for a60
col Options for a20
col "Object Name" for a50
col "Optimizer" for a15
select CHILD_NUMBER, lpad(' ', 2*(level-1))||operation "Operation",
options "Options",
decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name",
substr(optimizer, 1, 6) "Optimizer"
from ( select * from v$sql_plan where hash_value = '&1') a
start with id = 0
connect by prior id = a.parent_id
and prior a.address = a.address
and prior a.hash_value = a.hash_value
and prior a.CHILD_NUMBER = a.CHILD_NUMBER
order by CHILD_NUMBER;

查看表的基本情况
DE

FINE vTabName='&1'
col column_name format a30
select table_name,NUM_ROWS,BLOCKS,degree,LAST_ANALYZED,PARTITIONED from dba_tables where table_name='&vTabName';
select table_name,PARTITION_NAME,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_partitions where table_name='&vTabName';
select table_name,index_name,STATUS,DISTINCT_KEYS,LEAF_BLOCKS,LAST_ANALYZED,degree from dba_indexes where table_name='&vTabName';
select table_name,index_name,COLUMN_NAME from dba_ind_columns where table_name='&vTabName' order by COLUMN_POSITION;
select table_name,COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS from dba_tab_columns where table_name='&vTabName' ;



select spid ,PROGRAM from v$process where addr=(select paddr from v$session where sid=&1);
查看分析SYSSTAT
select b.sid,,b.value from v$sysstat a,v$sesstat b
where a.STATISTIC#=b.STATISTIC# and b.STATISTIC# between 114 and 125 and sid=...
order by 3
/


select ,a.value from v$sysstat a
where a.STATISTIC# between 114 and 125
order by
/


查找指定数据块

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(319604402) FILE#,DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(319604402) BLOCK#
from dual;

select DBARFIL,DBABLK,tch from x$bh where HLADDR='&1' and tch>10 order by tch;
select ,b.value from v$sysstat a ,v$sesstat b where a.STATISTIC#=b.STATISTIC# and b.sid=&1 order by 2;

select session_num,SQLHASH,sum(blocks) from v$sort_usage group by session_num,SQLHASH

SELECT * FROM v$session b
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid');



select *from dba_extents where file_id=&fileid and &blockid between block_id and block_id+blocks-1 ;

select ED_UBLK,ED_UREC,a.STATUS,b.status,b.program from v$session b ,v$transaction a where a.SES_ADDR=b.saddr order by 4
/

select *from v$session where paddr in ( select addr from v$process where spid in (9441,9443,9447,9451,9026,9131) );

SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = &pid ))
ORDER BY piece ASC
/
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid = &sid
)
ORDER BY piece ASC
/


select w.event,p.spid,w.p1,w.p2,w.p3,b.sid,b.sql_ha

sh_value
from v$session_wait w,v$session b ,v$process p
where w.sid=b.sid and b.paddr=p.addr and b.sid in ( select sid from v$latchholder);


col Operation for a30
col Options for a20
col "Object Name" for a20
col "Optimizer" for a15
select lpad(' ', 2*(level-1))||operation "Operation",
options "Options",
decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name",
substr(optimizer, 1, 6) "Optimizer"
from v$sql_plan a
start with hash_value = '&h'
and id = 0
connect by prior id = a.parent_id
and prior a.address = a.address
and prior a.hash_value = a.hash_value;

查看lock的情况
SELECT CHR(bitand(p1,-16777216)/16777215)||
CHR(bitand(p1, 16711680)/65535) "Lock",
TO_CHAR( bitand(p1,65535)) "Mode"
FROM v$session_wait
WHERE event = 'enqueue'


查看Hide 参数
col hidden_name format a50
col current_val format a30
col default_val format a30
select a.ksppinm hidden_name, b.ksppstvl current_val, b.KSPPSTDF default_val
from X$KSPPI a, x$ksppsv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
order by 1;


Select /*+rule*/sql_text,b.sid,ername, b.sql_hash_value From v$sqltext a,v$session b
Where --a.hash_value= DECODE(b.sql_hash_value, 0, b.prev_hash_value, b.sql_hash_value) and
a.address = DECODE (b.sql_hash_value, 0, b.prev_sql_addr, b.sql_address) and
b.sid In ( select sid from v$session_wait
where EVENT in ('db file sequential read','direct path read','direct path write','db file scattered read')
) and b.sid <> userenv('sessionid')
Order By b.sid,hash_value ,piece
/


Select /*+rule*/sql_text,b.sid,ername, b.sql_hash_value From v$sqltext a,v$session b
Where --a.hash_value= DECODE(b.sql_hash_value, 0, b.prev_hash_value, b.sql_hash_value) and
a.address = DECODE (b.sql_hash_value, 0, b.prev_sql_addr, b.sql_address) and
b.sid In ( select sid from v$session_wait
where EVENT ='db file scattered read'
)
Order By b.sid,hash_value ,piece
/


@?/rdbms/admin/utlxplan
explain plan for select * from cal.RT_STOP_DETAIL_2 WHERE SERV_ID=2380751 AND STOP_ID<=130912053
/
@?/rdbms/admin/utlxplp
/
#################高IO的SQL
set linesize 200
set long 50000
Select Sql_Text,Buffer_Gets, Buffer_Gets / Executions, Rows_Processed,Executions
From V$sql
Where Buffer_Gets / Executions > 100000 And Executions>0
Order By 2 Desc;
Select sql_text from v$sqltext where hash_value='&1' order by piece ;


解决library cache pin
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW'

SELECT distinct s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process",kglpnhdl
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event in ( 'library cache pin','library cache lock','library cache

load lock')
and s.paddr=o.addr
order by kglpnhdl,kglpnmod
/

解决library cache lock
select
kglnaobj, kgllkreq,KGLLKMOD,KGLLKSES
from
x$kgllk x join v$session s on
s.saddr = x.kgllkses and KGLLKHDL='C000000F8AE4D130'


select
waiter.sid waiter,
waiter.event wevent,
to_char(blocker_event.sid)||','||to_char(blocker_session.serial#) blocker,
substr(decode(blocker_event.wait_time,
0, blocker_event.event,
'ON CPU'),1,30) bevent
from
x$kglpn p,
gv$session blocker_session,
gv$session_wait waiter,
gv$session_wait blocker_event
where
p.kglpnuse=blocker_session.saddr
and p.kglpnhdl=waiter.p1raw
and waiter.event in ( 'library cache pin','library cache lock','library cache load lock')
and blocker_event.sid=blocker_session.sid
and waiter.sid != blocker_event.sid
order by
waiter.p1raw,waiter.sid;


namespace:
/* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
/* 8 = LOB, 9 = DIRECTORY, */
/* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
/* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */

x$kgllk
This table lists all held and requested library object locks for all sessions. It is more complete than v$lock.
The column kglnaobj displays the first 80 characters of the name of the object.
select
kglnaobj, kgllkreq
from
x$kgllk x join v$session s on
s.saddr = x.kgllkses and
kgllkreq = 0 means, the lock is held, while

Select Count(*),machine From v$session Group By machine Order By 1
/
Select a.sid,a.serial#,b.spid From v$session a ,v$process b Where a.paddr=b.addr And a.machine Like '%&1%'
/
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where machine Like '%&1%'
/
Select 'kill -9 '|| b.spid From v$session a ,v$process b Where a.paddr=b.addr And a.machine Like '%&1%'
/

获得pin S wait on X 的Blocking SID
pin S wait on X
Versions 10.2.0.1 - 11.2.0.2
P1 = idn
P2 = value
P3 = where (where|sleeps in 10.2)

SELECT sql_id, sql_text, version_count
FROM V$SQLAREA where HASH_VALUE=&IDN;



select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') HolderSID
from v$session
where event = 'cursor: pin S wait on X';



获得Library Cache Pin等待的对象

Select /*+ use_hash(x$kglob x$ksusecst x$Ksled ) */
Addr, Kglhdadr, Kglhdpar, Kglnaown, Kglnaobj, Kglnahsh, Kglhdobj
From X$kglob
Where Kglhdadr In (Select P1raw From V$session_Wait Where Event Like 'library%')


create view GV$ACCESS as select distinct s.inst_id,s.ksusenum,o.kglnaown,o.kglnaobj,
decode(o.kglobtyp, 0, 'CURSOR', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9,

'PACKAGE', 10,'NON-EXISTENT', 11,'PACKAGE BODY', 12,'TRIGGER', 13,'TYPE', 14,'TYPE BODY', 15,'OBJECT', 16,'USER', 17,'DBLINK', 18,'PIPE', 19,'TABLE PARTITION', 20,'INDEX PARTITION', 21,'LOB', 22,'LIBRARY', 23,'DIRECTORY', 24,'QUEUE', 25,'INDEX-ORGANIZED TABLE', 26,'REPLICATION OBJECT GROUP', 27,'REPLICATION PROPAGATOR', 28,'JAVA SOURCE', 29,'JAVA CLASS', 30,'JAVA RESOURCE', 31,'JAVA JAR','INVALID TYPE')
from x$ksuse s,x$kglob o,x$kgldp d,x$kgllk l where l.kgllkuse=s.addr and l.kgllkhdl=d.kglhdadr and l.kglnahsh=d.kglnahsh and o.kglnahsh=d.kglrf


.获得持有等待对象的session信息
Select a.Sid, ername, a.Program, b.Addr, b.Kglpnadr, b.Kglpnuse, b.Kglpnses, b.Kglpnhdl, b.Kglpnlck, b.Kglpnmod,
b.Kglpnreq
From V$session a, X$kglpn b
Where a.Saddr = b.Kglpnuse And b.Kglpnmod <> 0 And
b.Kglpnhdl In (Select P1raw From V$session_Wait Where Event Like 'library%')
.获得持有对象用户执行的代码
SELECT sql_text
FROM v$sqlarea
WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
SELECT sql_address, sql_hash_value
FROM v$session
WHERE SID IN (
SELECT SID
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')))

library cache lock
SELECT /*+ rule */KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE LOCK_B.KGLLKSES in ( select saddr from v$session where event like '%library cache lock%')
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)

获得持有对象用户的SID和正在执行的代码
SELECT a.sid,a.program,b.sql_text
FROM v$sqlarea a, v$session b
WHERE a.address=b.sql_address and a.hash_value= b.sql_hash_value
and b.saddr IN (
SELECT b.kglpnuse
FROM x$kglpn b
WHERE b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%'))

variable val_ROWID_TYPE NUMBER;
variable val_OBJECT_NUMBER NUMBER;
variable val_RELATIVE_FNO NUMBER;
variable val_BLOCK_NUMBER NUMBER;
variable val_ROW_NUMBER NUMBER;

begin
dbms_rowid.ROWID_INFO( 'AAAA9cAAPAAAt0dAAs',:val_ROWID_TYPE,:val_OBJECT_NUMBER,:val_RELATIVE_FNO,:val_BLOCK_NUMBER,:val_ROW_NUMBER);
end ;

表分析SQL
select 'exec dbms_stats.GATHER_TABLE_STATS(''DUNS'','''||table_name ||''',estimate_percent=>5,degree=>3 );'
from dba_tables where owner='DUNS'
and ( LAST_A

NALYZED is null or LAST_ANALYZED < to_date('2005-09-11','YYYY-MM-DD') )


select 'exec dbms_stats.GATHER_INDEX_STATS(''DUNS'','''||index_name ||''',estimate_percent=>10 );'
from dba_indexes where owner='DUNS'
and ( LAST_ANALYZED is null or LAST_ANALYZED < to_date('2005-09-11','YYYY-MM-DD') )



lib cache lock
SELECT * FROM X$KGLLK LOCK_A WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'C0000003D22964C0' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);

jre -DTRACING.ENABLED=true -DTRACING.LEVEL=2 -DPROGRAM=gsd -classpath /oracle/9.2/jlib/netcfg.jar:/oracle/9.2/jlib/srvm.jar oracle.ops.mgmt.daemon.OPSMDaemon /oracle/9.2


_system_trig_enabled=FALSE

查找锁等待

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
;

HW高水位锁
In v$lock Id1 is the tablespace number in which the segment (that is having its high water mark changed) resides and ID2 is the tablespace relative DBA of the segment header.

To identify the segment on which HW enqueue wait is being observed, run the following:



select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
from v$lock
where type = ‘HW’;


select *from v$open_cursor where sid=:holder;

col sess format a12
select /*+ NO_MERGE(a) */ a.*,b.sql_id,b.event,ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from
( SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, sid,
lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
) a ,v$session b
where a.sid=b.sid


select name,type#,tch from obj$ , x$bh where obj#=obj and hladdr='&1' order by tch;

查找外键引用
select *from dba_constraints a where a.r_constraint_name
in ( select b.constraint_name from dba_constraints b
where b.table_name=upper('&Parent_table') and b.constraint_type in ('P','U')
)


latch 12、13


回收临时段

alter session set events 'immediate trace name DROP_SEGMENTS level 2147483647 '


在Dict方式下
SYS.FET$ and SYS.UET$
在local方式下
SYS.X$KTFBFE and SYS.X$KTFBUE


查看Shared_pool情况
SELECT a.ksmchcom, SUM (a.CHUNK) CHUNK, SUM (a.recr) recr, SUM (a.freeabl) freeabl,
SUM (a.SUM) SUM
FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
SUM (ksmchsiz) SUM
FROM x$ksmsp GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom;

select '| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop |' as "Plan Tabl
e" from dual
union all
select '------------------------------------------------------------------------------------------------------------' from dual
union all
select * from
(select /*+ no_merge */
rpad('| '||substr(lpad(' ',1*(level-1))||operation||
decode(options, null,'',' '||options), 1, 28), 29, ' ')||'|'||
rpad(substr(object_name||' ',1,19), 20, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(object_node,null,' ',
substr(object_node,length(object_node)-3,1) || ',' ||
substr(object_node,length(object_node)-1,2))||' ', 6, ' ') || '|' ||
lpad(decode(other_tag, null,' ',
decode(other_tag,'PARALLEL_TO_SERIAL', ' P->S',
decode(other_tag, 'PARALLEL_TO_PARALLEL', ' P->P',
decode(other_tag, 'PARALLEL_COMBINED_WITH_PARENT', ' PCWP',
decode(other_tag, 'PARALLEL_FROM_SERIAL', ' S->P',
decode(other_tag, 'PARALLEL_COMBINED_WITH_CHILD', ' PCWC',
decode(other_tag,null,' ',other_tag)))))))||' ', 6, ' ') || '|' ||
rpad(' '||decode(distribution, null,' ',
decode(distribution, 'PARTITION (ROWID)', 'PART (RID)',
decode(distribution, 'PARTITION (KEY)', 'PART (KEY)',
decode(distribution, 'ROUND-ROBIN', 'RND-ROBIN',
decode(distribution, 'BROADCAST', 'BROADCAST', distribution))))), 12, ' ') || '|' ||
lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
decode(partition_start, 'KEY', 'KEY', decode(partition_start,
'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
'NUMBER', substr(substr(partition_start, 8, 10), 1,
length(substr(partition_start, 8, 10))-1),
decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||
lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',

decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
'NUMBER', substr(substr(partition_stop, 8, 10), 1,
length(substr(partition_stop, 8, 10))-1),
decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|' as "Explain plan"
from plan_table
start with id=0 and timestamp = (select max(timestamp) from plan_table
where id=0)
connect by prior id = parent_id
and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
and prior timestamp <= timestamp
order by id, position)
/


select * from table(dbms_xplan.display());

在9i中显示执行计划
col OPERATION format a25
col object_name format a20
col OPTIONS format 99

SELECT id
, lpad (' ', depth) || operation operation
, options , object_name --, optimizer , cost
FROM V$SQL_PLAN
WHERE hash_value = &hash_value
START WITH id = 0
CONNECT BY
( prior id = parent_id
AND prior hash_value = hash_value
AND prior child_number = child_number
)
ORDER SIBLINGS BY id, position;


ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true
exec dbms_transaction.PURGE_LOST_DB_ENTRY('1.61.342632');

查看死事务
select KTUXECFL,count(*) from x$ktuxe group by KTUXECFL

select sysdate, ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD';

加快串行恢复速度
_CLEANUP_ROLLBACK_ENTRIES

查看全局事务,Dblink状态的语句
SELECT /*+ ORDERED */
S.KSUSEMNM "ORIGIN",
S.KSUUDLNA USERNAME,
S.KSUSEPNM,
S.INDX SID,
S.KSUSESER SERIAL#,
P.KSUPRPID SPID,
G.K2GTITID_ORA "GTXID",
DECODE(BITAND(KSUSEIDL, 11),
1,
'ACTIVE',
0,
DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
2,
'SNIPED',
3,
'SNIPED',
'KILLED') "STATUS",
EVENT "WAITING"
FROM X$K2GTE G, X$KTCXB T, X$KSUSE S, V$SESSION_WAIT W, X$KSUPR P
WHERE G.K2GTDXCB = T.KTCXBXBA
AND G.K2GTDSES = T.KTCXBSES
AND S.ADDR = G.K2GTDSES
AND W.SID = S.INDX
AND S.KSUSEPRO = P.ADDR
-- AND SUBSTR(G.K2GTITID_ORA, 1, 35) = 'XREP.1e55ca62.77.4.729'
-- AND W.EVENT='SQL*NET MESSAGE FROM DBLINK'
ORDER BY WAITING, STA

ps -ef|grep ora|grep $ORACLE_SID| grep LOCAL=NO | grep -v grep|awk '{print $2}'|xargs -i kill -9 {}
ps -ef|grep ora|grep $ORACLE_SID| grep j00 | grep -v grep|awk '{print $2}'|xargs -i kill -9 {}



folderPath=`ls /oracle/9i/*.sql 2>>/dev/null`
for fileName in $folderPath
do
cnt=`cat $fileName|grep 'DATABASE' |wc |awk '{print $1}' `
if [ $cnt -ne 0 ]
then
echo $fileName
fi
done

"_smu_debug_mode=1024" disable SMON TX Recovery
"_smu_debug_mode" = 4; 在AUM进行手动UNDO管理
event = ‘10513 trace name context forever,level 2′ 禁用smon恢复


alter sessio

n set "_smu_debug_mode" = 4;
execute dbms_transaction.purge_lost_db_entry('');
select 'exec dbms_transaction.purge_lost_db_entry('''||LOCAL_TRAN_ID||''');'||chr(10)||'commit;' from dba_2pc_pending
select 'rollback force '''||LOCAL_TRAN_ID||''';' from dba_2pc_pending


alter session set "_smu_debug_mode" = 4;
declare
vloc varchar2(256);
BEGIN
for c1 IN
(
select LOCAL_TRAN_ID from dba_2pc_pending
)
LOOP
begin
dbms_transaction.purge_lost_db_entry(c1.LOCAL_TRAN_ID);
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put( 'Error:'||c1.LOCAL_TRAN_ID );
end;
commit;
end loop;

END;
/


检查没有使用的索引
select owner,index_name from dba_indexes where owner in ('BILL')
minus
select distinct a.object_owner, a.object_name
from v$sql_plan a, dba_objects b
where a.OBJECT# = b.object_Id
and b.object_type='INDEX'
and a.object_owner in ('BILL')



SELECT Distinct sid using_sid,
s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",kglnaown "Owner", kglnaobj "using_Object"
FROM x$kglpn p, v$session s,x$kglob x
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=kglhdadr
And p.KGLPNUSE = s.saddr
And kglpnreq=0
And upper(kglnaobj) = upper('&obj');

select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH)
;


subpool _KGHDSIDX_COUNT
select * from x$ksmss


SharedPool bucket Free
select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From", count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp where KSMCHSIZ<140 and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize",

trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp where KSMCHSIZ between 140 and 267 and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX,
50*trunc(KSMCHSIZ/50) , count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ))
"Total"
from x$ksmsp where KSMCHSIZ between 268 and 523 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX,
50*trunc(KSMCHSIZ/50)
UNION ALL select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) , count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp where KSMCHSIZ >= 4108 and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);

select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='CCARD_PLAN';


netstat -an |grep "132.239.10.11.1649"| grep "132.228.97.156" |sort -k 5,5 |wc

利用Flash query，查找表drop或truncate前，在系统中的信息
select *from obj$ as of timestamp to_date('20100426 173604','YYYYMMDD HH24MISS') where name='...'
select *from tab$ as of timestamp to_date('20100426 173604','YYYYMMDD HH24MISS') where obj#=75239;



alter session set events 'immediate trace name flush_cache level 1 ';



col owner format a10
col COLUMN_NAME format a30
col object_name format a30
select a.owner,a.object_name,b.COLUMN_NAME,count(1) from csm$errors c,dba_objects a ,dba_tab_columns b
where a.object_id=c.obj# and a.owner=b.owner and a.object_name=b.table_name and b.column_id=c.col#
group by a.owner,a.object_name,b.COLUMN_NAME
order by 1,2,3;


DBMS_SQLTUNE包使用
var my_recomm CLOB;
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id => '4yhu8tk48mbxa' );
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => my_task_name );
select DBMS_SQLTUNE.REPORT_TUNING_TASK( my_task_name ) into :my_recomm from dual;
END;
/

print :my_recomm



RAC环境UNDO使用情况监控
select a.inst_id,trunc(activeblks/totalblks*100,2) "Active%",trunc(unexpiredblks/totalblks*100,2) "Unexpire%"
from
(select inst_id, sum(blocks) as totalblks from dba_data_files, gv$parameter
where tablespace_name = value and name='undo_tablespace' group by inst_id) a ,
(select distinct inst_id,activeblks,unexpiredblks
from gv$undostat where end_time >(sysdate-2/(60*24))) b
where a.inst_id=b.inst_id




exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true,method_opt => 'for columns deptno size 10');
通过set_column_stat清除HISTOGRAM
declare
l_ndv number;
l_dummy

number;
l_srec dbms_stats.statrec;

begin
for r in (select owner, table_name, column_name, null partition_name from DBA_TAB_COL_STATISTICS
where table_name='BALANCE_SOURCE' and column_name='PAYMENT_ID'
union all
select owner, table_name, column_name, partition_name from DBA_PART_COL_STATISTICS where
table_name='BALANCE_SOURCE' and column_name='PAYMENT_ID' ) loop
dbms_stats.get_column_stats (
ownname => r.owner,
tabname => r.table_name,
colname => r.column_name,
partname => r.partition_name,
stattab => null,
statid => null,
distcnt => l_ndv,
density => l_dummy,
nullcnt => l_dummy,
srec => l_srec,
avgclen => l_dummy);
dbms_stats.set_column_stats (
ownname => r.owner,
tabname => r.table_name,
colname => r.column_name,
partname => r.partition_name,
distcnt => l_ndv);

end loop;
end;



exec dbms_stats.set_param(pname => 'METHOD_OPT',pval => 'FOR ALL INDEXED COLUMNS SIZE repeat');



UNDO使用追踪
select n,,s.extents,s.XACTS,s.CURBLK,s.status from v$rollstat s, v$rollname n
where n=n;
select SES_ADDR, xidusn,xidslot,xidsqn,USED_UBLK,UBAFIL from v$transaction order by UBABLK;
select t.tablespace_name,t.status,sum(t.bytes)/1024/1024/1024 from dba_undo_extents t
group by t.tablespace_name, t.status order by 1,2;




select a.JOB_NAME,b.sid from DBA_DATAPUMP_SESSIONS a ,v$session b where a.SADDR=b.SADDR;




设置时间列上的min max
DECLARE
vdistcnt NUMBER;
vdensity NUMBER;
vnullcnt NUMBER;
vsrec DBMS_STATS.StatRec;
vavgclen NUMBER;
vNOVALS DBMS_STATS.DATEARRAY;
BEGIN
DBMS_STATS.GET_COLUMN_STATS ('SZACCT','PAYMENT','CREATED_DATE',distcnt=>vdistcnt,density=>vdensity,nullcnt=>vnullcnt,srec=>vsrec,avgclen=>vavgclen);
dbms_output.put_line(vsrec.minval );
dbms_output.put_line(vsrec.maxval );
dbms_output.put_line(vdistcnt );
dbms_output.put_line(vdensity );
vNOVALS :=DBMS_STATS.DATEARRAY(sysdate-30000,sysdate+30000);
DBMS_STATS.PREPARE_COLUMN_VALUES(vsrec,vNOVALS);
dbms_output.put_line(vsrec.minval );
dbms_output.put_line(vsrec.maxval );
-- DBMS_STATS.set_COLUMN_STATS ('SZACCT','PAYMENT','CREATED_DATE',distcnt=>vdistcnt,density=>vdensity,nullcnt=>vnullcnt,srec=>vsrec,avgclen=>vavgclen);
end;




nodenum：定义每个session的序列号
sid：session的sid
sess_srno：session的Serial＃
ospid：OS的进程ID
state：node的状态
adjlist：表示blocker node
predecessor：表示waiter node



State有如下几种状态：

（1）IN_HANG：如果Session处于这种状态，表示

Session遇到deadlock或者处于hung状态。
（2）LEAF/LEAF_NW：这些Session通常是“blocker”或者是等待某些资源的“slow” node，通过字段“predecessor” 可以很容易标识出这些node。
（3）NLEAF：这些Session通常被认为是“stuck”会话，意味着这些Session在等待某些Session的资源。通过字段“adjlist”可以很容易的定义该进程的blocker。
（4）IGN/IGN_DMP：这些Session通常是IDLE Session。


查找被KILL的session
SELECT s.sid,ername,s.status,
x.ADDR
FROM x$ksupr x,v$session s
WHERE s.paddr(+)=x.addr
and bitand(ksspaflg,1)!=0
and s.status='KILLED'


检查UNDO offline的segment数
SELECT b.STATUS, SUM(BYTES), COUNT( distinct b.SEGMENT_NAME)
FROM DBA_UNDO_EXTENTS a,dba_rollback_segs b
where a.SEGMENT_NAME=b.SEGMENT_NAME
GROUP BY b.STATUS;




26 --打开enqueue lock的trace跟踪.
alter session set events '10704 trace name context forever,level 10';



Event 38003，用来重建非bootstrap对象（核心(OBJ<=56)BOOTSTRAP$对象 ）
alter system set EVENT="38003 trace name context forever, level 10" 2 SCOPE=SPFILE;


set long 20000
var task varchar2(64)
exec :task:= dbms_sqltune.CREATE_TUNING_TASK( sql_id=>'4yhu8tk48mbxa') ;
exec dbms_sqltune.EXECUTE_TUNING_TASK(:task)
select dbms_sqltune.REPORT_TUNING_TASK(:task) from dual;

select dbms_sqltune.REPORT_TUNING_TASK(task_name ) from DBA_ADVISOR_TASKS where advisor_name='SQL Tuning Advisor'

select executions,users_executing,trunc(buffer_gets/executions,0),rows_processed,sql_profile from v$sql where sql_id='&1'


从shared_pool中purge SQL
select address,hash_value,executions,parse_calls from v$sql where sql_id='&1';
alter session set events '5614566 trace name context forever';
exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');




select PLAN_HASH_VALUE,count(*) from v$sql group by PLAN_HASH_VALUE having count(*) >100 order by 2


exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;



_memory_imm_mode_without_autosga=false



定位latch持有者及location
select /*+ ORDERED USE_NL(l.x$ksuprlat) NO_TRANSFORM_DISTINCT_AGG */
l.pid, l.sid, , DDR, l.object, l.hmode, w.func, w.objtype
from (SELECT ksuprpid PID,
ksuprsid SID,
ksuprlnm NAME,
ksuprlat LADDR,
ksulawhr,
TO_CHAR(ksulawhy, 'XXXXXXXXXXXXXXXX') object,
ksulagts GETS,
lower(ksuprlmd) HMODE
FROM x$ksuprlat) l,
(SELECT indx, ksllwnam func, ksllwlbl objtype, ksllwlbl
FROM x$ksllw) w
WHERE l.sid LIKE '%'
AND l.ksulawhr = w.indx(+);


alter system set "_library_cache_advice"=false;



获取当前trace文件名称
select
a.value || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file_nam

e
from (select value from v$parameter where name = 'user_dump_dest')a,
(select substr(value,-6,1) symbol from v$parameter where name = 'user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr = p.addr and s.sid = m.sid and m.statistic#=0) d


select 'execute dbms_stats.gather_table_stats('''||owner||''','''||segment_name||''', estimate_percent=> 30,degree=>32,cascade=>true);' from dba_segments where owner='INST' and segment_name like 'OFFER%' and segment_name in (
select table_name from dba_tables where owner='INST' and last_analyzed is null ) order by blocks



select plan_hash_value,count(*) as cntSQL from v$sql group by plan_hash_value having count(*)>100 order by cntSQL;

select SQL_TEXT from v$sql where plan_hash_value='&1' and rownum<10;


Disable object statistics either via setting STATISTICS_LEVEL=BASIC or by setting "_object_statistics"=false



Clean index online rebuild失败
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 1332448;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
COMMIT;
END;
/

增量Checkpoint
select CPLRBA_SEQ , CPLRBA_BNO ,CPODR_SEQ ,CPODR_BNO from X$KCCCP where INDX<2;


ALTER DATABASE SET TIME_ZONE = '+8:00';
select dbtimezone from dual;
SELECT SESSIONTIMEZONE FROM DUAL;


本机向外DBLink访问的session
SELECT /*+ ORDERED */
S.KSUSEMNM "O_HOSTNAME",
S.KSUSEPID "O_SPID", --操作dblink用户信息
G.K2GTITID_ORA "O_TXID",
S.INDX "S_SID",
S.KSUSESER "S_SERIAL#", --dblink session信息
DECODE(BITAND(KSUSEIDL, 11),
1,
'ACTIVE',
0,
DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'),
2,
'SNIPED',
3,
'SNIPED',
'KILLED') "S_STATUS",
S.KSUUDNAM "DBLINK_USER"
FROM SYS.X$K2GTE G, SYS.X$KTCXB T, SYS.X$KSUSE S
WHERE G.K2GTDXCB = T.KTCXBXBA
AND G.K2GTDSES = T.KTCXBSES
AND S.ADDR = G.K2GTDSES;

11g要disable的自动任务
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);



CRS服务UNKNOWN
$ORACLE_HOME/log/sjzzw301/racg及子目录的权限不对导致