通過這個SQL得到問題SQL的SQLID:
select sql_id,program,username,last_call_et? from? v$session? where? type !='BACKGROUND' and status='ACTIVE';
查看表的每個列的數(shù)據(jù)分布情況：
select? column_name,t.num_rows,c.NUM_NULLS,c.num_distinct?
from dba_tables t,dba_tab_columns c
where t.owner=c.owner and t.table_name=c.table_name and t.table_name='SIE_BOE_HEADER'
order by num_distinct;
找到執(zhí)行計劃的中哪一步是瓶頸：
select? count(*),sql_plan_line_id? from? v$active_session_history?
where? sql_id='6pkd06hdx99xk'?
group by sql_plan_line_id?
order by 2;
select * from table(dbms_xplan.DISPLAY_CURSOR('6pkd06hdx99xk',null,'ALLSTATS'));
select * from table(dbms_xplan.display_awr('6pkd06hdx99xk'));
select * from table(dbms_xplan.display_cursor('6pkd06hdx99xk',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
SELECT t.VALUE_STRING FROM v$sql_bind_capture t WHERE sql_id = '6pkd06hdx99xk';
SELECT snap_id,NAME,position,value_string,last_captured,WAS_CAPTURED
FROM dba_hist_sqlbind
WHERE sql_id = '6pkd06hdx99xk';
select count(*),event,count(distinct session_id)?
from v$active_session_history?
where sql_id='cxsajwa1td6vc'
group by event;
select? sql_id,sql_plan_line_id,sql_plan_hash_value,count(*)?
from? v$active_session_history
where sql_id='6pkd06hdx99xk'
group by sql_id,sql_plan_line_id,sql_plan_hash_value;
SELECT * FROM
(SELECT PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10;
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<=5;
col hevent format a40
col wevent format a40? ?
select s.sid blocker,s.event hevent,w.event wevent,w.sid blocked
from v$session s,v$session w
where w.blocking_session = s.sid
and w.blocking_session_status='VALID';
SELECT a.VALUE + b.VALUE logical_reads,c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c?
WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads';
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT? from v$session_wait
where? event not like 'SQL%' and event not like 'rdbms%';
