SPA常用SQL

发表于 2015 年 02 月 13 日 由 Travel.liu

spa常用sql

  • 9i采集
    1. 采集trace

      2. 安装DBMS_SUPPORT

          @?/rdbms/admin/dbmssupp.SQL
    @?/rdbms/admin/prvtsupp.plb
    2. 创建存储过程
      3.     CREATE OR REPLACE PROCEDURE SYS.P_DISABLE_TRACE AS
    BEGIN
      FOR X IN (SELECT SID, SERIAL# SERIAL
                  FROM V$SESSION
                 WHERE USERNAME IN ('BILL' )) LOOP
        DBMS_SUPPORT.STOP_TRACE_IN_SESSION(X.SID, X.SERIAL);
      END LOOP;
    END;
    /
 
    CREATE OR REPLACE PROCEDURE SYS.P_ENABLE_TRACE(I_ENABLE_RANGE NUMBER) AS
    BEGIN
      IF (I_ENABLE_RANGE = 0) THEN
        FOR X IN (SELECT SID, SERIAL# SERIAL
                    FROM V$SESSION
                   WHERE USERNAME IN ('BILL')) LOOP
          DBMS_SUPPORT.START_TRACE_IN_SESSION(X.SID, X.SERIAL, TRUE, FALSE);
        END LOOP;
      ELSE
        FOR X IN (SELECT SID, SERIAL# SERIAL
                    FROM V$SESSION
                   WHERE USERNAME IN ('BILL'  )
                     AND LOGON_TIME > SYSDATE - I_ENABLE_RANGE / 1440) LOOP
          DBMS_SUPPORT.START_TRACE_IN_SESSION(X.SID, X.SERIAL, TRUE, FALSE);
        END LOOP;
      END IF;
    END;
    /
    3. 修改trc目录
      4.     ALTER system SET user_dump_dest='/arch/spa';
    ALTER system SET user_dump_dest='/oracle/app/oracle/admin/bill/udump';
    4. 创建job
      5.     variable job NUMBER;
    BEGIN
    sys.dbms_job.submit(job => :job,
    what => 'p_enable_trace(1);',
    next_date => to_date('2015-01-19 11:50:00', 'YYYY-MM-DD hh24:mi:ss'),
    INTERVAL => 'sysdate+10/1440');
    commit;
    END;
    /
    print job;
 
    SHOW parameter dump
 
    # EXEC p_enable_trace(0);
 
    # EXEC p_disable_trace();
    5. 运行job
      6.     EXEC dbms_job.run('269');
 
    EXEC dbms_job.remove('269');
 
    SELECT job,what FROM dba_jobs WHERE job=269;
    6. 创建map table
      7.     DROP TABLE mapping_table;
    CREATE TABLE mapping_table tablespace USERS AS
    SELECT object_id id, owner, substr(object_name, 1, 30) name
      FROM dba_objects
     WHERE object_type NOT IN ('CONSUMER GROUP',
                               'EVALUATION CONTEXT',
                               'FUNCTION',
                               'INDEXTYPE',
                               'JAVA CLASS',
                               'JAVA DATA',
                               'JAVA RESOURCE',
                               'LIBRARY',
                               'LOB',
                               'OPERATOR',
                               'PACKAGE',
                               'PACKAGE BODY',
                               'PROCEDURE',
                               'QUEUE',
                               'RESOURCE PLAN',
                               'SYNONYM',
                               'TRIGGER',
                               'TYPE',
                               'TYPE BODY')
    UNION ALL
    SELECT user_id id, username owner, NULL name FROM dba_users;
    7. 导入导出
      8.     -- exp \'/ as sysdba\' file=maptable_20150127.dmp tables=mapping_table
    -- scp -P 22 maptable_20150127.dmp 135.32.61.10:/acct/oradata01/spa
    -- imp \'/ as sysdba\' file=maptable_20150127.dmp fromuser=sys touser=spa
    8. 压缩传输trc
      9. 	aix tar没有压缩功能用管道解决
    mknod /arch/spa/bill_spa_20150127.tar p
    gzip < /arch/spa/bill_spa_20150127.tar > /arch/spa/bill_spa_20150127.tar.Z &
    tar -cvf bill_spa_20150127.tar *.trc
	linux
	tar -zcvf bill_spa_20150127.tar.gz *.trc
  • 10g采集
    1. 一次性采集游标
      2.     DECLARE
     cur sys_refcursor;
    BEGIN
     OPEN cur FOR
     SELECT VALUE(P)
     FROM TABLE(
     DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS'' and EXECUTIONS > 10',NULL,NULL,NULL,NULL,1,NULL,'ALL')) P;
     -- Process each statement (or pass cursor to load_sqlset)
       DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name     => 'CRM_SPA_20150127',
                               populate_cursor => cur,
                               load_option     => 'MERGE');
     CLOSE cur;
    END;
    /
 
