oracle SPA 效能分析案例
---建立中轉表
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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL 效能分析器（SPA）工具概覽SQL
- oracle rat spaOracle
- SQL效能優化案例分析SQL優化
- [轉] Android 效能分析案例Android
- 從案例分析如何優化前端效能優化前端
- Oracle SPA使用詳解Oracle
- Oracle效能解決的一個案例Oracle
- Oracle分析函式七——分析函式案例Oracle函式
- 偽三元表示式 效能分析案例
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- 一次ORACLE IO效能診斷案例Oracle
- 某公司oracle 效能調優診斷案例Oracle
- 資料庫Server效能問題分析案例一資料庫Server
- 【效能測試】常見的效能問題分析思路（二）案例&技巧
- 10G遷移升級到11G使用SPA 分析SQL效能例項SQL
- 【轉載】[效能分析]Oracle資料庫效能模型Oracle資料庫模型
- Oracle案例10——HWM（高水位線）效能優化Oracle優化
- 效能分析（4）- iowait 使用率過高案例AI
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- oracle日誌狀態為STALE案例分析Oracle
- 公有云(AWS)上的生產環境效能分析案例
- LGWR寫操作會導致效能全域性卡頓案例分析
- ORACLE 使用TRACE進行SQL效能分析OracleSQL
- Oracle億級大表高效刪除案例分析Oracle
- oracle 9i資料庫做spaOracle資料庫
- 案例分析
- SPA
- 效能分析（2）- 應用程式 CPU 使用率過高案例
- MySQL全面瓦解25：構建高效能索引（案例分析篇）MySql索引
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- 透過圖表分析oracle的parallel效能OracleParallel
- 通過圖表分析oracle的parallel效能OracleParallel
- ORACLE UPDATE 語句語法與效能分析Oracle
- Oracle報performing DMLDDL operation over object in bin案例分析OracleORMObject
- 批量載入效能案例