OraFAQ Forum: Performance Tuning » Extended Statistics

Home » RDBMS Server » Performance Tuning » Extended Statistics

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extended Statistics [message #573834]

Thu, 03 January 2013 07:58

alantany
Messages: 115
Registered: July 2007

Senior Member

Hi,
Here is a test about Oracle 11g Extended Statistics,but it seems not as smart as expected.

SQL> create table t as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2  
from dba_objects where rownum<10000;

表已创建。

SQL> select * from t where rownum<10;

     FLAG1      FLAG2
---------- ----------
         1          0
         0          1
         1          0
         0          1
         1          0
         0          1
         1          0
         0          1
         1          0

已选择9行。

SQL> select dbms_stats.create_extended_stats(user,'t',extension=>'(flag1,flag2)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T',EXTENSION=>'(FLAG1,FLAG2)')
--------------------------------------------------------------------------------
SYS_STUGVNB7PTIYWAVPJX#YT77WGD

SQL> exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL> set autotrace trace exp;

SQL> select * from t where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL> set autotrace off;
SQL> select * from t where flag1=0 and flag2=0;

未选定行

SQL>

Please point out if I got something wrong here?

Thank you very much.
Regards
Alan

Report message to a moderator

Re: Extended Statistics [message #573860 is a reply to message #573834]

Thu, 03 January 2013 11:48

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

Possible explanation: columns with histograms, recollect statistics after query.Search for Why did the extended statistics not help in this case? in the article

Report message to a moderator

Re: Extended Statistics [message #573899 is a reply to message #573860]

Fri, 04 January 2013 00:12

alantany
Messages: 115
Registered: July 2007

Senior Member

Flyby wrote on Thu, 03 January 2013 11:48

Possible explanation: columns with histograms, recollect statistics after query.Search for Why did the extended statistics not help in this case? in the article

Thanks!
I recollect statistics followed the link,but the result didn't change.

SQL> exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL> select column_name,num_distinct,num_nulls,histogram from user_tab_col_statistics where table_name='T';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
FLAG1                                     2          0 FREQUENCY
FLAG2                                     2          0 FREQUENCY
SYS_STUGVNB7PTIYWAVPJX#YT77WGD            2          0 FREQUENCY

SQL> set autotrace trace exp;
SQL> select * from t where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL> exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL> set autotrace off;
SQL> select column_name,num_distinct,num_nulls,histogram from user_tab_col_statistics where table_name='T';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
FLAG1                                     2          0 FREQUENCY
FLAG2                                     2          0 FREQUENCY
SYS_STUGVNB7PTIYWAVPJX#YT77WGD            2          0 FREQUENCY

SQL> set autotrace trace exp;
SQL> select * from t where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL> select * from t where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>

Regards
Alan

Report message to a moderator

Re: Extended Statistics [message #573930 is a reply to message #573899]

Fri, 04 January 2013 06:19

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I suppose, it is a problem of autotrace.

SQL> create table t1 as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

Table created.

SQL> 
SQL> 
SQL> select count(*) from t1;

  COUNT(*)
----------
     10000

SQL> 
SQL> select * from t1 where flag1=0 and flag2=0;

no rows selected

SQL> 
SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))


18 rows selected.

SQL> 
SQL> select dbms_stats.create_extended_stats(user,'T1',extension=>'(flag1,flag2)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T1',EXTENSION=>'(FLAG1,FLAG2)')
--------------------------------------------------------------------------------
SYS_STUGVNB7PTIYWAVPJX#YT77WGD

SQL> 
SQL> select * from t1 where flag1=0 and flag2=0;

no rows selected

SQL> 
SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))


18 rows selected.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from t1 where flag1=0 and flag2=0;

no rows selected

SQL> 
SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))


18 rows selected.

SQL> 
SQL> set autotrace trace exp
SQL> 
SQL> select * from t1 where flag1=0 and flag2=0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL> 
SQL> set autotrace off
SQL> 
SQL> select column_name,num_distinct,num_nulls,histogram from user_tab_col_statistics where table_name='T1';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
FLAG1                                     2          0 FREQUENCY
FLAG2                                     2          0 FREQUENCY
SYS_STUGVNB7PTIYWAVPJX#YT77WGD            2          0 FREQUENCY

SQL> 
SQL> drop table t1;

Table dropped.

SQL> 
SQL> exit

Report message to a moderator

