概述

简单来说，Oracle Performance Tuning 或 Oracle Performance Tuning是一种以尽可能快地运行和简化数据库性能的方式改进 SQL 查询的实践。无论 SQL 语句多么复杂，Oracle 性能调优都能简化流程并提高响应时间。目标是实现具体的、可衡量的和可实现的目标。

Oracle 中的性能调优是通过找到重要的瓶颈并通过适当的更改来解决它们以减少性能下降来完成的。

一、使用方法

Current Queries: - Simple run the @scriptname.sql

AWR/Statspack: - Run the @Scriptname.sql instnumber beginsnapid endsnapid

ASH: Run the @scriptname.sql and input the number of minutes you would like to see (2 hours data you can get)

DASH: Run the @scriptname.sql instnumber beginsnapid endsnapid

二、General Scripts for Troubleshooting 概览

诊断类型/诊断时间段	CURRENT (Non-ASH)	PAST AWR	PAST STATSPACK	ASH or DASH	Custom_ASH (those who don't have ASH)
High CPU	Current_CPU_Queries.sql	AWR_Top_CPU_Queries.sql	Statspack_CPU_Queries.sql		custom_dash_top_sessions.sql
High I/O
High Waits	current_top_waits.sql	awr_top_waits.sql	statspack_top_waits.sql
Wait by SOL					custom_ash_waits_by_sql.sql
Wait by SID					custom_ash_waits_by_sid.sql
PGA	current_pga.sql current_pga_by_sid.sql	awr_pga.sql	statspack_pga.sql	ash_pga_by_sid.sql	NA
Temp	current_temp.sql current_temp_by_sid.sql	awr_temp.sql	statspack_temp.sql	ash_temp_by_sid.sql	NA
Undo
Redo
Locks	Current_Lock.sql	<2HoursLocks.sql	>2hoursLocks.sql
Latches
Expensive Queries
Response Time Analysis		awr_response_time_analysis

2.1. High CPU样例展示

2.1.1. Current_CPU_Queries.sql

col USERNAME for a10
col machine for a30
col Text for a50 wrap on

select 
se.SID, ss.serial#,
 ss.username, 
 ss.machine,
 ss.sql_id,
 to_char(s.last_active_time,'DD-MON-YY HH:MI:SS'),
 s.last_load_time, 
 VALUE/100 cpu_secs ,
 substr(s.sql_text,1,50) Text
from 
 v$session ss, 
 v$sesstat se, 
 v$statname sn,
 v$sql s
where 
 se.STATISTIC# = sn.STATISTIC# 
and 
 NAME like '%CPU used by this session%' 
and 
 se.SID = ss.SID 
--and 
 --ss.status='ACTIVE' 
--and 
-- ss.username is not null  and ss.username!='DBMANAGER'
and 
 ss.sql_id=s.sql_id
order by VALUE desc;

2.1.2. AWR_Top_CPU_Queries.sql


set termout off feedback off verify off linesize 200

define v_inst_number="&1"
define v_begin_snap_id="&2"
define v_end_snap_id="&3"

set termout on serveroutput on size 1000000 format wrapped

DECLARE
    v_low  NUMBER;
    v_high NUMBER;
    v_end_t   VARCHAR2(30);   
    v_output  VARCHAR2(200);    
    type srec is record (
    sql_id VARCHAR2(13),
    plan_hash_value NUMBER,
    elapsed_seconds NUMBER,
    cpu_seconds NUMBER,
    rows_processed NUMBER,
    buffer_gets NUMBER,
    disk_reads NUMBER,
    executions NUMBER,
    parses NUMBER );
    type stab is table of srec index by pls_integer;
    v_stab stab;
    
    CURSOR c1 (inst_number NUMBER, begin_snap_id NUMBER, end_snap_id NUMBER) IS
    WITH sql_stats_delta AS
    ( SELECT SNAP_ID, SQL_ID, PLAN_HASH_VALUE,
        ELAPSED_TIME_DELTA,
        CPU_TIME_DELTA,
        ROWS_PROCESSED_DELTA,
        BUFFER_GETS_DELTA,
        DISK_READS_DELTA,
        EXECUTIONS_DELTA,
        PARSE_CALLS_DELTA
      FROM  DBA_HIST_SQLSTAT
      WHERE INSTANCE_NUMBER=inst_number AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id  
    ),
    sql_stats_delta_rollup AS
    ( SELECT sql_id, plan_hash_value,
        ROUND(SUM(elapsed_time_delta) /1000000)                 elapsed_seconds,
        ROUND(SUM(cpu_time_delta) /1000000)                     cpu_seconds,
        DENSE_RANK() OVER (ORDER BY SUM(cpu_time_delta) DESC)   rank_by_cpu_seconds,
        SUM(rows_processed_delta)                               rows_processed,
        SUM(buffer_gets_delta)                                  buffer_gets,
        SUM(disk_reads_delta)                                   disk_reads,
        SUM(executions_delta)                                   executions,
        SUM(parse_calls_delta)                                  parses      
      FROM  sql_stats_delta
      GROUP BY sql_id,plan_hash_value
    )
    SELECT sql_id,plan_hash_value,elapsed_seconds,cpu_seconds,rows_processed,buffer_gets,disk_reads,executions,parses
    FROM   sql_stats_delta_rollup
    WHERE  rank_by_cpu_seconds <= 10
    ORDER BY rank_by_cpu_seconds;
BEGIN
    FOR i IN &v_begin_snap_id...&v_end_snap_id LOOP
    v_low:=i;
    v_high:=i+1;
    IF v_high > &v_end_snap_id THEN
        EXIT;
    END IF;
    
        DBMS_OUTPUT.PUT_LINE('');
    SELECT TO_CHAR(END_INTERVAL_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t
    FROM DBA_HIST_SNAPSHOT
    WHERE INSTANCE_NUMBER=&v_inst_number AND SNAP_ID=v_high;
    DBMS_OUTPUT.PUT_LINE('Begin snapshot id='||v_low||' End snapshot id='||v_high||' End snapshot time='||v_end_t);
    
    IF c1%isopen THEN
        CLOSE c1;
    END IF;
    OPEN c1(&v_inst_number, v_low, v_high); 
    FETCH c1 bulk collect into v_stab;

    v_output:='';
    v_output:=v_output||lpad('Elapsed', 43,' ');
    v_output:=v_output||lpad('CPU', 10,' ');
    DBMS_OUTPUT.PUT_LINE(v_output);
    
    v_output:='';
    v_output:=v_output||rpad('Sql Id',15,' ');
    v_output:=v_output||lpad('Plan Hash Value',16,' ');
    v_output:=v_output||lpad('Seconds',12,' ');
    v_output:=v_output||lpad('Seconds', 12,' ');
    v_output:=v_output||lpad('Rows',13,' ');
    v_output:=v_output||lpad('Buffer Gets',15,' ');
    v_output:=v_output||lpad('Disk Reads',15,' ');
    v_output:=v_output||lpad('Executions', 12,' ');
    v_output:=v_output||lpad('Parses', 12,' ');
    DBMS_OUTPUT.PUT_LINE(v_output);
    DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));
    
    FOR j IN 1..v_stab.count LOOP
        v_output:='';
        v_output:=v_output||rpad(v_stab(j).sql_id, 15,' ');
        v_output:=v_output||to_char(v_stab(j).plan_hash_value, 999999999999);
        v_output:=v_output||to_char(v_stab(j).elapsed_seconds ,'999,999,999');
        v_output:=v_output||to_char(v_stab(j).cpu_seconds ,    '999,999,999');
        v_output:=v_output||to_char(v_stab(j).rows_processed , '999,999,999,999');
        v_output:=v_output||to_char(v_stab(j).buffer_gets ,    '999,999,999,999');
        v_output:=v_output||to_char(v_stab(j).disk_reads ,     '999,999,999,999');
        v_output:=v_output||to_char(v_stab(j).executions ,     '999,999,999');
        v_output:=v_output||to_char(v_stab(j).parses ,         '999,999,999');
            DBMS_OUTPUT.PUT_LINE(v_output);
    END LOOP;   
    
    IF c1%isopen THEN
        CLOSE c1;
    END IF;
    END LOOP;
