Home » RDBMS Server » Performance Tuning » How can we verify the statistic info is not good (Oracle 10.2 , 11.2 , AIX, Linux)
How can we verify the statistic info is not good [message #575533] Wed, 23 January 2013 22:19 Go to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, guys
When we found a sql with some problem, we should problemly check the explain, then we might check something around it, such as tables and indexes statistics, index on the tables, records on each tables and so on.

Let's talk about the statistics of tables or indexes.

This are some scenarios happen daily:

S1:
The real tables records are very different with statistics info.
e.g.
select count(*) from tab1;
select num_rows from dba_tables where table_name='TAB1';


S2:
The real tables records are very close to the statistics info.
e.g.
select count(*) from tab1;
select num_rows from dba_tables where table_name='TAB1';


For S1 and S2, these should be a simple method to verify, in S2 do we has metrics to check if there is something wrong with it, for example, if the percentage diff larger than 10%, then there might be a problem, need to gather the stats.
It' not a strict way to do this.
How did you estimate some table or index statistics has problem?

Thanks very much.

[Updated on: Wed, 23 January 2013 22:22]

Report message to a moderator

Re: How can we verify the statistic info is not good [message #575554 is a reply to message #575533] Thu, 24 January 2013 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just check the last time the statistics were gathered, it is long compare to the modifications that is made on the table then regather them.

Regards
Michel
Re: How can we verify the statistic info is not good [message #575556 is a reply to message #575554] Thu, 24 January 2013 01:54 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Michel
Thanks for your comments.
Indeed, regathering is a very simple and correct way to make sure all the statistic current, but it will only suitable for those table with less data in not busy time, but as large tables(indexes) or many partition tables(indexes) , regather will cost a very long time to finish, during this time some business might be affected.
Is there any alternative way?

Thanks very much.
Re: How can we verify the statistic info is not good [message #575559 is a reply to message #575556] Thu, 24 January 2013 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any alternative way?


No, if you want to check the accuracy of the statistics you should execute the same queries than the gathering does and so it is better to gather.

For instance, to check if num_rows is correct you executed count(*), this is correct but you use the same (and even more) resources than if you gathered this statistic and in the end num_rows is not updated, so you consumed resources for nothing.

Regards
Michel
Re: How can we verify the statistic info is not good [message #575609 is a reply to message #575559] Thu, 24 January 2013 13:13 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
You might use dbms_xplan.display_cursor passing 'ALLSTATS LAST' to get an extended plan that includes Estimated and Actual row counts. (You need to set statistics_level = 'ALL' at session level, or use hint 'gather_plan_statistics' in the statement, also you need to set serveroutput off - it can be easier to use a script like xplanx.sql).

I also have a query that lists the objects and their statistics status for a specified sql_id - I set this up as a detail tab in my PL/SQL Developer session browser, but if you don't have that you can make it into a script:

with plan_objects as
     ( select --+ materialize
              p.object_owner
            , p.object_name
            , p.object_type
            , p.partition_start
            , p.partition_stop
            , p.cardinality
            , p.operation
            , p.options
            , count(*) as occurs_in_plan
       from   v$sql_plan_statistics_all p
       where  p.sql_id = :sql_id
       and    p.child_number = :sql_child_number
       and    p.plan_hash_value =
              ( select plan_hash_value from
                       ( select plan_hash_value, row_number() over (order by timestamp desc) as seq
                         from   gv$sql_plan p
                         where  p.sql_id = :sql_id
                         and    p.child_number = :sql_child_number
                         and    p.inst_id = :instance )
                where seq = 1 )
       and    p.object_type != 'VIEW'
       group by p.object_owner, p.object_name, p.object_type, p.partition_start, p.partition_stop, p.cardinality, p.operation, p.options )
   , object_stats as
     ( select ts.owner as object_owner
            , ts.table_name as object_name
            , ts.table_name as display_name
            , ts.num_rows
            , ts.blocks
            , ts.last_analyzed
            , ts.stale_stats
       from   dba_tab_statistics ts
       where  (ts.owner, ts.table_name)  in
              (select object_owner, object_name from plan_objects where object_type like 'TABLE%')
       and    ts.partition_name is null
       union
       select xs.owner
            , xs.index_name
            , '(' || xs.table_name || ') ' || index_name as display_name
            , xs.num_rows
            , xs.leaf_blocks as blocks
            , xs.last_analyzed
            , xs.stale_stats
       from   dba_ind_statistics xs
       where  (xs.owner, xs.index_name) in
              (select object_owner, object_name from plan_objects where object_type like 'INDEX%')
       and    xs.partition_name is null
     )
select --+ dynamic_sampling(8)
       object_owner
     , nvl(s.display_name,object_name) as object_name
     , o.object_type
     , o.occurs_in_plan
     , o.operation || ' ' || o.options as operation
     , o.cardinality
     , s.num_rows as "Rows (global)"
     , s.blocks
     , s.last_analyzed
     , s.stale_stats as "Stale?"
     , o.partition_start
     , o.partition_stop
from   plan_objects o
       left join object_stats s using(object_owner, object_name)
order by
      case object_owner when 'SYS' then 2 else 1 end
    , object_owner
    , case substr(object_type,1,5) when 'TABLE' then 1 when 'CLUST' then 2 when 'INDEX' then 3 else 4 end
    , object_type
    , object_name


Note the "stale_stats" column - Oracle already knows which tables have changed significantly since the stats were gathered.

[Updated on: Thu, 24 January 2013 13:15]

Report message to a moderator

Re: How can we verify the statistic info is not good [message #575783 is a reply to message #575559] Sun, 27 January 2013 20:21 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Michel
Thanks for your suggestion.
Re: How can we verify the statistic info is not good [message #575786 is a reply to message #575609] Sun, 27 January 2013 21:10 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, William
I test the stale_status columns, it seems it's not good.

The statistics is good, it's show up stale_status is "NO".
SQL> select count(*) from t;

  COUNT(*)
----------
         0

SQL>  select table_name, num_rows, last_analyzed, stale_stats from user_tab_statistics where table_name='T';

TABLE_NAME                       NUM_ROWS LAST_ANAL STA
------------------------------ ---------- --------- ---
T                                       0 26-JAN-13 NO




After I insert the data, it's still not stale.
SQL> select count(*) from t;

  COUNT(*)
----------
    991000

SQL>  select table_name, num_rows, last_analyzed, stale_stats from user_tab_statistics where table_name='T';

TABLE_NAME                       NUM_ROWS LAST_ANAL STA
------------------------------ ---------- --------- ---
T                                       0 26-JAN-13 NO



So it might not work as it's not always correct.
Thanks anyway.
Re: How can we verify the statistic info is not good [message #575810 is a reply to message #575786] Mon, 28 January 2013 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you commit after the INSERT?
Did you wait a little while before checking user_tab_statistics? Wait until user_tab_modifications indicates the insert, it is an asynchronous system.

Regards
Michel
Re: How can we verify the statistic info is not good [message #575883 is a reply to message #575786] Mon, 28 January 2013 15:03 Go to previous messageGo to next message
LNossov
Messages: 294
Registered: July 2011
Location: Germany
Senior Member
After inserts you have to run

exec dbms_stats.flush_database_monitoring_info


Then the view user_tab_modifications is populated.

@Michel, you don't have to wait.
Re: How can we verify the statistic info is not good [message #575884 is a reply to message #575883] Mon, 28 January 2013 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't want to add some new things in the test case.

Regards
Michel
Re: How can we verify the statistic info is not good [message #575885 is a reply to message #575810] Mon, 28 January 2013 17:35 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, LNossov
I will check again.
Thanks.

[Updated on: Mon, 28 January 2013 17:43]

Report message to a moderator

Re: How can we verify the statistic info is not good [message #575910 is a reply to message #575885] Tue, 29 January 2013 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please answer my questions.

Regards
Michel
Re: How can we verify the statistic info is not good [message #575913 is a reply to message #575910] Tue, 29 January 2013 01:38 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Michel
Sorry.
I did commit the data. But I didn't test how long(exact time) this will finished the synchronizing work for column stale_stats.
But LNossov's statement works immediately, after changing the tables.






SQL> select 1901000*0.1 from dual;

1901000*0.1
-----------
     190100

SQL> delete from t where rownum <= 190100;

190100 rows deleted.

SQL> commit;

Commit complete.





flush the db monitor info in another session:

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.





SQL> select table_name, num_rows, last_analyzed, stale_stats from user_tab_statistics where table_name='T';

TABLE_NAME                       NUM_ROWS LAST_ANAL STA
------------------------------ ---------- --------- ---
T                                 1901000 29-JAN-13 YES





Thanks very much.

[Updated on: Tue, 29 January 2013 01:38]

Report message to a moderator

Re: How can we verify the statistic info is not good [message #575989 is a reply to message #575913] Wed, 30 January 2013 06:16 Go to previous messageGo to next message
LNossov
Messages: 294
Registered: July 2011
Location: Germany
Senior Member
You can flush in the same session.
Re: How can we verify the statistic info is not good [message #576036 is a reply to message #575989] Thu, 31 January 2013 00:43 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Thanks, LNossov.
Because I am not using the user that has sysdba privilege, so I used another session to flush.
Anyway, thanks again. Smile
Re: How can we verify the statistic info is not good [message #576039 is a reply to message #576036] Thu, 31 January 2013 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to have SYSDBA, ANALYZE ANY privilege is sufficient (or IMP_FULL_DATABASE or DBA role).

Regards
Michel

[Updated on: Thu, 31 January 2013 00:56]

Report message to a moderator

Re: How can we verify the statistic info is not good [message #576070 is a reply to message #576039] Thu, 31 January 2013 05:14 Go to previous message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

OK, Thanks Michel. Smile
Previous Topic: what's mean of plh in SQL_TRACE?
Next Topic: SQL ordered by Elapsed Time
Goto Forum:
  


Current Time: Mon Dec 22 21:06:46 CST 2014

Total time taken to generate the page: 0.14616 seconds