oracle SPA 效能分析案例

hurp_oracle發表於2015-07-17
---建立中轉表


begin
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET3_TAB',
schema_name => 'SPA',
tablespace_name => 'TBS_IDX_ORDER_04');
END;


-------------將最佳化集打包到stgtab表SQLSE3_TAB裡面

        ID NAME                           OWNER                          DESCRIPTION                              CREATED      LAST_MODIFIE STATEMENT_COUNT
---------- ------------------------------ ------------------------------ ---------------------------------------- ------------ ------------ ---------------
         2 sql_test                       SPA                            11g spa test                             18-APR-14    26-APR-14             720887
        10 maya3                          SPA                                                                     06-MAY-14    30-MAY-14             354215
        11 maya4                          SPA                                                                     06-MAY-14    30-MAY-14             259433
         6 cza1                           SPA                                                                     29-APR-14    02-MAY-14             416895
         7 cza2                           SPA                                                                     29-APR-14    02-MAY-14             369717
        14 10GCRMA1_MAY30                 SPA                            SPA MAY30 FULL GATHER                    29-MAY-14    03-JUN-14             216053
        16 10GCRMA1_JUNE3                 SPA                            SPA JUNE3 FULL GATHER                    03-JUN-14    07-JUN-14             153671
        15 10GCRMA2_MAY30                 SPA                            SPA MAY30 FULL GATHER                    29-MAY-14    07-JUN-14             128351
        17 10GCRMA2_JUNE3                 SPA                            SPA JUNE3 FULL GATHER                    03-JUN-14    03-JUN-14              80039
         5 sql_test2                      SPA                                                                     19-APR-14    26-APR-14             761752
         8 maya1                          SPA                                                                     30-APR-14    07-MAY-14             592194


        ID NAME                           OWNER                          DESCRIPTION                              CREATED      LAST_MODIFIE STATEMENT_COUNT
---------- ------------------------------ ------------------------------ ---------------------------------------- ------------ ------------ ---------------
         9 maya2                          SPA                                                                     30-APR-14    07-MAY-14             599695


12 rows selected.






BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'mayb1',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'mayb2',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '10GCRMB1_MAY30',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '10GCRMB2_MAY30',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/








oracle@w25k01db$more tail -f spa_pack.sh.log
tail: No such file or directory
-f: No such file or directory
::::::::::::::
spa_pack.sh.log
::::::::::::::


SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jun 7 12:20:27 2014


Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL>   2    3    4    5    6    7  BEGIN
*
ERROR at line 1:
ORA-06512: at line 1
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5032
ORA-06512: at line 2






SQL> SQL>   2    3    4    5    6    7  BEGIN
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1011 with name
"_SYSSMU1011$" too small
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5032
ORA-06512: at line 2




SQL> SQL>   2    3    4    5    6    7  
PL/SQL procedure successfully completed.


SQL> SQL>   2    3    4    5    6    7  
PL/SQL procedure successfully completed.


SQL> SQL>   2    3    4    5    6    7  BEGIN
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1197 with name
"_SYSSMU1197$" too small
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5032
ORA-06512: at line 2




SQL> SQL>   2    3    4    5    6    7  BEGIN
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1197 with name
"_SYSSMU1197$" too small
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5032
ORA-06512: at line 2




SQL> SQL>   2    3    4    5    6    7  BEGIN
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 717 with name
"_SYSSMU717$" too small
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5032
ORA-06512: at line 2




SQL> SQL>   2    3    4    5    6    7  
PL/SQL procedure successfully completed.


SQL> SQL>   2    3    4    5    6    7  
PL/SQL procedure successfully completed.














BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'mayb3',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'mayb4',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '10GCRMB1_MAY30',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '10GCRMB2_MAY30',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '10GCRMB1_JUNE3',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => '10GCRMB2_JUNE3',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


-------------匯出匯入資料到測試系統
exp tables=spa.SQLSET3_TAB file=/arch01/spa_sqlset3_tab.dmp log=/arch01/spa_sqlset3_tab.log direct=y;