END;
/

2.1.3. Statspack_CPU_Queries.sql

set termout off feedback off verify off linesize 200

define v_inst_number="&1"
define v_begin_snap_id="&2"
define v_end_snap_id="&3"

set termout on serveroutput on size 1000000 format wrapped

DECLARE
    v_low  NUMBER;
    v_high NUMBER;
    v_end_t   VARCHAR2(30);
    v_output  VARCHAR2(200);
    type srec is record (
        sql_id VARCHAR2(13),
        hash_value NUMBER,
        elapsed_seconds NUMBER,
        cpu_seconds NUMBER,
        rows_processed NUMBER,
        buffer_gets NUMBER,
        disk_reads NUMBER,
        executions NUMBER,
        parses NUMBER );
    type stab is table of srec index by pls_integer;
    v_stab stab;

    CURSOR c1 (inst_number NUMBER, begin_snap_id NUMBER, end_snap_id NUMBER) IS
        WITH sql_stats AS
        ( SELECT SNAP_ID, SQL_ID, HASH_VALUE,
                ELAPSED_TIME,
                CPU_TIME,
                ROWS_PROCESSED,
                BUFFER_GETS,
                DISK_READS,
                EXECUTIONS,
                PARSE_CALLS
          FROM  STATS$SQL_SUMMARY
          WHERE INSTANCE_NUMBER=inst_number AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id
        ),
        sql_stats_rollup AS
        ( SELECT sql_id, hash_value,
                ROUND(SUM(elapsed_time) /1000000)                 elapsed_seconds,
                ROUND(SUM(cpu_time) /1000000)                     cpu_seconds,
                DENSE_RANK() OVER (ORDER BY SUM(cpu_time) DESC)   rank_by_cpu_seconds,
                SUM(rows_processed)                               rows_processed,
                SUM(buffer_gets)                                  buffer_gets,
                SUM(disk_reads)                                   disk_reads,
                SUM(executions)                                   executions,
                SUM(parse_calls)                                  parses
          FROM  sql_stats
          GROUP BY sql_id,hash_value
        )
        SELECT sql_id,hash_value,elapsed_seconds,cpu_seconds,rows_processed,buffer_gets,disk_reads,executions,parses
        FROM   sql_stats_rollup
        WHERE  rank_by_cpu_seconds <= 10
        ORDER BY rank_by_cpu_seconds;
