搜尋
首頁資料庫mysql教程SPA游标采集之去除重复

SPA游标采集之去除重复

Jun 07, 2016 pm 04:36 PM
spa升級去除我們資料庫遊標目的採集重複

当我们做数据库升级项目的时候,我们一般会去做性能回归测试,通俗一点来说,就是把10g生产库的语句拿到11g生产环境上运行,如果发现运行过程中,由于优化器、实例参数等改变导致执行计划变化,最终导致性能退化的语句,需要拿出来单独进行分析及验证。要做这

当我们做数据库升级项目的时候,我们一般会去做性能回归测试,通俗一点来说,就是把10g生产库的语句拿到11g生产环境上运行,如果发现运行过程中,由于优化器、实例参数等改变导致执行计划变化,最终导致性能退化的语句,需要拿出来单独进行分析及验证。要做这个事情,首先我们需要把我们的10g上的语句给采集出来,采集方法分为以下几种方式。

  • cursor cache
  • awr snapshots
  • awr baselines
  • another sql tuning set
  • 10046 trace file(11g+)

对于大型的生产库,我们一般采集的是方式是:游标还有awr snapshots的数据。为了能够完美的抓取到全部的SQL语句,我们往往需要一天对cursor cache进行多次采集。大部分建议是放在高峰期的时候采集,这么做主要是为了防止有些SQL还没被抓取到sqlset就从shared pool中purge出去了。在这个抓取的过程中,有一个困扰的问题就是literal sql的一些语句。举个例子如下:

select * from emp where empno=1456;
select * from emp where empno=1457;
select * from emp where empno=1458;

这三个SQL语句会先后被采集进来,每天都这样采集,会导致我们的SQLSET的结果集越来越大。正常情况下,一个大型的生产库的SQL语句也就几w条而已,但是如果你的硬解析非常多的话,可能在短短的几天,你采集的语句就会突破到100w条以上。然后在做后面SQLSET转换到中转表的这个过程,会执行相当长的时间,搞不好就报ORA-01555,导致运行一段时间后无法成功转换。我在这上面被坑了好几次。可能你会说,就100w的数据,Oracle应该很快转换出来的吧。这个我得解释一下。我们的中转表里面其实包含了好几个LOB字段和特殊TYPE类型。一旦数据量大了,可以说速度完全不行。正是基于这种原因,我们需要考虑一种方式,在采集的过程中进行去除重复的操作。

我们来举个例子说明下。

1.新建SQLSET

SQL> exec dbms_sqltune.CREATE_SQLSET('sqlset1'); 
PL/SQL procedure successfully completed.
SQL> select * from dba_sqlset; 
        ID NAME            OWNER           DESCRIPTION                    CREATED   LAST_MODI STATEMENT_COUNT 
---------- --------------- --------------- ------------------------------ --------- --------- --------------- 
         1 sqlset1         SYS                                            11-MAY-14 11-MAY-14               0

2.使用scott用户,执行几条语句,执行前先flush下shared pool

SQL> alter system flush shared_pool; 
System altered.
connect scott/tiger
select * from emp;
select * from emp where empno=1456;
select * from emp where empno=1457;

3.使用sys用户开始采集语句

DECLARE 
  mycur DBMS_SQLTUNE.SQLSET_CURSOR; 
BEGIN 
  OPEN mycur FOR 
    SELECT value(P) 
      FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name in (''SCOTT'')', 
                                                  NULL, 
                                                  NULL, 
                                                  NULL, 
                                                  NULL, 
                                                  1, 
                                                  NULL, 
                                                  'ALL')) p; 
  dbms_sqltune.load_sqlset(sqlset_name     => 'sqlset1', 
                           populate_cursor => mycur, 
                           load_option     => 'MERGE'); 
  CLOSE mycur; 
END; 
/ 
SQL> select * from dba_sqlset; 
        ID NAME            OWNER           DESCRIPTION                    CREATED   LAST_MODI STATEMENT_COUNT 
---------- --------------- --------------- ------------------------------ --------- --------- --------------- 
         1 sqlset1         SYS                                            11-MAY-14 11-MAY-14               9

4.查看采集结果

