SQL Profile can be used since Oracle Ver. 10g, which can control the execution plan of specific sql statement without modifying.
When we can not find the program source that should be modified, or can not easily modify the sql because it's a solution like ERP or CRM, then SQL Profile could be a good weapon for tuning the sql.
Or when there are no enough times to modify the source and distribute, in this case too SQL Profile is one of the best remedies.
In case of Oracle Ver. 11g, we can choose SQL Plan Baseline as a solution to fix or change the Execution plan of a sql that of problematic. But in case of 10g, our best choice may be SQL Profile.
SQL Profile is used in two ways. One is with SQL Tuning Advisor and the other is by the tuner directly inputing the Hints as Oracle Outlines.
In following scenario, let's take a experience of SQL Profiles in both ways.
Or when there are no enough times to modify the source and distribute, in this case too SQL Profile is one of the best remedies.
In case of Oracle Ver. 11g, we can choose SQL Plan Baseline as a solution to fix or change the Execution plan of a sql that of problematic. But in case of 10g, our best choice may be SQL Profile.
SQL Profile is used in two ways. One is with SQL Tuning Advisor and the other is by the tuner directly inputing the Hints as Oracle Outlines.
In following scenario, let's take a experience of SQL Profiles in both ways.
<< Prerequisite >>
--Required Privileges
conn / as sysdba
conn / as sysdba
grant CREATE ANY SQL PROFILE to DEACON ;
grant ALTER ANY SQL PROFILE to DEACON ;
grant DROP ANY SQL PROFILE to DEACON ;
grant ALTER ANY SQL PROFILE to DEACON ;
grant DROP ANY SQL PROFILE to DEACON ;
--Create Test Tables and Data
conn DEACON/pass
conn DEACON/pass
create table t1 (id number, name varchar2(100));
create table t2 (id number, amt number);
create table t2 (id number, amt number);
insert into t1
select level, 'popular'
from dual
connect by level <= 100000;
select level, 'popular'
from dual
connect by level <= 100000;
insert into t1
select level, 'rare'
from dual
connect by level <= 10;
select level, 'rare'
from dual
connect by level <= 10;
insert into t2
select level, 100
from dual
connect by level <= 100000;
select level, 100
from dual
connect by level <= 100000;
create index t1_name on t1(name);
create index t2_id on t2(id);
create index t2_id on t2(id);
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');
exec dbms_stats.gather_table_stats(user,'t2');
<< Practice >>
Now we start the practice.
The purpose of the scenario is fixing the Plan of when the condition is met - name = 'popular'.
The purpose of the scenario is fixing the Plan of when the condition is met - name = 'popular'.
/*---------------------------------------------------------*/
/* 1. With Tuning Advisor
/*---------------------------------------------------------*/
/* 1. With Tuning Advisor
/*---------------------------------------------------------*/
set serveroutput off
alter session set statistics_level = all;
var name varchar2(100);
exec :name := 'popular';
exec :name := 'popular';
select /*+use_nl(t1 t2) index(t1) index(t2) test sql profile */
t1.name, sum(t2.amt)
from t1, t2
where t1.id = t2.id
and t1.name = :name
group by t1.name ;
t1.name, sum(t2.amt)
from t1, t2
where t1.id = t2.id
and t1.name = :name
group by t1.name ;
--Confirm the Plan of just pre-executed sql in this session
column sql_id new_value v_sql_id
select sql_id,sql_text
from v$sql
where sql_id = (select prev_sql_id from v$session where audsid = userenv('sessionid')) and rownum =1 ;
column sql_id new_value v_sql_id
select sql_id,sql_text
from v$sql
where sql_id = (select prev_sql_id from v$session where audsid = userenv('sessionid')) and rownum =1 ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&v_sql_id',NULL,'ALLSTATS LAST, OUTLINE, ALIAS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID 88pq7v8wdyauc, child number 0
-------------------------------------
select /*+use_nl(t1 t2) index(t1) index(t2) test sql profile */
t1.name, sum(t2.amt) from t1, t2 where t1.id = t2.id and t1.name =
:name group by t1.name
------------------------------------------------------------------------------------------------------
SQL_ID 88pq7v8wdyauc, child number 0
-------------------------------------
select /*+use_nl(t1 t2) index(t1) index(t2) test sql profile */
t1.name, sum(t2.amt) from t1, t2 where t1.id = t2.id and t1.name =
:name group by t1.name
Plan hash value: 2573114971
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.07 | 3950 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 1 |00:00:01.07 | 3950 |
| 2 | NESTED LOOPS | | 1 | | 100K|00:00:00.99 | 3950 |
| 3 | NESTED LOOPS | | 1 | 50005 | 100K|00:00:00.70 | 3526 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50005 | 100K|00:00:00.21 | 514 |
|* 5 | INDEX RANGE SCAN | T1_NAME | 1 | 50005 | 100K|00:00:00.09 | 267 |
|* 6 | INDEX RANGE SCAN | T2_ID | 100K| 1 | 100K|00:00:00.36 | 3012 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 100K| 1 | 100K|00:00:00.15 | 424 |
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.07 | 3950 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 1 |00:00:01.07 | 3950 |
| 2 | NESTED LOOPS | | 1 | | 100K|00:00:00.99 | 3950 |
| 3 | NESTED LOOPS | | 1 | 50005 | 100K|00:00:00.70 | 3526 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50005 | 100K|00:00:00.21 | 514 |
|* 5 | INDEX RANGE SCAN | T1_NAME | 1 | 50005 | 100K|00:00:00.09 | 267 |
|* 6 | INDEX RANGE SCAN | T2_ID | 100K| 1 | 100K|00:00:00.36 | 3012 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 100K| 1 | 100K|00:00:00.15 | 424 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
-------------------------------------------------------------
Outline Data
-------------
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('optimizer_index_cost_adj' 35)
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('_fix_control' '6239039:0')
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."NAME"))
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
--Confer to Tuning advisor
prompt creating tuning task...
var v_task_id varchar2(100);
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('optimizer_index_cost_adj' 35)
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('_fix_control' '6239039:0')
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."NAME"))
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
--Confer to Tuning advisor
prompt creating tuning task...
var v_task_id varchar2(100);
begin
begin
dbms_sqltune.drop_tuning_task(task_name => 'tuning_task_1');
exception when others then
null;
end;
:v_task_id := dbms_sqltune.create_tuning_task(
sql_id => '&v_sql_id',
task_name => 'tuning_task_1'
);
end;
/
begin
dbms_sqltune.drop_tuning_task(task_name => 'tuning_task_1');
exception when others then
null;
end;
:v_task_id := dbms_sqltune.create_tuning_task(
sql_id => '&v_sql_id',
task_name => 'tuning_task_1'
);
end;
/
prompt task id =
print :v_task_id
print :v_task_id
prompt executing tuning task...
exec dbms_sqltune.execute_tuning_task(task_name => 'tuning_task_1');
exec dbms_sqltune.execute_tuning_task(task_name => 'tuning_task_1');
select task_id, task_name, status
from user_advisor_log
where task_name = 'tuning_task_1'
;
from user_advisor_log
where task_name = 'tuning_task_1'
;
--Confirm the Plan which Tuning advisor recommend.
set long 100000
set pagesize 1000
set linesize 200
set long 100000
set pagesize 1000
set linesize 200
select dbms_sqltune.report_tuning_task('tuning_task_1')
from dual
;
--If the Plan look acceptable then Do accept.(script can get from the Tuning advisor report)
begin
dbms_sqltune.accept_sql_profile(task_name => 'tuning_task_1',
name => '88pq7v8wdyauc_prof',
replace => true)
;
end;
/
begin
dbms_sqltune.accept_sql_profile(task_name => 'tuning_task_1',
name => '88pq7v8wdyauc_prof',
replace => true)
;
end;
/
select name, sql_text, type, status
from dba_sql_profiles
where name = '88pq7v8wdyauc_prof'
;
from dba_sql_profiles
where name = '88pq7v8wdyauc_prof'
;
--Confirm the changed Plan
@plan2 88pq7v8wdyauc
PL/SQL 처리가 정상적으로 완료되었습니다.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2672875977
----------------------------------------------------------------------------------------------------
Plan hash value: 2672875977
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 109 (7)| 00:00:02 |
| 1 | SORT GROUP BY NOSORT| | 1 | 21 | 109 (7)| 00:00:02 |
|* 2 | HASH JOIN | | 50005 | 1025K| 109 (7)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 50005 | 634K| 61 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 781K| 46 (5)| 00:00:01 |
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 109 (7)| 00:00:02 |
| 1 | SORT GROUP BY NOSORT| | 1 | 21 | 109 (7)| 00:00:02 |
|* 2 | HASH JOIN | | 50005 | 1025K| 109 (7)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 50005 | 634K| 61 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 781K| 46 (5)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
-------------------------------------------------------------
Outline Data
-------------
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
FIRST_ROWS(1)
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 35)
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
FIRST_ROWS(1)
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 35)
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - filter("T1"."NAME"=:NAME)
3 - filter("T1"."NAME"=:NAME)
Note
-----
- SQL profile "88pq7v8wdyauc_prof" used for this statement
-----
- SQL profile "88pq7v8wdyauc_prof" used for this statement
47 개의 행이 선택되었습니다.
--Drop the Profile
SQL> @drop_sqlprof
==> sql_profile_name : 88pq7v8wdyauc_prof
dropping existing profile
SQL> @drop_sqlprof
==> sql_profile_name : 88pq7v8wdyauc_prof
dropping existing profile
PL/SQL 처리가 정상적으로 완료되었습니다.
/*--------------------------------------------------------*/
/* 2. Create SQL Profile directly by Tuner
/*--------------------------------------------------------*/
--Confirm the current Plan
@plan2 88pq7v8wdyauc
@plan2 88pq7v8wdyauc
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.02
================================================================
| Do Explain plan of specified SQL_ID and Display result
================================================================
==> Parsing_user : DEACON
==> Module : SQL*Plus
==> SQL_ID : 88pq7v8wdyauc
----------------------------------------------------------------
select /*+use_nl(t1 t2) index(t1) index(t2) test
sql profile */
t1.name, sum(t2.amt)
from t1, t2
where t1.id = t2.id
and t1.name = :name
group by t1.name
================================================================
| Do Explain plan of specified SQL_ID and Display result
================================================================
==> Parsing_user : DEACON
==> Module : SQL*Plus
==> SQL_ID : 88pq7v8wdyauc
----------------------------------------------------------------
select /*+use_nl(t1 t2) index(t1) index(t2) test
sql profile */
t1.name, sum(t2.amt)
from t1, t2
where t1.id = t2.id
and t1.name = :name
group by t1.name
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.14
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2573114971
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2573114971
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 17609 (1)| 00:03:32 |
| 1 | SORT GROUP BY NOSORT | | 1 | 21 | 17609 (1)| 00:03:32 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 50005 | 1025K| 17609 (1)| 00:03:32 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 50005 | 634K| 91 (2)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | T1_NAME | 50005 | | 47 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 8 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 17609 (1)| 00:03:32 |
| 1 | SORT GROUP BY NOSORT | | 1 | 21 | 17609 (1)| 00:03:32 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 50005 | 1025K| 17609 (1)| 00:03:32 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 50005 | 634K| 91 (2)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | T1_NAME | 50005 | | 47 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 8 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
-------------------------------------------------------------
Outline Data
-------------
-------------
/*+
BEGIN_OUTLINE_DATA
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."NAME"))
OUTLINE_LEAF(@"SEL$1")
FIRST_ROWS(1)
OPT_PARAM('_fix_control' '6239039:0')
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 35)
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optim_peek_user_binds' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
BEGIN_OUTLINE_DATA
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."NAME"))
OUTLINE_LEAF(@"SEL$1")
FIRST_ROWS(1)
OPT_PARAM('_fix_control' '6239039:0')
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 35)
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optim_peek_user_binds' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
5 - access("T1"."NAME"=:NAME)
6 - access("T1"."ID"="T2"."ID")
6 - access("T1"."ID"="T2"."ID")
58 개의 행이 선택되었습니다.
--Create the SQL Profile by directly inputting the Hints
--For convenience use the script SET_SQLPROF.SQL
SQL > @set_sqlprof
--For convenience use the script SET_SQLPROF.SQL
SQL > @set_sqlprof
==> sql_id : 88pq7v8wdyauc
==> sql_profile_name : 88pq7v8wdyauc_prof
/*------------------------------------------------------*/
/* ==> now enter the profile hints in following 7 lines */
/*------------------------------------------------------*/
==> profile hint (1/7) : use_hash(T1@SEL$1 T2@SEL$1)
==> profile hint (2/7) : full(T1@SEL$1) full(T2@SEL$1)
==> profile hint (3/7) :
==> profile hint (4/7) :
==> profile hint (5/7) :
==> profile hint (6/7) :
==> profile hint (7/7) :
==> sql_profile_name : 88pq7v8wdyauc_prof
/*------------------------------------------------------*/
/* ==> now enter the profile hints in following 7 lines */
/*------------------------------------------------------*/
==> profile hint (1/7) : use_hash(T1@SEL$1 T2@SEL$1)
==> profile hint (2/7) : full(T1@SEL$1) full(T2@SEL$1)
==> profile hint (3/7) :
==> profile hint (4/7) :
==> profile hint (5/7) :
==> profile hint (6/7) :
==> profile hint (7/7) :
PL/SQL 처리가 정상적으로 완료되었습니다.
================================================================
| Do Explain plan of specified SQL_ID and Display result
================================================================
==> Parsing_user : DEACON
==> Module : SQL*Plus
==> SQL_ID : 88pq7v8wdyauc
----------------------------------------------------------------
select /*+use_nl(t1 t2) index(t1) index(t2) test sql profile */
t1.name, sum(t2.amt)
from t1, t2
where t1.id = t2.id
and t1.name = :name
group by t1.name
| Do Explain plan of specified SQL_ID and Display result
================================================================
==> Parsing_user : DEACON
==> Module : SQL*Plus
==> SQL_ID : 88pq7v8wdyauc
----------------------------------------------------------------
select /*+use_nl(t1 t2) index(t1) index(t2) test sql profile */
t1.name, sum(t2.amt)
from t1, t2
where t1.id = t2.id
and t1.name = :name
group by t1.name
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.26
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 2672875977
---------------------------------------------------------------------------------------------------------
Plan hash value: 2672875977
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 109 (7)| 00:00:02 |
| 1 | SORT GROUP BY NOSORT| | 1 | 21 | 109 (7)| 00:00:02 |
|* 2 | HASH JOIN | | 50005 | 1025K| 109 (7)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 50005 | 634K| 61 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 781K| 46 (5)| 00:00:01 |
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 109 (7)| 00:00:02 |
| 1 | SORT GROUP BY NOSORT| | 1 | 21 | 109 (7)| 00:00:02 |
|* 2 | HASH JOIN | | 50005 | 1025K| 109 (7)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 50005 | 634K| 61 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 100K| 781K| 46 (5)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
-------------------------------------------------------------
Outline Data
-------------
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
FIRST_ROWS(1)
OPT_PARAM('_fix_control' '6239039:0')
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 35)
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optim_peek_user_binds' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
FIRST_ROWS(1)
OPT_PARAM('_fix_control' '6239039:0')
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 35)
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optim_peek_user_binds' 'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - filter("T1"."NAME"=:NAME)
3 - filter("T1"."NAME"=:NAME)
Note
-----
- SQL profile "88pq7v8wdyauc_prof" used for this statement
-----
- SQL profile "88pq7v8wdyauc_prof" used for this statement
56 개의 행이 선택되었습니다.
--Drop the Profile
SQL> @drop_sqlprof
==> sql_profile_name : 88pq7v8wdyauc_prof
dropping existing profile
SQL> @drop_sqlprof
==> sql_profile_name : 88pq7v8wdyauc_prof
dropping existing profile
PL/SQL 처리가 정상적으로 완료되었습니다.
<< Practice Scripts Attachment >>
<< SET_SQLPROF.SQL >>
REM script SET_SQLPROF.SQL (Set SQL Profile on given sql_id)
REM author : Tim Kwangseo Park
REM usage: @set_sqlprof
REM =====================================
REM author : Tim Kwangseo Park
REM usage: @set_sqlprof
REM =====================================
accept h_sql_id prompt " ==> sql_id : "
accept h_profile_name prompt " ==> sql_profile_name : "
prompt /*------------------------------------------------------*/
prompt /* ==> now enter the profile hints in following 7 lines */
prompt /*------------------------------------------------------*/
accept h_sqlprof_attr_1 prompt " ==> profile hint (1/7) : "
accept h_sqlprof_attr_2 prompt " ==> profile hint (2/7) : "
accept h_sqlprof_attr_3 prompt " ==> profile hint (3/7) : "
accept h_sqlprof_attr_4 prompt " ==> profile hint (4/7) : "
accept h_sqlprof_attr_5 prompt " ==> profile hint (5/7) : "
accept h_sqlprof_attr_6 prompt " ==> profile hint (6/7) : "
accept h_sqlprof_attr_7 prompt " ==> profile hint (7/7) : "
accept h_profile_name prompt " ==> sql_profile_name : "
prompt /*------------------------------------------------------*/
prompt /* ==> now enter the profile hints in following 7 lines */
prompt /*------------------------------------------------------*/
accept h_sqlprof_attr_1 prompt " ==> profile hint (1/7) : "
accept h_sqlprof_attr_2 prompt " ==> profile hint (2/7) : "
accept h_sqlprof_attr_3 prompt " ==> profile hint (3/7) : "
accept h_sqlprof_attr_4 prompt " ==> profile hint (4/7) : "
accept h_sqlprof_attr_5 prompt " ==> profile hint (5/7) : "
accept h_sqlprof_attr_6 prompt " ==> profile hint (6/7) : "
accept h_sqlprof_attr_7 prompt " ==> profile hint (7/7) : "
var h_sql_id varchar2(13)
var h_profile_name varchar2(30)
var h_sqlprof_attr varchar2(500)
declare
v_sql_fulltext clob := null;
var h_profile_name varchar2(30)
var h_sqlprof_attr varchar2(500)
declare
v_sql_fulltext clob := null;
begin
:h_sql_id := '&h_sql_id' ;
:h_profile_name := '&h_profile_name' ;
:h_sqlprof_attr := '&h_sqlprof_attr_1' ||' '||
'&h_sqlprof_attr_2' ||' '||
'&h_sqlprof_attr_3' ||' '||
'&h_sqlprof_attr_4' ||' '||
'&h_sqlprof_attr_5' ||' '||
'&h_sqlprof_attr_6' ||' '||
'&h_sqlprof_attr_7'
; --max 500 byte 까지 가능
:h_sql_id := '&h_sql_id' ;
:h_profile_name := '&h_profile_name' ;
:h_sqlprof_attr := '&h_sqlprof_attr_1' ||' '||
'&h_sqlprof_attr_2' ||' '||
'&h_sqlprof_attr_3' ||' '||
'&h_sqlprof_attr_4' ||' '||
'&h_sqlprof_attr_5' ||' '||
'&h_sqlprof_attr_6' ||' '||
'&h_sqlprof_attr_7'
; --max 500 byte 까지 가능
select sql_fulltext
into v_sql_fulltext
from v$sql
where sql_id = :h_sql_id
and rownum = 1
;
into v_sql_fulltext
from v$sql
where sql_id = :h_sql_id
and rownum = 1
;
dbms_sqltune.import_sql_profile(
name => :h_profile_name,
sql_text => v_sql_fulltext,
profile => sqlprof_attr(:h_sqlprof_attr)
--profile => sqlprof_attr('FULL(T1@SEL$1)',
-- 'FULL(T2@SEL$1)',
-- 'USE_HASH(@SEL$1 T2@SEL$1)')
);
end;
/
name => :h_profile_name,
sql_text => v_sql_fulltext,
profile => sqlprof_attr(:h_sqlprof_attr)
--profile => sqlprof_attr('FULL(T1@SEL$1)',
-- 'FULL(T2@SEL$1)',
-- 'USE_HASH(@SEL$1 T2@SEL$1)')
);
end;
/
select name, sql_text, type, status
from dba_sql_profiles
where name = :h_profile_name
;
from dba_sql_profiles
where name = :h_profile_name
;
prompt /*-----------------------------------------------------*/
prompt /* Confirm the Plan after creation of the Sql profile
prompt /*-----------------------------------------------------*/
prompt
prompt /* Confirm the Plan after creation of the Sql profile
prompt /*-----------------------------------------------------*/
prompt
@plan2 '&h_sql_id'
--if you'd drop this sql profile use @drop_sqlprof.sql script
<< DROP_SQLPROF.SQL >>
REM script DROP_SQLPROF.SQL (Drop SQL Profile on given sql profile name)
REM author : Tim Kwangseo Park
REM usage: @drop_sqlprof
REM =====================================
REM author : Tim Kwangseo Park
REM usage: @drop_sqlprof
REM =====================================
accept h_profile_name prompt " ==> sql_profile_name : "
prompt dropping existing profile
exec dbms_sqltune.drop_sql_profile('&h_profile_name', true);
select name, sql_text, type, status, created
from dba_sql_profiles
where name = '&h_profile_name'
;
from dba_sql_profiles
where name = '&h_profile_name'
;
<< PLAN2.SQL >>
REM script PLAN2.SQL (Explain plan of specified sql_id)
REM author : Tim Kwangseo Park
REM usage: @plan2 [sql_id]
REM =====================================
REM author : Tim Kwangseo Park
REM usage: @plan2 [sql_id]
REM =====================================
rem set serveroutput on size unlimited
set serveroutput on
set serveroutput on
var h_sql_id varchar2(13)
exec :h_sql_id := '&1'
declare
v_session_user varchar2(20) := null;
v_parsing_schema_name varchar2(20) := null;
v_module varchar2(100) := null;
v_sql_id varchar(13);
v_sql_fulltext clob; --11g feature
--v_sql_fulltext varchar2(32767);
begin
v_parsing_schema_name varchar2(20) := null;
v_module varchar2(100) := null;
v_sql_id varchar(13);
v_sql_fulltext clob; --11g feature
--v_sql_fulltext varchar2(32767);
begin
dbms_output.enable(buffer_size => null) ; --mean buffer size unlimited
select parsing_schema_name, module, sql_id, sql_fulltext
into v_parsing_schema_name, v_module, v_sql_id, v_sql_fulltext
from v$sql
where sql_id = :h_sql_id
and rownum = 1
;
select parsing_schema_name, module, sql_id, sql_fulltext
into v_parsing_schema_name, v_module, v_sql_id, v_sql_fulltext
from v$sql
where sql_id = :h_sql_id
and rownum = 1
;
execute immediate 'alter session set current_schema = ' || v_parsing_schema_name ;
--execute immediate 'explain plan into deacon.plan_table for ' || chr(10) || v_sql_fulltext ;
execute immediate 'explain plan for ' || chr(10) || v_sql_fulltext ;
--execute immediate 'explain plan into deacon.plan_table for ' || chr(10) || v_sql_fulltext ;
execute immediate 'explain plan for ' || chr(10) || v_sql_fulltext ;
dbms_output.put_line ('================================================================');
dbms_output.put_line ('| Do Explain plan of specified SQL_ID and Display result ');
dbms_output.put_line ('================================================================');
dbms_output.put_line ('==> Parsing_user : ' || v_parsing_schema_name || chr(10) ||
'==> Module : ' || v_module || chr(10) ||
'==> SQL_ID : ' || v_sql_id || chr(10) ||
'----------------------------------------------------------------' || chr(10) ||
v_sql_fulltext);
dbms_output.put_line ('| Do Explain plan of specified SQL_ID and Display result ');
dbms_output.put_line ('================================================================');
dbms_output.put_line ('==> Parsing_user : ' || v_parsing_schema_name || chr(10) ||
'==> Module : ' || v_module || chr(10) ||
'==> SQL_ID : ' || v_sql_id || chr(10) ||
'----------------------------------------------------------------' || chr(10) ||
v_sql_fulltext);
select sys_context('userenv','session_user')
into v_session_user
from dual;
execute immediate 'alter session set current_schema = ' || v_session_user ;
end;
/
into v_session_user
from dual;
execute immediate 'alter session set current_schema = ' || v_session_user ;
end;
/
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('deacon.plan_table',NULL,'OUTLINE, ALIAS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'OUTLINE, ALIAS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'OUTLINE, ALIAS'));
set serveroutput off
No comments:
Post a Comment