Home » RDBMS Server » Performance Tuning » question about stats (oracle 10.2.0.4 on solaris 8)
question about stats [message #445389] Mon, 01 March 2010 07:19 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

can someone give an example which demonstrate how wrong stats on a table lead to a different/inefficient execution plan? I tried one but in what I tried, optimizer picked same plan when stats were incorrect. Here is what I tried;

SQL> CREATE TABLE TAB2 AS SELECT * FROM TAB1 WHERE 1=2;

Table created.

SQL> CREATE INDEX I2 ON TAB2(COL1);

Index created.

SQL> DESC TAB2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                      NOT NULL NUMBER
 COL2                                               NUMBER

SQL> ED 1

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,TABNAME=>'TAB2',cascade=>true);

PL/SQL procedure successfully completed.
--this proc is a simple for loop that adds 100000 rows from 1 to that number in the tab2 table. 
SQL> @1

PL/SQL procedure successfully completed.

SQL> select count(*) from tab2;

  COUNT(*)
----------
    100000

SQL> select num_rows from user_tables where table_name='TAB2';

  NUM_ROWS
----------
         0

SQL> set autot on
SQL> select * from tab2 where col1 between 1178 and 1192;

      COL1       COL2
---------- ----------
      1178       1178
      1179       1179
      1180       1180
      1181       1181
      1182       1182
      1183       1183
      1184       1184
      1185       1185
      1186       1186
      1187       1187
      1188       1188

      COL1       COL2
---------- ----------
      1189       1189
      1190       1190
      1191       1191
      1192       1192

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 115759578

--------------------------------------------------------------------------------
----

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
   |

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT            |      |     1 |    26 |     0   (0)| 00:00:
01 |

|   1 |  [b]TABLE ACCESS BY INDEX ROWID[/b]| TAB2 |     1 |    26 |     0   (0)| 00:00:
01 |

|*  2 |   INDEX RANGE SCAN          | I2   |     1 |       |     0   (0)| 00:00:
01 |

--------------------------------------------------------------------------------
----


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

   2 - access("COL1">=1178 AND "COL1"<=1192)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed
--now we update the stats
SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,TABNAME=>'TAB2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot off
SQL> select num_rows from user_tables where table_name='TAB2';

  NUM_ROWS
----------
    100000

SQL> set autot on
SQL>  select * from tab2 where col1 between 1178 and 1192;

      COL1       COL2
---------- ----------
      1178       1178
      1179       1179
      1180       1180
      1181       1181
      1182       1182
      1183       1183
      1184       1184
      1185       1185
      1186       1186
      1187       1187
      1188       1188

      COL1       COL2
---------- ----------
      1189       1189
      1190       1190
      1191       1191
      1192       1192

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 115759578

--------------------------------------------------------------------------------
----

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
   |

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT            |      |    16 |   144 |     3   (0)| 00:00:
01 |

|   1 |  [b]TABLE ACCESS BY INDEX ROWID[/b]| TAB2 |    16 |   144 |     3   (0)| 00:00:
01 |

|*  2 |   INDEX RANGE SCAN          | I2   |    16 |       |     2   (0)| 00:00:
01 |

--------------------------------------------------------------------------------
----
--the plan is same as the plan oracle used in absence of an index

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

   2 - access("COL1">=1178 AND "COL1"<=1192)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL> 

what I am looking for is an example where optimizer chooses a wrong plan due to incorrect stats..

thanks
Re: question about stats [message #445395 is a reply to message #445389] Mon, 01 March 2010 08:26 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
What is your Oracle version?
Re: question about stats [message #445396 is a reply to message #445395] Mon, 01 March 2010 08:48 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
sorry, it is 10.2.0.4 on Solaris 10.
Re: question about stats [message #445403 is a reply to message #445396] Mon, 01 March 2010 09:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>what I am looking for is an example where optimizer chooses a wrong plan due to incorrect stats..
One of the simple methods is to make use of dbms_stats.set_table_stats or set_column_stats (depending on sql you use and level you are about to demonstrate).
Here is an old example.
http://www.orafaq.com/forum/m/170708/42800/?srch=set_table_stats#msg_170708
Re: question about stats [message #445405 is a reply to message #445396] Mon, 01 March 2010 09:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Example for set_column_stats
http://www.orafaq.com/forum/mv/msg/58301/156882/42800/#msg_156882
Re: question about stats [message #445407 is a reply to message #445405] Mon, 01 March 2010 10:11 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
This is great!! thanks a lot!
Previous Topic: Performance regression from 8i to 10G
Next Topic: ora-1652
Goto Forum:
  


Current Time: Wed Dec 07 14:46:05 CST 2016

Total time taken to generate the page: 0.11158 seconds