SQL> select sql_id,sql_text from DBA_SQLSET_STATEMENTS ; 
SQL_ID        SQL_TEXT 
------------- -------------------------------------------------------------------------------- 
1srhq04p4x0zz SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB 
38mhtu5pc7d07 select * from emp where empno=1456 
7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P 
a2dk8bdn0ujx7 select * from emp 
bc26hcc8td76f select * from emp where empno=1457 
cw6vxf0kbz3v1 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE 
d6vwqbw6r2ffk SELECT USER FROM DUAL 
dyk4dprp70d74 SELECT DECODE('A','A','1','2') FROM DUAL 
g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;

从这里我们可以观察到我们的三条语句都采集进来了。这里我们可以看到我们的literal sql,如果每天对游标采集好几次的话,我们的literal sql会越采集越多,导致SQLSET的结果集非常大。当SQL数量达到百万级别后,使得我们的转换非常慢。如何去重呢?我们看下这个DBA_SQLSET_STATEMENTS的结构。

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
SQL> select sql_id,sql_text,FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS; 
SQL_ID        SQL_TEXT                                                                            FORCE_MATCHING_SIGNATURE 
------------- -------------------------------------------------------------------------------- --------------------------- 
1srhq04p4x0zz SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB         4094562552765466770 
38mhtu5pc7d07 select * from emp where empno=1456                                                      16946033956547040230 
7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P        10967007256268736959 
a2dk8bdn0ujx7 select * from emp                                                                        7001777653489406494 
bc26hcc8td76f select * from emp where empno=1457                                                      16946033956547040230 
cw6vxf0kbz3v1 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE        18201431879876406267 
d6vwqbw6r2ffk SELECT USER FROM DUAL                                                                   17376422952071979402 
dyk4dprp70d74 SELECT DECODE('A','A','1','2') FROM DUAL                                                 1846728577492307645 
g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;                                   0

这里我们主要利用FORCE_MATCHING_SIGNATURE这个字段。可以看到我们的literal sql的FORCE_MATCHING_SIGNATURE的值是相同的。这里是16946033956547040230。所以我们要对这个列进行distinct,并将distinct出来的值放在一个我们自定义的Table里面。

5.去重采集

SQL> create table spaqc as select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS; 
Table created. 
SQL> select * from spaqc; 
   FORCE_MATCHING_SIGNATURE 
--------------------------- 
       18201431879876406267 
        1846728577492307645 
        4094562552765466770 
       17376422952071979402 
       10967007256268736959 
        7001777653489406494 
       16946033956547040230 
                          0 
8 rows selected.

这里需要注意一下FORCE_MATCHING_SIGNATURE为0的情况下,一般是运行PL/SQL、JOB之类的操作,这个我们不能过滤掉。所以我们要把0这行给删掉。

SQL> delete from spaqc where FORCE_MATCHING_SIGNATURE=0; 
1 row deleted. 
SQL> commit; 
Commit complete.

6.再次测试,看看literal sql会不会被采集。

select * from emp where empno=1458;
select * from emp where empno=1459;
select * from emp where empno=1460;
select * from emp where empno=1460 and ENAME='scott';
DECLARE 
  mycur DBMS_SQLTUNE.SQLSET_CURSOR; 
BEGIN 
  OPEN mycur FOR 
    SELECT value(P) 
      FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name in (''SCOTT'') and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spaqc)', 
                                                  NULL, 
                                                  NULL, 
                                                  NULL, 
                                                  NULL, 
                                                  1, 
                                                  NULL, 
                                                  'ALL')) p; 
  dbms_sqltune.load_sqlset(sqlset_name     => 'sqlset1', 
                           populate_cursor => mycur, 
                           load_option     => 'MERGE'); 
  CLOSE mycur; 
END; 
/ 
SQL> select sql_id,sql_text,FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS ; 
SQL_ID        SQL_TEXT                                                                            FORCE_MATCHING_SIGNATURE 
------------- -------------------------------------------------------------------------------- --------------------------- 
1srhq04p4x0zz SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB         4094562552765466770 
38mhtu5pc7d07 select * from emp where empno=1456                                                      16946033956547040230 
7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P        10967007256268736959 
a2dk8bdn0ujx7 select * from emp                                                                        7001777653489406494 
bc26hcc8td76f select * from emp where empno=1457                                                      16946033956547040230 
cw6vxf0kbz3v1 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE        18201431879876406267 
d6vwqbw6r2ffk SELECT USER FROM DUAL                                                                   17376422952071979402 
d8fw5smyjva0b select * from emp where empno=1460 and ENAME='scott'                                    17445701640293030006 
dyk4dprp70d74 SELECT DECODE('A','A','1','2') FROM DUAL                                                 1846728577492307645 
g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;                                                      0 
10 rows selected.