BEGIN
    FOR i IN &v_begin_snap_id...&v_end_snap_id LOOP
        v_low:=i;
        v_high:=i+1;
        IF v_high > &v_end_snap_id THEN
                EXIT;
        END IF;

        DBMS_OUTPUT.PUT_LINE('');
        SELECT TO_CHAR(SNAP_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t
        FROM STATS$SNAPSHOT
        WHERE INSTANCE_NUMBER=&v_inst_number AND SNAP_ID=v_high;
        DBMS_OUTPUT.PUT_LINE('Begin snapshot id='||v_low||' End snapshot id='||v_high||' End snapshot time='||v_end_t);

        IF c1%isopen THEN
                CLOSE c1;
        END IF;
        OPEN c1(&v_inst_number, v_low, v_high);
        FETCH c1 bulk collect into v_stab;

        v_output:='';
        v_output:=v_output||lpad('Elapsed', 43,' ');
        v_output:=v_output||lpad('CPU', 10,' ');
        DBMS_OUTPUT.PUT_LINE(v_output);

        v_output:='';
        v_output:=v_output||rpad('Sql Id',15,' ');
        v_output:=v_output||lpad('Plan Hash Value',16,' ');
        v_output:=v_output||lpad('Seconds',12,' ');
        v_output:=v_output||lpad('Seconds', 12,' ');
        v_output:=v_output||lpad('Rows',13,' ');
        v_output:=v_output||lpad('Buffer Gets',15,' ');
        v_output:=v_output||lpad('Disk Reads',15,' ');
        v_output:=v_output||lpad('Executions', 12,' ');
        v_output:=v_output||lpad('Parses', 12,' ');
        DBMS_OUTPUT.PUT_LINE(v_output);
        DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));

        FOR j IN 1..v_stab.count LOOP
            v_output:='';
            v_output:=v_output||rpad(v_stab(j).sql_id, 15,' ');
            v_output:=v_output||to_char(v_stab(j).hash_value, 999999999999);
            v_output:=v_output||to_char(v_stab(j).elapsed_seconds ,'999,999,999');
            v_output:=v_output||to_char(v_stab(j).cpu_seconds ,    '999,999,999');
            v_output:=v_output||to_char(v_stab(j).rows_processed , '999,999,999,999');
            v_output:=v_output||to_char(v_stab(j).buffer_gets ,    '999,999,999,999');
            v_output:=v_output||to_char(v_stab(j).disk_reads ,     '999,999,999,999');
            v_output:=v_output||to_char(v_stab(j).executions ,     '999,999,999');
            v_output:=v_output||to_char(v_stab(j).parses ,         '999,999,999');
            DBMS_OUTPUT.PUT_LINE(v_output);
        END LOOP;

        IF c1%isopen THEN
                CLOSE c1;
        END IF;
    END LOOP;
