Home » RDBMS Server » Performance Tuning » Is this a bug on Oracle11gr2? (11gr2)
Is this a bug on Oracle11gr2? [message #567274] Tue, 25 September 2012 22:42 Go to next message
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 #567275 is a reply to message #567274] Tue, 25 September 2012 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 21939
Registered: January 2009
Senior Member
Nice Post!

>Plan hash value: 1594971208 (top)
Bytes=220
>Plan hash value: 1594971208 (bottom)
Bytes=103

why the difference?
Re: Is this a bug on Oracle11gr2? [message #567276 is a reply to message #567275] Tue, 25 September 2012 23:01 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
You question is somewhat confusing. Did you

1) get the wrong answer?

2) get a plan you don't like?

IF #2 then what can anyone say. The optimizer is good, but not perfect.

IF #1 then TAR w/ Oracle Support as there are known issues where certain optimizations produce incorrect results. They are few and rare but if you got wrong results they want to know about it.

Good luck, Kevin
Re: Is this a bug on Oracle11gr2? [message #567277 is a reply to message #567276] Tue, 25 September 2012 23:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3651
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
Re: Is this a bug on Oracle11gr2? [message #567741 is a reply to message #567704] Thu, 04 October 2012 15:20 Go to previous message
Flyby
Messages: 139
Registered: March 2011
Location: Belgium
Senior Member
Plausible. Perhaps a case the new AUTO_SAMPLE_SIZE didn't catch.
Previous Topic: Help required for tuning referring AWR Reports
Next Topic: cluter in index
Goto Forum:
  


Current Time: Thu Apr 17 12:13:08 CDT 2014

Total time taken to generate the page: 0.06437 seconds