我们建立一个测试表,这是个数据分布极不均匀的表
Elapsed: 00:00:00.12
10:10:26 kiwi@orcl> select count(*) from test;
COUNT(*)
-------------
1380400
Elapsed: 00:00:00.15
10:12:22 kiwi@orcl> select count(*) from test where object_id=100;
COUNT(*)
-------------
1380000
总共138w的数据其中有130w的数据的object_id=100
我们先不收集直方图,我们执行sql语句
select * from test where object_id=100;
Elapsed: 00:00:51.44
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1369K| 125M| 22201 (1)| 00:04:27 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1369K| 125M| 22201 (1)| 00:04:27 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 1369K| | 2686 (1)| 00:00:33 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
204896 consistent gets
183 physical reads
0 redo size
156057934 bytes sent via SQL*Net to client
1012508 bytes received via SQL*Net from client
92001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1380000 rows processed
收集直方图
10:04:45 kiwi@orcl> exec dbms_stats.gather_table_stats(user,'TEST',method_opt=>'for columns OBJECT_ID size skewonly');
我们执行select * from test where object_id=100
看执行计划是全表扫描,是正确的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1369K| 125M| 5528 (1)| 00:01:07 |
|* 1 | TABLE ACCESS FULL| TEST | 1369K| 125M| 5528 (1)| 00:01:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
110564 consistent gets
0 physical reads
0 redo size
66355149 bytes sent via SQL*Net to client
1012508 bytes received via SQL*Net from client
92001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1380000 rows processed
如果我们此时添加了hint,显然,oracle就会按照hint来走错误的执行计划了
如果我们查看此时这条sql的执行计划,会发现他生成了两个子游标
select * from table(dbms_xplan.display_cursor('fbqy56z7kmdxk',null,'advanced'));
10:22:30 kiwi@orcl> /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fbqy56z7kmdxk, child number 0
-------------------------------------
select * from test where object_id=100
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 87 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 13804 | 1294K| 87 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 5522 | | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
SQL_ID fbqy56z7kmdxk, child number 1
-------------------------------------
select * from test where object_id=100
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5528 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1380K| 126M| 5528 (1)| 00:01:07 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
Note
-----
- cardinality feedback used for this statement
105 rows selected.
下面我们就来演示如果通过sqlt的中提供的coe_xfr_sql_profile.sql在不改变sql语句的情况下,自由的改变执行计划
通过这个脚本,我们就能手动的生成2份不同执行计划的profile文件
10:26:49 sys@orcl> @coe_xfr_sql_profile.sql
10:26:56 sys@orcl> SPO coe_xfr_sql_profile.log;
10:26:56 sys@orcl> SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
Parameter 1:
SQL_ID (required)
Enter value for 1: fbqy56z7kmdxk
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -------------
2473784974 2.13
1357081020 4.253
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 2473784974
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "fbqy56z7kmdxk"
PLAN_HASH_VALUE: "2473784974"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF;
Execute coe_xfr_sql_profile_fbqy56z7kmdxk_2473784974.sql
on TARGET system in order to create a custom SQL Profile
with plan 2473784974 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: fbqy56z7kmdxk
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -------------
2473784974 2.13
1357081020 4.253
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1357081020
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "fbqy56z7kmdxk"
PLAN_HASH_VALUE: "1357081020"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF;
Execute coe_xfr_sql_profile_fbqy56z7kmdxk_1357081020.sql
on TARGET system in order to create a custom SQL Profile
with plan 1357081020 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
我们比较这两个profile文件当中的不同
plan_hash_value=1357081020的
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "TEST"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
plan_hash_value=2473784974
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');
通过这两个sql_profile我们就可以自由的变动目标sql的执行计划
10:37:38 kiwi@orcl> @coe_xfr_sql_profile_fbqy56z7kmdxk_2473784974.sql
我们就可以发现sql的执行计划从正确的全表扫面变成了索引扫描了
10:41:06 kiwi@orcl> select * from test where object_id=100;
1380000 rows selected.
Elapsed: 00:00:48.17
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1369K| 125M| 22201 (1)| 00:04:27 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1369K| 125M| 22201 (1)| 00:04:27 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 1369K| | 2686 (1)| 00:00:33 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- SQL profile "coe_fbqy56z7kmdxk_2473784974" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
204896 consistent gets
0 physical reads
0 redo size
156057934 bytes sent via SQL*Net to client
1012509 bytes received via SQL*Net from client
92001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1380000 rows processed
再来测试另一种情况,删除sql_profile,删除柱状图
10:42:42 kiwi@orcl> exec dbms_sqltune.drop_sql_profile('coe_fbqy56z7kmdxk_2473784974')
PL/SQL procedure successfully completed.
10:45:28 kiwi@orcl> exec dbms_stats.delete_column_stats('KIWI','TEST', 'OBJECT_ID', col_stat_type=>'HISTOGRAM');
PL/SQL procedure successfully completed.
由于没有直方图的信息,oracle必然选择错误的执行计划,走索引范围扫描
10:46:59 kiwi@orcl> select * from test where object_id=100;
1380000 rows selected.
Elapsed: 00:00:44.50
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3442 | 322K| 58 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3442 | 322K| 58 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 3442 | | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
110564 consistent gets
0 physical reads
0 redo size
66355149 bytes sent via SQL*Net to client
1012509 bytes received via SQL*Net from client
92001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1380000 rows processed
此时我们可以通过另外一个全表的profile在不改写sql的情况下引导sql语句走正确的执行计划
10:48:11 kiwi@orcl> @coe_xfr_sql_profile_fbqy56z7kmdxk_1357081020.sql
10:51:16 kiwi@orcl> select * from test where object_id=100;
1380000 rows selected.
Elapsed: 00:00:45.11
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3442 | 322K| 5520 (1)| 00:01:07 |
|* 1 | TABLE ACCESS FULL| TEST | 3442 | 322K| 5520 (1)| 00:01:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL profile "coe_fbqy56z7kmdxk_1357081020" used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
110569 consistent gets
1 physical reads
0 redo size
66355149 bytes sent via SQL*Net to client
1012509 bytes received via SQL*Net from client
92001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1380000 rows processed
手动生成sql_profile的方法
最新推荐文章于 2023-06-25 19:38:20 发布