暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / SPA for CACHE执行命令

SPA for CACHE执行命令

原创 张玉龙 2020-08-17
1521

–DBMS_SQLTUNE
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/index.html
–DBMS_SQLPA
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLPA.html#GUID-9AAF3672-1CF3-4354-AF41-5BA50BA295F8

SPA for CACHE执行过程,如果是RAC，需要收集每个实例的CACHE SQL

1. creates a SQL tuning set object in the database

alter user SPA identified by oracle;

EXEC DBMS_SQLTUNE.CREATE_SQLSET ( -
                  SQLSET_NAME  => 'TESTDB_STSTAB_0519', -
                  DESCRIPTION  => '11204 to 19600 upgrade SQL Set Create at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
                  SQLSET_OWNER => 'SPA');

复制

2. 从AWR中收集SQL语句（DBMS_SQLTUNE.SELECT_CURSOR_CACHE）

PARSING_SCHEMA_NAME 可选择收集的SCHEMA，也可排除某些SCHEMA

DECLARE
  SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN SQLSET_CUR FOR
    SELECT VALUE(P) FROM TABLE(
           DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'', ''SPA'')',
                        NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
  DBMS_SQLTUNE.LOAD_SQLSET(
               SQLSET_NAME => 'TESTDB_STSTAB_0519',
               SQLSET_OWNER => 'SPA',
               POPULATE_CURSOR => SQLSET_CUR);
  CLOSE SQLSET_CUR;
    END;
/

复制

3. 创建一个临时表，通过该临时表导入和导出SQL tuning sets

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('TESTDB_STSTAB_0519', 'SPA', 'USERS');

复制

4. 将一个或多个SQL tuning sets从它们在SYS schema中的位置复制到 CREATE_STGTAB_SQLSET 过程创建的临时表

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( -
                  SQLSET_NAME          => 'TESTDB_SQLSET_0519', -
                  SQLSET_OWNER         => 'SPA', -
                  STAGING_TABLE_NAME   => 'TESTDB_STSTAB_0519', -
                  STAGING_SCHEMA_OWNER => 'SPA');

复制

5. 查询当前用户可访问的SQL tuning sets

STATEMENT_COUNT，SQL tuning sets中的语句数

set line 300
col NAME for a25
col DESCRIPTION for a70
select NAME,STATEMENT_COUNT,DESCRIPTION from user_sqlset;

NAME                      STATEMENT_COUNT DESCRIPTION
------------------------- --------------- ----------------------------------------------------------------------
TESTDB_SQLSET_0519                    400 11204 to 19600 upgrade SQL Set Create at : 2020-05-22 15:27:00

复制

6. 导出临时表，并导入到目标端上

expdp SPA/oracle dumpfile=TESTDB_STSTAB_0519.dmp directory=PUMP_DIR tables=TESTDB_STSTAB_0519 logfile=expdp_TESTDB_STSTAB_0519.log exclude=STATISTICS cluster=N

scp /backup/expdp/TESTDB_STSTAB_0519.dmp oracle@192.168.1.197:/data/expdp

复制

目标端创建用户

sqlplus system/oracle@TESTDBPDB 
alter user SPA identified by oracle;

复制

目标端导入

impdp SPA/oracle@TESTDBPDB dumpfile=TESTDB_STSTAB_0519.dmp directory=PUMP_DIR FULL=Y logfile=impdp_TESTDB_STSTAB_0519.log cluster=N

复制

7. 将一个或多个SQL tuning sets从它们在临时表中的位置复制到SQL tuning sets schema中，从而使它们成为正确的SQL tuning sets。

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (-
    SQLSET_NAME          => 'TESTDB_SQLSET_0519', -
    SQLSET_OWNER         => 'SPA', -
    REPLACE              => TRUE, -
    STAGING_TABLE_NAME   => 'TESTDB_STSTAB_0519', -
    STAGING_SCHEMA_OWNER => 'SPA');

复制

8. 创建了一个任务来处理和分析一个或多个SQL语句

VARIABLE SPA_TASK VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(  -
    TASK_NAME    => 'SPA_TASK_0519', -
    DESCRIPTION  => '11.2.0.4 to 19.6.0.0 upgrade SPA Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
    SQLSET_NAME  => 'TESTDB_SQLSET_0519', -
    SQLSET_OWNER => 'SPA');

复制

9. 执行以前创建的分析任务,读取源端SQL语句执行信息

EXECUTION_TYPE=CONVERT SQLSET 用于读取在SQL Tuning Set中捕获的统计信息。
当希望避免执行SQL语句时可以使用它，因为SQL Tuning Set中已经存在用于实验的有效数据。

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
    TASK_NAME      => 'SPA_TASK_0519', -
    EXECUTION_NAME => 'EXEC_11204_0519', -
    EXECUTION_TYPE => 'CONVERT SQLSET', -
    EXECUTION_DESC => 'Convert 11204 SQLSET for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

