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

Comments

8 responses to “Script:数据库最近的性能度量”

1. 

   Charlie 木匠 (@mujiang)

   October 28, 2011

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

   Reply
   1. 

      admin

      October 28, 2011

      Good!

      Reply
   2. 

      tester

      October 28, 2011

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

      Reply
      1. 

         admin

         October 28, 2011

         没有建procedure啊？

         Reply
2. 

   zhouyf

   October 28, 2011

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

   Reply
3. 

   tester

   October 28, 2011

   和从v$sysmetric查有啥区别

   Reply
4. 

   tester

   October 28, 2011

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

   Reply
5. 

   Charlie 木匠 (@mujiang)

   October 29, 2011

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

   Reply