通过statspack诊断物理dg数据库性能方法 - 数据库编程

ser_jobs; no rows selected SQL> variable jobno number; SQL> variable instno number; SQL> begin 2 select instance_number into :instno from v$instance@STDBY_LINK_DG2; 3 dbms_job.submit(:jobno, 'statspack_dg2_dg2.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); 4 commit; 5 end; 6 / PL/SQL procedure successfully completed. SQL> select job,schema_user,last_date,what,instance from user_jobs; JOB SCHEMA_USER LAST_DATE WHAT INSTANCE ---------- ------------------------------ --------- ------------------------------------------------------------ ---------- 23 STDBYPERF statspack_dg2_dg2.snap; 1 SQL>

便于验证修改job，使其5分钟运行一次。

SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
  2    select instance_number into :instno from v$instance@STDBY_LINK_DG2;
  3    dbms_job.submit(:jobno, 'statspack_dg2_dg2.snap;', trunc(sysdate+1/288,'MI'), 'trunc(sysdate+1/288,''MI'')', TRUE, :instno);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select last_date,this_date,next_date,what from user_jobs;

LAST_DATE           THIS_DATE           NEXT_DATE           WHAT
------------------- ------------------- ------------------- ------------------------------------------------------------
                                        2014-04-23 16:00:00 statspack_dg2_dg2.snap;
                                        2014-04-23 15:20:00 statspack_dg2_dg2.snap;

SQL> 

SQL> select job,next_date,what from user_jobs;

       JOB NEXT_DATE           WHAT
---------- ------------------- ------------------------------------------------------------
        23 2014-04-23 16:00:00 statspack_dg2_dg2.snap;
        24 2014-04-23 15:20:00 statspack_dg2_dg2.snap;

SQL> exec dbms_job.remove('23');

PL/SQL procedure successfully completed.

SQL> select job,next_date,what from user_jobs;

       JOB NEXT_DATE           WHAT
---------- ------------------- ------------------------------------------------------------
        24 2014-04-23 15:20:00 statspack_dg2_dg2.snap;

SQL>

然后再次查看报告：

SQL>

SQL> conn STDBYPERF/Amy Connected. SQL> @ /rdbms/admin/sbreport.sql

Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name Instance Name ------------------------------ ---------------- dg2 dg2

Enter the DATABASE UNIQUE NAME of the standby database to report Enter value for db_unique_name: dg2 You entered: dg2

Enter the INSTANCE NAME of the standby database instance to report Enter value for inst_name: dg2 You entered: dg2

Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap Instance Snap Id Snap Started Level Comment ------------ --------- ----------------- ----- -------------------- dg2 1 23 Apr 2014 14:36 5 2 23 Apr 2014 14:46 5 11 23 Apr 2014 15:20 5 12 23 Apr 2014 15:25 5 13 23 Apr 2014 15:30 5 14 23 Apr 2014 15:35 5 15 23 Apr 2014 15:40 5

Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:

可以看到job执行正常。

至此完成。

通过statspack诊断物理dg数据库性能方法(九)