Script:数据库最近的性能度量

以下SQL脚本可以用于列出数据库最近1分钟的性能度量信息(performance metric):

set linesize 80 pagesize 1400


SELECT /*+ ORDERED USE_MERGE(m) */
 TO_CHAR(FROM_TZ(CAST(m.end_time AS TIMESTAMP),
                 TO_CHAR(systimestamp, 'tzr')) AT TIME ZONE sessiontimezone,
         'YYYY-MM-DD HH24:MI:SS'),
 SUM(CASE
       WHEN a.internal_metric_name = 'transactions_ps' THEN
        m.value
       ELSE
        0
     END) transactions_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physreads_ps' THEN
        m.value
       ELSE
        0
     END) physreads_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physreads_pt' THEN
        m.value
       ELSE
        0
     END) physreads_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'physwrites_ps' THEN
        m.value
       ELSE
        0
     END) physwrites_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physwrites_pt' THEN
        m.value
       ELSE
        0
     END) physwrites_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'physreadsdir_ps' THEN
        m.value
       ELSE
        0
     END) physreadsdir_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physreadsdir_pt' THEN
        m.value
       ELSE
        0
     END) physreadsdir_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'physwritesdir_ps' THEN
        m.value
       ELSE
        0
     END) physwritesdir_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physwritesdir_pt' THEN
        m.value
       ELSE
        0
     END) physwritesdir_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'physreadslob_ps' THEN
        m.value
       ELSE
        0
     END) physreadslob_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physreadslob_pt' THEN
        m.value
       ELSE
        0
     END) physreadslob_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'physwriteslob_ps' THEN
        m.value
       ELSE
        0
     END) physwriteslob_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'physwriteslob_pt' THEN
        m.value
       ELSE
        0
     END) physwriteslob_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'redosize_ps' THEN
        m.value
       ELSE
        0
     END) redosize_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'logons_ps' THEN
        m.value
       ELSE
        0
     END) logons_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'logons_pt' THEN
        m.value
       ELSE
        0
     END) logons_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'opncurs_ps' THEN
        m.value
       ELSE
        0
     END) opncurs_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'opncurs_pt' THEN
        m.value
       ELSE
        0
     END) opncurs_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'commits_ps' THEN
        m.value
       ELSE
        0
     END) commits_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'commits_pt' THEN
        m.value
       ELSE
        0
     END) commits_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'rollbacks_ps' THEN
        m.value
       ELSE
        0
     END) rollbacks_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'rollbacks_pt' THEN
        m.value
       ELSE
        0
     END) rollbacks_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'usercalls_ps' THEN
        m.value
       ELSE
        0
     END) usercalls_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'usercalls_pt' THEN
        m.value
       ELSE
        0
     END) usercalls_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'recurscalls_ps' THEN
        m.value
       ELSE
        0
     END) recurscalls_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'recurscalls_pt' THEN
        m.value
       ELSE
        0
     END) recurscalls_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'logreads_ps' THEN
        m.value
       ELSE
        0
     END) logreads_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'logreads_pt' THEN
        m.value
       ELSE
        0
     END) logreads_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'dbwrcheckpoints_ps' THEN
        m.value
       ELSE
        0
     END) dbwrcheckpoints_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'bgcheckpoints_ps' THEN
        m.value
       ELSE
        0
     END) bgcheckpoints_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'redowrites_ps' THEN
        m.value
       ELSE
        0
     END) redowrites_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'redowrites_pt' THEN
        m.value
       ELSE
        0
     END) redowrites_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'tabscanslong_ps' THEN
        m.value
       ELSE
        0
     END) tabscanslong_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'tabscanslong_pt' THEN
        m.value
       ELSE
        0
     END) tabscanslong_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'tabscanstotal_ps' THEN
        m.value
       ELSE
        0
     END) tabscanstotal_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'tabscanstotal_pt' THEN
        m.value
       ELSE
        0
     END) tabscanstotal_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'indxscansfull_pt' THEN
        m.value
       ELSE
        0
     END) indxscansfull_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'indxscansfull_ps' THEN
        m.value
       ELSE
        0
     END) indxscansfull_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'indxscanstotal_ps' THEN
        m.value
       ELSE
        0
     END) indxscanstotal_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'indxscanstotal_pt' THEN
        m.value
       ELSE
        0
     END) indxscanstotal_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'parses_ps' THEN
        m.value
       ELSE
        0
     END) parses_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'parses_pt' THEN
        m.value
       ELSE
        0
     END) parses_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'hardparses_ps' THEN
        m.value
       ELSE
        0
     END) hardparses_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'hardparses_pt' THEN
        m.value
       ELSE
        0
     END) hardparses_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'failedparses_ps' THEN
        m.value
       ELSE
        0
     END) failedparses_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'failedparses_pt' THEN
        m.value
       ELSE
        0
     END) failedparses_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'executions_ps' THEN
        m.value
       ELSE
        0
     END) executions_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'sortsdisk_ps' THEN
        m.value
       ELSE
        0
     END) sortsdisk_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'sortsdisk_pt' THEN
        m.value
       ELSE
        0
     END) sortsdisk_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'rows_psort' THEN
        m.value
       ELSE
        0
     END) rows_psort,
 SUM(CASE
       WHEN a.internal_metric_name = 'executeswoparse_pct' THEN
        m.value
       ELSE
        0
     END) executeswoparse_pct,
 SUM(CASE
       WHEN a.internal_metric_name = 'softparse_pct' THEN
        m.value
       ELSE
        0
     END) softparse_pct,
 SUM(CASE
       WHEN a.internal_metric_name = 'usercall_pct' THEN
        m.value
       ELSE
        0
     END) usercall_pct,
 SUM(CASE
       WHEN a.internal_metric_name = 'networkbytes_ps' THEN
        m.value
       ELSE
        0
     END) networkbytes_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqtimeouts_ps' THEN
        m.value
       ELSE
        0
     END) enqtimeouts_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqtimeouts_pt' THEN
        m.value
       ELSE
        0
     END) enqtimeouts_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqwaits_ps' THEN
        m.value
       ELSE
        0
     END) enqwaits_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqwaits_pt' THEN
        m.value
       ELSE
        0
     END) enqwaits_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqdeadlocks_ps' THEN
        m.value
       ELSE
        0
     END) enqdeadlocks_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqdeadlocks_pt' THEN
        m.value
       ELSE
        0
     END) enqdeadlocks_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqreqs_ps' THEN
        m.value
       ELSE
        0
     END) enqreqs_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'enqreqs_pt' THEN
        m.value
       ELSE
        0
     END) enqreqs_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'dbblkgets_ps' THEN
        m.value
       ELSE
        0
     END) dbblkgets_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'dbblkgets_pt' THEN
        m.value
       ELSE
        0
     END) dbblkgets_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'consistentreadgets_ps' THEN
        m.value
       ELSE
        0
     END) consistentreadgets_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'consistentreadgets_pt' THEN
        m.value
       ELSE
        0
     END) consistentreadgets_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'dbblkchanges_ps' THEN
        m.value
       ELSE
        0
     END) dbblkchanges_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'dbblkchanges_pt' THEN
        m.value
       ELSE
        0
     END) dbblkchanges_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'consistentreadchanges_ps' THEN
        m.value
       ELSE
        0
     END) consistentreadchanges_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'consistentreadchanges_pt' THEN
        m.value
       ELSE
        0
     END) consistentreadchanges_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'crblks_ps' THEN
        m.value
       ELSE
        0
     END) crblks_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'crblks_pt' THEN
        m.value
       ELSE
        0
     END) crblks_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'crundorecs_pt' THEN
        m.value
       ELSE
        0
     END) crundorecs_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'userrollbackundorec_ps' THEN
        m.value
       ELSE
        0
     END) userrollbackundorec_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'userrollbackundorec_pt' THEN
        m.value
       ELSE
        0
     END) userrollbackundorec_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'leafnodesplits_ps' THEN
        m.value
       ELSE
        0
     END) leafnodesplits_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'leafnodesplits_pt' THEN
        m.value
       ELSE
        0
     END) leafnodesplits_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'branchnodesplits_ps' THEN
        m.value
       ELSE
        0
     END) branchnodesplits_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'branchnodesplits_pt' THEN
        m.value
       ELSE
        0
     END) branchnodesplits_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'redosize_pt' THEN
        m.value
       ELSE
        0
     END) redosize_pt,
 SUM(CASE
       WHEN a.internal_metric_name = 'crundorecs_ps' THEN
        m.value
       ELSE
        0
     END) crundorecs_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'dbtime_ps' THEN
        m.value
       ELSE
        0
     END) dbtime_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'avg_active_sessions' THEN
        m.value
       ELSE
        0
     END) avg_active_sessions,
 SUM(CASE
       WHEN a.internal_metric_name = 'avg_sync_singleblk_read_latency' THEN
        m.value
       ELSE
        0
     END) avg_block_read_latency,
 SUM(CASE
       WHEN a.internal_metric_name = 'iombs_ps' THEN
        m.value
       ELSE
        0
     END) iombs_ps,
 SUM(CASE
       WHEN a.internal_metric_name = 'iorequests_ps' THEN
        m.value
       ELSE
        0
     END) iorequests_ps
  FROM v$alert_types a, v$threshold_types t, v$sysmetric m
 WHERE a.internal_metric_category = 'instance_throughput'
   AND a.reason_id = t.alert_reason_id
   AND t.metrics_id = m.metric_id
   AND m.group_id = 2
   AND m.end_time <= SYSDATE
 GROUP BY m.end_time
 ORDER BY m.end_time ASC