END;
/

2.1.4. custom_dash_top_sessions.sql

select
                        decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')   "STATUS",
                        topsession.session_id            "SESSION_ID",
                        u.name                                         "NAME",
                        topsession.session_serial# serial#,
                        topsession.sql_id    "SQL_ID",
                        topsession.program                  "PROGRAM",
                        max(topsession.CPU)               "CPU",
                        max(topsession.WAITING)       "WAITING",
                        max(topsession.IO)                   "IO",
                        max(topsession.TOTAL)            "TOTAL"
                        from (   select
                 ash.session_id,
                 ash.session_serial#,
                 ash.user_id,
                 ash.sql_id,
                 ash.program,
                 sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
                 sum(decode(ash.session_state,'WAITING',1,0))    -
                 sum(decode(ash.session_state,'WAITING',
                        decode(en.wait_class,'User I/O',1, 0 ), 0))    "WAITING" ,
                 sum(decode(ash.session_state,'WAITING',
                        decode(en.wait_class,'User I/O',1, 0 ), 0))    "IO" ,
                 sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
        from user_ash_history ash, v$event_name en
        where en.event# = ash.event#  and to_char(SAMPLE_TIME,'DD-MON-YY HH24:MI') between '&EnterStartTIME' and '&EnterEndTime'
        group by session_id,user_id,session_serial#,program,sql_id
        order by sum(decode(session_state,'ON CPU',1,1))
           )        topsession, v$session s, sys.user$ u
           where
                                                u.user# =topsession.user_id and s.username!='DBMANAGER' and
                                           /* outer join to v$session because the session might be disconnected */
                                           topsession.session_id         = s.sid         (+) and
                                           topsession.session_serial# = s.serial#   (+)
           group by  topsession.session_id, topsession.session_serial#,  topsession.sql_id,
                                           topsession.program, s.username,s.sid,s.paddr,u.name
           order by max(topsession.IO) desc
/

2.2. High Waits

2.2.1. current_top_waits.sql

set termout off feedback off verify off linesize 200

define v_inst_number="&1"

set termout on serveroutput on size 1000000 format wrapped