export NLS_LANG=American_America.zhs16gbk
imp  fromuser=spa touser=spa file=/oradata04_emc/spa_sqlset3_tab.dmp feedback=100






SQL> select distinct name from SPA.SQLSET3_TAB;


NAME
------------------------------
mayfull


SQL> select count(*) from SPA.SQLSET3_TAB;




  COUNT(*)
----------
   2384007




轉換成中轉表之後,我們可以再做一次去除重複的操作。當然,你也可以根據module來刪除一些不必要的遊標。




delete from SPA.SQLSET3_TAB where MODULE='PL/SQL Developer';
2665 rows deleted.


SQL> commit;


Commit complete.




SQL> delete from SPA.SQLSET3_TAB a where rowid !=(select max(rowid)  from SQLSET3_TAB b where a.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATURE) and a.FORCE_MATCHING_SIGNATURE<>0;




2136320 rows deleted.


SQL> SQL> 
SQL> 
SQL> 
SQL> 
SQL> commit;


Commit complete.


SQL> select count(*) from SPA.SQLSET3_TAB ;


  COUNT(*)
----------
    245022








18:53:46 SQL> select distinct name from spa.SQLSET3_TAB;


NAME
------------------------------
10GCRMB1_JUNE3
mayb3
mayb2
10GCRMB2_JUNE3
mayb4
10GCRMB2_MAY30
mayb1
10GCRMB1_MAY30


8 rows selected.
update SPA.SQLSET3_TAB set name='mayfull';




------------匯入匯出統計資訊
--wcrma 
exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'STAT_TABLE',TBLSPACE=>'TBS_IDX_ORDER_04');  
exec dbms_stats.export_database_stats(stattab=>'STAT_TABLE',statown=>'SYS');     




-wcrmb-
exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'STAT_TABLE',TBLSPACE=>'TBS_IDX_ORDER_04');  
exec dbms_stats.export_database_stats(stattab=>'STAT_TABLE',statown=>'SYS');  


-----------遷移統計資訊
-exp/imp
exp tables=sys.stat_table file=stat_table.dmp log=stat_table.log  
imp tables=stat_table fromuser=sys touser=sys file=stat_table.dmp log=stat_table.log IGNORE=y; 
imp tables=stat_table fromuser=sys touser=sys file=stat_table.dmp log=stat_table.log IGNORE=y;
-----------升級11g統計資訊表
-11g crma-
exec dbms_stats.upgrade_stat_table(ownname=>'SYS',stattab=>'stat_table');
-11g crmb
exec dbms_stats.upgrade_stat_table(ownname=>'SYS',stattab=>'stat_table');


----------刪除11G的統計資訊
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DATACLSADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'EXTDCMS');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'MTCUST');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'CAPSEDATA');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DMCSADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'CAPESADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBDICTADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'AAAA');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBSYNOPR');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBCUSTADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'UNIAGENT');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'BILLDEV');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'AUTOGRANT');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBDCTOPR');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'EXTBILL');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBORDERADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBCUSTOPR');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBMARKETADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBWEBOPR');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'EXTBI');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBACCADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBPRODADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'YWDH');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBMARKADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBOBOSSOPR');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'HFAUDIT');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBCNNTADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBHISADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'TMTCUST');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'PBOSSJS');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBPRODMNG');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DBEMERGADM');
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'TIVOLI');  
exec dbms_stats.delete_SCHEMA_STATS(OWNNAME=>'DB');


-----------------匯入統計資訊
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DATACLSADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'EXTDCMS',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'MTCUST',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'CAPSEDATA',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DMCSADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'CAPESADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBDICTADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'AAAA',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBSYNOPR',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBCUSTADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'UNIAGENT',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'BILLDEV',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'AUTOGRANT',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBDCTOPR',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'EXTBILL',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBORDERADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBCUSTOPR',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBMARKETADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBWEBOPR',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'EXTBI',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBACCADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBPRODADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'YWDH',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBMARKADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBOBOSSOPR',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'HFAUDIT',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBCNNTADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBHISADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'TMTCUST',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'PBOSSJS',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBPRODMNG',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'DBEMERGADM',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');
exec dbms_stats.IMPORT_SCHEMA_STATS(OWNNAME=>'TIVOLI',STATTAB=>'STAT_TABLE',STATOWN=>'SYS');   


