資訊專欄INFORMATION COLUMN
一. SQL Performance Analyzer（SPA）簡介
二. SPA測試范圍和目標
測試范圍：
本次計劃采集的兩套庫分別為XXA庫和XXB庫。因為是雙節(jié)點的RAC環(huán)境，需要在兩個實例里面均做采集。
測試目標：
需要盡可能多的捕捉生產(chǎn)環(huán)境的SQL語句，并對關(guān)鍵業(yè)務、AWR實現(xiàn)完整的覆蓋。這里我們將計劃對游標緩存和AWR歷史資料庫進行雙重的采集。
三. SPA采集：捕獲生產(chǎn)SQL負載
對于SPA采集、需要先建立環(huán)境，然后采用游標采集、AWR采集等。
SPA采集環(huán)境包括創(chuàng)建SPA采集用戶并授權(quán)、創(chuàng)建腳本部署目錄、創(chuàng)建SQL Tuning Sets（STS）。
create user spa identified by spa default tablespace sysaux;
grant connect ,resource to spa;
grant ADMINISTER SQL TUNING SET to spa;
grant execute on dbms_sqltune to spa;
grant select any dictionary to spa;
需要在A,B庫分別創(chuàng)建SPA用戶。
1）創(chuàng)建spa目錄，用戶部署spa采集腳本
cd /oracle
mkdir spa
--a庫
exec dbms_sqltune.create_sqlset(sqlseta1_tab1,sqlset_owner=>SPA);
exec dbms_sqltune.create_sqlset(sqlseta1_tab2,sqlset_owner=>SPA);
…
exec dbms_sqltune.create_sqlset(sqlseta2_tab1,sqlset_owner=>SPA);
exec dbms_sqltune.create_sqlset(sqlseta2_tab2,sqlset_owner=>SPA);
…
--b庫與a庫方式一致，命名有差別
首先確定需要采集的SPA用戶，確認好后，查詢下v$sql中對應的sql_id分布，對于sql_id占5W的+的要多帶帶采集，否則很慢，如下DBAOPER1用戶需要多帶帶采集，其它用戶放在一起采集。
--a庫 26個用戶
select PARSING_SCHEMA_NAME,count(*)
from v$sql where PARSING_SCHEMA_NAME in (…省略
)
group by PARSING_SCHEMA_NAME;
--b庫 28個用戶
select PARSING_SCHEMA_NAME,count(distinct sql_id),count(*)
from v$sql where PARSING_SCHEMA_NAME in (…省略
)
group by PARSING_SCHEMA_NAME;
--a庫26個用戶。集中在BILLING,DBAOPER1用戶下，分布如下：
PARSING_SCHEMA_NAME COUNT(*)
------------------------------------------- ----------
ADMTEST 6
BILLING 6224
ADMTEST1 2
MONITORX 3
BIDB1 15
ZWOPTADM 8
TESAGENT 1
DBAOPER1 156870 --10w條+
TEST 359
--b庫28個用戶，和a庫分布一樣
PARSING_SCHEMA_NAME COUNT(DISTINCTSQL_ID) COUNT(*)
------------------------------------------ ------------------------------------ ------------
BILLING 7473 7477
ADMTEST 9 9
ZWOPTADM 8 8
DBAOPER1 156505 156746
DBCMOPR 80 80
MONITORX 3 3
BIDB1 69 69
AUDITOR 2 2
TESAGENT 2 2
TEST 355 355
由于庫中的v$sql存放的數(shù)據(jù)量較大，有15w+,多的時候超過20w條，直接采集耗時長，而且重復類型的SQL_ID較多（字面量不同），游標采集需要可以最大限度的幫助我們采集到更多的SQL語句。為了保證采集到更多的SQL，我們需要進行一個長期的捕捉，采用不間斷捕獲。采集的過程中可能因為有l(wèi)iteral sql，這會導致我們的SQLSET的結(jié)果集非常大，因為相關(guān)的表涉及到一些CLOB字段，如果結(jié)果集過大的話，將導致轉(zhuǎn)換成中間表非常的慢。轉(zhuǎn)換到一半因為UNDO不夠大，還還會導致出現(xiàn)ORA-01555錯誤。為了解決這個問題，建議在采集的過程中實施過濾。對于采集腳本可以編寫shell腳本后臺執(zhí)行，每個節(jié)點均需采集，設置不同的sqlset,最后按庫合并。
以a庫節(jié)點1為例，采集數(shù)據(jù)放到sqlseta1_tab1中。
主要通過buffer_gets,elapsed_time等構(gòu)造查詢條件，并且通過rownum<5000限制在5000條，5000條是個大概數(shù)據(jù)，對v$sql有20w條sql的情況，查詢5000條耗時2-5分鐘，第一次采集約耗時1小時左右。對于buffer_gets,elapsed_time,disk_reads等條件的選擇，可以通過v$sql查看max(buffer_gets),max(elapsed_time),min(buffer_gets),min(elapsed_time)以及求平均avg,然后取平均值和最大值測試，一般條件選出的數(shù)據(jù)在5000行之內(nèi)，如果超過5000行，再加其他條件拆分，比如executions。
第一個采集腳本可以采集多次，但是第一次采集完畢后，需要建立剔重表
（每個節(jié)點1個）：
create table spa.spaqc_a1 as select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS;
delete from spa.spaqc_a1 where FORCE_MATCHING_SIGNATURE=0;
之后采集腳本條件改為：
and buffer_gets<=7 and elapsed_time>2000 and elapsed_time<=5000 and rownum<5000
and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spa.spaqc_a1)
后面如果還需要采集，則可以在腳本中增加：
execute immediate truncate table spa.spaqc_a1 ;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS;
直到第一個SQLSET采集完畢，20w條左右。
以下sql腳本是：sqlseta1_tab1.sql，可以編寫對應shell腳本調(diào)度，放到crontab中。
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets>150 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 1:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets>50 and buffer_gets<=150 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 2:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets>40 and buffer_gets<=50 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 3:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
…此處省略很多
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets<=7 and elapsed_time>700 and elapsed_time<=1000 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 19:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets<=7 and elapsed_time<=700 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 20:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
--非DBAOPER1用戶采集，因為比較少，<5w,放到一起
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (AAAA,BILLING…此處省略
) and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 21:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
等第一個sqlset采集完畢后，采用過濾采集，需要建立控制表，控制SPA采集是否啟動。并且采用循環(huán)采集。每5分鐘執(zhí)行一次采集。
0:exit,1:running
create table spa.sqlseta1_control(status number);
insert into spa.sqlseta1_control values(1);
grant execute on dbms_lock to spa;
從sqlseta1_tab22開始
控制表控制SPA是否采集
選擇spaqc_a1spaqc_a sts_A1_0830等作為已經(jīng)采集過的sqlset，不需要再采集，進行過濾。
每個sqlset容納20w條SQL，如果達到20w條，放到下1個sqlset中。
因為循環(huán)采集，每次采集500條（才開始可以設5000條，后續(xù)SQL采集的差不多了，可以逐步減少），并且不采集insert into values…等
可以增加其他條件，比如MODULE等，排除SQL*PLUS,PL/SQL DEVELOPER。。。
--sqlseta1_tab22
declare
v_status number;
v_cnt number;
v_sqlset_name varchar2(100) :=sqlseta1_tab22;
begin
loop
select nvl(max(status),0) into v_status from spa.sqlseta1_control;
if v_status = 0 then
exit;
end if;
execute immediate truncate table spa.spaqc_a1 ;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS;
commit;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from spa.spaqc_a a where not exists(select 1 from spa.spaqc_a1 b where a.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATURE);
commit;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from spa.STS_TAB_A_TEST1;
commit;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from spa.sts_A1_0830;
commit;
delete from spa.spaqc_a1 where FORCE_MATCHING_SIGNATURE=0;
commit;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab22;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab23;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab23;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab24;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab24;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab25;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab25;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab26;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab26;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab27;
end if;
end if;
end if;
end if;
end if;
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and rownum<500
and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spa.spaqc_a1)
and FORCE_MATCHING_SIGNATURE IS NOT NULL
and upper(sql_text) not like %INSERT%INTO%VALUES%,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => v_sqlset_name,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 1:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (AAAA,BILLING,ADMTEST1,ADMTEST…此處省略
) and rownum<500
and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spa.spaqc_a1)
and FORCE_MATCHING_SIGNATURE IS NOT NULL
and upper(sql_text) not like %INSERT%INTO%VALUES%,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => v_sqlset_name,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 2:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
sys.dbms_lock.sleep(300);
end loop;
end;
/
采集45天前到當前日期的數(shù)據(jù)，只需要根據(jù)dba_hist_snapshot查詢開始和結(jié)束snap_id即可。只關(guān)注DBAOPER1,ZWOPTADM兩個用戶，其它用戶SQL較少，也可以全部取。