这里我们看到literal sql没有被采集进来,我们实现了游标采集的过滤。

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
您如何確定MySQL中的慢速查詢?您如何確定MySQL中的慢速查詢?Apr 26, 2025 am 12:15 AM

在MySQL中識別慢查詢可以通過啟用慢查詢日誌並設置閾值來實現。 1.啟用慢查詢日誌並設置閾值。 2.查看和分析慢查詢日誌文件,使用工具如mysqldumpslow或pt-query-digest進行深入分析。 3.優化慢查詢可以通過索引優化、查詢重寫和避免使用SELECT*來實現。

如何監視MySQL Server的健康和性能?如何監視MySQL Server的健康和性能?Apr 26, 2025 am 12:15 AM

要監控MySQL服務器的健康和性能,應關注系統健康、性能指標和查詢執行。 1)監控系統健康:使用top、htop或SHOWGLOBALSTATUS命令查看CPU、內存、磁盤I/O和網絡活動。 2)追踪性能指標:監控查詢每秒數、平均查詢時間和緩存命中率等關鍵指標。 3)確保查詢執行優化:啟用慢查詢日誌,記錄並優化執行時間超過設定閾值的查詢。

比較和對比Mysql和Mariadb。比較和對比Mysql和Mariadb。Apr 26, 2025 am 12:08 AM

MySQL和MariaDB的主要區別在於性能、功能和許可證:1.MySQL由Oracle開發,MariaDB是其分支。 2.MariaDB在高負載環境中性能可能更好。 3.MariaDB提供了更多的存儲引擎和功能。 4.MySQL採用雙重許可證,MariaDB完全開源。選擇時應考慮現有基礎設施、性能需求、功能需求和許可證成本。

MySQL的許可與其他數據庫系統相比如何?MySQL的許可與其他數據庫系統相比如何?Apr 25, 2025 am 12:26 AM

MySQL使用的是GPL許可證。 1)GPL許可證允許自由使用、修改和分發MySQL,但修改後的分發需遵循GPL。 2)商業許可證可避免公開修改,適合需要保密的商業應用。

您什麼時候選擇InnoDB而不是Myisam,反之亦然?您什麼時候選擇InnoDB而不是Myisam,反之亦然?Apr 25, 2025 am 12:22 AM

選擇InnoDB而不是MyISAM的情況包括:1)需要事務支持,2)高並發環境,3)需要高數據一致性;反之,選擇MyISAM的情況包括:1)主要是讀操作,2)不需要事務支持。 InnoDB適合需要高數據一致性和事務處理的應用,如電商平台,而MyISAM適合讀密集型且無需事務的應用,如博客系統。

在MySQL中解釋外鍵的目的。在MySQL中解釋外鍵的目的。Apr 25, 2025 am 12:17 AM

在MySQL中,外鍵的作用是建立表與表之間的關係,確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性,使用時需注意性能優化和避免常見錯誤。

MySQL中有哪些不同類型的索引?MySQL中有哪些不同類型的索引?Apr 25, 2025 am 12:12 AM

MySQL中有四種主要的索引類型:B-Tree索引、哈希索引、全文索引和空間索引。 1.B-Tree索引適用於範圍查詢、排序和分組,適合在employees表的name列上創建。 2.哈希索引適用於等值查詢,適合在MEMORY存儲引擎的hash_table表的id列上創建。 3.全文索引用於文本搜索,適合在articles表的content列上創建。 4.空間索引用於地理空間查詢,適合在locations表的geom列上創建。

您如何在MySQL中創建索引?您如何在MySQL中創建索引?Apr 25, 2025 am 12:06 AM

toCreateAnIndexinMysql,usethecReateIndexStatement.1)forasingLecolumn,使用“ createIndexIdx_lastNameEnemployees(lastName); 2)foracompositeIndex,使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees(lastName,firstName,firstName);” 3)forauniqe instex,creationexexexexex,

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

這個專案正在遷移到osdn.net/projects/mingw的過程中,你可以繼續在那裡關注我們。 MinGW:GNU編譯器集合(GCC)的本機Windows移植版本,可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔;包括對MSVC執行時間的擴展,以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境

VSCode Windows 64位元 下載

VSCode Windows 64位元 下載

微軟推出的免費、功能強大的一款IDE編輯器

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

將Eclipse與SAP NetWeaver應用伺服器整合。

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )專業的PHP整合開發工具