10046 trace and sql
- 1. SQLT 下载
从metalink上下载SQLT工具，参考文档 （以下大部分（SQL可以在sqlt\utl 目录下找到））
1.1 SQLT 安装
SQLT安装在自己的schema SQLTXPLAIN下，它不会在其他业务用户下安装任何对象.你可以在10.2以上版本的UNIX、LINUX或WINDOWS上安装.
安装步骤:
- 卸载之前版本 (可选).
这个步骤可选，用来删除之前的SQLTXPLAIN schema 对象，准备为全新安装做准备.
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqdrop.sql
- 使用SYS用户登录并执行安装脚本sqlt/install/sqcreate.sql.
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqcreate.sql
安装过程中，你需要按照提示确认一些参数:
- 可选的连接标示符.
In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key.
- SQLTXPLAIN 用户的密码.
按照提示设置密码，注意区分大小写.
(如果数据库的用户密码，设置了安全加固，安装SQLT时候，不会报错。那么检查SQLT安装成功的方法)
(select count(*) from dba_objects where owner=‘SQLTXPLAIN’)
- SQLTXPLAIN 用户默认表空间.
选择一个SQLTXPLAIN用户使用的默认表空间，用于存放SQLT信息，要求至少存在50M空余空间.
- SQLTXPLAIN 用户临时表空间.
选择一个SQLTXPLAIN用户使用的临时表空间.
- 可选应用程序用户.
选择你要对哪个用户下的SQL语句进行分析.例如，如果是EBS系统就选择APPS用户，如果是Siebel系统则选择SIEBEL用户，PeopleSoft系统则是SYSADM.这个步骤不会要求输入对应应用用户的密码. 你也可以在SQLT安装完成后手工添加其他SQLT分析用户，赋予需要分析的用户SQLT_USER_ROLE权限或者使用脚本 sqlt/install/sqguser.sql
- Oracle Pack. 使用许可 (T, D or N)
选择你拥有那些ORACLE PACK的使用许可。T代表Oracle Tuning, D代表 Oracle Diagnostic， N代表没有. 默认是T.
附件：安装实例
-bash-3.2$ ls -ld sqlt
drwxr-xr-x 7 oracle oinstall 4096 Apr 2 12:44 sqlt
-bash-3.2$ cd sqlt/install
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:20:45 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> START sqcreate.sql
PL/SQL procedure successfully completed.
Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key
Optional Connect Identifier (ie: @PROD): 保持默认，回车
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN: è设置SQLTXPLAIN用户密码
Re-enter password:
PL/SQL procedure successfully completed.
... please wait
no rows selected
Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: USERS è选择SQLTXPLAIN用户表空间，不能是SYSTEM或SYSAUX
PL/SQL procedure successfully completed.
... please wait
TABLESPACE
------------------------------
TEMP
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP è选择临时表空间
PL/SQL procedure successfully completed.
The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role, or execute sqlt/install/sqguser.sql.
Main application user of SQLT: FRED è选择要分析哪个用户下的SQL
PL/SQL procedure successfully completed.
SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:
"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]: 默认是T，保持
PL/SQL procedure successfully completed.
创建步骤省略 ……
SQCREATE completed. Installation completed successfully.
SQL>
1.2 SQLT 数据收集
- 授予业务用户权限。
SQL>Grant SQLT_USER_ROLE to appuser;
Method 1). 使用SQLT的XTRACT方法对特定的SQL语句进行分析.
首先使用应用用户登录SQL*Plus（安装时选择的应用用户），执行脚本sqlt/run/sqltxtract.sql，输入需要分析语句的SQL_ID或者HASH_VALUE.
If the SQL is still in memory, or it has been captured by AWR, then XTRACT finds it and provides a set of diagnostics files, else XTRACT errors out.
$ cd sqlt/run
$ sqlplus apps
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE]
SQL> START sqltxtract.sql 0w6uydn50g8cx
SQL> START sqltxtract.sql 2524255098
Above method will not print sql advise
$ cd sqlt/run
$ sqlplus / as sysdba
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE]
SQL> START sqltxtract.sql 0w6uydn50g8cx
SQL> START sqltxtract.sql 2524255098
Above method will print sql advise
###sample
##安装SQLT 5分钟
##运行分析：先授权，在运行分析，总时间大概5分钟
Grant SQLT_USER_ROLE to dbmonopr;
START sqltxtract.sql 7yqwwybwctqsp sqlt
注意事项：
In case of a session disconnect please verify the following:
1. There are no errors in sqltxtract.log.
2. Your SQL 7yqwwybwctqsp exists in memory or in AWR.
3. You connected as the application user that issued original SQL. 或者SYS用户
4. User SYS has been granted SQLT_USER_ROLE.
5. temp 表空间1024M 以上
6. application user 有SQLT_USER_ROLE 权限。
Method 2). 使用SQLT的XECUTE方法对特定的SQL语句进行分析.
XECUTE收集信息更加详尽，相对而言，消耗时间更长。
This method provides more detail than XTRACT. As the name XECUTE implies, it executes the SQL being analyzed, then it produces a set of diagnostics files. Its major drawback is that if the SQL being analyzed takes long to execute, this method will also take long.
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE]
SQL> START sqltxtrxec.sql 0w6uydn50g8cx
SQL> START sqltxtrxec.sql 2524255098
Method 3)如何找到是优化器的哪个变化导致SQL性能变化
- 单独安装sqlt->utl->xplore目录下执行install.sql
- 生成create_xplore_script.sql并执行
- 修改sql语句，增加hint /* ^^unique_id */保存到文件
- 选择XECUTE模式执行
- 选择CBO Parameters Y
- 选择EXADATA Parameters N
- 选择Fix Control Y
- 选择SQL Monitor N
- 执行@xplore_script_1.sql带入变量用户、密码、sql文件名
- 分析生成的html格式报告
Method 4 )找到最优的执行计划的profile,并固化
1.如何从sqlset中获取生成sql profile的脚本：
COE提供了一个脚本coe_xfr_sql_profile.sql，通过该脚本可以从AWR和当
前内存中生成问题语句sql profile的脚本。
但是实际上很多sqlset中问题SQL在AWR和内存中已经找不到。
我们对COE的脚本进行了调整，可以直接从sqlset中获取生成sql profile的
脚本，大大减少了我们生成sql profile的工作量。
Core_xfr_sql_profile.sql （SQL可以在sqlt\utl 目录下找到）
举例如下：####sample@############
https://www.cnblogs.com/gull/p/5602166.html
1.具有SQL_ID和plan_hash_id，运行以下命令：
SQLPLUS / AS SYSDBA
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
这将生成一个名为类似的脚本：
coe_xfr_sql_profile_ && sql_id._ && plan_hash_value .. SQL
例如：
coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql
2.
此文件包含您从获得它的数据库中存储的配置文件。现在，您可以使用该脚本相同的计划应用到另一个数据库：4）在DB要创建此配置文件就执行这个脚本
SQLPLUS / AS SYSDBA
SQL> START coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql
#########################
通过outline导入sql profile
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr('<hint>'); dbms_sqltune.import_sql_profile('<sqlstatment>',v_hints,'sqlprofile',force_match=>true);
end;
/
Method 4: 强制使用force
2、run /*+ test */ Hinted SQL
SELECT /*+ test */TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test';
3、Find sql_id and plan_hash_value for the /*+ test */ Hinted SQL
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%/*+ test */%';
bpwkfmp2yzmdd 244914142
SELECT /*+ test */TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test'
4、Run the script coe_xfr_sql_profile.sql as SYSDBA the sql_id and the good Plan Hash Value
START coe_xfr_sql_profile.sql <sql_id> <plan hash value for good plan>
1、执行以下语句生成好的执行计划
SELECT /*+ test */TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test';
2、找出/*+ test */语句的sql_id
bpwkfmp2yzmdd
SELECT /*+ test */TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test'
3、通过coe脚本生成sqlprofile
coe_xfr_sql_profile_bpwkfmp2yzmdd_244914142.sql
4、修改coe_xfr_sql_profile_bpwkfmp2yzmdd_244914142.sql
将脚本中以下部分参照以下方式调整：
wa(q'[SELECT /*+ test */TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNO]');
wa(q'[NYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test' ]');
==============================================================》
wa(q'[SELECT /*+RULE*/ TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNO]');
wa(q'[NYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test' ]');
force_match => FALSE
====================》
force_match => TRUE
5、在数据库中执行绑定脚本
@coe_xfr_sql_profile_bpwkfmp2yzmdd_244914142.sql
6、测试语句是否使用此sqlprofile
set autot on后会出现以下
Note
-----
- SQL profile "coe_bpwkfmp2yzmdd_244914142" used for this statement-----说明绑定成功
6、Using coe_xfr_sql_profile_bpwkfmp2yzmdd_244914142.sql as sysdba
@coe_xfr_sql_profile_bpwkfmp2yzmdd_244914142.sql
select to_char(sql_text) from dba_sql_profiles;
Method 5:从内存中手工生成OUTLINE (out line support 10 ~ 11.1, 11.2 despport)
测试库：
1)
保存语句的outline
SQL> alter session set create_stored_outlines=true;
SQL>select SQL_ID,hash_value,outline_category,SQL_PROFILE,child_number from v$sql where sql_id='c5w1f1cd898xu'
SQL> exec dbms_outln.create_outline(1481129178, 0); ----参数值（sql hash value,child number)
SQL> alter session set create_stored_outlines=false;
或者
SQL> conn / as sysdba
SQL> grant create any outline, drop any outline to scott;
1) Create an outline in default category with a system-generated name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter session set create_stored_outlines=true;
SQL> select ename from emp where empno=7788;
SQL> alter session set create_stored_outlines=false;
2)
3)
导出outline相关数据
exp username/password tables=outln.ol\$ outln.ol\$hints outln.ol\$nodes file=ol.dmp log=ol_exp.log
生产库：
1.
拷贝导出文件到生产库并导入
2.
Ftp dump file到目标端
imp username/password file=ol.dmp full=y ignore=y log=ol_imp.log
3.
启用outline
4.
触发sql语句重新解析,相关对象的DDL会触发sql重新解析以使用outline
Grant select on table_name to username;
5.
Kill 掉数据库正在执行的使用坏执行计划的SQL
6.
复核outline是否生效
select hash_value,sql_text,outline_category from v$sqlarea where hash_value='1481129178';
1) get output of DBMS_XPLAN.DISPLAY_CURSOR as follows:
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));
Among the execution plan and other information, if a Stored Outline has been
used, the following note will be part of the above output:
Note
-----
- outline "SYS_OUTLINE_08102118055046801" used for this statement
2) a query can also be used, three varieties are given, for 10.1, 10.2
and 11.1:
for 10.1 the query is:
select info outline_name from (
SELECT rownum r, extractValue(value(d), '/info') info
FROM v$sql_plan p, table(xmlsequence(extract(xmltype(p.remarks), '/remark/info'))) d
WHERE remarks is not NULL and id=1 and sql_id='&SQL_ID' and child_number=&CHILD)
where r=2;
for 10.2 the query is:
select info outline_name from (
SELECT rownum r, extractValue(value(d), '/info') info
FROM v$sql_plan p, table(xmlsequence(extract(xmltype(p.other_xml), '/other_xml/info'))) d
WHERE other_xml is not NULL and id=1 and sql_id='&SQL_ID' and child_number=&CHILD)
where r=4;
for 11.1 the query is:
select info outline_name from (
SELECT rownum r, extractValue(value(d), '/info') info
FROM v$sql_plan p, table(xmlsequence(extract(xmltype(p.other_xml), '/other_xml/info'))) d
WHERE other_xml is not NULL and id=1 and sql_id='&SQL_ID' and child_number=&CHILD)
where r=5;
example output of this query is:
...
Enter value for sql_id: dfuhrw94jv820
Enter value for child: 0
old 4: WHERE other_xml is not NULL and id=1 and sql_id='&SQL_ID' and child_number=&CHILD)
new 4: WHERE other_xml is not NULL and id=1 and sql_id='dfuhrw94jv820' and child_number=0)
OUTLINE_NAME
----------------------------------------------------------------------------------------------
"SYS_OUTLINE_08102118055046801"
dba_outlines视图有outline相关的信息
7.检查OTLINE的hint
SELECT * FROM user_outline_hints WHERE name=UPPER('test_outline_exchange');
SELECT HINT#,HINT_TEXT FROM outln.ol$hints WHERE ol_name='TEST_OUTLINE_EXCHANGE2';
8.查看PROFILE 的hint
.coe_xfr_sql_profile_83zu6pbuujtxs_3617692013.sql
http://www.killdb.com/2012/09/16/%E4%BD%BF%E7%94%A8sql-profle%E8%BF%9B%E8%A1%8C%E5%81%B7%E6%A2%81%E6%8D%A2%E6%9F%B1%E7%9A%84%E5%B0%8F%E4%BE%8B%E5%AD%90-outline-exchange%E7%BB%AD.html
9.查看执行计划的Note
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));
10.profile 操作
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(20);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => 'test_tuning_task',
name => 'test_profile');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
BEGIN
DBMS_SQLTUNE.alter_sql_profile (
name =>
'test_profile',
attribute_name => 'STATUS',
value =>
'DISABLED');
END;
/
BEGIN
DBMS_SQLTUNE.drop_sql_profile
(
name => 'test_profile',
ignore => TRUE);
END;
/
How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (Doc ID 457531.1)
11.
coe_xfr_sql_profile_<original_sql_id>_<planhash_value>.sql
SOP: MOS - Creating a SQL Profile from a Hinted SQL Statement (Doc ID 1507152.1)
12.
This document shows how to get execution statistics and history for a SQL using sql_id.
From Memory
set pages 1000 lines 200
col first_load_time for a20
col last_load_time for a20
col outline_category for a20
col sql_profile for a32
select sql_id, child_number, plan_hash_value, first_load_time, last_load_time,
outline_category, sql_profile, executions,
trunc(decode(executions, 0, 0, rows_processed/executions)) rows_avg,
trunc(decode(executions, 0, 0, fetches/executions)) fetches_avg,
trunc(decode(executions, 0, 0, disk_reads/executions)) disk_reads_avg,
trunc(decode(executions, 0, 0, buffer_gets/executions)) buffer_gets_avg,
trunc(decode(executions, 0, 0, cpu_time/executions)) cpu_time_avg,
trunc(decode(executions, 0, 0, elapsed_time/executions)) elapsed_time_avg,
trunc(decode(executions, 0, 0, application_wait_time/executions))
apwait_time_avg,
trunc(decode(executions, 0, 0, concurrency_wait_time/executions))
cwait_time_avg,
trunc(decode(executions, 0, 0, cluster_wait_time/executions)) clwait_time_avg,
trunc(decode(executions, 0, 0, user_io_wait_time/executions)) iowait_time_avg,
trunc(decode(executions, 0, 0, plsql_exec_time/executions)) plsexec_time_avg,
trunc(decode(executions, 0, 0, java_exec_time/executions)) javexec_time_avg
from v$sql
where sql_id = '&sql_id'
order by sql_id, child_number;
From AWR
set pages 1000 lines 200
col sql_profile for a32
select sql_id, snap_id, plan_hash_value, sql_profile, executions_total,
trunc(decode(executions_total, 0, 0, rows_processed_total/executions_total))
rows_avg,
trunc(decode(executions_total, 0, 0, fetches_total/executions_total))
fetches_avg,
trunc(decode(executions_total, 0, 0, disk_reads_total/executions_total))
disk_reads_avg,
trunc(decode(executions_total, 0, 0, buffer_gets_total/executions_total))
buffer_gets_avg,
trunc(decode(executions_total, 0, 0, cpu_time_total/executions_total))
cpu_time_avg,
trunc(decode(executions_total, 0, 0, elapsed_time_total/executions_total))
elapsed_time_avg,
trunc(decode(executions_total, 0, 0, iowait_total/executions_total))
iowait_time_avg,
trunc(decode(executions_total, 0, 0, clwait_total/executions_total)) clwait_time_avg,
trunc(decode(executions_total, 0, 0, apwait_total/executions_total))
apwait_time_avg,
trunc(decode(executions_total, 0, 0, ccwait_total/executions_total))
ccwait_time_avg,
trunc(decode(executions_total, 0, 0, plsexec_time_total/executions_total)) plsexec_time_avg,
trunc(decode(executions_total, 0, 0, javexec_time_total/executions_total))
javexec_time_avg
from dba_hist_sqlstat
where sql_id = '&sql_id'
order by sql_id, snap_id;
13:
How to use hints to customize SQL Profile or SQL
PLAN Baseline (文档 ID 1400903.1)
Step 4a: Using coe_load_sql_profile.sql
Both plans need to be in cache or in AWR
- Connect as user with DBA privilege, for example SYSTEM
- Note: Do not connect as SYS as the staging table cannot be created in SYS schema and you will receive an error: ORA-19381: cannot create staging table in SYS schema
connect system/pass
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 329d885bxvrcr
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 4f74t4ab7rd5y
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
2872589290 .003
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 2872589290
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: "2872589290"
.
.
.
ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5Y PHV:2872589290 SIGNATURE:15822026218863957422 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
****************************************************************************
* Enter SCOTT password to export staging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************
Export: Release 11.2.0.3.0 - Production on Sun Mar 11 14:45:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
.
.
.
coe_load_sql_profile completed.
######sample
SQLT工具包含一个脚本，名字是coe_load_sql_profile.sql,下面以用户SCOTT的EMP表为例，说明如何使用该脚本固定sql profile.
1.
SQL> -- 对emp的列ename创建一个索引
SQL> create index i_emp_ename on scott.emp(ename);
索引已创建。
SQL> --收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP')
PL/SQL 过程已成功完成。
2.运行原始的SQL语句
SQL> select ename from scott.emp where ename='MILLER';
ENAME
----------
MILLER
执行计划如下:
-------------------------------
SQL_ID 329d885bxvrcr
-------------------------------
Plan hash value: 4001599462
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | INDEX RANGE SCAN| I_EMP_ENAME | 1 |
-------------------------------------------------
--这是我们需要更改的plan
3. 运行带有hint的SQL
SQL> select /*+ FULL (EMP) */ ename from scott.emp where ename='MILLER';
执行计划如下:
-------------------------------
SQL_ID 4f74t4ab7rd5y
-------------------------------
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| EMP | 1 |
-------------------------------------------
--这是我们需要的plan
4: 可以通过下面的SQL获取这2个SQL的sql_id和plan_hash_value
SQL> select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like '%scott.emp%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------------------------------------------------------
4f74t4ab7rd5y 3956160932 select /*+ FULL (EMP) */ ename from scott.emp where ename='MILLER'
329d885bxvrcr 4001599462 select ename from scott.emp where ename='MILLER'
--329d885bxvrcr - 这是原始语句的SQL ID
--4f74t4ab7rd5y - 这是使用hint的SQL ID
--3956160932 - 这是需要替换的plan hash value.
5.进行plan的替换
--这两个计划都需要在缓存或AWR中
--需要以具有DBA权限的用户身份连接，例如SYSTEM OR DBMGR (不接受sys用户，会抱错)
SQLTXPLAIN
SQL> conn system
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
输入 1 的值: 329d885bxvrcr
Parameter 2:
MODIFIED_SQL_ID (required)
输入 2 的值: 4f74t4ab7rd5y
Parameter 3:
PLAN_HASH_VALUE (required)
输入 3 的值: 3956160932
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: "3956160932"
ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5Y PHV:3956160932 SIGNATURE:15822026218863957422 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
****************************************************************************
* Enter SYSTEM password to export staging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************
Export: Release 11.2.0.4.0 - Production on 星期二 12月 5 15:36:24 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
口令:
coe_load_sql_profile completed.
SQL>
About to export specified tables via Conventional Path ...
. . exporting table STGTAB_SQLPROF_39JJ63TQ3KJAB 1 rows exported
Export terminated successfully without warnings.
If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:
imp DBMGR file=STGTAB_SQLPROF_39jj63tq3kjab.dmp tables=STGTAB_SQLPROF_39jj63tq3kjab ignore=Y
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '39JJ63TQ3KJAB_3617692013',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_39jj63tq3kjab',
staging_schema_owner => 'DBMGR' );
END;
/
adding: coe_load_sql_profile_39jj63tq3kjab.log (deflated 76%)
adding: STGTAB_SQLPROF_39jj63tq3kjab.dmp (deflated 89%)
adding: coe_load_sql_profile.log (deflated 62%)
deleting: coe_load_sql_profile.log
coe_load_sql_profile completed.
6.运行原来的SQL语句
SQL>conn scott/tiger
SQL> select ename from scott.emp where ename='MILLER';
PLAN_TABLE_OUTPUT
-------------------------------
SQL_ID 329d885bxvrcr
-------------------------------
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| EMP | 1 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='MILLER')
Note
-----
- SQL profile 329D885BXVRCR_3956160932 used for this statement
我们可以看到，原始的SQL现在和使用hint的sql具有相同的plan_hash_value和plan。
此外，我们看到这个SQL启用了一个SQL配置文件。
相关参考:
Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1)
15
Loading Hinted Execution Plans into SQL Plan Baseline. (Doc ID 787692.1)
#####
#########test 2
STEP 1: RUN THE Original SQL
select
client_name client,
window_name,
jobs_created,
jobs_started,
jobs_completed,
to_char(window_start_time,'YYYY-MM-DD HH24:MI:SS') start_time,
to_char(window_end_time,'YYYY-MM-DD HH24:MI:SS') end_time,
window_duration
from dba_autotask_client_history
where client_name = 'auto optimizer stats collection';
Step 2: Run Hinted SQL
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B584FDD1")
MERGE(@"SEL$29F99543" >"SEL$14")
OUTLINE_LEAF(@"SEL$ABDE6DFF")
MERGE(@"SEL$6" >"SEL$5")
OUTLINE_LEAF(@"SEL$8A3193DA")
MERGE(@"SEL$8" >"SEL$7")
OUTLINE_LEAF(@"SEL$0EE6DB63")
MERGE(@"SEL$10" >"SEL$9")
OUTLINE_LEAF(@"SEL$42DFC41A")
MERGE(@"SEL$12" >"SEL$11")
OUTLINE_LEAF(@"SEL$6450F756")
MERGE(@"SEL$13" >"SEL$4")
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A" >"SEL$1")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$29F99543")
MERGE(@"SEL$16" >"SEL$15")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3" >"SEL$2")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$16")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "X"@"SEL$2")
NO_ACCESS(@"SEL$5C160134" "WLOG"@"SEL$2")
FULL(@"SEL$5C160134" "E"@"SEL$3")
INDEX_RS_ASC(@"SEL$5C160134" "CO"@"SEL$3" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5C160134" "CO"@"SEL$3")
LEADING(@"SEL$5C160134" "X"@"SEL$2" "WLOG"@"SEL$2" "E"@"SEL$3" "CO"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$5C160134" "WLOG"@"SEL$2")
USE_NL(@"SEL$5C160134" "E"@"SEL$3")
USE_NL(@"SEL$5C160134" "CO"@"SEL$3")
PUSH_SUBQ(@"SEL$B584FDD1")
FULL(@"SEL$6450F756" "SCHEDULER$_EVENT_LOG"@"SEL$13")
FULL(@"SEL$42DFC41A" "QU"@"SEL$12")
FULL(@"SEL$42DFC41A" "X$KJIDT"@"SEL$12")
FULL(@"SEL$42DFC41A" "KS"@"SEL$12")
FULL(@"SEL$42DFC41A" "KV"@"SEL$12")
LEADING(@"SEL$42DFC41A" "QU"@"SEL$12" "X$KJIDT"@"SEL$12" "KS"@"SEL$12" "KV"@"SEL$12")
USE_MERGE_CARTESIAN(@"SEL$42DFC41A" "X$KJIDT"@"SEL$12")
USE_MERGE_CARTESIAN(@"SEL$42DFC41A" "KS"@"SEL$12")
USE_MERGE_CARTESIAN(@"SEL$42DFC41A" "KV"@"SEL$12")
FULL(@"SEL$0EE6DB63" "W"@"SEL$10")
INDEX_RS_ASC(@"SEL$0EE6DB63" "WO"@"SEL$10" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$0EE6DB63" "WO"@"SEL$10")
INDEX(@"SEL$0EE6DB63" "U"@"SEL$10" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
LEADING(@"SEL$0EE6DB63" "W"@"SEL$10" "WO"@"SEL$10" "U"@"SEL$10")
USE_NL(@"SEL$0EE6DB63" "WO"@"SEL$10")
USE_NL(@"SEL$0EE6DB63" "U"@"SEL$10")
FULL(@"SEL$8A3193DA" "SCHEDULER$_EVENT_LOG"@"SEL$8")
FULL(@"SEL$ABDE6DFF" "SCHEDULER$_EVENT_LOG"@"SEL$6")
FULL(@"SEL$B584FDD1" "X"@"SEL$16")
FULL(@"SEL$B584FDD1" "Y"@"SEL$16")
LEADING(@"SEL$B584FDD1" "X"@"SEL$16" "Y"@"SEL$16")
USE_NL(@"SEL$B584FDD1" "Y"@"SEL$16")
END_OUTLINE_DATA
(MIKE) */
client_name client,
window_name,
jobs_created,
jobs_started,
jobs_completed,
to_char(window_start_time,'YYYY-MM-DD HH24:MI:SS') start_time,
to_char(window_end_time,'YYYY-MM-DD HH24:MI:SS') end_time,
window_duration
from dba_autotask_client_history
where client_name = 'auto optimizer stats collection';
Step 3: Find sql_id and plan_hash_value for the 2 SQLs (有的时候，加入hint后发现V$SQL找不到该条SQL,可以改写成gv$sqlarea )
SQL> select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like '%MIKE%';
select sql_id ,plan_hash_value,sql_fulltext from gv$sqlarea where sql_fulltext like '%MIKE%';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
5p9qdczsk4nfy 222931604
Original sql id: 84gvjqukvktcp - this is the SQL ID of the original statement. We want to change the plan for this statement to that of the hinted SQL.
Modified sql id: 5p9qdczsk4nfy - this is the SQL ID of the hinted statement. This is the source of the plan for the statement we want to change.
Modified Plan Hash Value: 222931604 - this is the PHV of the target plan (the plan from the hinted query)
step4:
此时，有2个方案可以稳定计划。
A：如果要强制访问路径，请使用轮廓（coe_load_sql_baseline.sql）
B：如果要沿特定方向推动计划，但又需要一定的灵活性，请使用配置文件（coe_load_sql_profile.sql）
---这里选择sql_baseline,发现一个问题ORA-01403: no data found ，改用sql_profile（coe_load_sql_profile.sql） 碰到问题 ORA-19381: cannot create staging table in SYS schema
SQL>@coe_load_sql_baseline.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 84gvjqukvktcp
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 5p9qdczsk4nfy
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
222931604 .003
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 222931604
Values passed to coe_load_sql_baseline:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "329d885bxvrcr"
MODIFIED_SQL_ID: "4f74t4ab7rd5y"
PLAN_HASH_VALUE: " 2872589290"
.
.
.
--
改用sql_profile（coe_load_sql_profile.sql） 碰到问题 ORA-19381: cannot create staging table in SYS schema
FIX:Note: Do not connect as SYS as the staging table cannot be created in SYS schema and you will receive an error: ORA-19381: cannot create staging table in SYS schema
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 84gvjqukvktcp
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 5p9qdczsk4nfy
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
222931604 1.663
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 222931604
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "84gvjqukvktcp"
MODIFIED_SQL_ID: "5p9qdczsk4nfy"
PLAN_HASH_VALUE: "222931604"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>
SQL>SET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
0004 DB_VERSION('12.2.0.1')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$B584FDD1")
0007 MERGE(@"SEL$29F99543" >"SEL$14")
0008 OUTLINE_LEAF(@"SEL$ABDE6DFF")
0009 MERGE(@"SEL$6" >"SEL$5")
0010 OUTLINE_LEAF(@"SEL$8A3193DA")
0011 MERGE(@"SEL$8" >"SEL$7")
0012 OUTLINE_LEAF(@"SEL$0EE6DB63")
0013 MERGE(@"SEL$10" >"SEL$9")
0014 OUTLINE_LEAF(@"SEL$42DFC41A")
0015 MERGE(@"SEL$12" >"SEL$11")
0016 OUTLINE_LEAF(@"SEL$6450F756")
0017 MERGE(@"SEL$13" >"SEL$4")
0018 OUTLINE_LEAF(@"SEL$5C160134")
0019 MERGE(@"SEL$335DD26A" >"SEL$1")
0020 OUTLINE(@"SEL$14")
0021 OUTLINE(@"SEL$29F99543")
0022 MERGE(@"SEL$16" >"SEL$15")
0023 OUTLINE(@"SEL$5")
0024 OUTLINE(@"SEL$6")
0025 OUTLINE(@"SEL$7")
0026 OUTLINE(@"SEL$8")
0027 OUTLINE(@"SEL$9")
0028 OUTLINE(@"SEL$10")
0029 OUTLINE(@"SEL$11")
0030 OUTLINE(@"SEL$12")
0031 OUTLINE(@"SEL$4")
0032 OUTLINE(@"SEL$13")
0033 OUTLINE(@"SEL$1")
0034 OUTLINE(@"SEL$335DD26A")
0035 MERGE(@"SEL$3" >"SEL$2")
0036 OUTLINE(@"SEL$15")
0037 OUTLINE(@"SEL$16")
0038 OUTLINE(@"SEL$2")
0039 OUTLINE(@"SEL$3")
0040 FULL(@"SEL$5C160134" "X"@"SEL$2")
0041 NO_ACCESS(@"SEL$5C160134" "WLOG"@"SEL$2")
0042 FULL(@"SEL$5C160134" "E"@"SEL$3")
0043 INDEX_RS_ASC(@"SEL$5C160134" "CO"@"SEL$3" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
0044 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5C160134" "CO"@"SEL$3")
0045 LEADING(@"SEL$5C160134" "X"@"SEL$2" "WLOG"@"SEL$2" "E"@"SEL$3" "CO"@"SEL$3")
0046 USE_MERGE_CARTESIAN(@"SEL$5C160134" "WLOG"@"SEL$2")
0047 USE_NL(@"SEL$5C160134" "E"@"SEL$3")
0048 USE_NL(@"SEL$5C160134" "CO"@"SEL$3")
0049 PUSH_SUBQ(@"SEL$B584FDD1")
0050 FULL(@"SEL$6450F756" "SCHEDULER$_EVENT_LOG"@"SEL$13")
0051 FULL(@"SEL$42DFC41A" "QU"@"SEL$12")
0052 FULL(@"SEL$42DFC41A" "X$KJIDT"@"SEL$12")
0053 FULL(@"SEL$42DFC41A" "KS"@"SEL$12")
0054 FULL(@"SEL$42DFC41A" "KV"@"SEL$12")
0055 LEADING(@"SEL$42DFC41A" "QU"@"SEL$12" "X$KJIDT"@"SEL$12" "KS"@"SEL$12" "KV"@"SEL$12")
0056 USE_MERGE_CARTESIAN(@"SEL$42DFC41A" "X$KJIDT"@"SEL$12")
0057 USE_MERGE_CARTESIAN(@"SEL$42DFC41A" "KS"@"SEL$12")
0058 USE_MERGE_CARTESIAN(@"SEL$42DFC41A" "KV"@"SEL$12")
0059 FULL(@"SEL$0EE6DB63" "W"@"SEL$10")
0060 INDEX_RS_ASC(@"SEL$0EE6DB63" "WO"@"SEL$10" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
0061 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$0EE6DB63" "WO"@"SEL$10")
0062 INDEX(@"SEL$0EE6DB63" "U"@"SEL$10" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
0063 LEADING(@"SEL$0EE6DB63" "W"@"SEL$10" "WO"@"SEL$10" "U"@"SEL$10")
0064 USE_NL(@"SEL$0EE6DB63" "WO"@"SEL$10")
0065 USE_NL(@"SEL$0EE6DB63" "U"@"SEL$10")
0066 FULL(@"SEL$8A3193DA" "SCHEDULER$_EVENT_LOG"@"SEL$8")
0067 FULL(@"SEL$ABDE6DFF" "SCHEDULER$_EVENT_LOG"@"SEL$6")
0068 FULL(@"SEL$B584FDD1" "X"@"SEL$16")
0069 FULL(@"SEL$B584FDD1" "Y"@"SEL$16")
0070 LEADING(@"SEL$B584FDD1" "X"@"SEL$16" "Y"@"SEL$16")
0071 USE_NL(@"SEL$B584FDD1" "Y"@"SEL$16")
0072 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_84GVJQUKVKTCP"
staging table "STGTAB_SQLPROF_84GVJQUKVKTCP" did not exist
creating staging table "STGTAB_SQLPROF_84GVJQUKVKTCP"
packaging new sql profile into staging table "STGTAB_SQLPROF_84GVJQUKVKTCP"
PROFILE_NAME
------------------------------
84GVJQUKVKTCP_222931604
SQL>REM
SQL>REM SQL Profile
SQL>REM ~~~~~~~~~~~
SQL>REM
SQL>SELECT signature, name, category, type, status
2 FROM dba_sql_profiles WHERE name = :name;
SIGNATURE NAME CATEGORY TYPE STATUS
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------- ----------------
9978660567500671649 84GVJQUKVKTCP_222931604 DEFAULT MANUAL ENABLED
SQL>SELECT description
2 FROM dba_sql_profiles WHERE name = :name;
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:84GVJQUKVKTCP MODIFIED:5P9QDCZSK4NFY PHV:222931604 SIGNATURE:9978660567500671649 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
****************************************************************************
* Enter DBMGR password to export staging table STGTAB_SQLPROF_84gvjqukvktcp
****************************************************************************
Export: Release 12.2.0.1.0 - Production on Wed May 6 10:34:57 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
About to export specified tables via Conventional Path ...
. . exporting table STGTAB_SQLPROF_84GVJQUKVKTCP 1 rows exported
Export terminated successfully without warnings.
If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:
imp DBMGR file=STGTAB_SQLPROF_84gvjqukvktcp.dmp tables=STGTAB_SQLPROF_84gvjqukvktcp ignore=Y
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '84GVJQUKVKTCP_222931604',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_84gvjqukvktcp',
staging_schema_owner => 'DBMGR' );
END;
/
adding: coe_load_sql_profile_84gvjqukvktcp.log (deflated 79%)
adding: STGTAB_SQLPROF_84gvjqukvktcp.dmp (deflated 87%)
adding: coe_load_sql_profile.log (deflated 62%)
deleting: coe_load_sql_profile.log
coe_load_sql_profile completed.
STEP 7 .运行原来的SQL语句验证是否生效
set atutotrace on
select
client_name client,
window_name,
jobs_created,
jobs_started,
jobs_completed,
to_char(window_start_time,'YYYY-MM-DD HH24:MI:SS') start_time,
to_char(window_end_time,'YYYY-MM-DD HH24:MI:SS') end_time,
window_duration
from dba_autotask_client_history
where client_name = 'auto optimizer stats collection';
- SQL profile "84GVJQUKVKTCP_222931604" used for this statement
- SQL plan baseline "SQL_PLAN_8nyukxay16kp1f229150f" used for this statement
NOTE 1:
SQLT这个报错使用SYS用户执行会有，使用APP用户执行，也会有。但是使用SYS用户执行的时候，会有
STA report,
sqlt$a: *** i:"EXPLAIN PLAN FOR" this SQL errored out when executed connected as "SYS". Always execute SQLT connected as the application user.
sqlt$a: *** i:ORA-00942: table or view does not exist
NOTE2：
SQLT 的10053 trace 实际上是EXPLAIN PLAN FOR，如下：
EXPLAIN PLAN SET statement_id = '46969' INTO SQLTXPLAIN.sqlt$_sql_plan_table FOR
SQL_TEXT
NOTE3:
Select *** into bind from table where …….
(其中into 可以忽略，其余部分按照正常调整方法调整即可)
Method 6: sqltrpt使用
10G 自动SQL调优脚本 sqltrpt使用
在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql脚本,用于抽取占用资源较多的sql,并可以为指定的sql生成执行计划,资源占用较多的sql分为两部分
$ sqlplus / as sysdba
SQL> set pages 50
SQL> @?/rdbms/admin/sqltrpt
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: aykvshm7zsabd (此处输入想要查看执行计划的sqlid)
建议如下：
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'TASK_2105', replace
=> TRUE);
有predicate information
问题1： 如果使用sqltrpt 碰到问题,参考如下手册
12
" Sql ran abnormally longer with changed
exec plan " This kind of query problems mostly regarding stale / incorrect
statistics for further investigation kindly do one of the following and then
ZIP and upload to SR
--If query finish but it toke ti much time
++SQLT XECUTE
from where to download: Note
215187.1
1- Unzip the file sqlt.zip to a directory
2- Go to ./install directory
3- run the file sqcreate.sql (connected
INTERNAL as SYSDBA i.e. as SYS)
make sure there are no errors in
the *.lis files
4- Go to ./run then next
5- For XECUTE need to have the SQL in file
XECUTE: sample script2.sql:
have the correct bind variables and the correct SQL
make
sure to have the unique comment left in the SQL /* &&unique_id */
save
the file then next
6- SQL> start sqltxecute.sql script2.txt
7- find the files under the current directory
8- Find more instructions and details about
installation and use in ./instructions.txt and ./doc/SQLTXPLAIN.pdf
--If query runs and does not finish kindly do
the following
XTRACT Method
Use this method if you know the SQL_ID or the
HASH_VALUE of the SQL to be analyzed, else use XECUTE. The SQL_ID can be found
on an AWR report, and the HASH_VALUE on any SQL Trace (above the SQL text and
identified by the "hv=" token).
If the SQL is still in memory, or it has been
captured by AWR, then XTRACT finds it and provides a set of diagnostics files,
else XTRACT errors out.
Important performance statistics, like actual
number of rows per execution plan operation, will be available if the SQL was
parsed while parameter STATISTICS_LEVEL was set to ALL when the SQL was
hard-parsed. You can also produce same valuable performance statistics by
inlcuding the following CBO hint in your SQL: /*+ GATHER_PLAN_STATISTICS */. On
11g you may want your SQL to contain the following CBO Hints for emhanced
diagnostics: /*+ GATHER_PLAN_STATISTICS MONITOR */
To use this XTRACT method, be sure SQLT has been
installed first, then connect into SQL*Plus as the application user that
executed the SQL to be analyzed and execute the sqlt/run/sqltxtract.sql script
passing the SQL_ID or HASH_VALUE.
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtract.sql
[SQL_ID]|[HASH_VALUE]
SQL> START sqltxtract.sql 0w6uydn50g8cx
SQL> START sqltxtract.sql 2524255098
Please tell me if you need further clarification
and I will be happy to contact you
Please keep me posted
13.
use of sql tuning advisor
variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);
EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sql_id => '4hu3f8jmv4jvx', -
scope => 'comprehensive', -
time_limit => 18000, -
task_name => 'sql_tuning_task1',-
description => 'Tune my query');
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sql_tuning_task1');
SET LONG 1000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task1') from DUAL;
附件：分析实例
-bash-3.2$ ls -ld sqlt
drwxr-xr-x 7 oracle oinstall 4096 Apr 2 12:44 sqlt
-bash-3.2$ cd sqlt/run
-bash-3.2$ sqlplus fred/oracle
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:42:13 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> START sqltxtract.sql 00wmnkqhsb7ap
PL/SQL procedure successfully completed.
Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)
Paremeter 2:
SQLTXPLAIN password (required)
Password for user SQLTXPLAIN: 输入SQLTXPLAIN用户密码
Re-enter password:
PL/SQL procedure successfully completed.
Value passed to sqltxtract:
~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID_OR_HASH_VALUE: "00wmnkqhsb7ap"
省略……
... collecting diagnostics details ... 开始收集需要数据
收集数据过程省略……
File sqlt_s77207_xtract_00wmnkqhsb7ap.zip for 00wmnkqhsb7ap has been created.
SQLTXTRACT completed.
收集完成，报告已经生成在当前目录下的sqlt_s77207_xtract_00wmnkqhsb7ap.zip
2.1 SQLHC 下载
从文档SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)，附件中找到如下文件“SQLHC Tool”，然后下载。
For example:
|
|
- SQLHC PPT Presentation(1.32 MB)
- Annotated SQLHC example(89.48 KB)
- SQLHC Tool(71.87 KB)
- SQLHC Output Sample(1.2 MB)
2.2 SQLHC 数据收集
收集方法如下：
Login to the database server and set the environment used by the Database Instance
Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute this script. It will request to enter two parameters:
Oracle Pack License (Tuning,
Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning
pack includes Oracle Diagnostics)
A valid SQL_ID for the SQL to be analyzed. for example sql_id= djkbyr8vkc64h
For example:
# sqlplus / as sysdba
SQL> START sqlhc.sql T djkbyr8vkc64h
3.SQL-Monitor 数据收集
1．在以下情况下，SQL-Monito才会开启收集SQL实时运行数据。
-- Monitors hint
– Serial statements with 5 seconds of total CPU/IO time
– All parallel statements
2.收集方法
For completed or currently executing statement。
方法如下：
- Once the SQL_ID is identified, we can generate a report using the REPORT_SQL_MONITOR function.
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '526mvccm5nfy4',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
PS：
Examples of the output for each available TYPE are displayed below.
ACTIVE - Active HTML available in 11gR2 requires a download of Javascript libraries and a Flash movie from an Oracle website, so must be used on a PC connected to the internet, unless you download the relevant libraries and use the BASE_PATH parameter in the function call to identify their location.
For example:
set timing on
select /*+ MONITOR */ count(*) cnt
from test_tbl, test_tbl, test_tbl, test_tbl;
CNT
----------
100000000
Elapsed: 00:00:04.662
you can read details about it in V$SQL_MONITOR
select key, sql_id, status
from v$sql_monitor
where sql_text='select /*+ MONITOR */ count(*) cnt
from test_tbl, test_tbl, test_tbl, test_tbl';
KEY SQL_ID STATUS
-------------------------- ------------- -------------------
657129996295 9fvrwtjugnkup DONE (ALL ROWS)
Elapsed: 00:00:00.016
and generate your report as TEXT
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'9fvrwtjugnkup',
report_level=>'TYPICAL',
type=>'TEXT')
from dual;
SQL Monitoring Report
SQL Text
------------------------------
select /*+ MONITOR */ count(*) cnt from test_tbl, test_tbl, test_tbl, test_tbl
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (204:115)
SQL ID : 9fvrwtjugnkup
SQL Execution ID : 16777216
Execution Started : 02/03/2013 01:57:49
First Refresh Time : 02/03/2013 01:57:49
Last Refresh Time : 02/03/2013 01:57:54
Duration : 5s
Module/Action : SQL Developer/-
Service : SYS$USERS
Program : SQL Developer
Fetch Calls : 1
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 4.85 | 4.76 | 0.09 | 1 | 12 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=2897041765)
===========================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===========================================================================================================================================
| 0 | SELECT STATEMENT | | | | 4 | +2 | 1 | 1 | | | |
| 1 | SORT AGGREGATE | | 1 | | 4 | +2 | 1 | 1 | | | |
| 2 | MERGE JOIN CARTESIAN | | 100M | 275K | 4 | +2 | 1 | 100M | | | |
| 3 | MERGE JOIN CARTESIAN | | 1M | 2752 | 4 | +2 | 1 | 1M | | | |
| 4 | MERGE JOIN CARTESIAN | | 10000 | 31 | 4 | +2 | 1 | 10000 | | | |
| 5 | TABLE ACCESS FULL | TEST_TBL | 100 | 2 | 4 | +2 | 1 | 100 | | | |
| 6 | BUFFER SORT | | 100 | 29 | 4 | +2 | 100 | 10000 | 4096 | | |
| 7 | TABLE ACCESS FULL | TEST_TBL | 100 | | 1 | +2 | 1 | 100 | | | |
| 8 | BUFFER SORT | | 100 | 2752 | 4 | +2 | 10000 | 1M | 4096 | | |
| 9 | TABLE ACCESS FULL | TEST_TBL | 100 | | 1 | +2 | 1 | 100 | | | |
| 10 | BUFFER SORT | | 100 | 275K | 5 | +1 | 1M | 100M | 4096 | 100.00 | Cpu (5) |
| 11 | TABLE ACCESS FULL | TEST_TBL | 100 | | 1 | +2 | 1 | 100 | | | |
===========================================================================================================================================
or generate full report using ACTIVE option. Save the output in HTML file to view it.
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'9fvrwtjugnkup',
report_level=>'TYPICAL',
type=>'ACTIVE')
from dual;
- 上传SPOOL信息
4.SQL数据收集
1.加入hint 收集SQL语句的10046 trace 和执行信息。
1).．修改SQL语句，加入如下/*+ gather_plan_statistics */ hint.
将
select m.mkt_terminal_sale_interface_id,
m.line_no,
m.msale_no,
m.customer_id,
修改为
select /*+ gather_plan_statistics */
m.mkt_terminal_sale_interface_id,
m.line_no,
m.msale_no,
m.customer_id,
需要收集的文件如下：
1).收集10046 trace.如果需要两个窗口来收集信息。可以一个窗口运行sql,另一个窗口用sysdba 登陆，使用oradebug 收集
Oradebug 方法如下：
SQL> oradebug setospid < OS PID of PROCESS >
SQL> oradebug unlimit
SQL> oradebug Event 10046 trace name context forever, level 12
等待2-3分钟左右(可以根据实际情况调整实际此间隔)
SQL> oradebug Event 10046 trace name context off;
SQL> oradebug tracefile_name
SQL>exit
2),收集SQL详细执行信息。
找到优化SQL的SQL_ID,
select sql_id, child_number, sql_text
from v$sql
where sql_text like '%gather_plan_statistic %'
然后根据sql_id ,收集SQL详细执行信息
SQL> select * from table(dbms_xplan.display_cursor('SQL_ID', 0, 'ALLSTATS LAST'))
