資訊專欄INFORMATION COLUMN
一. SQL Performance Analyzer（SPA）簡(jiǎn)介
二. SPA測(cè)試范圍和目標(biāo)
測(cè)試范圍：
本次計(jì)劃采集的兩套庫(kù)分別為XXA庫(kù)和XXB庫(kù)。因?yàn)槭请p節(jié)點(diǎn)的RAC環(huán)境，需要在兩個(gè)實(shí)例里面均做采集。
測(cè)試目標(biāo)：
需要盡可能多的捕捉生產(chǎn)環(huán)境的SQL語(yǔ)句，并對(duì)關(guān)鍵業(yè)務(wù)、AWR實(shí)現(xiàn)完整的覆蓋。這里我們將計(jì)劃對(duì)游標(biāo)緩存和AWR歷史資料庫(kù)進(jìn)行雙重的采集。
三. SPA采集：捕獲生產(chǎn)SQL負(fù)載
對(duì)于SPA采集、需要先建立環(huán)境，然后采用游標(biāo)采集、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庫(kù)分別創(chuàng)建SPA用戶。
1）創(chuàng)建spa目錄，用戶部署spa采集腳本
cd /oracle
mkdir spa
--a庫(kù)
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庫(kù)與a庫(kù)方式一致，命名有差別
首先確定需要采集的SPA用戶，確認(rèn)好后，查詢下v$sql中對(duì)應(yīng)的sql_id分布，對(duì)于sql_id占5W的+的要多帶帶采集，否則很慢，如下DBAOPER1用戶需要多帶帶采集，其它用戶放在一起采集。
--a庫(kù) 26個(gè)用戶
select PARSING_SCHEMA_NAME,count(*)
from v$sql where PARSING_SCHEMA_NAME in (…省略
)
group by PARSING_SCHEMA_NAME;
--b庫(kù) 28個(gè)用戶
select PARSING_SCHEMA_NAME,count(distinct sql_id),count(*)
from v$sql where PARSING_SCHEMA_NAME in (…省略
)
group by PARSING_SCHEMA_NAME;
--a庫(kù)26個(gè)用戶。集中在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庫(kù)28個(gè)用戶，和a庫(kù)分布一樣
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
由于庫(kù)中的v$sql存放的數(shù)據(jù)量較大，有15w+,多的時(shí)候超過(guò)20w條，直接采集耗時(shí)長(zhǎng)，而且重復(fù)類型的SQL_ID較多（字面量不同），游標(biāo)采集需要可以最大限度的幫助我們采集到更多的SQL語(yǔ)句。為了保證采集到更多的SQL，我們需要進(jìn)行一個(gè)長(zhǎng)期的捕捉，采用不間斷捕獲。采集的過(guò)程中可能因?yàn)橛衛(wèi)iteral sql，這會(huì)導(dǎo)致我們的SQLSET的結(jié)果集非常大，因?yàn)橄嚓P(guān)的表涉及到一些CLOB字段，如果結(jié)果集過(guò)大的話，將導(dǎo)致轉(zhuǎn)換成中間表非常的慢。轉(zhuǎn)換到一半因?yàn)閁NDO不夠大，還還會(huì)導(dǎo)致出現(xiàn)ORA-01555錯(cuò)誤。為了解決這個(gè)問(wèn)題，建議在采集的過(guò)程中實(shí)施過(guò)濾。對(duì)于采集腳本可以編寫shell腳本后臺(tái)執(zhí)行，每個(gè)節(jié)點(diǎn)均需采集，設(shè)置不同的sqlset,最后按庫(kù)合并。
以a庫(kù)節(jié)點(diǎn)1為例，采集數(shù)據(jù)放到sqlseta1_tab1中。
主要通過(guò)buffer_gets,elapsed_time等構(gòu)造查詢條件，并且通過(guò)rownum<5000限制在5000條，5000條是個(gè)大概數(shù)據(jù)，對(duì)v$sql有20w條sql的情況，查詢5000條耗時(shí)2-5分鐘，第一次采集約耗時(shí)1小時(shí)左右。對(duì)于buffer_gets,elapsed_time,disk_reads等條件的選擇，可以通過(guò)v$sql查看max(buffer_gets),max(elapsed_time),min(buffer_gets),min(elapsed_time)以及求平均avg,然后取平均值和最大值測(cè)試，一般條件選出的數(shù)據(jù)在5000行之內(nèi)，如果超過(guò)5000行，再加其他條件拆分，比如executions。
第一個(gè)采集腳本可以采集多次，但是第一次采集完畢后，需要建立剔重表
（每個(gè)節(jié)點(diǎn)1個(gè)）：
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;
直到第一個(gè)SQLSET采集完畢，20w條左右。
以下sql腳本是：sqlseta1_tab1.sql，可以編寫對(duì)應(yīng)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用戶采集，因?yàn)楸容^少，<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;
/
等第一個(gè)sqlset采集完畢后，采用過(guò)濾采集，需要建立控制表，控制SPA采集是否啟動(dòng)。并且采用循環(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開(kāi)始
控制表控制SPA是否采集
選擇spaqc_a1spaqc_a sts_A1_0830等作為已經(jīng)采集過(guò)的sqlset，不需要再采集，進(jìn)行過(guò)濾。
每個(gè)sqlset容納20w條SQL，如果達(dá)到20w條，放到下1個(gè)sqlset中。
因?yàn)檠h(huán)采集，每次采集500條（才開(kāi)始可以設(shè)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天前到當(dāng)前日期的數(shù)據(jù)，只需要根據(jù)dba_hist_snapshot查詢開(kāi)始和結(jié)束snap_id即可。只關(guān)注DBAOPER1,ZWOPTADM兩個(gè)用戶，其它用戶SQL較少，也可以全部取。