-----------------確認
select table_name,NUM_ROWS,last_analyzed from dba_tables where owner='DATACLSADM';
select table_name,NUM_ROWS,last_analyzed from dba_tables where owner='EXTDCMS';
select table_name,NUM_ROWS,last_analyzed from dba_tables where owner='CAPSEDATA';
select table_name,num_rows,last_analyzed from dba_tables where owner='DMCSADM';
select table_name,num_rows,last_analyzed from dba_tables where owner='CAPESADM';
select table_name,num_rows,last_analyzed from dba_tables where owner='AAAA';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBSYNOPR';
select table_name,num_rows,last_analyzed from dba_tables where owner='UNIAGENT';
select table_name,num_rows,last_analyzed from dba_tables where owner='BILLDEV';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBDCTOPR';
select table_name,num_rows,last_analyzed from dba_tables where owner='EXTBILL';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBCUSTOPR';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBWEBOPR';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBACCADM';
select table_name,num_rows,last_analyzed from dba_tables where owner='YWDH';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBOBOSSOPR';
select table_name,num_rows,last_analyzed from dba_tables where owner='HFAUDIT';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBCNNTADM';
select table_name,num_rows,last_analyzed from dba_tables where owner='TMTCUST';
select table_name,num_rows,last_analyzed from dba_tables where owner='PBOSSJS';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBPRODMNG';
select table_name,num_rows,last_analyzed from dba_tables where owner='DBEMERGADM';
select table_name,num_rows,last_analyzed from dba_tables where owner='TIVOLI';












-------------建立sqlset
SQL> connect spa/spa
Connected.
SQL> exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'mayfull');
PL/SQL procedure successfully completed.




-------------unpack到sqlset
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'mayfull',
sqlset_owner => 'SPA',
replace => TRUE,
staging_table_name => 'SQLSET3_TAB',
staging_schema_owner => 'SPA');
END;
/


PL/SQL procedure successfully completed.


Elapsed: 00:30:19.19




-------------新建SPA任務




var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'mayfull';
exec :tname := 'SPA_TEST';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);


exec dbms_sqlpa.drop_analysis_task(task_name=>'SPA_TEST');




var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'mayfull';
exec :tname := 'SPA_TEST06';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);




-------------生成10g的trail
alter session set events='31156 trace name context forever, level 0x400';


begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST6',
execution_type => 'CONVERT SQLSET',
execution_name => 'CONVERT_10G');
end;
/




-------------清空shared pool和buffer cache
alter system flush shared_pool;
alter system flush BUFFER_CACHE;




-------------生成11g的trail
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST6',
execution_type => 'TEST EXECUTE',
execution_name => 'EXEC_11G');
end;
/






alter session set events='31156 trace name context forever, level 0x400';


begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST06',
execution_type => 'CONVERT SQLSET',
execution_name => 'CONVERT_10G_06');
end;
/


alter system flush shared_pool;
alter system flush BUFFER_CACHE;


begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST06',
execution_type => 'TEST EXECUTE',
execution_name => 'EXEC_11G_06');
end;
/










----檢視執行情況
set lines 200
col owner for a10
col status_message for a30
col error_message for a30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 
select owner,task_id,execution_name,EXECUTION_LAST_MODIFIED,EXECUTION_END,STATUS,STATUS_MESSAGE,ERROR_MESSAGE from dba_advisor_executions where task_name='SPA_TEST';




SQL> select owner,task_id,task_name,advisor_name,created,last_modified,LAST_EXECUTION,EXECUTION_START,EXECUTION_END,STATUS,PCT_COMPLETION_TIME,PROGRESS_METRIC,RECOMMENDATION_COUNT,status# from dba_advisor_tasks where advisor_name='SQL Performance Analyzer';


