-----------------------------Cryking原创------------------------------
-----------------------转载请注明出处,谢谢!------------------------ 

1.查询AWR相关的视图名称

SELECT table_name  FROM dba_tables t WHERE table_name LIKE 'WRH$%'       AND NOT EXISTS (SELECT 'x'          FROM dba_tab_columns c         WHERE c.column_name = 'SNAP_ID'               AND c.table_name = t.table_name);

2. CPU耗时查询

with AASSTAT as (           select                 decode(n.wait_class,'User I/O','User I/O',                                     'Commit','Commit',                                     'Wait')                               CLASS,                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS,                 BEGIN_TIME ,                 END_TIME           from  v$waitclassmetric  m,                 v$system_wait_class n           where m.wait_class_id=n.wait_class_id             and n.wait_class != 'Idle'           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME          union             select 'CPU_ORA_CONSUMED'                                     CLASS,                    round(value/100,3)                                     AAS,                 BEGIN_TIME ,                 END_TIME             from v$sysmetric             where metric_name='CPU Usage Per Sec'               and group_id=2          union            select 'CPU_OS'                                                CLASS ,                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS,                 BEGIN_TIME ,                 END_TIME            from              ( select value busy, BEGIN_TIME,END_TIME from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter          union             select               'CPU_ORA_DEMAND'                                            CLASS,               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,               cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,               cast(max(SAMPLE_TIME) as date) END_TIME             from v$active_session_history ash              where SAMPLE_TIME >= (select BEGIN_TIME from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )               and SAMPLE_TIME < (select END_TIME from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 ))select       to_char(BEGIN_TIME,'HH:MI:SS') BEGIN_TIME,       to_char(END_TIME,'HH:MI:SS') END_TIME,       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +       CPU_ORA_CONSUMED +        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,       CPU_ORA_CONSUMED CPU_ORA,       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,       COMMIT,       READIO,       WAITfrom (select       min(BEGIN_TIME) BEGIN_TIME,       max(END_TIME) END_TIME,       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,       sum(decode(CLASS,'User I/O'        ,AAS,0)) READIO,       sum(decode(CLASS,'Wait'            ,AAS,0)) WAITfrom AASSTAT)

3.  等待事件信息

select     sid sw_sid,     CASE WHEN state != 'WAITING' THEN 'WORKING'         ELSE 'WAITING'    END AS state,     CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'         ELSE event    END AS sw_event,     seq#,     seconds_in_wait sec_in_wait,     CASE state WHEN 'WAITING' THEN NVL2(p1text,p1text||'= ',null)||CASE WHEN P1 < 536870912 THEN to_char(P1) ELSE '0x'||rawtohex(P1RAW) END ELSE null END SW_P1,    CASE state WHEN 'WAITING' THEN NVL2(p2text,p2text||'= ',null)||CASE WHEN P2 < 536870912 THEN to_char(P2) ELSE '0x'||rawtohex(P2RAW) END ELSE null END SW_P2,    CASE state WHEN 'WAITING' THEN NVL2(p3text,p3text||'= ',null)||CASE WHEN P3 < 536870912 THEN to_char(P3) ELSE '0x'||rawtohex(P3RAW) END ELSE null END SW_P3,    CASE state WHEN 'WAITING' THEN         CASE             WHEN event like 'cursor:%' THEN                '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))                    WHEN (event like 'enq%' OR event = 'DFS lock handle') AND state = 'WAITING' THEN                 '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||                chr(bitand(p1, -16777216)/16777215)||                chr(bitand(p1,16711680)/65535)||                ' mode '||bitand(p1, power(2,14)-1)            WHEN event like 'latch%' AND state = 'WAITING' THEN                   '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||(                        select name||'[par'                             from v$latch_parent                             where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))                        union all                        select name||'[c'||child#||']'                             from v$latch_children                             where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))                  )            WHEN event like 'library cache pin' THEN                  '0x'||RAWTOHEX(p1raw)        ELSE NULL END     ELSE NULL END AS sw_p1translFROM     v$session_wait ORDER BY    state,    sw_event,    p1,    p2,    p3;

4.  查询当前监听的连接信息

SELECT host_short || '.' || sid || '=    (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = ' || hostname || ')(PORT = ' || port || '))    (CONNECT_DATA =      http://www.mamicode.com/(SERVER = DEDICATED)>

 
 
更多的DBA脚本见一老外的BLOG: http://www.idevelopment.info/cgi/ORACLE_dba_scripts.cgi