Re: Extended Statistics [message #573938 is a reply to message #573930]

Fri, 04 January 2013 07:33

alantany
Messages: 115
Registered: July 2007

Senior Member

Hi,LNossov

Thank you very much.I followed your test but get a different result,my db is Oracle11gR2 EE

D:\Windows\system32>sqlplus test/test

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 1月 4 21:26:59 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table t1 as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

表已创建。

SQL> select * from t1 where flag1=0 and flag2=0;

未选定行

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))

Note
-----
   - dynamic sampling used for this statement (level=2)


已选择22行。

SQL>  select dbms_stats.create_extended_stats(user,'T1',extension=>'(flag1,flag2)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T1',EXTENSION=>'(FLAG1,FLAG2)')
--------------------------------------------------------------------------------
SYS_STUGVNB7PTIYWAVPJX#YT77WGD

SQL>  select * from t1 where flag1=0 and flag2=0;

未选定行

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dxk56tp89t49c, child number 0
-------------------------------------
 select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))

Note
-----
   - dynamic sampling used for this statement (level=2)


已选择22行。

SQL>  exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL> select * from t1 where flag1=0 and flag2=0;

未选定行

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))


已选择18行。

SQL>  exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL> select * from t1 where flag1=0 and flag2=0;

未选定行

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))


已选择18行。

Regards
Alan

[Edit MC: remove useless quote of complete previous post]

[Updated on: Fri, 04 January 2013 08:53] by Moderator

Report message to a moderator

