當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQL Performance Analyzer SPA常用脚本汇总

發(fā)布時(shí)間：2024/7/19 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL Performance Analyzer SPA常用脚本汇总小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

SPA常用腳本匯總

附件為一個(gè)SPA報(bào)告?spa_buffergets_summary

SQL 性能分析器?SQL Performance Analyzer SPA

Oracle Database 11g 引入了 SQL 性能分析器；使用該工具可以準(zhǔn)確地評(píng)估更改對(duì)組成工作量的 SQL 語句的影響。SQL 性能分析器可幫助預(yù)測潛在的更改對(duì) SQL 查詢工作量的性能影響。這種功能可向 DBA 提供有關(guān) SQL 語句性能的詳細(xì)信息，例如，執(zhí)行前后的統(tǒng)計(jì)信息，提高或降低性能的語句。這樣一來，您就可以執(zhí)行諸如以下操作的操作：在測試環(huán)境中進(jìn)行更改，以確定數(shù)據(jù)庫升級(jí)是否會(huì)改進(jìn)工作量性能。

11g 的新增功能

目標(biāo)用戶：DBA、QA、應(yīng)用程序開發(fā)人員

幫助預(yù)測系統(tǒng)更改對(duì) SQL 工作量響應(yīng)時(shí)間的影響

建立不同版本的 SQL 工作量性能（即 SQL 執(zhí)行計(jì)劃和執(zhí)行統(tǒng)計(jì)信息）

以串行方式執(zhí)行 SQL（不考慮并發(fā)性）

分析性能差異

提供對(duì)單個(gè) SQL 的細(xì)粒度性能分析

與 SQL 優(yōu)化指導(dǎo)集成在一起以優(yōu)化回歸

SQL 性能分析器：使用情形?
SQL 性能分析器可用于預(yù)測和防止會(huì)影響 SQL 執(zhí)行計(jì)劃結(jié)構(gòu)的任何數(shù)據(jù)庫環(huán)境更改所帶來的潛在性能問題。這些更改可以包括（但不限于）以下任何一種更改：

數(shù)據(jù)庫升級(jí)

實(shí)施優(yōu)化建議

更改方案

收集統(tǒng)計(jì)信息

更改數(shù)據(jù)庫參數(shù)

更改操作系統(tǒng)和硬件

DBA 甚至可以使用 SQL 性能分析器為最復(fù)雜的環(huán)境預(yù)測先期更改導(dǎo)致的 SQL 性能更改。例如，隨著應(yīng)用程序在開發(fā)周期中的變化，數(shù)據(jù)庫應(yīng)用程序開發(fā)人員可以測試對(duì)方案、數(shù)據(jù)庫對(duì)象和重寫應(yīng)用程序的更改，以減輕任何潛在的性能影響。
使用 SQL 性能分析器還可以比較 SQL 性能統(tǒng)計(jì)信息。

SQL 性能分析器：概要

1.? 收集 SQL：在這個(gè)階段中，將收集用于表示生產(chǎn)系統(tǒng)中的 SQL 工作量的 SQL 語句集。可以使用 SQL 優(yōu)化集或自動(dòng)工作量資料檔案庫 (AWR) 來捕獲要傳送的信息。因?yàn)?AWR 本質(zhì)上是捕獲高負(fù)載的 SQL，所以應(yīng)考慮修改默認(rèn)的 AWR 快照設(shè)置和捕獲的頂級(jí) SQL，以確保 AWR 捕獲最大數(shù)量的 SQL 語句。這可以確保捕獲更加完整的 SQL 工作量。

2.? 傳送：在這個(gè)階段中，應(yīng)將得到的工作量結(jié)果傳送到測試系統(tǒng)。從生產(chǎn)系統(tǒng)導(dǎo)出 STS，然后將 STS 導(dǎo)入到測試系統(tǒng)。

3.? 計(jì)算“之前版本”性能：在進(jìn)行任何更改之前，執(zhí)行 SQL 語句，收集評(píng)估將來的更改對(duì)工作量性能的可能影響所需的基線信息。在此階段收集的信息給出了系統(tǒng)工作量當(dāng)前狀態(tài)的一個(gè)快照。性能數(shù)據(jù)包括：