-- query singre sql_id
-- SELECT *
-- FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));
-- SELECT sql_id,
--        plan_hash_value
-- FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''ay1m3ssvtrh24'''))
-- ORDER BY sql_id,
--          plan_hash_value;
 
-- query module
-- SELECT VALUE(P)
-- FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P;
 
-- query sql elapsed_time
-- SELECT VALUE(P)
-- FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
 
-- query sql buffer_gets
-- SELECT VALUE(P)
-- FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P;
 
 
-- SELECT VALUE(P)
-- FROM table(dbms_sqltune.select_cursor_cache( 'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P;
    2. 自动采集游标
      3.     sqlplus "/ as sysdba"<<EOF
    DECLARE
     cur sys_refcursor;
    BEGIN
     -- Process each statement (or pass cursor to load_sqlset).
       DBMS_SQLTUNE.capture_cursor_cache_sqlset(sqlset_name     => 'CRM_SPA_20150127',time_limit=>60*60,REPEAT_INTERVAL=>60,
                               capture_option     => 'MERGE',BASIC_FILTER=>'parsing_schema_name <> ''SYS'' and EXECUTIONS > 10 ');
    END;
    /
EOF
-- FUNCTION SELECT_WORKLOAD_REPOSITORY RETURNS SQLSET
--  Argument Name                  Type                    In/Out Default?
--  ------------------------------ ----------------------- ------ --------
--  BEGIN_SNAP                     NUMBER                  IN
--  END_SNAP                       NUMBER                  IN
--  BASIC_FILTER                   VARCHAR2                IN     DEFAULT
--  OBJECT_FILTER                  VARCHAR2                IN     DEFAULT
--  RANKING_MEASURE1               VARCHAR2                IN     DEFAULT
--  RANKING_MEASURE2               VARCHAR2                IN     DEFAULT
--  RANKING_MEASURE3               VARCHAR2                IN     DEFAULT
--  RESULT_PERCENTAGE              NUMBER                  IN     DEFAULT
--  RESULT_LIMIT                   NUMBER                  IN     DEFAULT
--  ATTRIBUTE_LIST                 VARCHAR2                IN     DEFAULT
    3. awr
      4.     SELECT mix(snap_id),MAX(snap_id) FROM dba_hist_snaphost;
    sqlplus "/ as sysdba"<<EOF
    DECLARE
     cur sys_refcursor;
    BEGIN
     OPEN cur FOR
     SELECT VALUE(P)
     FROM TABLE(
     DBMS_SQLTUNE.select_workload_repository(73494,73971 ,'parsing_schema_name <> ''SYS''')) P;
     -- Process each statement (or pass cursor to load_sqlset).
       DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name     => 'CRM_SPA_20150127',
                               populate_cursor => cur,
                               load_option     => 'MERGE');
     CLOSE cur;
    END;
    /
EOF
    4. 打包
      5. -- exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('CRM_SPA_20150127','SPA');
 
-- exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('CRM_SPA_20150127','SPA','CRM_SPA_20150127','SPA');
 
    EXEC dbms_sqltune.create_stgtab_sqlset(TABLE_NAME => 'SQLSET_TAB_20150123',schema_name => 'SPA');
 
 
    BEGIN
        FOR X IN 0..31 LOOP
          DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'SQLSET_20150122_'||X,SQLSET_OWNER=>'SPA',
            staging_table_name => 'SQLSET_TAB_20150123',staging_schema_owner => 'SPA');
        END LOOP;
        END;
    /
    5. 解包
      6.     EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( -
                      SQLSET_NAME          => 'SQLSET_20150123_30', -
                      SQLSET_OWNER         => 'SPA', -
                      REPLACE              => TRUE, -
                      STAGING_TABLE_NAME   => 'SQLSET_TAB_20150123', -
                      STAGING_SCHEMA_OWNER => 'SPA');
  • 9i trace load
    1. 创建SQLSET
      2.     LOAD trace INTO sqlset
    DECLARE
      mycur dbms_sqltune.sqlset_cursor;
    BEGIN
      -- dbms_sqltune.create_sqlset('SQLSET_20150127_0');
        OPEN mycur FOR
          SELECT VALUE(p)
          FROM TABLE(dbms_sqltune.select_sql_trace(
                       directory=>'SPA_DIR_0',
                       file_name=>'%trc',
                       mapping_table_name => 'MAPPING_TABLE',
                       select_mode => dbms_sqltune.single_execution)) p;
      dbms_sqltune.load_sqlset(
        sqlset_name => 'SQLSET_20150127_0',
        populate_cursor => mycur,
        commit_rows => 100);
      close mycur;
    END;
    /
    2. 执行分析
      3.     --创建SPA任务（成功）
    DECLARE sts_task  VARCHAR2(64);
    BEGIN
    FOR X IN 1..11
    LOOP
    sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
      task_name   => '9i_11g_spa_bdbill'||'_'||X,
      description => 'Upgrade from 9010 to 112042, SQL Testing',
      sqlset_name => '9i_bdbill_spa_trc'||X);
    END LOOP;
    END;
    /
    3. 创建9i的Trail
      4.     BEGIN
    FOR X IN 1..11
    LOOP
    dbms_sqlpa.execute_analysis_task(
      task_name   => '9i_11g_spa_bdbill'||'_'||X,
      execution_name => '9i_trail_bdbill'||'_9i',
      execution_type => 'CONVERT SQLSET',
      execution_desc => '9i sql trial generated from STS');
    END LOOP;
    END;
    /
    4. 创建11g的Trail
      5.     BEGIN
    FOR X IN 1..11
    LOOP
    dbms_sqlpa.execute_analysis_task(
      task_name      => '9i_11g_spa_bdbill'||'_'||X,
      execution_name => '11g_trail_bdbill'||'_11G',
      execution_type => 'TEST EXECUTE',
      execution_desc => 'remote test-execute trial on 11g db');
    END LOOP;
    END;
    /
  • 10g
    1. 创建分析任务
      2.   DECLARE
    L_SPA_TASK_NAME  VARCHAR2(64);
  BEGIN
    FOR X IN 0..".($execParallels - 1)." LOOP
      L_SPA_TASK_NAME := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
                               TASK_NAME    => 'SPA_TASK_20150127'||'_'||X,
                               DESCRIPTION  => 'SPA Analysis task on : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
                               SQLSET_NAME  => 'SQLSET_20150127'||'_'||X,
                               SQLSET_OWNER => 'SPA');
    END LOOP;
  END;
  /
    2. 执行10G SQLSET 转换
      3.   BEGIN
    FOR X IN 0..31 LOOP
      DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
                 TASK_NAME      => 'SPA_TASK_20150127'||'_'||X,
                 EXECUTION_NAME => 'SPA_TASK_20150127'||'_10G',
                 EXECUTION_TYPE => 'CONVERT SQLSET',
                 EXECUTION_DESC => 'Convert 10g SQLSET on : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    END LOOP;
  END;
