当前位置: 开发笔记 > 数据库 > 正文

SPA游标采集之去除重复

作者：UTOB | 来源：互联网 | 2017-05-12 15:28

当我们做数据库升级项目的时候，我们一般会去做性能回归测试，通俗一点来说，就是把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没有被采集进来，我们实现了游标采集的过滤。

原文地址：SPA游标采集之去除重复, 感谢原作者分享。

推荐阅读

数据库
新浪笔试题

1:有如下一段程序：packagea.b.c;publicclassTest{privatestaticinti0;publicintgetNext(){return ... [详细]

蜡笔小新 2024-12-27 19:32:17
数据库
PHP 5.2.5 安装与配置指南

本文详细介绍了 PHP 5.2.5 的安装和配置步骤，帮助开发者解决常见的环境配置问题，特别是上传图片时遇到的错误。通过本教程，您可以顺利搭建并优化 PHP 运行环境。 ... [详细]

蜡笔小新 2024-12-27 19:05:41
数据库
网站与MySQL数据库的连接与交互

本文详细介绍了如何通过多种编程语言（如PHP、JSP）实现网站与MySQL数据库的连接，包括创建数据库、表的基本操作，以及数据的读取和写入方法。 ... [详细]

蜡笔小新 2024-12-27 14:09:23
数据库
MyBatis：深入解析与应用

在当前众多持久层框架中，MyBatis（前身为iBatis）凭借其轻量级、易用性和对SQL的直接支持，成为许多开发者的首选。本文将详细探讨MyBatis的核心概念、设计理念及其优势。 ... [详细]

蜡笔小新 2024-12-27 12:17:16
数据库
DataGridView 保存时，为什么当前单元格的值无法保存？

在使用 DataGridView 时，如果在当前单元格中输入内容但光标未移开，点击保存按钮后，输入的内容可能无法保存。只有当光标离开单元格后，才能成功保存数据。本文将探讨如何通过调用 DataGridView 的内置方法解决此问题。 ... [详细]

蜡笔小新 2024-12-27 09:27:14
数据库
在 Linux 系统中部署 PostgreSQL 数据库

本文详细介绍了如何在 Linux 平台上安装和配置 PostgreSQL 数据库。通过访问官方资源并遵循特定的操作步骤，用户可以在不同发行版（如 Ubuntu 和 Red Hat）上顺利完成 PostgreSQL 的安装。 ... [详细]

蜡笔小新 2024-12-27 03:46:27
数据库
如何在PostgreSQL中查看数据表

本文将指导您使用pgAdmin工具连接到PostgreSQL数据库，并展示如何浏览和查找其中的数据表。通过简单的步骤，您可以轻松访问所需的表结构和数据。 ... [详细]

蜡笔小新 2024-12-26 19:55:24
数据库
利用存储过程构建年度日历表的详细指南

本文将介绍如何使用SQL存储过程创建一个完整的年度日历表。通过实例演示，帮助读者掌握存储过程的应用技巧，并提供详细的代码解析和执行步骤。 ... [详细]

蜡笔小新 2024-12-26 18:20:17
数据库
使用 SQLiteJDBC 和 HikariCP 实现 Java 程序连接 SQLite 数据库

本文介绍了如何通过 Maven 依赖引入 SQLiteJDBC 和 HikariCP 包，从而在 Java 应用中高效地连接和操作 SQLite 数据库。文章提供了详细的代码示例，并解释了每个步骤的实现细节。 ... [详细]

蜡笔小新 2024-12-26 17:34:42
sql
解决SQL Server动态SQL中LIKE语句参数传递导致无返回值的问题

在使用SQL Server进行动态SQL查询时，如果遇到LIKE语句无法正确返回预期结果的情况，通常是因为参数传递方式不当。本文将详细探讨这一问题，并提供解决方案及相关的技术背景。 ... [详细]

蜡笔小新 2024-12-26 17:02:33
insert
SQL 触发器实现视图插入操作

本文介绍如何通过创建替代插入触发器，使对视图的插入操作能够正确更新相关的基本表。涉及的表包括：飞机（Aircraft）、员工（Employee）和认证（Certification）。 ... [详细]

蜡笔小新 2024-12-26 15:53:40
memcached
MySQL缓存机制深度解析

本文详细探讨了MySQL的缓存机制，包括主从复制、读写分离以及缓存同步策略等内容。通过理解这些概念和技术，读者可以更好地优化数据库性能。 ... [详细]

蜡笔小新 2024-12-26 15:15:06
sql
Qt 环境下 SQLite 动态创建表的实现方法

SQLite 动态创建多个表的需求在网络上有不少讨论，但很少有详细的解决方案。本文将介绍如何在 Qt 环境中使用 QString 类轻松实现 SQLite 表的动态创建，并提供详细的步骤和示例代码。 ... [详细]

蜡笔小新 2024-12-26 15:11:34
sql
精选30本C# ASP.NET SQL中文PDF电子书合集

欢迎订阅我们的技术博客，获取更多关于C#、ASP.NET和SQL的最新资讯和资源。 ... [详细]

蜡笔小新 2024-12-26 13:54:47
数据库
MySQL 数据库迁移指南：从本地到远程及磁盘间迁移

本文详细介绍了如何在不同场景下进行 MySQL 数据库的迁移，包括从一个硬盘迁移到另一个硬盘、从一台计算机迁移到另一台计算机，以及解决迁移过程中可能遇到的问题。 ... [详细]

蜡笔小新 2024-12-26 13:21:38

UTOB

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章