DECLARE
    v_low  NUMBER;
    v_high NUMBER;
    v_end_t   VARCHAR2(30);
    v_output  VARCHAR2(200);
    type srec is record (
        inst_id NUMBER,
        event      VARCHAR2(64),
        waits           NUMBER,
        time_s          NUMBER,
        avg_ms          NUMBER,
        pct             NUMBER);
    type stab is table of srec index by pls_integer;
    v_stab stab;

    CURSOR c1 (inst_number NUMBER) IS
        WITH db_time AS (
        SELECT DISTINCT INST_ID,
          ROUND((MAX(VALUE) OVER (PARTITION BY (SELECT DBID FROM V$DATABASE),INST_ID))/1000000,0) db_t
        FROM gv$SYS_TIME_MODEL
        WHERE STAT_NAME='DB time' AND INST_ID=inst_number
        ),
        event_time AS (
        SELECT inst_id,event,waits,time_s,avg_ms,pos
        FROM (
          SELECT inst_id,event,waits,time_s,
            DECODE(waits, NULL, NULL, 0, NULL, ROUND((time_s/waits)*1000) ) avg_ms,
            ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY time_s desc) pos
          FROM (
            SELECT DISTINCT INST_ID, EVENT,
              MAX(TOTAL_WAITS) OVER (PARTITION BY (select DBID from v$database),INST_ID, EVENT) waits,
              ROUND((MAX(TIME_WAITED_MICRO) OVER (PARTITION BY (select dbid from v$database),INST_ID,EVENT))/1000000) time_s
            FROM gv$SYSTEM_EVENT
            WHERE WAIT_CLASS<>'Idle'  and INST_ID=inst_number
            UNION
            SELECT DISTINCT INST_ID, 'CPU time', NULL,
              ROUND((MAX(VALUE) OVER (PARTITION BY (select DBID from v$database),inst_id))/1000000)
            FROM gv$SYS_TIME_MODEL
            WHERE STAT_NAME='DB CPU' and inst_id=inst_number
          )
        )
        WHERE pos<6
        )
        SELECT db_time.inst_id,event,nvl(waits,0),time_s,nvl(avg_ms,0),ROUND(time_s*100/db_t ,1) pct
        FROM db_time, event_time
        WHERE db_time.inst_id=event_time.inst_id
        ORDER BY db_time.inst_id,pos;
BEGIN


        DBMS_OUTPUT.PUT_LINE('');

        IF c1%isopen THEN
                CLOSE c1;
        END IF;
        OPEN c1(&v_inst_number);
        FETCH c1 bulk collect into v_stab;

        v_output:='';
        v_output:=v_output||rpad('Instance',9,' ');
        v_output:=v_output||lpad('Averge',76, ' ');
        v_output:=v_output||lpad('% Total',34,' ');
        DBMS_OUTPUT.PUT_LINE(v_output);
        v_output:='';
        v_output:=v_output||rpad('Number',9,' ');
        v_output:=v_output||lpad('Event Name',64,' ');
        v_output:=v_output||lpad('Waits',12,' ');
        v_output:=v_output||lpad('Time (s)', 12,' ');
        v_output:=v_output||lpad('Wait (ms)',12, ' ');
        v_output:=v_output||lpad('Call Time',12,' ');
        DBMS_OUTPUT.PUT_LINE(v_output);
        DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));

        FOR j IN 1..v_stab.count LOOP
            v_output:='';
            v_output:=v_output||rpad(to_char(v_stab(j).inst_id), 9,' ');
            v_output:=v_output||lpad(v_stab(j).event, 64,' ');

            IF v_stab(j).waits = 0 THEN
                v_output:=v_output||lpad(' ', 12,' ');
            ELSE
                v_output:=v_output||lpad(to_char(v_stab(j).waits,'999,999,999'), 12,' ');
            END IF;

            v_output:=v_output||lpad(to_char(v_stab(j).time_s), 10,' ');

            IF v_stab(j).avg_ms = 0 THEN
                v_output:=v_output||lpad(' ', 10,' ');
            ELSE
                 v_output:=v_output||lpad(to_char(v_stab(j).avg_ms), 10,' ');
            END IF;

            v_output:=v_output||lpad(to_char(v_stab(j).pct), 12,' ');
            DBMS_OUTPUT.PUT_LINE(v_output);
        END LOOP;

        IF c1%isopen THEN
                CLOSE c1;
        END IF;