OWNER                             TASK_ID TASK_NAME                      ADVISOR_NAME                   CREATED      LAST_MODIFIE LAST_EXECUTION                 EXECUTION_ST EXECUTION_EN STATUS         PCT_COMPLETION_TIME PROGRESS_METRIC RECOMMENDATION_COUNT    STATUS#
------------------------------ ---------- ------------------------------ ------------------------------ ------------ ------------ ------------------------------ ------------ ------------ -----------    ------------------- --------------- -------------------- ----------


SPA                                   550 SPA_TEST                       SQL Performance Analyzer       09-JUN-14    11-JUN-14    EXEC_11G                       09-JUN-14                 EXECUTING                    0               0                    0          2
                  
   
SELECT TASK_NAME,EXECUTION_NAME,EXECUTION_TYPE,SQL_ID,PLAN_HASH_VALUE,ROWS_PROCESSED,EXECUTIONS FROM DBA_ADVISOR_SQLSTATS WHERE TASK_NAME='SPA_TEST';
select distinct executions_name from dba_advisor_sqlstats where task_name='SPA_TEST';   




SELECT COUNT(*) FROM dba_advisor_sqlstats where task_name='SPA_TEST' ;




SQL> select SOFAR,TOTALWORK from v$advisor_progress;


     SOFAR  TOTALWORK
---------- ----------
    149929     181006
    
    


SQL> DESC USER_ADVISOR_FINDINGS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TASK_ID                                   NOT NULL NUMBER
 TASK_NAME                                          VARCHAR2(30)
 EXECUTION_NAME                                     VARCHAR2(30)
 FINDING_ID                                NOT NULL NUMBER
 FINDING_NAME                                       VARCHAR2(4000)
 TYPE                                               VARCHAR2(11)
 TYPE_ID                                   NOT NULL NUMBER
 PARENT                                    NOT NULL NUMBER
 OBJECT_ID                                          NUMBER
 IMPACT_TYPE                                        VARCHAR2(4000)
 IMPACT                                             NUMBER
 MESSAGE                                            VARCHAR2(4000)
 MORE_INFO                                          VARCHAR2(4000)
 FILTERED                                           VARCHAR2(1)
 FLAGS                                              NUMBER
 


SQL> select MESSAGE,COUNT(*) FROM DBA_ADVISOR_FINDINGS WHERE TYPE='ERROR' GROUP BY MESSAGE ORDER BY 2;


MESSAGE                                                                                                           COUNT(*)
---------------------------------------------------------------------------------------------------- ---------------------
ORA-08004: sequence SEQ_UR_UNI_VPMNNO22.NEXTVAL exceeds MAXVALUE and cannot be instantiated                              1
ORA-00904: "GETUSERPASSWD": invalid identifier                                                                           1
ORA-02019: connection description for remote database not found                                                          1
Error in execution 'EXEC_11G': ORA-02019: connection description for remote database not found                           2
Error in execution 'EXEC_11G': ORA-00904: "GETUSERPASSWD": invalid identifier                                            2
Error in execution 'EXEC_11G': ORA-08004: sequence SEQ_UR_UNI_VPMNNO22.NEXTVAL exceeds MAXVALUE and                      2
cannot be instantiated


ORA-01722: invalid number                                                                                                4
Error in execution 'EXEC_11G': ORA-01722: invalid number                                                                 8
ORA-01732: data manipulation operation not legal on this view                                                           12
Error in execution 'EXEC_11G': ORA-01732: data manipulation operation not legal on this view                            24
ORA-02289: sequence does not exist                                                                                      47
ORA-01843: not a valid month                                                                                            56
Error in execution 'EXEC_11G': ORA-02289: sequence does not exist                                                       94
Error in execution 'EXEC_11G': ORA-01843: not a valid month                                                            112
ORA-00907: missing right parenthesis                                                                                   275
Error in execution 'EXEC_11G': ORA-00907: missing right parenthesis                                                    550
ORA-01410: invalid ROWID                                                                                              4958
Error in execution 'EXEC_11G': ORA-01410: invalid ROWID                                                               9916
ORA-00942: table or view does not exist                                                                              45939
Error in execution 'EXEC_11G': ORA-00942: table or view does not exist                                               91878


