版本信息
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
T1表信息
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
T1 1,163,041 17,263 0 0 0 97 YES NO 1,163,041 02-18-2014
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------
OWNER VARCHAR2(30) 26 0 1 0 YES NO 1,163,041 02-18-2014
OBJECT_NAME VARCHAR2(128) 43,908 0 1 0 YES NO 1,163,041 02-18-2014
SUBOBJECT_NAME VARCHAR2(30) 165 0 1 1,156,545 YES NO 6,496 02-18-2014
OBJECT_ID NUMBER(22) 73,752 0 254 0 YES NO 5,508 02-18-2014
DATA_OBJECT_ID NUMBER(22) 7,698 0 1 1,039,136 YES NO 123,905 02-18-2014
OBJECT_TYPE VARCHAR2(19) 44 0 1 0 YES NO 1,163,041 02-18-2014
CREATED DATE 1,132 0 1 0 YES NO 1,163,041 02-18-2014
LAST_DDL_TIME DATE 1,230 0 1 0 YES NO 1,163,041 02-18-2014
TIMESTAMP VARCHAR2(19) 1,302 0 1 0 YES NO 1,163,041 02-18-2014
STATUS VARCHAR2(7) 2 1 1 0 YES NO 1,163,041 02-18-2014
TEMPORARY VARCHAR2(1) 2 1 1 0 YES NO 1,163,041 02-18-2014
GENERATED VARCHAR2(1) 2 1 1 0 YES NO 1,163,041 02-18-2014
SECONDARY VARCHAR2(1) 2 1 1 0 YES NO 1,163,041 02-18-2014
NAMESPACE NUMBER(22) 20 0 1 0 YES NO 1,163,041 02-18-2014
EDITION_NAME VARCHAR2(30) 0 0 0 1,163,041 YES NO 02-18-2014
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
IDX_T1_OBJECTID NONUNIQUE 2 #### 73,752 1,163,041 1 14 1,054,525 YES NO 1,163,041 02-18-2014
IDX_OBJECT_TYPE NONUNIQUE 2 #### 44 1,163,041 72 1,316 57,941 YES NO 1,163,041 02-18-2014
IDX_T1_OBJECT_N NONUNIQUE 2 #### 43,908 1,156,718 1 18 814,218 YES NO 225,791 02-18-2014
AME
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_OBJECT_TYPE OBJECT_TYPE 1 VARCHAR2(19)
IDX_T1_OBJECTID OBJECT_ID 1 NUMBER(22)
IDX_T1_OBJECT_N OBJECT_NAME 1 VARCHAR2(128)
AME
T2表的信息
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
T2 72,685 1,062 0 0 0 97 YES NO 72,685 02-18-2014
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------
OWNER VARCHAR2(30) 26 0 18 0 YES NO 5,469 02-18-2014
OBJECT_NAME VARCHAR2(128) 43,908 0 1 0 YES NO 72,685 02-18-2014
SUBOBJECT_NAME VARCHAR2(30) 165 0 1 72,279 YES NO 406 02-18-2014
OBJECT_ID NUMBER(22) 72,685 0 1 0 YES NO 72,685 02-18-2014
DATA_OBJECT_ID NUMBER(22) 7,699 0 1 64,946 YES NO 7,739 02-18-2014
OBJECT_TYPE VARCHAR2(19) 44 0 30 0 YES NO 5,469 02-18-2014
CREATED DATE 1,133 0 1 0 YES NO 72,685 02-18-2014
LAST_DDL_TIME DATE 1,231 0 1 0 YES NO 72,685 02-18-2014
TIMESTAMP VARCHAR2(19) 1,303 0 1 0 YES NO 72,685 02-18-2014
STATUS VARCHAR2(7) 2 1 1 0 YES NO 72,685 02-18-2014
TEMPORARY VARCHAR2(1) 2 1 1 0 YES NO 72,685 02-18-2014
GENERATED VARCHAR2(1) 2 1 1 0 YES NO 72,685 02-18-2014
SECONDARY VARCHAR2(1) 2 1 1 0 YES NO 72,685 02-18-2014
NAMESPACE NUMBER(22) 20 0 1 0 YES NO 72,685 02-18-2014
EDITION_NAME VARCHAR2(30) 0 0 0 72,685 YES NO 02-18-2014
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
IDX_T2_TYPE_OWN NONUNIQUE 1 258 237 72,685 1 13 3,186 NO NO 72,685 02-18-2014
ER
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2_TYPE_OWN OBJECT_TYPE 1 VARCHAR2(19)
ER
OWNER 2 VARCHAR2(30)
T2_IDX OBJECT_ID 1 NUMBER(22)第1次执行
SQL_ID 07nvz4ghrbb8u, child number 0
-------------------------------------
select /*+ gather_plan_statistics */distinct
t1.object_type,t1.status,t11.object_name,t11.status from t1,t1 t11
where t1.object_id=t11.object_id and t1.object_name in (select
distinct object_name from t2 where object_type='INDEX' and
owner='SCOTT' ) and t11.object_name in (select distinct object_name
from t2 where object_type='INDEX' and owner='SCOTT')
Plan hash value: 3070061988
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |
00:00:00.80 |
25590 | | | |
| 1 | HASH UNIQUE | | 1 | 4 | 2 |00:00:00.80 | 25590 | 941K| 941K| 468K (0)|
| 2 | NESTED LOOPS | | 1 | | 576 |00:00:03.92 | 25590 | | | |
| 3 | NESTED LOOPS | | 1 | 4 | 576 |00:00:03.91 | 25014 | | | |
| 4 | NESTED LOOPS | | 1 | 14 | 96 |00:00:00.01 | 112 | | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 1 | 4 |00:00:00.01 | 6 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 2 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | IDX_T2_TYPE_OWNER | 1 | 1 | 2 |00:00:00.01 | 2 | | | |
| 8 | BUFFER SORT | | 2 | 1 | 4 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 2 |00:00:00.01 | 3 | | | |
|* 10 | INDEX RANGE SCAN | IDX_T2_TYPE_OWNER | 1 | 1 | 2 |00:00:00.01 | 2 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | T1 | 4 | 26 | 96 |00:00:00.01 | 106 | | | |
|* 12 | INDEX RANGE SCAN | IDX_T1_OBJECT_NAME | 4 | 26 | 96 |00:00:00.01 | 10 | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | 96 | | 576 |00:00:00.80 | 24902 | | | |
| 14 | BITMAP AND | | 96 | | 60 |00:00:00.80 | 24902 | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS| | 96 | | 96 |00:00:00.01 | 24 | | | |
|* 16 | INDEX RANGE SCAN | IDX_T1_OBJECT_NAME | 96 | 16 | 2304 |00:00:00.01 | 24 | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS| | 96 | | 120 |00:00:00.79 | 24878 | | | |
|* 18 | INDEX RANGE SCAN | IDX_T1_OBJECTID | 96 | 16 | 7201K|00:00:00.94 | 24878 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | T1 | 576 | 1 | 576 |00:00:00.01 | 576 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("OBJECT_TYPE"='INDEX' AND "OWNER"='SCOTT')
10 - access("OBJECT_TYPE"='INDEX' AND "OWNER"='SCOTT')
12 - access("T1"."OBJECT_NAME"="OBJECT_NAME")
16 - access("T11"."OBJECT_NAME"="OBJECT_NAME")
18 - access("T1"."OBJECT_ID"="T11"."OBJECT_ID")
第2次执行，关闭位图转换功能
SQL_ID 9ds876bm27n8x, child number 0
-------------------------------------
select /*+ gather_plan_statistics opt_param('_b_tree_bitmap_plans','fal
se') */distinct t1.object_type,t1.status,t11.object_name,t11.status
from t1,t1 t11 where t1.object_id=t11.object_id and t1.object_name in
(select distinct object_name from t2 where object_type='INDEX' and
owner='SCOTT' ) and t11.object_name in (select distinct object_name
from t2 where object_type='INDEX' and owner='SCOTT')
Plan hash value: 1432114955
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:04.84 | 128K| | | |
| 1 | HASH UNIQUE | | 1 | 4 | 2 |00:00:04.84 | 128K| 941K| 941K| 480K (0)|
| 2 | NESTED LOOPS | | 1 | | 576 |00:00:13.80 | 128K| | | |
| 3 | NESTED LOOPS | | 1 | 4 | 7201K|00:00:02.11 | 24990 | | | |
| 4 | NESTED LOOPS | | 1 | 14 | 96 |00:00:00.01 | 112 | | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 1 | 4 |00:00:00.01 | 6 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 2 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | IDX_T2_TYPE_OWNER | 1 | 1 | 2 |00:00:00.01 | 2 | | | |
| 8 | BUFFER SORT | | 2 | 1 | 4 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 2 |00:00:00.01 | 3 | | | |
|* 10 | INDEX RANGE SCAN | IDX_T2_TYPE_OWNER | 1 | 1 | 2 |00:00:00.01 | 2 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | T1 | 4 | 26 | 96 |00:00:00.01 | 106 | | | |
|* 12 | INDEX RANGE SCAN | IDX_T1_OBJECT_NAME | 4 | 26 | 96 |00:00:00.01 | 10 | | | |
|* 13 | INDEX RANGE SCAN | IDX_T1_OBJECTID | 96 | 16 | 7201K|00:00:01.02 | 24878 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | T1 | 7201K| 1 | 576 |00:00:02.38 | 103K| | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("OBJECT_TYPE"='INDEX' AND "OWNER"='SCOTT')
10 - access("OBJECT_TYPE"='INDEX' AND "OWNER"='SCOTT')
12 - access("T1"."OBJECT_NAME"="OBJECT_NAME")
13 - access("T1"."OBJECT_ID"="T11"."OBJECT_ID")
14 - filter("T11"."OBJECT_NAME"="OBJECT_NAME")第3次执行，重新改写SQL语句
SQL_ID a92twbz0094wh, child number 0
-------------------------------------
select /*+ gather_plan_statistics opt_param('_b_tree_bitmap_plans','fal
se') */distinct t1.object_type,t1.status,t11.object_name,t11.status
from t1,t1 t11,(select distinct object_name from t2 where
object_type='INDEX' and owner='SCOTT' ) cc where
t1.object_id=t11.object_id and t1.object_name=cc.object_name and
t11.object_name=cc.object_name
Plan hash value: 1494189659
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 1361 | | | |
| 1 | HASH UNIQUE | | 1 | 5 | 2 |00:00:00.01 | 1361 | 941K| 941K| 491K (0)|
| 2 | NESTED LOOPS | | 1 | | 512 |00:00:00.01 | 1361 | | | |
| 3 | NESTED LOOPS | | 1 | 5 | 1280 |00:00:00.01 | 81 | | | |
| 4 | NESTED LOOPS | | 1 | 19 | 48 |00:00:00.01 | 57 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 2 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | IDX_T2_TYPE_OWNER | 1 | 1 | 2 |00:00:00.01 | 2 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 26 | 48 |00:00:00.01 | 54 | | | |
|* 8 | INDEX RANGE SCAN | IDX_T1_OBJECT_NAME | 2 | 26 | 48 |00:00:00.01 | 6 | | | |
|* 9 | INDEX RANGE SCAN | IDX_T1_OBJECT_NAME | 48 | 26 | 1280 |00:00:00.01 | 24 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | T1 | 1280 | 1 | 512 |00:00:00.01 | 1280 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("OBJECT_TYPE"='INDEX' AND "OWNER"='SCOTT')
8 - access("T1"."OBJECT_NAME"="OBJECT_NAME")
9 - access("T11"."OBJECT_NAME"="OBJECT_NAME")
10 - filter("T1"."OBJECT_ID"="T11"."OBJECT_ID")以上执行可得出结论：想减低COST，先执行全力降低结果集的语句（想方法设法过滤掉不必要的数据），才能降低整体的COST和执行的速度；
