| Is this a bug on Oracle11gr2? [message #567274] |
Tue, 25 September 2012 22:42  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hello,
I have a test on 11gr2 with dbms_stats.gather_table_stats,but I got a wrong SQL plan,
is this a bug on 11gr2?
SQL> create table t as select 1 id, a.* from dba_objects a;
表已创建。
SQL> update t set id=2 where rownum=1;
已更新 1 行。
SQL> create index idx_t on t(id);
索引已创建。
SQL> select id, count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 72524
2 1
SQL>
SQL> set autotrace traceonly exp;
SQL> select * from t where id = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 220 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 220 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select * from t where id = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36263 | 3541K| 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 36263 | 3541K| 299 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=2)
-------WRONG SQL plan,because id=2 just 1 record in table T,CBO should choose INDEX SCAN,but here is FULL TABLE SCAN.
SQL>
SQL> col column_name for a20;
SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where TABLE_NAME='T';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
T ID 5483 1
T OWNER 0 3.3913E+35
T OBJECT_NAME 0 2.4504E+35
T SUBOBJECT_NAME 0 1.8867E+35
T OBJECT_ID 0 2
T DATA_OBJECT_ID 0 0
T OBJECT_TYPE 0 3.4943E+35
T CREATED 0 2455289.55
T LAST_DDL_TIME 0 2452549.53
T TIMESTAMP 0 2.5558E+35
T STATUS 0 4.4786E+35
T TEMPORARY 0 4.0500E+35
T GENERATED 0 4.0500E+35
T SECONDARY 0 4.0500E+35
T NAMESPACE 0 1
T OWNER 1 4.5831E+35
T OBJECT_NAME 1 6.2963E+35
T SUBOBJECT_NAME 1 4.5340E+35
T OBJECT_ID 1 75089
T DATA_OBJECT_ID 1 75089
T OBJECT_TYPE 1 4.5849E+35
T CREATED 1 2456197.45
T LAST_DDL_TIME 1 2456197.45
T TIMESTAMP 1 2.6059E+35
T STATUS 1 4.4786E+35
T TEMPORARY 1 4.6211E+35
T GENERATED 1 4.6211E+35
T SECONDARY 1 4.6211E+35
T NAMESPACE 1 64
已选择29行。
SQL> select column_name,density,num_buckets,histogram from user_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
-------------------- ---------- ----------- ---------------
ID 6.9155E-06 1 FREQUENCY
OWNER .033333333 1 NONE
OBJECT_NAME .000022841 1 NONE
SUBOBJECT_NAME .007874016 1 NONE
OBJECT_ID .000013788 1 NONE
DATA_OBJECT_ID .000130141 1 NONE
OBJECT_TYPE .022727273 1 NONE
CREATED .000709723 1 NONE
LAST_DDL_TIME .000683527 1 NONE
TIMESTAMP .000652742 1 NONE
STATUS 1 1 NONE
TEMPORARY .5 1 NONE
GENERATED .5 1 NONE
SECONDARY .5 1 NONE
NAMESPACE .047619048 1 NONE
EDITION_NAME 0 0 NONE
已选择16行。
SQL>
---------I tried ANALYZE command,the SQL plan looks very nice!
SQL> analyze table T compute statistics for table for all indexes for all indexed columns;
表已分析。
SQL> select * from t where id = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 103 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
SQL>
SQL> select column_name,density,num_buckets,histogram from user_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
-------------------- ---------- ----------- ---------------
ID 6.8942E-06 2 FREQUENCY
OWNER .033333333 1 NONE
OBJECT_NAME .000022841 1 NONE
SUBOBJECT_NAME .007874016 1 NONE
OBJECT_ID .000013788 1 NONE
DATA_OBJECT_ID .000130141 1 NONE
OBJECT_TYPE .022727273 1 NONE
CREATED .000709723 1 NONE
LAST_DDL_TIME .000683527 1 NONE
TIMESTAMP .000652742 1 NONE
STATUS 1 1 NONE
TEMPORARY .5 1 NONE
GENERATED .5 1 NONE
SECONDARY .5 1 NONE
NAMESPACE .047619048 1 NONE
EDITION_NAME 0 0 NONE
已选择16行。
SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where TABLE_NAME='T';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
T ID 72524 1
T ID 72525 2
T OWNER 0 3.3913E+35
T OBJECT_NAME 0 2.4504E+35
T SUBOBJECT_NAME 0 1.8867E+35
T OBJECT_ID 0 2
T DATA_OBJECT_ID 0 0
T OBJECT_TYPE 0 3.4943E+35
T CREATED 0 2455289.55
T LAST_DDL_TIME 0 2452549.53
T TIMESTAMP 0 2.5558E+35
T STATUS 0 4.4786E+35
T TEMPORARY 0 4.0500E+35
T GENERATED 0 4.0500E+35
T SECONDARY 0 4.0500E+35
T NAMESPACE 0 1
T OWNER 1 4.5831E+35
T OBJECT_NAME 1 6.2963E+35
T SUBOBJECT_NAME 1 4.5340E+35
T OBJECT_ID 1 75089
T DATA_OBJECT_ID 1 75089
T OBJECT_TYPE 1 4.5849E+35
T CREATED 1 2456197.45
T LAST_DDL_TIME 1 2456197.45
T TIMESTAMP 1 2.6059E+35
T STATUS 1 4.4786E+35
T TEMPORARY 1 4.6211E+35
T GENERATED 1 4.6211E+35
T SECONDARY 1 4.6211E+35
T NAMESPACE 1 64
已选择30行。
SQL>
Regards
Alan
|
|
|
|
|
|
|
|
| Re: Is this a bug on Oracle11gr2? [message #567277 is a reply to message #567276] |
Tue, 25 September 2012 23:44  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
I want ONE record(id=2) from a table which have nearly 70K rows ,why CBO choose a FULL TABLE SCAN,it is a expensive cost .
SQL> select * from t where id=2;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36263 | 3541K| 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 36263 | 3541K| 299 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=2)
If there were NO statistics on the table T,Oracle use dynamic sampling ,it gets a nice plan.
SQL> select * from t where id = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 220 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 220 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
Thanks!
Alan
|
|
|
|
| Re: Is this a bug on Oracle11gr2? [message #567704 is a reply to message #567277] |
Thu, 04 October 2012 07:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I don't *know* the answer, but here's my best guess:
Dynamic Sampling: When Dynamic Sampling was performed, the CBO already knew what it was looking for: ID=2. During the sample, it found either 0 or 1 row with ID=2 and all the rest with ID=1, so it knew that ID=2 was unusual enough to benefit from an index.
Gather Table Stats: DBMS_STATS.GATHER_TABLE_STATS samples each column to determine whether it should build a histogram or not. The sampling process may have found every row with ID=1, so it could only build a histogram with one bucket (ie. useless). When the SQL ran, Oracle knew that ID was a non-selective column, so it did not use an index. Theoretically it could have looked at the ENDPOINT_NUMBER of the one-bucket histogram and realised that ID=2 was outside of that bucket (and therefore rare), but it seems it did not.
Analyze: You used COMPUTE with the Analyze, so it *definitely* saw the ID=2 row - realised the column was skewed, and create a 2-bucket histogram. When the SQL came along it knew from the histogram that ID=2 was selective.
Lesson: If your column is *so* skewed that sampling is unlikely to see the rare values, then increase your sample percent - to 100% if necessary.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