20 rows selected.
20


SQL> SELECT COUNT(*) FROM SPA.SQLSET3_TAB;


  COUNT(*)
----------
    207990


 
select execution_name,FINDING_ID,FINDING_NAME,PARENT,MESSAGE,FLAGS,OBJECT_ID FROM DBA_ADVISOR_FINDINGS WHERE TYPE='ERROR' AND message like '%ORA-00942%' and rownum<20;
SQL> select count(*)  FROM DBA_ADVISOR_FINDINGS WHERE TYPE='ERROR' AND message like '%ORA-00942%';


  COUNT(*)
----------
    137817
    
    


SQL> with obj_id as (select distinct object_id from DBA_ADVISOR_FINDINGS WHERE TYPE='ERROR' AND message like '%ORA-01555%'),    
  2  dis_sql_id as (select distinct sql_id from dba_advisor_sqlstats where object_id in (select object_id from obj_id))
  3  select count(*)  from SPA.SQLSET3_TAB where sql_id in (select sql_id from dis_sql_id);


  COUNT(*)
----------
     52928
         
select aa.* from (    
with obj_id as (select distinct object_id from DBA_ADVISOR_FINDINGS WHERE TYPE='ERROR' AND message like '%ORA-00942%'),    
dis_sql_id as (select distinct sql_id from dba_advisor_sqlstats where object_id in (select object_id from obj_id)) 
select * from SPA.SQLSET3_TAB where sql_id in (select sql_id from dis_sql_id)) aa ;








create table spa.sqlset_erorr tablespace TBS_IDX_ORDER_04 as select aa.* from (    
with obj_id as (select distinct object_id from DBA_ADVISOR_FINDINGS WHERE TYPE='ERROR' AND message like '%ORA-00942%'),    
dis_sql_id as (select distinct sql_id from dba_advisor_sqlstats where object_id in (select object_id from obj_id)) 
select * from SPA.SQLSET3_TAB where sql_id in (select sql_id from dis_sql_id)) aa ;






create table spa.sqlset_erorr(
 NAME,
 OWNER,
 DESCRIPTION,
 SQL_ID,
 FORCE_MATCHING_SIGNATURE,
 SQL_TEXT,
 PARSING_SCHEMA_NAME,
 BIND_DATA,
 BIND_LIST,
 MODULE,
 ACTION,
 ELAPSED_TIME,
 CPU_TIME,
 BUFFER_GETS,
 DISK_READS,
 DIRECT_WRITES,
 ROWS_PROCESSED,
 FETCHES,
 EXECUTIONS,
 END_OF_FETCH_COUNT,
 OPTIMIZER_COST,
 OPTIMIZER_ENV,
 PRIORITY,
 COMMAND_TYPE,
 FIRST_LOAD_TIME,
 STAT_PERIOD,
 ACTIVE_STAT_PERIOD,
 OTHER,
 PLAN_HASH_VALUE,
 PLAN,
 SPARE1,
 SPARE2,
 SPARE3,
 SPARE4)
 tablespace TBS_IDX_ORDER_04  as
