OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » statistics update (11.2.0.3 on Windows 2008 R2)

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

statistics update [message #641366]

Thu, 13 August 2015 13:34

sant_new1
Messages: 46
Registered: June 2014

Member

Hi friends,

We are troubleshooting multiple performance problems and suspect stale statistics could be the base problem.. We found some tables that are not analyzed for few months to a year now.. We run 'dbms_stats.gather_database_stats' on a monthly basis but since some tables are being skipped after running this we are wondering if these tables did not have any significant data changes that they got skipped to gather statistics.. Is there a way to find out what tables have old information(tables that did not have much of data changes since the last analyzed) and so did not get analyzed to update statistics?

Thank you so much

Report message to a moderator

Re: statistics update [message #641368 is a reply to message #641366]

Thu, 13 August 2015 13:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>We are troubleshooting multiple performance problems and suspect stale statistics could be the base problem
Why?
what proof do you have that supports this speculation?

Enumerate the troubleshooting steps that have been completed?
How long have these performance problems existed?
Have they been getting worse?
When was the last time application performed OK?
What changed since then?

[Updated on: Thu, 13 August 2015 13:51]

Report message to a moderator

Re: statistics update [message #641369 is a reply to message #641366]

Thu, 13 August 2015 14:39

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can find the opposite: the tables that need to have statistics updated using this script.

Report message to a moderator

Re: statistics update [message #641370 is a reply to message #641369]

Thu, 13 August 2015 14:44

sant_new1
Messages: 46
Registered: June 2014

Member

Thanks Michel. I ran the script and it listed a few tables. But all these tables listed as 'stale' were last_analyzed yesterday. Does it mean we don't need to update statistics on them or the gather_stats job is not working on these tables?

Thanks a lot

Report message to a moderator

Re: statistics update [message #641371 is a reply to message #641370]

Thu, 13 August 2015 14:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If these tables do not contribute to the application slow down, what difference does it make?

Report message to a moderator

Re: statistics update [message #641372 is a reply to message #641371]

Thu, 13 August 2015 14:57

sant_new1
Messages: 46
Registered: June 2014

Member

Hi Blackswan,

Some of these tables are used in the program(batch) that is causing slowness.. please help

Thank you

Report message to a moderator

Re: statistics update [message #641373 is a reply to message #641372]

Thu, 13 August 2015 15:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

sant_new1 wrote on Thu, 13 August 2015 12:57

Hi Blackswan,

Some of these tables are used in the program(batch) that is causing slowness.. please help

Thank you

Help how?
You answered NONE of my previously posted questions.
You have posted NOTHING of substance; only idle speculation unsubstantiated by any facts.
Unless & until you know where slowness specifically occurs, you are blindly shooting in the dark & hoping to get lucky.

At the start of the slow batch job add the following statement
ALTER SESSION SET SQL_TRACE=TRUE;
After the job completes process the trace file using TKPROF while having it produce EXPLAIN PLAN for every SQL statement.
post the results back here

Report message to a moderator

Re: statistics update [message #641384 is a reply to message #641370]

Fri, 14 August 2015 01:05

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

sant_new1 wrote on Thu, 13 August 2015 21:44

Thanks Michel. I ran the script and it listed a few tables. But all these tables listed as 'stale' were last_analyzed yesterday. Does it mean we don't need to update statistics on them or the gather_stats job is not working on these tables?

Thanks a lot

The gather statistics job run once per day, if the statistics of these tables are stale since this means they have been modified in this time and the statistics will be gather again next time. So there is no problem with this point.

Report message to a moderator

Re: statistics update [message #641386 is a reply to message #641372]

Fri, 14 August 2015 01:06

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

sant_new1 wrote on Thu, 13 August 2015 21:57

Hi Blackswan,

Some of these tables are used in the program(batch) that is causing slowness.. please help

Thank you

Optimize the queries.

Report message to a moderator

Re: statistics update [message #641425 is a reply to message #641366]

Fri, 14 August 2015 10:37

sant_new1
Messages: 46
Registered: June 2014

Member

Thank you.. Here is the tkprof output of the query that is taking long.. Overall the batch takes about 9 minutes to run and the below tkprof output of the SQL alone takes 5 minutes.. It is a complex join query, due to restrictions sorry I'm afraid I cannot post it here but I'm hoping you can give me suggestions based on the wait events from the tkprof.

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      4667       4667       4667  HASH JOIN RIGHT OUTER (cr=32924344 pr=99540 pw=0 time=553566500 us cost=9879 size=1683120 card=21039)
       122        122        122   VIEW  (cr=32744435 pr=97410 pw=0 time=552302536 us cost=488 size=5734 card=122)
       122        122        122    HASH GROUP BY (cr=32744435 pr=97410 pw=0 time=552302535 us cost=488 size=3416 card=122)
       828        828        828     VIEW  (cr=32744435 pr=97410 pw=0 time=76392 us cost=487 size=9604 card=343)
       828        828        828      UNION-ALL  (cr=32744435 pr=97410 pw=0 time=76036 us)
       828        828        828       NESTED LOOPS  (cr=36187 pr=5 pw=0 time=74728 us)
     10901      10901      10901        NESTED LOOPS  (cr=3539 pr=5 pw=0 time=53158 us cost=6 size=111 card=1)
      1925       1925       1925         HASH JOIN  (cr=820 pr=2 pw=0 time=37689 us cost=5 size=80 card=1)
      5038       5038       5038          NESTED LOOPS  (cr=814 pr=2 pw=0 time=38141 us)
      5038       5038       5038           NESTED LOOPS  (cr=88 pr=1 pw=0 time=12555 us cost=4 size=754 card=13)
        11         11         11            TABLE ACCESS BY INDEX ROWID M_TABLE (cr=9 pr=0 pw=0 time=181 us cost=1 size=116 card=4)
        21         21         21             INDEX RANGE SCAN M_TYPE (cr=2 pr=0 pw=0 time=54 us cost=1 size=0 card=8)(object id 151355)
      5038       5038       5038            INLIST ITERATOR  (cr=79 pr=1 pw=0 time=9790 us)
      5038       5038       5038             INDEX RANGE SCAN ORDER_IDX (cr=79 pr=1 pw=0 time=7178 us cost=1 size=0 card=3)(object id 151821)
      5038       5038       5038           TABLE ACCESS BY INDEX ROWID ORD_TABLE (cr=726 pr=1 pw=0 time=20329 us cost=1 size=87 card=3)
         2          2          2          TABLE ACCESS BY INDEX ROWID M_TABLE (cr=6 pr=0 pw=0 time=47 us cost=1 size=154 card=7)
        12         12         12           INDEX RANGE SCAN M_TYPE (cr=2 pr=0 pw=0 time=27 us cost=1 size=0 card=8)(object id 151355)
     10901      10901      10901         INDEX RANGE SCAN ORDP_IDX (cr=2719 pr=3 pw=0 time=17551 us cost=1 size=0 card=1)(object id 151910)
       828        828        828        TABLE ACCESS BY INDEX ROWID ODP(cr=32648 pr=0 pw=0 time=56432 us cost=1 size=31 card=1)
         0          0          0       NESTED LOOPS  (cr=7824 pr=107 pw=0 time=231147 us)
      2385       2385       2385        NESTED LOOPS  (cr=675 pr=5 pw=0 time=10443 us cost=90 size=18414 card=341)
       458        458        458         INLIST ITERATOR  (cr=75 pr=0 pw=0 time=2190 us)
       458        458        458          TABLE ACCESS BY INDEX ROWID ORD_TABLE (cr=75 pr=0 pw=0 time=2042 us cost=5 size=9207 card=341)
       458        458        458           INDEX RANGE SCAN ORDER_IDX (cr=9 pr=0 pw=0 time=480 us cost=1 size=0 card=341)(object id 151821)
      2385       2385       2385         INDEX RANGE SCAN ORDP_IDX (cr=600 pr=5 pw=0 time=11155 us cost=1 size=0 card=1)(object id 151910)
         0          0          0        TABLE ACCESS BY INDEX ROWID ODP(cr=7149 pr=102 pw=0 time=216151 us cost=1 size=27 card=1)
         0          0          0       NESTED LOOPS  (cr=32700424 pr=97298 pw=0 time=551950536 us)
  23806541   23806541   23806541        NESTED LOOPS  (cr=6813255 pr=48105 pw=0 time=214184896 us cost=390 size=118 card=1)
  11073568   11073568   11073568         HASH JOIN  (cr=728964 pr=38966 pw=0 time=137769639 us cost=389 size=81 card=1)
         2          2          2          TABLE ACCESS BY INDEX ROWID M_TABLE (cr=6 pr=0 pw=0 time=63 us cost=1 size=154 card=7)
        12         12         12           INDEX RANGE SCAN M_TYPE (cr=2 pr=0 pw=0 time=18 us cost=1 size=0 card=8)(object id 151355)
  11540892   11540892   11540892          NESTED LOOPS  (cr=728958 pr=38966 pw=0 time=130747005 us cost=387 size=2270320 card=38480)
        11         11         11           TABLE ACCESS BY INDEX ROWID M_TABLE (cr=9 pr=0 pw=0 time=187 us cost=1 size=116 card=4)
        21         21         21            INDEX RANGE SCAN M_TYPE (cr=2 pr=0 pw=0 time=72 us cost=1 size=0 card=8)(object id 151355)
  11540892   11540892   11540892           TABLE ACCESS BY INDEX ROWID ODEL (cr=728949 pr=38966 pw=0 time=124881724 us cost=97 size=313740 card=10458)
  11540892   11540892   11540892            INDEX SKIP SCAN DELF_IDX (cr=25861 pr=2351 pw=0 time=23434830 us cost=60 size=0 card=4183)(object id 151788)
  23806541   23806541   23806541         INDEX RANGE SCAN ODPF_IDX (cr=6084291 pr=9139 pw=0 time=76869150 us cost=1 size=0 card=1)(object id 151908)
         0          0          0        TABLE ACCESS BY INDEX ROWID ODP(cr=25887169 pr=49193 pw=0 time=300679690 us cost=1 size=37 card=1)
      4667       4667       4667   HASH JOIN  (cr=179909 pr=2130 pw=0 time=1256273 us cost=9391 size=694287 card=21039)
      5480       5480       5480    VIEW  VW_NSO_1 (cr=177872 pr=117 pw=0 time=1103159 us cost=8828 size=396474 card=30498)
      5480       5480       5480     SORT UNIQUE (cr=177872 pr=117 pw=0 time=1101500 us cost=8828 size=358634 card=30498)
      7349       7349       7349      UNION-ALL  (cr=177872 pr=117 pw=0 time=619554 us)
      7128       7128       7128       INDEX SKIP SCAN ODP_CH_IDX (cr=7222 pr=6 pw=0 time=286263 us cost=2419 size=326381 card=29671)(object id 151902)
       221        221        221       NESTED LOOPS  (cr=170650 pr=111 pw=0 time=4318105 us cost=6406 size=32253 card=827)
     31686      31686      31686        HASH JOIN  (cr=152816 pr=111 pw=0 time=782427 us cost=6405 size=236012 card=8429)
     19685      19685      19685         TABLE ACCESS FULL C_CUST (cr=151578 pr=0 pw=0 time=78805 us cost=6126 size=53055 card=3537)
     51371      51371      51371         VIEW  index$_join$_005 (cr=1238 pr=111 pw=0 time=504507 us cost=278 size=675532 card=51964)
     51371      51371      51371          HASH JOIN  (cr=1238 pr=111 pw=0 time=477857 us)
     51371      51371      51371           INDEX RANGE SCAN C_CEN_IDX (cr=116 pr=109 pw=0 time=317061 us cost=29 size=675532 card=51964)(object id 156511)
    307032     307032     307032           INDEX FAST FULL SCAN C_CUST_IDX (cr=1122 pr=2 pw=0 time=116176 us cost=309 size=675532 card=51964)(object id 150741)
       221        221        221        INDEX RANGE SCAN ODP_CH_IDX (cr=17834 pr=0 pw=0 time=74358 us cost=1 size=11 card=1)(object id 151902)
    214974     214974     214974    TABLE ACCESS FULL CL_TABLE (cr=2037 pr=2013 pw=0 time=132623 us cost=561 size=4294400 card=214720)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      17        0.00          0.00
  db file sequential read                     97528        0.61        320.24
  latch: cache buffers chains                     1        0.00          0.00
  db file scattered read                         19        0.20          0.61
  SQL*Net message from client                    17        0.18          0.23
********************************************************************************

THank you so much for all your help..

Report message to a moderator

Re: statistics update [message #641427 is a reply to message #641425]

Fri, 14 August 2015 12:03

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

First let us have some perspective on statistics.

The purpose of collecting statistics is to change query plans.
Once a production system is stable (queries are meeting SLA) most DBAs and Developers do NOT want query plans to change.

What does the above make you think?

The goal of statistics collection is make every query on a system run with the fastest possible query plan.
The goal of DBAs and Developers (the intelligent ones anyway), is to build a system where all queries run within their SLA and are thus stable and predictable.

Are the two above goals the same?

So where am I going with this. Well... no where in particular. I just want everyone to understand that in order to work with statistics, your first priority is not to learn the technical stuff behind them, but rather to understand the difference between the THEORY of statistics, and the PRACTICE of statistics. You are worried about stale statistics, but though it is hard to measure, statistics are only stale when they cause query plans to be generated that are much slower that expected. Year old statistics are not stale if they are generating acceptable query plans. Day old statistics are stale if they are generating unacceptable query plans.

Getting on to your specific tuning need, here are questions from me:

1. do you have a query that you know is running slow or otherwise want to tune?
2. what is the good runtime of this query (what you liked before)
3. what is the slow runtime of this query that you are trying to improve

Assuming you have a query you are trying to tune, do you know how to use the GATHER_PLAN_STATISTICS hint? If not, read up about it, then use it to produce a query plan which will show us Estimated and Actual cardinalities of the plan steps. Then post the full QEP here (minimally that means PLAN_OUTPUT and PREDICATES).

After you do this, we can talk about the query plan and what might be its problem. It could indeed be stats. I will show you how to figure that out once you post the plan.

Kevin

Report message to a moderator

Re: statistics update [message #641428 is a reply to message #641425]

Fri, 14 August 2015 12:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> db file sequential read 97528 0.61 320.24
above is where you should focus your search & resolution efforts
Which objects are involved with all these db file sequential reads? I suspect these are reading INDEX

Report message to a moderator

Previous Topic:	query with hundreds of bind variables
Next Topic:	Please help for Resolving Issue for Explain plan as query taking long time for execution

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 13:24:08 CST 2025