国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

數據庫升級性能保障利器—SQL Performance Analyzer（上篇）

IT那活兒 / 3682人閱讀
數據庫升級性能保障利器—SQL Performance Analyzer（上篇）




一. SQL Performance Analyzer（SPA）簡介



為了保障數據庫升級后性能穩定，我們將采用Oracle性能分析器(SQL Performance Analyzer)來預測數據庫的關鍵SQL在新版本上的性能情況，以便提前發現問題并做相關性能優化。
大型業務關鍵應用程序要在響應時間、吞吐量、運行時間和可用性方面提供特定服務級別的保證。對系統的任何更改（如升級數據庫或修改配置）通常都需要進行全面的測試和驗證，然后才能在生產系統中實施這些更改。在移到生產系統之前為了保證安全，數據庫管理員(DBA) 必須讓測試系統承受與生產環境中的工作量很近似的壓力，以便分析系統級更改對整體SQL 性能的影響，并在在移到生產之前進行必要的優化。
Oracle Database 11g引入了SQL 性能分析器；使用該工具可以準確地預測系統更改對SQL 語句性能的影響。這種功能可向DBA 提供有關SQL 語句性能的詳細信息，例如，執行前后的統計信息，提高或降低性能的語句。這樣，你就在測試環境中先進行更改，以確定數據庫升級是否會影響SQL性能。
SQL 性能分析器(SPA)可用于預測和防止會影響SQL 執行計劃結構的任何數據庫環境更改所帶來的潛在性能問題。這些更改可以包括（但不限于）以下任何一種更改：
  • 數據庫升級
  • 實施優化建議
  • 更改方案
  • 收集統計信息
  • 更改數據庫參數
  • 更改操作系統和硬件
SQL 性能分析器(SPA)是11g的新功能，大致的框架如下：
先在老庫上通過各種方法生成SQL優化集(SQL Tuning Set)，然后再將優化集導入到新庫上（升級目標環境或完全一致的測試環境）,最后對每條SQL生成新老庫上的性能對比報告，從這個報告便能發現SQL性能是否改變，執行計劃有沒有變化等。SPA分析流程如下圖：




二. SPA測試范圍和目標



測試范圍：

本次計劃采集的兩套庫分別為XXA庫和XXB庫。因為是雙節點的RAC環境，需要在兩個實例里面均做采集。

測試目標：

需要盡可能多的捕捉生產環境的SQL語句，并對關鍵業務、AWR實現完整的覆蓋。這里我們將計劃對游標緩存和AWR歷史資料庫進行雙重的采集。





三. SPA采集：捕獲生產SQL負載



ORACLE使用SQL Tuning Sets（STS）來存儲和管理SQL負載，在捕獲生產SQL負載階段，主要任務就是盡可能多地采集游標中存儲的SQL以及SQL相關信息，這些信息都會存儲在STS中。STS包含SQL執行的上下文信息：
  • SQL內容、綁定變量、parsing schema
  • SQL執行計劃、運行期統計信息如executions、buffer gets等
捕獲生產SQL負載，主要采集游標緩存中的SQL，等30天采集周期完畢后，做SPA分析前，還需要采集45天AWR信息。ORACLE提供DBMS_SQLTUNE包進行采集。如下圖所示：


3.1  SPA采集準備工作

對于SPA采集、需要先建立環境，然后采用游標采集、AWR采集等。


3.2 建立SPA采集環境

SPA采集環境包括創建SPA采集用戶并授權、創建腳本部署目錄、創建SQL Tuning Sets（STS）。

創建SPA用戶并授權
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庫分別創建SPA用戶。

1）創建spa目錄，用戶部署spa采集腳本

cd /oracle
mkdir spa
在A,B庫每個節點都需要創建。
2）創建sqlset,用戶存儲采集到的SQL Tuning Sets
sts命名：sqlseta1_1  sqlset節點_tab編號,awr的為sqlseta_awr1
（每個sts存放20w SQL左右，不要超過25w條,超過20w條之后分給下一個sts，因為一個sts存放過多，后續pack,unpack sqlset會很慢，也可能報ORA-01555錯誤）
每個庫需要針對不同節點，各創建20個以上。也可以先少創建點，之后超過20w條，再增加。
--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庫方式一致，命名有差別


3.3 SPA采集目標用戶確定

首先確定需要采集的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


3.4  SPA不間斷采集方案

由于庫中的v$sql存放的數據量較大，有15w+,多的時候超過20w條，直接采集耗時長，而且重復類型的SQL_ID較多（字面量不同），游標采集需要可以最大限度的幫助我們采集到更多的SQL語句。為了保證采集到更多的SQL，我們需要進行一個長期的捕捉，采用不間斷捕獲。采集的過程中可能因為有literal sql，這會導致我們的SQLSET的結果集非常大，因為相關的表涉及到一些CLOB字段，如果結果集過大的話，將導致轉換成中間表非常的慢。轉換到一半因為UNDO不夠大，還還會導致出現ORA-01555錯誤。為了解決這個問題，建議在采集的過程中實施過濾。對于采集腳本可以編寫shell腳本后臺執行，每個節點均需采集，設置不同的sqlset,最后按庫合并。