select NAME,OWNER,DESCRIPTION,SQL_ID,FORCE_MATCHING_SIGNATURE,SQL_TEXT,PARSING_SCHEMA_NAME,BIND_DATA,BIND_LIST,MODULE,ACTION,ELAPSED_TIME,CPU_TIME,BUFFER_GETS,DISK_READS,DIRECT_WRITES,ROWS_PROCESSED,FETCHES,EXECUTIONS,END_OF_FETCH_COUNT,OPTIMIZER_COST,OPTIMIZER_ENV,PRIORITY,COMMAND_TYPE,FIRST_LOAD_TIME,STAT_PERIOD,ACTIVE_STAT_PERIOD,OTHER,PLAN_HASH_VALUE,PLAN,SPARE1,SPARE2,SPARE3,SPARE4 from 
SPA.SQLSET3_TAB st where st.sql_id 
in (select distinct das.sql_id from dba_advisor_sqlstats das where das.object_id in
 (select distinct daf.object_id from DBA_ADVISOR_FINDINGS daf WHERE daf.TYPE='ERROR' AND daf.message like '%ORA-00942%'));
 
 create table spa.sqlset_erorr  nested table projs store as nested_projs tablespace TBS_IDX_ORDER_04  as select NAME,OWNER,DESCRIPTION,SQL_ID,FORCE_MATCHING_SIGNATURE,SQL_TEXT,PARSING_SCHEMA_NAME,BIND_DATA,BIND_LIST,MODULE,ACTION,ELAPSED_TIME,CPU_TIME,BUFFER_GETS,DISK_READS,DIRECT_WRITES,ROWS_PROCESSED,FETCHES,EXECUTIONS,END_OF_FETCH_COUNT,OPTIMIZER_COST,OPTIMIZER_ENV,PRIORITY,COMMAND_TYPE,FIRST_LOAD_TIME,STAT_PERIOD,ACTIVE_STAT_PERIOD,OTHER,PLAN_HASH_VALUE,PLAN,SPARE1,SPARE2,SPARE3,SPARE4 from 
SPA.SQLSET3_TAB;
SPA




create table spa.sqlset_error(BIND_LIST SPA.SQLSET3_TAB ) nested table hobby store as SQLSET3_TAB
 tablespace TBS_IDX_ORDER_04 as select * from SPA.SQLSET3_TAB nested table hobby store as SQLSET3_TAB;;








with obj_id as (select distinct object_id from DBA_ADVISOR_FINDINGS WHERE task_name='SPA_TEST06' AND TYPE='ERROR' AND message like 'ORA-01732%')   
select distinct sql_id from dba_advisor_sqlstats where object_id in (select object_id from obj_id);












SQL> DESC DBA_ADVISOR_SQLSTATS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TASK_NAME                                          VARCHAR2(30)
 TASK_ID                                   NOT NULL NUMBER(38)
 EXECUTION_NAME                            NOT NULL VARCHAR2(30)
 EXECUTION_TYPE                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER(38)
 PLAN_ID                                   NOT NULL NUMBER
 SQL_ID                                    NOT NULL VARCHAR2(13)
 PLAN_HASH_VALUE                           NOT NULL NUMBER
 ATTR1                                              NUMBER
 PARSE_TIME                                         NUMBER
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 USER_IO_TIME                                       NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 PHYSICAL_READ_REQUESTS                             NUMBER
 PHYSICAL_WRITE_REQUESTS                            NUMBER
 PHYSICAL_READ_BYTES                                NUMBER
 PHYSICAL_WRITE_BYTES                               NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 EXECUTIONS                                         NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 OPTIMIZER_COST                                     NUMBER
 OTHER                                              CLOB
 TESTEXEC_TOTAL_EXECS                               NUMBER
 IO_INTERCONNECT_BYTES                              NUMBER
 TESTEXEC_FIRST_EXEC_IGNORED                        VARCHAR2(1)    




SQL> DESC DBA_SQLSET_STATEMENTS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SQLSET_NAME                               NOT NULL VARCHAR2(30)
 SQLSET_OWNER                                       VARCHAR2(30)
 SQLSET_ID                                 NOT NULL NUMBER
 SQL_ID                                    NOT NULL VARCHAR2(13)
 FORCE_MATCHING_SIGNATURE                  NOT NULL NUMBER
 SQL_TEXT                                           CLOB
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 PARSING_SCHEMA_ID                                  NUMBER
 PLAN_HASH_VALUE                           NOT NULL NUMBER
 BIND_DATA                                          RAW(2000)
 BINDS_CAPTURED                                     CHAR(1)
 MODULE                                             VARCHAR2(64)
 ACTION                                             VARCHAR2(64)
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 EXECUTIONS                                         NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 OPTIMIZER_COST                                     NUMBER
 OPTIMIZER_ENV                                      RAW(2000)
 PRIORITY                                           NUMBER
 COMMAND_TYPE                                       NUMBER
 FIRST_LOAD_TIME                                    VARCHAR2(19)
 STAT_PERIOD                                        NUMBER
 ACTIVE_STAT_PERIOD                                 NUMBER
 OTHER                                              CLOB
 PLAN_TIMESTAMP                                     DATE
 SQL_SEQ                                   NOT NULL NUMBER