/
    3. 执行11g SQL分析
      4.     sqlplus SPA/spa <<EOF
    EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK('SPA_TASK_20150127_30',
        'TEST EXECUTE', 'SPA_TASK_20150127_11G', NULL,
        'Execute SQL in 11g on : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    EXIT
    EOF
  • 对比
    1. 执行时间
      2.   EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                  TASK_NAME      => 'SPA_TASK_20150127', -
                  EXECUTION_TYPE => 'compare performance', -
                  EXECUTION_NAME => 'SPA_TASK_20150127'||'_COMP_ET', -
                  EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                   'COMPARISON_METRIC', 'ELAPSED_TIME', -
                                                   'EXECUTION_NAME1','SPA_TASK_20150127'||'_10G', -
                                                   'EXECUTION_NAME2','SPA_TASK_20150127'||'_11G'), -
                  EXECUTION_DESC => 'Compare SQLs between 10g and 11g on :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    2. buffer get
      3.   EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                  TASK_NAME      => 'SPA_TASK_20150127', -
                  EXECUTION_TYPE => 'compare performance', -
                  EXECUTION_NAME => 'SPA_TASK_20150127'||'_COMP_BG', -
                  EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                   'COMPARISON_METRIC', 'BUFFER_GETS', -
                                                   'EXECUTION_NAME1','SPA_TASK_20150127'||'_10G', -
                                                   'EXECUTION_NAME2','SPA_TASK_20150127'||'_11G'), -
                  EXECUTION_DESC => 'Compare SQLs between 10g and 11g on :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
 
</ol>
<li>报告</li>
<ol>
	<li>获取所有SQL 执行时间 的性能变化情况</li>
<pre lang="sql">
SPOOL sql_result.tmp
CONN SPA/spa
  ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
  SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
  SPOOL elapsed_all.html
  SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_20150117','HTML','ALL','ALL',NULL,100,'SPA_TASK_20150117'||'_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
SPOOL OFF
COMMIT;
EXIT
    3. 获取所有SQL buffer get 的性能变化情况
      4. SPOOL sql_result.tmp
CONN SPA/spa
  ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
  SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
  SPOOL buffer_all.html
  SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_20150117','HTML','ALL','ALL',NULL,100,'SPA_TASK_20150117'||'_COMP_BG')).GETCLOBVAL(0,0) FROM DUAL;
 SPOOL OFF
COMMIT;
EXIT
    4. 获取不支持的SQL列表
      5. SPOOL sql_result.tmp
CONN SPA/spa
  ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
  SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
  SPOOL unsupported.html
  SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_20150117','HTML','UNSUPPORTED','ALL',NULL,10000,'SPA_TASK_20150117'||'_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
SPOOL OFF
COMMIT;
EXIT
    5. 获取所有执行出错的SQL列表
      6. SPOOL sql_result.tmp
CONN SPA/spa
  ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
  SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
  SPOOL error.html
  SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_20150117','HTML','ERRORS','ALL',NULL,10000,'SPA_TASK_20150117'||'_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
SPOOL OFF
COMMIT;
EXIT
    6. 清理环境
      7.  
    查看sqlset
    SELECT owner,name,STATEMENT_COUNT FROM dba_sqlset;
    查看任务
    SELECT owner,task_name FROM DBA_ADVISOR_TASKS  WHERE owner='SPA';
 
    删除sqlset
    BEGIN
    FOR X IN 0..31
    LOOP
     dbms_sqltune.DROP_SQLSET('SQLSET_20150127_'||X);
    END LOOP;
    END;
    /
 
    删除ANALYSIS_TASK
    BEGIN
    FOR X IN 0..31
    LOOP
     dbms_sqlpa.DROP_ANALYSIS_TASK('SPA_TASK_20150127_'||X);
    END LOOP;
    END;
    /
    DELETE FROM wri$_sqlset_references
    WHERE sqlset_id IN (SELECT id
    FROM wri$_sqlset_definitions
    WHERE name IN ('SQLSET_20150118','SQLSET_20150118_0'));
    commit;
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

评论功能已关闭。