END;
/

2.2.2. awr_top_waits.sql


set termout off feedback off verify off linesize 200

define v_inst_number="&1"
define v_begin_snap_id="&2"
define v_end_snap_id="&3"

set termout on serveroutput on size 1000000 format wrapped

DECLARE
    v_low  NUMBER;
    v_high NUMBER;
    v_end_t   VARCHAR2(30);   
    v_output  VARCHAR2(200);    
    type srec is record (
    instance_number NUMBER,
    event_name  VARCHAR2(64),
    waits       NUMBER,
    time_s      NUMBER,
    avg_ms      NUMBER,
    pct         NUMBER);
    type stab is table of srec index by pls_integer;
    v_stab stab;
    
    CURSOR c1 (inst_number NUMBER, begin_snap_id NUMBER, end_snap_id NUMBER) IS
    WITH db_time AS (
    SELECT DISTINCT INSTANCE_NUMBER,  
      ROUND((MAX(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER) - MIN(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER))/1000000,0) db_t
    FROM DBA_HIST_SYS_TIME_MODEL
    WHERE STAT_NAME='DB time' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
    ), 
    event_time AS (
    SELECT instance_number,event_name,waits,time_s,avg_ms,pos 
    FROM (
      SELECT instance_number,event_name,waits,time_s,
        DECODE(waits, NULL, NULL, 0, NULL, ROUND((time_s/waits)*1000) ) avg_ms,
        ROW_NUMBER() OVER (PARTITION BY instance_number ORDER BY time_s desc) pos
      FROM (
        SELECT DISTINCT INSTANCE_NUMBER, EVENT_NAME, 
          MAX(TOTAL_WAITS) OVER (PARTITION BY DBID,INSTANCE_NUMBER, EVENT_NAME) - MIN(TOTAL_WAITS) over (PARTITION BY DBID,INSTANCE_NUMBER, EVENT_NAME) waits,
          ROUND((MAX(TIME_WAITED_MICRO) OVER (PARTITION BY dbid,INSTANCE_NUMBER,EVENT_NAME) - MIN(TIME_WAITED_MICRO) OVER (PARTITION BY DBID,INSTANCE_NUMBER, EVENT_NAME))/1000000) time_s 
        FROM DBA_HIST_SYSTEM_EVENT
        WHERE WAIT_CLASS<>'Idle' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
        UNION
        SELECT DISTINCT INSTANCE_NUMBER, 'CPU time', NULL,
          ROUND((MAX(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER) - MIN(VALUE) OVER (PARTITION BY DBID, INSTANCE_NUMBER))/1000000)
        FROM DBA_HIST_SYS_TIME_MODEL
        WHERE STAT_NAME='DB CPU' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
      )
    ) 
    WHERE pos<6
    ) 
    SELECT db_time.instance_number,event_name,nvl(waits,0),time_s,nvl(avg_ms,0),ROUND(time_s*100/db_t ,1) pct   
    FROM db_time, event_time 
    WHERE db_time.instance_number=event_time.instance_number
    ORDER BY db_time.instance_number,pos;
