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

Comments

1. Charlie 木匠 (@mujiang) says

   2011/10/28 at 04:29

   使用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.

   回复
   • admin says

     2011/10/28 at 10:34

     Good!

     回复
   • tester says

     2011/10/28 at 13:59

     难道你们的生成库可以随便建procedure么？

     回复
     • admin says

       2011/10/28 at 16:19

       没有建procedure啊？

       回复
2. zhouyf says

   2011/10/28 at 10:46

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

   回复
3. tester says

   2011/10/28 at 13:47

   和从v$sysmetric查有啥区别

   回复
4. tester says

   2011/10/28 at 13:58

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

   回复
5. Charlie 木匠 (@mujiang) says

   2011/10/29 at 03:06

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

   回复