/

使用方法:


SQL> @metric

TO_CHAR(FROM_TZ(CAS TRANSACTIONS_PS PHYSREADS_PS PHYSREADS_PT PHYSWRITES_PS
------------------- --------------- ------------ ------------ -------------
PHYSWRITES_PT PHYSREADSDIR_PS PHYSREADSDIR_PT PHYSWRITESDIR_PS PHYSWRITESDIR_PT
------------- --------------- --------------- ---------------- ----------------
PHYSREADSLOB_PS PHYSREADSLOB_PT PHYSWRITESLOB_PS PHYSWRITESLOB_PT REDOSIZE_PS
--------------- --------------- ---------------- ---------------- -----------
 LOGONS_PS  LOGONS_PT OPNCURS_PS OPNCURS_PT COMMITS_PS COMMITS_PT ROLLBACKS_PS
---------- ---------- ---------- ---------- ---------- ---------- ------------
ROLLBACKS_PT USERCALLS_PS USERCALLS_PT RECURSCALLS_PS RECURSCALLS_PT LOGREADS_PS
------------ ------------ ------------ -------------- -------------- -----------
LOGREADS_PT DBWRCHECKPOINTS_PS BGCHECKPOINTS_PS REDOWRITES_PS REDOWRITES_PT
----------- ------------------ ---------------- ------------- -------------
TABSCANSLONG_PS TABSCANSLONG_PT TABSCANSTOTAL_PS TABSCANSTOTAL_PT
--------------- --------------- ---------------- ----------------
INDXSCANSFULL_PT INDXSCANSFULL_PS INDXSCANSTOTAL_PS INDXSCANSTOTAL_PT  PARSES_PS
---------------- ---------------- ----------------- ----------------- ----------
 PARSES_PT HARDPARSES_PS HARDPARSES_PT FAILEDPARSES_PS FAILEDPARSES_PT
---------- ------------- ------------- --------------- ---------------
EXECUTIONS_PS SORTSDISK_PS SORTSDISK_PT ROWS_PSORT EXECUTESWOPARSE_PCT
------------- ------------ ------------ ---------- -------------------
SOFTPARSE_PCT USERCALL_PCT NETWORKBYTES_PS ENQTIMEOUTS_PS ENQTIMEOUTS_PT
------------- ------------ --------------- -------------- --------------
ENQWAITS_PS ENQWAITS_PT ENQDEADLOCKS_PS ENQDEADLOCKS_PT ENQREQS_PS ENQREQS_PT
----------- ----------- --------------- --------------- ---------- ----------
DBBLKGETS_PS DBBLKGETS_PT CONSISTENTREADGETS_PS CONSISTENTREADGETS_PT
------------ ------------ --------------------- ---------------------
DBBLKCHANGES_PS DBBLKCHANGES_PT CONSISTENTREADCHANGES_PS
--------------- --------------- ------------------------
CONSISTENTREADCHANGES_PT  CRBLKS_PS  CRBLKS_PT CRUNDORECS_PT
------------------------ ---------- ---------- -------------
USERROLLBACKUNDOREC_PS USERROLLBACKUNDOREC_PT LEAFNODESPLITS_PS
---------------------- ---------------------- -----------------
LEAFNODESPLITS_PT BRANCHNODESPLITS_PS BRANCHNODESPLITS_PT REDOSIZE_PT
----------------- ------------------- ------------------- -----------
CRUNDORECS_PS  DBTIME_PS AVG_ACTIVE_SESSIONS AVG_BLOCK_READ_LATENCY   IOMBS_PS
------------- ---------- ------------------- ---------------------- ----------
IOREQUESTS_PS
-------------
2011-10-27 20:02:23      .349533955   2.69640479   7.71428571    .116511318
   .333333333      .199733688      .571428571       .116511318       .333333333
     .116511318      .333333333       .116511318       .333333333  16212.0506
.016644474 .047619048 19.0745672 54.5714286 .349533955        100            0
           0   .349533955            1     81.1917443     232.285714   130.54261
  373.47619                  0                0    .515978695    1.47619048
              0               0       .216378162       .619047619
               0                0        19.4573901        55.6666667 4.92676431
14.0952381    1.89747004    5.42857143               0               0
   20.4560586            0            0  22.026087          75.9153784
   61.4864865    .42865891      995.838881              0              0
          0           0               0               0  8.9713715 25.6666667
  44.9400799   128.571429              85.60253            244.904762
     45.1398136      129.142857               .249667111
              .714285714          0          0             0
                     0                      0        .199733688
       .571428571                   0                   0  46381.9048
            0 .023586884          .000235869             .032960413 3.09587217
   195.489348

PS Per Second
PT Per Transaction

Comments

  1. Charlie 木匠 (@mujiang) says

    使用print_table单列显示输出,容易阅读.

    create or replace procedure utility.print_table
    ( p_query in varchar2,
    p_date_fmt in varchar2 default ‘dd-mon-yyyy hh24:mi:ss’ )

    — this utility is designed to be installed ONCE in a database and used
    — by all. Also, it is nice to have roles enabled so that queries by
    — DBA’s that use a role to gain access to the DBA_* views still work
    — that is the purpose of AUTHID CURRENT_USER
    AUTHID CURRENT_USER
    is
    l_theCursor integer default dbms_sql.open_cursor;
    l_columnValue varchar2(4000);
    l_status integer;
    l_descTbl dbms_sql.desc_tab;
    l_colCnt number;
    l_cs varchar2(255);
    l_date_fmt varchar2(255);

    — small inline procedure to restore the sessions state
    — we may have modified the cursor sharing and nls date format
    — session variables, this just restores them
    procedure restore
    is
    begin
    if ( upper(l_cs) not in ( ‘FORCE’,’SIMILAR’ ))
    then
    execute immediate
    ‘alter session set cursor_sharing=exact’;
    end if;
    if ( p_date_fmt is not null )
    then
    execute immediate
    ‘alter session set nls_date_format=”’ || l_date_fmt || ””;
    end if;
    dbms_sql.close_cursor(l_theCursor);
    end restore;
    begin
    — I like to see the dates print out with times, by default, the
    — format mask I use includes that. In order to be “friendly”
    — we save the date current sessions date format and then use
    — the one with the date and time. Passing in NULL will cause
    — this routine just to use the current date format
    if ( p_date_fmt is not null )
    then
    select sys_context( ‘userenv’, ‘nls_date_format’ )
    into l_date_fmt
    from dual;

    execute immediate
    ‘alter session set nls_date_format=”’ || p_date_fmt || ””;
    end if;

    — to be bind variable friendly on this ad-hoc queries, we
    — look to see if cursor sharing is already set to FORCE or
    — similar, if not, set it so when we parse — literals
    — are replaced with binds
    if ( dbms_utility.get_parameter_value
    ( ‘cursor_sharing’, l_status, l_cs ) = 1 )
    then
    if ( upper(l_cs) not in (‘FORCE’,’SIMILAR’))
    then
    execute immediate
    ‘alter session set cursor_sharing=force’;
    end if;
    end if;

    — parse and describe the query sent to us. we need
    — to know the number of columns and their names.
    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    — define all columns to be cast to varchar2’s, we
    — are just printing them out
    for i in 1 .. l_colCnt loop
    if ( l_descTbl(i).col_type not in ( 113 ) )
    then
    dbms_sql.define_column
    (l_theCursor, i, l_columnValue, 4000);
    end if;
    end loop;

    — execute the query, so we can fetch
    l_status := dbms_sql.execute(l_theCursor);

    — loop and print out each column on a separate line
    — bear in mind that dbms_output only prints 255 characters/line
    — so we’ll only see the first 200 characters by my design…
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
    loop
    for i in 1 .. l_colCnt loop
    if ( l_descTbl(i).col_type not in ( 113 ) )
    then
    dbms_sql.column_value
    ( l_theCursor, i, l_columnValue );
    dbms_output.put_line
    ( rpad( l_descTbl(i).col_name, 30 )
    || ‘: ‘ ||
    substr( l_columnValue, 1, 200 ) );
    end if;
    end loop;
    dbms_output.put_line( ‘—————–‘ );
    end loop;

    — now, restore the session state, no matter what
    restore;
    exception
    when others then
    restore;
    raise;
    end;
    /

    begin
    utility.print_table(‘select * from dba_users where rownum < 2');
    end;
    /

    begin

    utility.print_table( q'{SELECT /*+ ORDERED USE_MERGE(m) */
    TO_CHAR(FROM_TZ(CAST(m.end_time AS TIMESTAMP),
    TO_CHAR(systimestamp, 'tzr')) AT TIME ZONE sessiontimezone,
    'YYYY-MM-DD HH24:MI:SS') snap_time,
    SUM(CASE
    WHEN a.internal_metric_name = 'transactions_ps' THEN
    m.value
    ELSE
    0
    END) transactions_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physreads_ps' THEN
    m.value
    ELSE
    0
    END) physreads_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physreads_pt' THEN
    m.value
    ELSE
    0
    END) physreads_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'physwrites_ps' THEN
    m.value
    ELSE
    0
    END) physwrites_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physwrites_pt' THEN
    m.value
    ELSE
    0
    END) physwrites_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'physreadsdir_ps' THEN
    m.value
    ELSE
    0
    END) physreadsdir_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physreadsdir_pt' THEN
    m.value
    ELSE
    0
    END) physreadsdir_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'physwritesdir_ps' THEN
    m.value
    ELSE
    0
    END) physwritesdir_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physwritesdir_pt' THEN
    m.value
    ELSE
    0
    END) physwritesdir_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'physreadslob_ps' THEN
    m.value
    ELSE
    0
    END) physreadslob_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physreadslob_pt' THEN
    m.value
    ELSE
    0
    END) physreadslob_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'physwriteslob_ps' THEN
    m.value
    ELSE
    0
    END) physwriteslob_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'physwriteslob_pt' THEN
    m.value
    ELSE
    0
    END) physwriteslob_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'redosize_ps' THEN
    m.value
    ELSE
    0
    END) redosize_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'logons_ps' THEN
    m.value
    ELSE
    0
    END) logons_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'logons_pt' THEN
    m.value
    ELSE
    0
    END) logons_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'opncurs_ps' THEN
    m.value
    ELSE
    0
    END) opncurs_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'opncurs_pt' THEN
    m.value
    ELSE
    0
    END) opncurs_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'commits_ps' THEN
    m.value
    ELSE
    0
    END) commits_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'commits_pt' THEN
    m.value
    ELSE
    0
    END) commits_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'rollbacks_ps' THEN
    m.value
    ELSE
    0
    END) rollbacks_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'rollbacks_pt' THEN
    m.value
    ELSE
    0
    END) rollbacks_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'usercalls_ps' THEN
    m.value
    ELSE
    0
    END) usercalls_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'usercalls_pt' THEN
    m.value
    ELSE
    0
    END) usercalls_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'recurscalls_ps' THEN
    m.value
    ELSE
    0
    END) recurscalls_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'recurscalls_pt' THEN
    m.value
    ELSE
    0
    END) recurscalls_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'logreads_ps' THEN
    m.value
    ELSE
    0
    END) logreads_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'logreads_pt' THEN
    m.value
    ELSE
    0
    END) logreads_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'dbwrcheckpoints_ps' THEN
    m.value
    ELSE
    0
    END) dbwrcheckpoints_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'bgcheckpoints_ps' THEN
    m.value
    ELSE
    0
    END) bgcheckpoints_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'redowrites_ps' THEN
    m.value
    ELSE
    0
    END) redowrites_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'redowrites_pt' THEN
    m.value
    ELSE
    0
    END) redowrites_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'tabscanslong_ps' THEN
    m.value
    ELSE
    0
    END) tabscanslong_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'tabscanslong_pt' THEN
    m.value
    ELSE
    0
    END) tabscanslong_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'tabscanstotal_ps' THEN
    m.value
    ELSE
    0
    END) tabscanstotal_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'tabscanstotal_pt' THEN
    m.value
    ELSE
    0
    END) tabscanstotal_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'indxscansfull_pt' THEN
    m.value
    ELSE
    0
    END) indxscansfull_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'indxscansfull_ps' THEN
    m.value
    ELSE
    0
    END) indxscansfull_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'indxscanstotal_ps' THEN
    m.value
    ELSE
    0
    END) indxscanstotal_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'indxscanstotal_pt' THEN
    m.value
    ELSE
    0
    END) indxscanstotal_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'parses_ps' THEN
    m.value
    ELSE
    0
    END) parses_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'parses_pt' THEN
    m.value
    ELSE
    0
    END) parses_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'hardparses_ps' THEN
    m.value
    ELSE
    0
    END) hardparses_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'hardparses_pt' THEN
    m.value
    ELSE
    0
    END) hardparses_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'failedparses_ps' THEN
    m.value
    ELSE
    0
    END) failedparses_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'failedparses_pt' THEN
    m.value
    ELSE
    0
    END) failedparses_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'executions_ps' THEN
    m.value
    ELSE
    0
    END) executions_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'sortsdisk_ps' THEN
    m.value
    ELSE
    0
    END) sortsdisk_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'sortsdisk_pt' THEN
    m.value
    ELSE
    0
    END) sortsdisk_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'rows_psort' THEN
    m.value
    ELSE
    0
    END) rows_psort,
    SUM(CASE
    WHEN a.internal_metric_name = 'executeswoparse_pct' THEN
    m.value
    ELSE
    0
    END) executeswoparse_pct,
    SUM(CASE
    WHEN a.internal_metric_name = 'softparse_pct' THEN
    m.value
    ELSE
    0
    END) softparse_pct,
    SUM(CASE
    WHEN a.internal_metric_name = 'usercall_pct' THEN
    m.value
    ELSE
    0
    END) usercall_pct,
    SUM(CASE
    WHEN a.internal_metric_name = 'networkbytes_ps' THEN
    m.value
    ELSE
    0
    END) networkbytes_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqtimeouts_ps' THEN
    m.value
    ELSE
    0
    END) enqtimeouts_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqtimeouts_pt' THEN
    m.value
    ELSE
    0
    END) enqtimeouts_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqwaits_ps' THEN
    m.value
    ELSE
    0
    END) enqwaits_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqwaits_pt' THEN
    m.value
    ELSE
    0
    END) enqwaits_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqdeadlocks_ps' THEN
    m.value
    ELSE
    0
    END) enqdeadlocks_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqdeadlocks_pt' THEN
    m.value
    ELSE
    0
    END) enqdeadlocks_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqreqs_ps' THEN
    m.value
    ELSE
    0
    END) enqreqs_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'enqreqs_pt' THEN
    m.value
    ELSE
    0
    END) enqreqs_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'dbblkgets_ps' THEN
    m.value
    ELSE
    0
    END) dbblkgets_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'dbblkgets_pt' THEN
    m.value
    ELSE
    0
    END) dbblkgets_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'consistentreadgets_ps' THEN
    m.value
    ELSE
    0
    END) consistentreadgets_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'consistentreadgets_pt' THEN
    m.value
    ELSE
    0
    END) consistentreadgets_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'dbblkchanges_ps' THEN
    m.value
    ELSE
    0
    END) dbblkchanges_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'dbblkchanges_pt' THEN
    m.value
    ELSE
    0
    END) dbblkchanges_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'consistentreadchanges_ps' THEN
    m.value
    ELSE
    0
    END) consistentreadchanges_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'consistentreadchanges_pt' THEN
    m.value
    ELSE
    0
    END) consistentreadchanges_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'crblks_ps' THEN
    m.value
    ELSE
    0
    END) crblks_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'crblks_pt' THEN
    m.value
    ELSE
    0
    END) crblks_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'crundorecs_pt' THEN
    m.value
    ELSE
    0
    END) crundorecs_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'userrollbackundorec_ps' THEN
    m.value
    ELSE
    0
    END) userrollbackundorec_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'userrollbackundorec_pt' THEN
    m.value
    ELSE
    0
    END) userrollbackundorec_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'leafnodesplits_ps' THEN
    m.value
    ELSE
    0
    END) leafnodesplits_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'leafnodesplits_pt' THEN
    m.value
    ELSE
    0
    END) leafnodesplits_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'branchnodesplits_ps' THEN
    m.value
    ELSE
    0
    END) branchnodesplits_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'branchnodesplits_pt' THEN
    m.value
    ELSE
    0
    END) branchnodesplits_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'redosize_pt' THEN
    m.value
    ELSE
    0
    END) redosize_pt,
    SUM(CASE
    WHEN a.internal_metric_name = 'crundorecs_ps' THEN
    m.value
    ELSE
    0
    END) crundorecs_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'dbtime_ps' THEN
    m.value
    ELSE
    0
    END) dbtime_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'avg_active_sessions' THEN
    m.value
    ELSE
    0
    END) avg_active_sessions,
    SUM(CASE
    WHEN a.internal_metric_name = 'avg_sync_singleblk_read_latency' THEN
    m.value
    ELSE
    0
    END) avg_block_read_latency,
    SUM(CASE
    WHEN a.internal_metric_name = 'iombs_ps' THEN
    m.value
    ELSE
    0
    END) iombs_ps,
    SUM(CASE
    WHEN a.internal_metric_name = 'iorequests_ps' THEN
    m.value
    ELSE
    0
    END) iorequests_ps
    FROM v$alert_types a, v$threshold_types t, v$sysmetric m
    WHERE a.internal_metric_category = 'instance_throughput'
    AND a.reason_id = t.alert_reason_id
    AND t.metrics_id = m.metric_id
    AND m.group_id = 2
    AND m.end_time <= SYSDATE
    GROUP BY m.end_time
    ORDER BY m.end_time ASC
    }');

    end;
    /

    SNAP_TIME : 2011-10-27 13:25:16
    TRANSACTIONS_PS : .549359081072083
    PHYSREADS_PS : .0166472448809722
    PHYSREADS_PT : .0303030303030303
    PHYSWRITES_PS : .283003162976527
    PHYSWRITES_PT : .515151515151515
    PHYSREADSDIR_PS : 0
    PHYSREADSDIR_PT : 0
    PHYSWRITESDIR_PS : 0
    PHYSWRITESDIR_PT : 0
    PHYSREADSLOB_PS : 0
    PHYSREADSLOB_PT : 0
    PHYSWRITESLOB_PS : 0
    PHYSWRITESLOB_PT : 0
    REDOSIZE_PS : 30324.9542200766
    LOGONS_PS : .0499417346429166
    LOGONS_PT : .0909090909090909
    OPNCURS_PS : 46.1794572998169
    OPNCURS_PT : 84.0606060606061
    COMMITS_PS : .549359081072083
    COMMITS_PT : 100
    ROLLBACKS_PS : 0
    ROLLBACKS_PT : 0
    USERCALLS_PS : .432828366905277
    USERCALLS_PT : .787878787878788
    RECURSCALLS_PS : 557.915764940902
    RECURSCALLS_PT : 1015.57575757576
    LOGREADS_PS : 356.251040452805
    LOGREADS_PT : 648.484848484849
    DBWRCHECKPOINTS_PS : 0
    BGCHECKPOINTS_PS : 0
    REDOWRITES_PS : 1.53154652904944
    REDOWRITES_PT : 2.78787878787879
    TABSCANSLONG_PS : 0
    TABSCANSLONG_PT : 0
    TABSCANSTOTAL_PS : 2.74679540536041
    TABSCANSTOTAL_PT : 5
    INDXSCANSFULL_PT : 0
    INDXSCANSFULL_PS : 0
    INDXSCANSTOTAL_PS : 31.4965873147994
    INDXSCANSTOTAL_PT : 57.3333333333333
    PARSES_PS : 24.8543366072915
    PARSES_PT : 45.2424242424242
    HARDPARSES_PS : 2.46379224238389
    HARDPARSES_PT : 4.48484848484848
    FAILEDPARSES_PS : .0499417346429166
    FAILEDPARSES_PT : .0909090909090909
    EXECUTIONS_PS : 48.6432495422008
    SORTSDISK_PS : 0
    SORTSDISK_PT : 0
    ROWS_PSORT : 92
    EXECUTESWOPARSE_PCT : 48.9048596851472
    SOFTPARSE_PCT : 90.0870730073677
    USERCALL_PCT : .0775193798449612
    NETWORKBYTES_PS : 588.563342766772
    ENQTIMEOUTS_PS : 0
    ENQTIMEOUTS_PT : 0
    ENQWAITS_PS : 0
    ENQWAITS_PT : 0
    ENQDEADLOCKS_PS : 0
    ENQDEADLOCKS_PT : 0
    ENQREQS_PS : 23.1563176294323
    ENQREQS_PT : 42.1515151515151
    DBBLKGETS_PS : 196.837023472615
    DBBLKGETS_PT : 358.30303030303
    CONSISTENTREADGETS_PS : 159.41401698019
    CONSISTENTREADGETS_PT : 290.181818181818
    DBBLKCHANGES_PS : 229.44897619444
    DBBLKCHANGES_PT : 417.666666666667
    CONSISTENTREADCHANGES_PS : 7.3081405027468
    CONSISTENTREADCHANGES_PT : 13.3030303030303
    CRBLKS_PS : .516064591310138
    CRBLKS_PT : .939393939393939
    CRUNDORECS_PT : 13.2727272727273
    USERROLLBACKUNDOREC_PS : 0
    USERROLLBACKUNDOREC_PT : 0
    LEAFNODESPLITS_PS : 0
    LEAFNODESPLITS_PT : 0
    BRANCHNODESPLITS_PS : 0
    BRANCHNODESPLITS_PT : 0
    REDOSIZE_PT : 55200.6060606061
    CRUNDORECS_PS : 7.29149325786582
    DBTIME_PS : 3.71469452305643
    AVG_ACTIVE_SESSIONS : .0371469452305643
    AVG_BLOCK_READ_LATENCY : 0
    IOMBS_PS : .0332944897619444
    IOREQUESTS_PS : 5.85983019810221
    —————–

    PL/SQL procedure successfully completed.

  2. 我个人觉得where要加上这个条件 t.intsize_csec > 2000.

  3. tester says

    和从v$sysmetric查有啥区别

  4. tester says

    直接查询V$SYSMETRIC有啥提高?

  5. Charlie 木匠 (@mujiang) says

    我们是DevOPS, 自己开发存储过程(Store Procedure packages),自己按需要部署.
    There is no SELECT/INSERT/UPDATE/DELETE from application, only to call Transaction Database API.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号