BEGIN
    FOR i IN &v_begin_snap_id...&v_end_snap_id LOOP
    v_low:=i;
    v_high:=i+1;
    IF v_high > &v_end_snap_id THEN
        EXIT;
    END IF;
    
        DBMS_OUTPUT.PUT_LINE('');
    SELECT TO_CHAR(END_INTERVAL_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t
    FROM DBA_HIST_SNAPSHOT
    WHERE INSTANCE_NUMBER=&v_inst_number AND SNAP_ID=v_high;
    DBMS_OUTPUT.PUT_LINE('Begin snapshot id='||v_low||' End snapshot id='||v_high||' End snapshot time='||v_end_t);
    
    IF c1%isopen THEN
        CLOSE c1;
    END IF;
    OPEN c1(&v_inst_number, v_low, v_high); 
    FETCH c1 bulk collect into v_stab;

    v_output:='';
    v_output:=v_output||rpad('Instance',9,' ');
    v_output:=v_output||lpad('Averge',76, ' ');
    v_output:=v_output||lpad('% Total',34,' ');
    DBMS_OUTPUT.PUT_LINE(v_output); 
    v_output:='';
    v_output:=v_output||rpad('Number',9,' ');
    v_output:=v_output||lpad('Event Name',64,' ');
    v_output:=v_output||lpad('Waits',12,' ');
    v_output:=v_output||lpad('Time (s)', 12,' ');
    v_output:=v_output||lpad('Wait (ms)',12, ' ');
    v_output:=v_output||lpad('Call Time',12,' ');
    DBMS_OUTPUT.PUT_LINE(v_output);
    DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-'));
    
    FOR j IN 1..v_stab.count LOOP
        v_output:='';
        v_output:=v_output||rpad(to_char(v_stab(j).instance_number), 9,' ');
        v_output:=v_output||lpad(v_stab(j).event_name, 64,' ');     
        
        IF v_stab(j).waits = 0 THEN
            v_output:=v_output||lpad(' ', 12,' ');
        ELSE
            v_output:=v_output||lpad(to_char(v_stab(j).waits,'999,999,999'), 12,' ');
        END IF;
                
        v_output:=v_output||lpad(to_char(v_stab(j).time_s), 10,' ');
        
        IF v_stab(j).avg_ms = 0 THEN
            v_output:=v_output||lpad(' ', 10,' ');
        ELSE
             v_output:=v_output||lpad(to_char(v_stab(j).avg_ms), 10,' ');
        END IF;
           
        v_output:=v_output||lpad(to_char(v_stab(j).pct), 12,' ');
            DBMS_OUTPUT.PUT_LINE(v_output);
    END LOOP;   
    
    IF c1%isopen THEN
        CLOSE c1;
    END IF;
    END LOOP;
END;
/

oracle数据库性能优化方法（oracle数据库性能优化方法论和最佳实践）

概述

一、使用方法

二、General Scripts for Troubleshooting 概览

2.1. High CPU样例展示

2.1.1. Current_CPU_Queries.sql

2.1.2. AWR_Top_CPU_Queries.sql

2.1.3. Statspack_CPU_Queries.sql

2.1.4. custom_dash_top_sessions.sql

2.2. High Waits

2.2.1. current_top_waits.sql

2.2.2. awr_top_waits.sql

发表评论

oracle数据库性能优化方法（oracle数据库性能优化方法论和最佳实践）

概述

一、使用方法

二、General Scripts for Troubleshooting 概览

2.1. High CPU样例展示

2.1.1. Current_CPU_Queries.sql

2.1.2. AWR_Top_CPU_Queries.sql

2.1.3. Statspack_CPU_Queries.sql

2.1.4. custom_dash_top_sessions.sql

2.2. High Waits

2.2.1. current_top_waits.sql

2.2.2. awr_top_waits.sql

相关推荐

华为手机怎样设置手机不卡很流畅呢

特需和专家门诊哪个好（专家门诊和特需门诊哪个医生更厉害）

华为WATCHGTRunner测评（华为WATCHGTRunner跑步功能如何）

唯美古风男孩名字（让人惊艳的古风男孩名字）

电动车充电正确方法（电动车充电的正确步骤流程）

工作总结详细（工作总结写法范文）

发表评论