set lines 200\r 
set pagesize 999\r 
SELECT D.TABLESPACE_NAME,\r 
SPACE "SUM_SPACE(M)",\r 
BLOCKS SUM_BLOCKS,\r 
USED_SPACE "USED_SPACE(M)",\r 
ROUND(NVL(USED_SPACE,0)\r 
/SPACE*100,2) "USED_RATE(%)",\r 
NVL(FREE_SPACE,0)\r 
"FREE_SPACE(M)"\r 
FROM\r 
(SELECT TABLESPACE_NAME,\r 
ROUND(SUM(BYTES)/\r 
(1024*1024),2) SPACE,\r 
SUM(BLOCKS) BLOCKS\r 
FROM DBA_TEMP_FILES\r 
GROUP BY TABLESPACE_NAME) D,\r 
(SELECT TABLESPACE_NAME,\r 
ROUND(SUM(BYTES_USED)/\r 
(1024*1024),2) USED_SPACE,\r 
ROUND(SUM(BYTES_FREE)/\r 
(1024*1024),2) FREE_SPACE\r 
FROM V$TEMP_SPACE_HEADER\r 
GROUP BY TABLESPACE_NAME) F\r 
WHERE D.TABLESPACE_NAME\r 
= F.TABLESPACE_NAME(+); 


------中斷SPA


begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'TEST EXECUTE',
execution_name => 'EXEC_11G');
end;
/




begin 
dbms_sqlpa.interrupt_analysis_task(task_name => 'SPA_TEST');
end;
/




-------假如中斷了,可以透過如下操作從新開啟


begin
DBMS_SQLPA.RESUME_ANALYSIS_TASK(task_name => 'SPA_TEST06');
end;
/




---------執行比較任務------------------------------------------------------------------------------------------------------


-----------從elapsed_time來進行比較
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST06',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G_06', 'execution_name2', 'EXEC_11G_06', 'comparison_metric', 'elapsed_time') );
end;
/






-------------從cpu_time來進行比較
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST06',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G_06', 'execution_name2', 'EXEC_11G_06', 'comparison_metric', 'CPU_TIME') );
end;
/
-------------從buffer_gets來進行比較
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST06',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G_06', 'execution_name2', 'EXEC_11G_06', 'comparison_metric', 'BUFFER_GETS') );
end;
/
-------------生成SPA報告




alter session set events='31156 trace name context forever, level 0x400';
set trimspool on
set trim on
set pages 0
set long 999999999
set linesize 1000




spool spa_report_elapsed_time_top500.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST06', 'HTML', 'ALL','ALL', top_sql=>500,execution_name=>'Compare_elapsed_time') FROM dual;
spool off;


spool spa_report_CPU_time_top500.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST06', 'HTML', 'ALL','ALL', top_sql=>500,execution_name=>'Compare_CPU_time') FROM dual;
spool off;


spool spa_report_buffer_time_top500.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST06','HTML','ALL','ALL',top_sql=>500,execution_name=>'Compare_BUFFER_GETS_time') FROM dual;
spool off;


spool spa_report_errors_top.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST06', 'HTML', 'errors','summary') FROM dual;
spool off;


spool spa_report_unsupport_top.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST06', 'HTML', 'unsupported','all') FROM dual;
spool off;
/






[/home/oracle/hurp]more sofar.sh
#!/bin/sh


sqlplus -s "/as sysdba" <<EOF
set lines 132 pages 1000
select sofar,totalwork, sofar/totalwork from v\$advisor_progress;
col message for a80
select message,count(*) from dba_advisor_findings where task_name='SPA_TEST06' and type='ERROR' group by message;
quit;
EOF

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29446986/viewspace-1736043/,如需轉載,請註明出處,否則將追究法律責任。

相關文章