-執(zhí)行計(jì)劃（如由解釋計(jì)劃生成的計(jì)劃） -執(zhí)行統(tǒng)計(jì)信息（如由占用時(shí)間、緩沖獲取次數(shù)、磁盤讀取次數(shù)和已處理的行數(shù)組成的信息）

4. 進(jìn)行更改：獲得了之前版本數(shù)據(jù)后，可以實(shí)施計(jì)劃的更改，然后開始查看對(duì)性能的影響。

5.? 計(jì)算“之后版本”性能：在數(shù)據(jù)庫環(huán)境中進(jìn)行了更改之后才執(zhí)行此步驟。SQL 工作量的每個(gè)語句都在虛擬執(zhí)行（僅收集統(tǒng)計(jì)信息）模式下運(yùn)行，收集與步驟 3 所捕獲的信息相同的信息。

6.? 比較和分析 SQL 性能：在獲得了兩個(gè)版本的 SQL 工作量性能數(shù)據(jù)后，可以通過比較之后版本與之前版本的數(shù)據(jù)來進(jìn)行性能分析。比較的根據(jù)是執(zhí)行統(tǒng)計(jì)信息，如所用時(shí)間、CPU 時(shí)間和緩沖區(qū)獲取次數(shù)等。

7.? 優(yōu)化回歸的 SQL：在此階段中，已經(jīng)準(zhǔn)確地確認(rèn)了哪些 SQL 語句在進(jìn)行數(shù)據(jù)庫更改時(shí)可能導(dǎo)致性能問題。在此階段中可以使用任何一種數(shù)據(jù)庫工具來優(yōu)化系統(tǒng)。例如，可以對(duì)確認(rèn)的語句使用 SQL 優(yōu)化指導(dǎo)或訪問指導(dǎo)，然后實(shí)施相應(yīng)的建議。也可以使用在步驟 3 中捕獲的計(jì)劃植入 SQL 計(jì)劃管理 (SPM) 以確保計(jì)劃保持不變。在實(shí)施了任何優(yōu)化操作后，應(yīng)重復(fù)該過程來創(chuàng)建新的之后版本，然后分析性能差異以確保新的性能是可接受的。
默認(rèn)情況下SPA若涉及到DML語句則只有查詢部分Query會(huì)被執(zhí)行，但是貌似是從11.2開始可以執(zhí)行完全的DML了，需要加入?yún)?shù)EXECUTE_FULLDML，但是該參數(shù)目前有一些BUG:

Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1

Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3

By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.

執(zhí)行方法如下：

execute DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name => 'TASK_21137', -parameter => 'EXECUTE_FULLDML', -value => 'TRUE');

從cursor cache中收集tuning set, 持續(xù)12分鐘，間隔5秒鐘

begin DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'MAC_SPA'); dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name => 'MAC_SPA' , time_limit => 12*60, repeat_interval => 5); end ; /basic_filter=> q'# module like 'DWH_TEST%' and sql_text not like '%applicat%' and parsing_schema_name in ('APPS') #'basic_filter => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',==>過濾條件使用

從當(dāng)前cursor cache中匹配條件獲得SQLset ROW

SELECT sql_id, sql_text FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) ORDER BY sql_id;SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));DECLAREcur sys_refcursor; BEGINOPEN cur FORSELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;-- Process each statement (or pass cursor to load_sqlset).CLOSE cur; END; /-- create the tuning set EXEC DBMS_SQLTUNE.CREATE_SQLSET('MAC_SPA'); -- populate the tuning set from the cursor cache DECLAREcur DBMS_SQLTUNE.SQLSET_CURSOR; BEGINOPEN cur FORSELECT VALUE(P)FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000',NULL, NULL, NULL, NULL, 1, NULL,'ALL')) P;DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MAC_SPA',populate_cursor => cur);END; /

從AWR快照中加載SQLset ROW到SQL TUNING SET

DECLAREcur sys_refcursor; BEGINOPEN cur FORSELECT VALUE (P) FROM table(dbms_sqltune.select_workload_repository(4146,4161)) P;-- Process each statement (or pass cursor to load_sqlset)DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MAC_SPA',populate_cursor => cur);CLOSE cur; END; /

將SQL TUNING SET Pack到表中：