复制
  1. 执行以前创建的分析任务,在目标端执行SQL获取执行信息
    EXECUTION_TYPE=TEST EXECUTE 测试执行每条SQL语句并收集其执行计划和执行统计信息
vi spa_exec_sql_TESTDB_0519.sh
 
echo "WARNING: spa_exec_sql Start @`date`"
sqlplus SPA/oracle@TESTDBPDB << EOF!
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
    TASK_NAME      => 'SPA_TASK_0519', -
    EXECUTION_NAME => 'EXEC_19600_0519', -
    EXECUTION_TYPE => 'TEST EXECUTE', -
    EXECUTION_DESC => 'Execute SQL in 19600 for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
exit
EOF!
echo "WARNING:spa_exec_sql OK @`date`"
 
nohup sh spa_exec_sql_TESTDB_0519.sh &  

复制

11. 生成对比报告

vi spa_html_TESTDB_0519.sql

--ELAPSED_TIME
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_0519', -
                EXECUTION_NAME => 'COMPARE_ET_0519', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'ELAPSED_TIME', -
                                                 'EXECUTE_FULLDML', 'TRUE', -
                                                 'EXECUTION_NAME1','EXEC_11204_0519', -
                                                 'EXECUTION_NAME2','EXEC_19600_0519'), -
                EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

--CPU_TIME
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_0519', -
                EXECUTION_NAME => 'COMPARE_CT_0519', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'CPU_TIME', -
                                                 'EXECUTE_FULLDML', 'TRUE', -
                                                 'EXECUTION_NAME1','EXEC_11204_0519', -
                                                 'EXECUTION_NAME2','EXEC_19600_0519'), -
                EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

--BUFFER_GETS
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_0519', -
                EXECUTION_NAME => 'COMPARE_BG_0519', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'BUFFER_GETS', -
                                                 'EXECUTE_FULLDML', 'TRUE', -
                                                 'EXECUTION_NAME1','EXEC_11204_0519', -
                                                 'EXECUTION_NAME2','EXEC_19600_0519'), -
                EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

--DISK_READS
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_0519', -
                EXECUTION_NAME => 'COMPARE_DR_0519', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'DISK_READS', -
                                                 'EXECUTE_FULLDML', 'TRUE', -
                                                 'EXECUTION_NAME1','EXEC_11204_0519', -
                                                 'EXECUTION_NAME2','EXEC_19600_0519'), -
                EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

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 spa_TESTDB_elapsed_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_ET_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_buffer_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_BG_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_cputime_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_CT_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_disk_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_DR_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_error_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ERRORS','ALL',NULL,1000,'COMPARE_ET_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_unsupported_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','UNSUPPORTED','ALL',NULL,1000,'COMPARE_ET_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off

sqlplus SPA/oracle@TESTDBPDB
set echo on
@spa_html_TESTDB_0519.sql

复制
oracleoracle迁移
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

TA的专栏
OceanBase 学习笔记
收录11篇内容
oracle运维笔记
收录6篇内容
GBase 8s GDCA
收录11篇内容
热门文章
Oracle VM Virtualbox如何实现双向拖拽、共享剪贴板和共享文件夹
2020-07-01 14784浏览
记录一个非常简单的问题--DATAPUMP导入报ORA-39083和ORA-01917，用户或角色不存在
2020-05-30 11165浏览
PostgreSQL插件orafce--安装以及简单介绍
2022-04-01 9433浏览
使用Go语言连接PostgreSQL数据库，pq可能要被pgx取代
2021-11-10 9382浏览
Debezium Oracle Connector 的快照模式 snapshot.mode
2022-04-21 8681浏览
在线实训环境入口
Oracle 18C 在线实训环境
查看详情
最新文章
OceanBase 中的函数索引
2024-05-28 311浏览
OceanBase V3 的分区表上的索引状态是unavailable时添加分区失败
2024-05-27 497浏览
Oceanbase V3 Oracle模式不支持分区表的split partition
2024-05-25 489浏览
Oceanbase V3 分区表 truncate/drop 空分区对全局索引的影响
2024-05-24 465浏览
Oceanbase V3 的 dba_objects 视图针对表分区的 CREATED 字段显示不准确
2024-05-23 143浏览
目录
  • 1. creates a SQL tuning set object in the database
  • 2. 从AWR中收集SQL语句（DBMS_SQLTUNE.SELECT_CURSOR_CACHE）
  • 3. 创建一个临时表，通过该临时表导入和导出SQL tuning sets
  • 4. 将一个或多个SQL tuning sets从它们在SYS schema中的位置复制到 CREATE_STGTAB_SQLSET 过程创建的临时表
  • 5. 查询当前用户可访问的SQL tuning sets
  • 6. 导出临时表，并导入到目标端上
  • 7. 将一个或多个SQL tuning sets从它们在临时表中的位置复制到SQL tuning sets schema中，从而使它们成为正确的SQL tuning sets。
  • 8. 创建了一个任务来处理和分析一个或多个SQL语句
  • 9. 执行以前创建的分析任务,读取源端SQL语句执行信息
  • 11. 生成对比报告