Re: Extended Statistics [message #573941 is a reply to message #573938]

Fri, 04 January 2013 08:05

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hello Alan

I checked on the 11.2.0.3.

Regards
Leonid

Report message to a moderator

Re: Extended Statistics [message #573951 is a reply to message #573938]

Fri, 04 January 2013 09:13

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Try this testcase please.

create table t1 as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

insert into t1 select level, level from dual connect by level <= 1;

commit;

exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

select dbms_stats.create_extended_stats(user,'T1',extension=>'(FLAG1,FLAG2)') from dual;

alter system flush shared_pool;

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

alter system flush shared_pool;

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

truncate table t1;

insert into t1 select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

commit;

exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

insert into t1 select level, level from dual connect by level <= 1;

commit;

exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

drop table t1;

Report message to a moderator

Re: Extended Statistics [message #573956 is a reply to message #573951]

Fri, 04 January 2013 09:41

alantany
Messages: 115
Registered: July 2007

Senior Member

Hi,Leonid
It works with your test,thank you !

but could you please give explanation on your test?

SQL> create table t1 as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

表已创建。

SQL>
SQL> insert into t1 select level, level from dual connect by level <= 1;

已创建 1 行。

SQL>
SQL> commit;

提交完成。

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL>
SQL> set autotrace trace exp
SQL>
SQL> select * from t1 where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>
SQL> set autotrace off
SQL> select dbms_stats.create_extended_stats(user,'T1',extension=>'(FLAG1,FLAG2)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T1',EXTENSION=>'(FLAG1,FLAG2)')
--------------------------------------------------------------------------------
SYS_STUGVNB7PTIYWAVPJX#YT77WGD

SQL>
SQL> alter system flush shared_pool;

系统已更改。

SQL>
SQL> set autotrace trace exp
SQL>
SQL> select * from t1 where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>
SQL> set autotrace off
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL>
SQL> alter system flush shared_pool;

系统已更改。

SQL>
SQL> set autotrace trace exp
SQL>
SQL> select * from t1 where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     6 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;

表被截断。

SQL> insert into t1 select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

已创建10000行。

SQL>
SQL> commit;

提交完成。

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL>
SQL> set autotrace trace exp
SQL>
SQL> select * from t1 where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>
SQL> set autotrace off
SQL>
SQL> set autotrace trace exp
SQL>
SQL> select * from t1 where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2500 | 15000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2500 | 15000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>
SQL> set autotrace off
SQL>
SQL> insert into t1 select level, level from dual connect by level <= 1;

已创建 1 行。

SQL>
SQL> commit;

提交完成。

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 254');

PL/SQL 过程已成功完成。

SQL>
SQL> set autotrace trace exp
SQL>
SQL> select * from t1 where flag1=0 and flag2=0;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     6 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL>
SQL> set autotrace off
SQL>
SQL> drop table t1;

表已删除。

SQL>

Regards
Alan

Report message to a moderator

Re: Extended Statistics [message #573968 is a reply to message #573956]

Fri, 04 January 2013 11:32

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hi Alan

I didn't investigate properly this issue. But it seems to be so:

- it doesn't work with only 2 different values for a column group,
- after creation of extended stats it is necessary to gather the stats,
- the package dbms_xplan has problems with extended stats (my testcase doesn't include the appropriate test, but you can test it yourself).

Regards
Leonid

Report message to a moderator

Re: Extended Statistics [message #573981 is a reply to message #573968]

Fri, 04 January 2013 13:02

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

May I say "thank you" for this topic? I have learnt something. That makes it a good day.

Report message to a moderator

Re: Extended Statistics [message #573984 is a reply to message #573968]

Fri, 04 January 2013 13:06

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hi Alan

I got a good testcase. So

- it doesn't work with only 2 different values for a column group,
- after creation of extended stats it is necessary to gather the stats,
- there are some differences in dbms_xplan and autotrace. But in this testcase they produce identical outputs.

Regards
Leonid

col ext new_value ext

create table t1 as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

insert into t1 select 0, 100 from dual connect by level <= 100;
insert into t1 select 100, 0 from dual connect by level <= 100;
insert into t1 select 0, 0 from dual connect by level <= 10;

commit;

select dbms_stats.create_extended_stats(null,'T1','(FLAG1,FLAG2)') ext from dual;

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns "&ext" size 254');

select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'T1';

select * from user_histograms where table_name = 'T1';

alter system flush shared_pool;

set autotrace trace exp

select * from t1 where flag1=0 and flag2=0;

set autotrace off

select * from t1 where flag1=0 and flag2=0;

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED LAST'));

drop table t1;

The output:

SQL> create table t1 as select decode(mod(rownum,2),0,0,1) flag1,decode(mod(rownum,2),0,1,0) flag2 from dual connect by level <= 10000;

Table created.

SQL> 
SQL> insert into t1 select 0, 100 from dual connect by level <= 100;

100 rows created.

SQL> insert into t1 select 100, 0 from dual connect by level <= 100;

100 rows created.

SQL> insert into t1 select 0, 0 from dual connect by level <= 10;

10 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select dbms_stats.create_extended_stats(null,'T1','(FLAG1,FLAG2)') ext from dual;

EXT
--------------------------------------------------------------------------------
SYS_STUGVNB7PTIYWAVPJX#YT77WGD

SQL> 
SQL> exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns "&ext" size 254');

PL/SQL procedure successfully completed.

SQL> 
SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'T1';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
SYS_STUGVNB7PTIYWAVPJX#YT77WGD            5 FREQUENCY

SQL> 
SQL> select * from user_histograms where table_name = 'T1';

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------
T1
SYS_STUGVNB7PTIYWAVPJX#YT77WGD
            100     2591110632



TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------
T1
SYS_STUGVNB7PTIYWAVPJX#YT77WGD
           5100     4787288623



TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------
T1
SYS_STUGVNB7PTIYWAVPJX#YT77WGD
           5110     6202751823



TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------
T1
SYS_STUGVNB7PTIYWAVPJX#YT77WGD
          10110     9877439385



TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------
T1
SYS_STUGVNB7PTIYWAVPJX#YT77WGD
          10210     9880799046



SQL> 
SQL> alter system flush shared_pool;

System altered.

SQL> 
SQL> set autotrace trace exp
SQL> 
SQL> select * from t1 where flag1=0 and flag2=0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   160 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |   160 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"=0 AND "FLAG2"=0)

SQL> 
SQL> set autotrace off
SQL> 
SQL> select * from t1 where flag1=0 and flag2=0;

     FLAG1      FLAG2
---------- ----------
         0          0
         0          0
         0          0
         0          0
         0          0
         0          0
         0          0
         0          0
         0          0
         0          0

10 rows selected.

SQL> 
SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3dy4xq2jj5b2y, child number 0
-------------------------------------
select * from t1 where flag1=0 and flag2=0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     8 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |   160 |     8   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("FLAG1"=0 AND "FLAG2"=0))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "FLAG1"[NUMBER,22], "FLAG2"[NUMBER,22]


43 rows selected.

SQL> 
SQL> drop table t1;

Table dropped.

[Updated on: Fri, 04 January 2013 13:09]

Report message to a moderator