為了防止采集很多重復的字面量SQL，可以先按照buffer_gets,disk_reads,executions等條件采集（防止采集慢等情況），等采集完成第一個sqlset（20w條左右），再采用剔除重復行過濾采集，并增加rownum條件。


1）第一個sqlset采集腳本

  • 以a庫節點1為例，采集數據放到sqlseta1_tab1中。

    主要通過buffer_gets,elapsed_time等構造查詢條件，并且通過rownum<5000限制在5000條，5000條是個大概數據，對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,然后取平均值和最大值測試，一般條件選出的數據在5000行之內，如果超過5000行，再加其他條件拆分，比如executions。

  • 第一個采集腳本可以采集多次，但是第一次采集完畢后，需要建立剔重表

    （每個節點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腳本調度，放到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;
/

2） 過濾不間斷采集方案

等第一個sqlset采集完畢后，采用過濾采集，需要建立控制表，控制SPA采集是否啟動。并且采用循環采集。每5分鐘執行一次采集。

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開始采集為例子，過濾采集主要使用死循環，不過加了一個控制表sqlseta1_control判斷，如果status=0,則退出采集，否則繼續采集。以下腳本實現：

  • 從sqlseta1_tab22開始

  • 控制表控制SPA是否采集

  • 選擇spaqc_a1spaqc_a sts_A1_0830等作為已經采集過的sqlset，不需要再采集，進行過濾。

  • 每個sqlset容納20w條SQL，如果達到20w條，放到下1個sqlset中。

  • 因為循環采集，每次采集500條（才開始可以設5000條，后續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;
/


3.5采集AWR中的SQL

采集45天前到當前日期的數據，只需要根據dba_hist_snapshot查詢開始和結束snap_id即可。只關注DBAOPER1,ZWOPTADM兩個用戶，其它用戶SQL較少，也可以全部取。

--gatherawr.sh
echo start `date`
sqlplus spa/spa <DECLARE
  mycur sys_refcursor;
BEGIN
  open mycur for
    select value(p)
      from table(dbms_sqltune.select_workload_repository(40601,
                                                         41698,
                                                         parsing_schema_name in (DBAOPER1,ZWOPTADM)
                                                         )
               ) p;
  dbms_sqltune.load_sqlset(sqlset_name => sqlseta_awr1,
                           sqlset_owner=>SPA,
                           populate_cursor => mycur,
                           load_option => MERGE);
  close mycur;
END;
/
exit
EOF
echo end `date`
exit

nohup ./gatherawr.sh >gatherawr.log 2>&1 &



未完待續...


更多精彩干貨分享

點擊下方名片關注

IT那活兒

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129781.html

相關文章

  • DRDS SQL 審計與分析——全面洞察 SQL 之利器

    背景 數據庫存儲著系統的核心數據，其安全方面的問題在傳統環境中已經成為泄漏和被篡改的重要根源。而在云端，數據庫所面臨的威脅被進一步的放大。因此，對云數據庫的操作行為尤其是全量 SQL 執行記錄的審計日志，就顯得尤為重要，是保障云數據庫安全的最基本要求。那么針對云數據庫的 SQL 審計，您是否存在如下疑問： SQL 審計對數據庫的性能有影響嗎？ 數據被篡改，但是沒啟用 SQL 審計，還能追溯篡改者...

    WalkerXu 評論0 收藏0

  • 探索Greenplum的實踐，了解新一代大數據處理利器

    摘要：上有主節點和從節點兩部分，兩者主要的功能是生成查詢計劃并派發，以及協調并行計算，同時在上保存著，這個全局目錄存著一組數據庫系統本身所具有的元數據的系統表。 前言：近年來，互聯網的快速發展積累了海量大數據，而在這些大數據的處理上，不同技術棧所具備的性能也有所不同，如何快速有效地處理這些龐大的數據倉，成為很多運營者為之苦惱的問題！隨著Greenplum的異軍突起，以往大數據倉庫所面臨的很多...

    supernavy 評論0 收藏0

  • 2017雙11技術揭秘—雙十一海量數據下EagleEye的使命和挑戰

    摘要：今年的無論是常態全鏈路壓測或者是雙十一當天，面臨的主要問題是如何保障自身系統在海量數據沖擊下的穩定性，以及如何更快的展現各個系統的狀態及更好的幫助開發同學發現及定位問題。在整個雙十一備戰過程中，遇到并解決了很多疑難雜癥。 摘要： EagleEye作為阿里集團老牌的鏈路跟蹤系統，其自身業務雖不在交易鏈路上，但卻監控著全集團的鏈路狀態，特別是在中間件的遠程調用上，覆蓋了集團絕大部分的場景，...

    ssshooter 評論0 收藏0

  • Laravel 學習筆記之 Query Builder 源碼解析(中)

    說明：本篇主要學習數據庫連接階段和編譯SQL語句部分相關源碼。實際上，上篇已經聊到Query Builder通過連接工廠類ConnectionFactory構造出了MySqlConnection實例(假設驅動driver是mysql)，在該MySqlConnection中主要有三件利器：IlluminateDatabaseMysqlConnector;IlluminateDatabaseQuery...

    zhou_you 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<