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.
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
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): 保持預設，回車
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN: è設定SQLTXPLAIN使用者密碼
Re-enter password:
... 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
TABLESPACE
------------------------------
TEMP
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Temporary tablespace [UNKNOWN]: TEMP è選擇臨時表空間
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
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，保持
建立步驟省略 ……
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
$ sqlplus / as sysdba
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要建立此配置檔案就執行這個腳本
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、執行以下語句生成好的執行計劃
ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'test';
2、找出/*+ test */語句的sql_id
bpwkfmp2yzmdd
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
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
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)
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> select ename from emp
where empno=7788;
2)
3)
導出outline相關資料
exp username/password
tables=outln.ol\$ outln.ol\$hints outln.ol\$nodes file=ol.dmp log=ol_exp.log
生産庫：
1.
拷貝導出檔案到生産庫并導入
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:
- 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:
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:
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
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;
/
DBMS_SQLTUNE.alter_sql_profile (
name =>
'test_profile',
attribute_name => 'STATUS',
value =>
'DISABLED');
DBMS_SQLTUNE.drop_sql_profile
(
name => 'test_profile',
ignore => TRUE);
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
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))
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
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
######sample
SQLT工具包含一個腳本，名字是coe_load_sql_profile.sql,下面以使用者SCOTT的EMP表為例，說明如何使用該腳本固定sql profile.
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.
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
6.運作原來的SQL語句
SQL>conn scott/tiger
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='MILLER')
- 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
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
/*+
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) */
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
Enter value for 1: 84gvjqukvktcp
Enter value for 2: 5p9qdczsk4nfy
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
222931604 .003
Enter value for 3: 222931604
Values passed to coe_load_sql_baseline:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
PLAN_HASH_VALUE AVG_ET_SECS
222931604 1.663
Enter value for 3: 222931604
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;
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).');
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>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
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:84GVJQUKVKTCP MODIFIED:5P9QDCZSK4NFY PHV:222931604 SIGNATURE:9978660567500671649 CREATED BY COE_LOAD_SQL_PROFILE.SQL
* 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:
. . exporting table STGTAB_SQLPROF_84GVJQUKVKTCP 1 rows exported
imp DBMGR file=STGTAB_SQLPROF_84gvjqukvktcp.dmp tables=STGTAB_SQLPROF_84gvjqukvktcp ignore=Y
profile_name => '84GVJQUKVKTCP_222931604',
staging_table_name => 'STGTAB_SQLPROF_84gvjqukvktcp',
adding: coe_load_sql_profile_84gvjqukvktcp.log (deflated 79%)
adding: STGTAB_SQLPROF_84gvjqukvktcp.dmp (deflated 87%)
STEP 7 .運作原來的SQL語句驗證是否生效
set atutotrace on
- 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分為兩部分
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
" 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.
SQL> START sqltxtract.sql
[SQL_ID]|[HASH_VALUE]
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);
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.
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.
SQL_ID or
HASH_VALUE of the SQL to be extracted (required)
Paremeter 2:
SQLTXPLAIN password
(required)
Password for user
SQLTXPLAIN: 輸入SQLTXPLAIN使用者密碼
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:
Attachments
…
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
# 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 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
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.
TEXT
HTML
XML
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.
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
test_tbl, test_tbl';
KEY
SQL_ID
STATUS
-------------------------- -------------
-------------------
657129996295
9fvrwtjugnkup DONE (ALL ROWS)
Elapsed: 00:00:00.016
and generate your report as TEXT
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'9fvrwtjugnkup',
report_level=>'TYPICAL',
type=>'TEXT')
from dual;
SQL Monitoring Report
SQL Text
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 :
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
+2 | 1 | 1M
| 4
| MERGE JOIN CARTESIAN
| | 10000
| 31 | 4
| +2 | 1 |
10000 |
| 5
| TABLE ACCESS FULL | TEST_TBL
| 100 | 2
+2 | 1 | 100
| 6
| BUFFER
SORT
| 100 | 29
+2 | 100 | 10000 | 4096
| 7
| TABLE ACCESS FULL | TEST_TBL
| 100 |
| 1 |
| |
| 8
| BUFFER
SORT
| 100 | 2752
+2 | 10000 | 1M | 4096
| 9
| 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.
type=>'ACTIVE')
上傳SPOOL資訊
4.SQL資料收集
1.加入hint 收集SQL語句的10046 trace 和執行資訊。
1).．修改SQL語句，加入如下/*+ gather_plan_statistics */ hint.
将
m.mkt_terminal_sale_interface_id,
m.line_no,
m.msale_no,
m.customer_id,
修改為
select /*+
gather_plan_statistics */
m.msale_no,
需要收集的檔案如下：
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分鐘左右(可以根據實際情況調整實際此間隔)
trace name context off;
SQL> oradebug tracefile_name
SQL>exit
2),收集SQL詳細執行資訊。
找到優化SQL的SQL_ID,
select sql_id,
child_number, sql_text
where sql_text like '%gather_plan_statistic %'
然後根據sql_id ,收集SQL詳細執行資訊
SQL> select * from
table(dbms_xplan.display_cursor('SQL_ID', 0, 'ALLSTATS LAST'))
炊煙起了；夕陽下了；細雨來了
多調試,互動式程式設計體驗
記錄，獨立思考，對比
感謝轉載作者
修車
國産化
read and connect
匍匐前進,
講故事
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)