set echo on select name,statement_count from dba_sqlset;drop table maclean.pack_sqlset purge;exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET','MACLEAN');exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('MAC_SPA','SYS','PACK_SQLSET','MACLEAN');SQL> desc maclean.pack_sqlset;Name Null? Type----------------------------------------- -------- ----------------------------NAME VARCHAR2(30)OWNER VARCHAR2(30)DESCRIPTION VARCHAR2(256)SQL_ID VARCHAR2(13)FORCE_MATCHING_SIGNATURE NUMBERSQL_TEXT CLOBPARSING_SCHEMA_NAME VARCHAR2(30)BIND_DATA RAW(2000)BIND_LIST SQL_BIND_SETMODULE VARCHAR2(48)ACTION VARCHAR2(32)ELAPSED_TIME NUMBERCPU_TIME NUMBERBUFFER_GETS NUMBERDISK_READS NUMBERDIRECT_WRITES NUMBERROWS_PROCESSED NUMBERFETCHES NUMBEREXECUTIONS NUMBEREND_OF_FETCH_COUNT NUMBEROPTIMIZER_COST NUMBEROPTIMIZER_ENV RAW(1000)PRIORITY NUMBERCOMMAND_TYPE NUMBERFIRST_LOAD_TIME VARCHAR2(19)STAT_PERIOD NUMBERACTIVE_STAT_PERIOD NUMBEROTHER CLOBPLAN_HASH_VALUE NUMBERPLAN SQL_PLAN_TABLE_TYPESPARE1 NUMBERSPARE2 NUMBERSPARE3 BLOBSPARE4 CLOB

將測試對(duì)應(yīng) schema的數(shù)據(jù)和上述PACK TABLE 導(dǎo)出導(dǎo)入到目標(biāo)測試庫中：

set echo on exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('MAC_SPA','SYS',TRUE,'PACK_SQLSET','MACLEAN'); alter system flush buffer_cache; alter system flush shared_pool;

創(chuàng)建SPA任務(wù) 并運(yùn)行;

var sts_task varchar2(64); exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '10g_11g_spa',description => 'experiment for 10gR2 to 11gR2 upgrade',sqlset_name=> 'MAC_SPA');PL/SQL procedure successfully completed.var exe_task varchar2(64); exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'10g_11g_spa',execution_name=>'10g_trail',execution_type=>'CONVERT SQLSET',execution_desc=>'10g sql trail');var exe_task varchar2(64); exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'10g_11g_spa',execution_name=>'11g_trail',execution_type=>'TEST EXECUTE',execution_desc=>'11g sql trail');

執(zhí)行任務(wù)比較

比較CPU_TIME EXEC dbms_sqlpa.execute_analysis_task( -task_name => '10g_11g_spa', -execution_name => 'compare_10g_112_cpu', -execution_type => 'COMPARE PERFORMANCE', -execution_params => dbms_advisor.arglist('COMPARISON_METRIC','CPU_TIME','EXECUTION_NAME1','10g_trail','EXECUTION_NAME2','11g_trail'), -execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for CPU_TIME')/比較BUFFER_GETS EXEC dbms_sqlpa.execute_analysis_task( -task_name => '10g_11g_spa', -execution_name => 'compare_10g_112_buffergets', -execution_type => 'COMPARE PERFORMANCE', -execution_params => dbms_advisor.arglist('COMPARISON_METRIC','BUFFER_GETS','EXECUTION_NAME1','10g_trail','EXECUTION_NAME2','11g_trail'), -execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for BUFFER_GETS')/比較實(shí)際執(zhí)行時(shí)長 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_TEST', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_elapsed_time', execution_params => dbms_advisor.arglist('execution_name1', '10g_trail', 'execution_name2', '11g_trail', 'comparison_metric', 'elapsed_time') ); end; /比較物理讀begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => '10g_11g_spa', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_physical_reads0', execution_params => dbms_advisor.arglist('execution_name1', '10g_trail', 'execution_name2', '11g_trail', 'comparison_metric', 'disk_reads') ); end; /Set the comparison_metric parameter to specify an expression of execution statistics to use in the performance impact analysis. Possible values include the following metrics or any combination of them: elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.

獲得SPA報(bào)告:

set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off spool spa_report_elapsed_time.html SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual; spool off產(chǎn)生buffergets 比較report set heading off long 100000000 longchunksize 10000 echo off; set linesize 1000 trimspool on; spool buffergets_summary.html select xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa','html','typical','all',null,100,'compare_10g_112_buffergets')).getclobval(0,0) from dual; spool off產(chǎn)生errors比較report spool errors_summary.html select xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa','html','errors','summary',null,100,'11g_trail')).getclobval(0,0) from dual; spool off產(chǎn)生unsupport比較report spool unsuppor_all.html select xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa','html','unsupported','all',null,100,'11g_trail')).getclobval(0,0) from dual; spool off

execution_type
Type of the action to perform by the function. If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE parameter. Possible values are:
[TEST] EXECUTE – test-execute every SQL statement and collect its execution plans and execution statistics. The resulting plans and statistics will be stored in the advisor framework. This is default.
EXPLAIN PLAN – generate explain plan for every statement in the SQL workload. This is similar to the EXPLAIN PLAN command. The resulting plans will be stored in the advisor framework in association with the task.
COMPARE [PERFORMANCE] – analyze and compare two versions of SQL performance data. The performance data is generated by test-executing or generating explain plan of the SQL statements. Use this option when two executions of type EXPLAIN_PLAN or TEST_EXECUTE already exist in the task
CONVERT SQLSET – used to read the statistics captured in a SQL Tuning Set and model them as a task execution. This can be used when you wish to avoid executing the SQL statements because valid data for the experiment already exists in the SQL Tuning Set.

For 9i Upgrade to 10g

exec dbms_stats.gather_system_stats(gathering_mode=>'NOWORKLOAD');alter system set "_optim_peek_user_binds"=false; ==> 禁用BIND PEEK特性，該特性在10g中有exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' ); commit;9i ?/rdbms/admin/dbmssuppexec dbms_support.start_trace(binds=>TRUE, waits=> FALSE);exec dbms_support.stop_trace;exec dbms_support.start_trace_in_session(sid=>sid,serial=>ser, binds=>TRUE, waits=>FALSE);select sid,serial# from v$SESSION WHERE ... ;exec dbms_support.stop_trace_in_session(sid=>SID,serial=>ser);create table mapping_table tablespace USERS as select object_id id, owner, substr(object_name, 1, 30) namefrom dba_objectswhere object_type not in ('CONSUMER GROUP','EVALUATION CONTEXT','FUNCTION','INDEXTYPE','JAVA CLASS','JAVA DATA','JAVA RESOURCE','LIBRARY','LOB','OPERATOR','PACKAGE','PACKAGE BODY','PROCEDURE','QUEUE','RESOURCE PLAN','SYNONYM','TRIGGER','TYPE','TYPE BODY') union all select user_id id, username owner, null name from dba_users;declaremycur dbms_sqltune.sqlset_cursor; begindbms_sqltune.create_sqlset('9i_prod_wkld');open mycur forselect value(p)from table(dbms_sqltune.select_sql_trace(directory=>'SPADIR',file_name=>'%trc',mapping_table_name => 'MAPPING_TABLE',select_mode => dbms_sqltune.single_execution)) p;dbms_sqltune.load_sqlset(sqlset_name => '9i_prod_wkld',populate_cursor => mycur,commit_rows => 1000);close mycur; end; /create user spadba identified by oracle; grant dba to spadba; grant all on dbms_sqlpa to spadba;create public database link to10g connect to spadba identified by oracle using 'STRINGS';var sts_task varchar2(64); exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '9i_11g_spa1',description => 'experiment for 9i to 11gR2 upgrade',sqlset_name=> '9i_prod_wkld');var exe_task varchar2(64); exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'9i_11g_spa1',execution_name=>'9i_trail1',execution_type=>'CONVERT SQLSET',execution_desc=>'9i sql trail generated from sts');dbms_sqlpa.execute_analysis_task(task_name=>'9i_11g_spa1',execution_name=>'10g_trail1',execution_type=>'TEST EXECUTE',execution_desc=>'10g trail test',- execution_params=>dbms_advisor.arglist('DATABASE_LINK','DBLINKNAME'));select sofar,totalwork from V$ADVISOR_PROGRESS where task_id=<TID>;

轉(zhuǎn)載于:https://www.cnblogs.com/macleanoracle/archive/2013/03/18/2965448.html

總結(jié)

以上是生活随笔為你收集整理的SQL Performance Analyzer SPA常用脚本汇总的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。