Home » RDBMS Server » Performance Tuning » stale stats (Oracle 12c)
stale stats [message #658242] Wed, 07 December 2016 08:34 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member

Hello,

We have an issue with slowness in DB and one of the suggestions from DBA is to check stale stats for the table. He has provided below info.

Examples:

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP from dba_tab_modifications where table_owner='LA' and TABLE_NAME='WORKFLOW_S';

   INSERTS    UPDATES    DELETES TIMESTAMP
---------- ---------- ---------- --------------   ( 100% modification )
       146          0        146 06-DEC-16

SQL> select INSERTS,UPDATES,DELETES,TIMESTAMP from dba_tab_modifications where table_owner='LA' and TABLE_NAME='PROVS';

   INSERTS    UPDATES    DELETES TIMESTAMP
---------- ---------- ---------- ------------------
      8118       9083       8115 06-DEC-16



DBA guys are telling our DEV team that too much of stale in the above tables and hence we need to plan to change DB architecture for these tables.

But I am not convinced because there are very few records in the tables. Yes, there are inserts and deletes happens to above tables daily but that does not mean it is hampering any performance related to queries. Am I correct here? Please advise.

Your suggestions are valuable to us.

Regards,
SRK

Re: stale stats [message #658243 is a reply to message #658242] Wed, 07 December 2016 08:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
based upon posted details there is no way to determine if you are correct or not.

>We have an issue with slowness in DB
is EVERY query slow?
are only a few query slow?
stale statistics may be why query is slow or some other issue may be why query is slow.
quantify slow!

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: stale stats [message #658247 is a reply to message #658243] Wed, 07 December 2016 09:00 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
@BlackSwan,

Thanks for the details.

We have already taken explain plan and as well as checked the size of the tables. The cost of the query is less and executing fine now after gather stats is done.
We are investigating now why there was slowness earlier.

Note: we have not taken explain plan for the query before gather stats was done.

Meanwhile, lets's consider we have one query which is taking more time and that query is having the tables mentioned below and those in stale='YES' state.
Does this is one of the reason for slowness of the query? NOT to forget the tables are having very less data!!

So,in my opinion, this can never be "stale stats" reason for slowness as these tables are having very less data.

If the tables are having very huge data modifications, then it is fine to "stale stats". But it is not the case.

Please advise.

Regards,
SRK

Re: stale stats [message #658248 is a reply to message #658242] Wed, 07 December 2016 09:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Those figures show that number of rows in the tables has not really changed since the last time they were analyzed. It is however possible that the distribution of column values has changed, and you need to rebuild your histograms. This is just SQL basic tuning, You need to get the execution plans out, showing the estimated and actual cardinalities.
Re: stale stats [message #658252 is a reply to message #658247] Wed, 07 December 2016 09:23 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
The explain plan and query details below.

SELECT COUNT(:"SYS_B_0") INV_NUM
  FROM INVOICE         I,
       BAN             B,
       COMPANY_CODE    C,
       VENDOR          V,
       CUSTOMER        CS,
       INVOICE_CHARGES IC
WHERE I.BAN_ID IS NOT NULL
   AND I.INVOICE_NO IS NOT NULL
   AND I.INTERNAL_STATUS IS NOT NULL
   AND I.BAN_ID = B.BAN_ID(+)
   AND B.COMPANY_CODE_ID = C.COMPANY_CODE_ID(+)
   AND C.VENDOR_ID = V.VENDOR_ID(+)
   AND I.CUSTOMER_ID = CS.CUSTOMER_ID(+)
   AND IC.INVOICE_ID(+) = I.INVOICE_ID
   AND IC.CHARGE_ID(+) = :1
   and I.CUSTOMER_ID = :2
   and LOWER(CS.CUSTOMER_NAME) LIKE
       LOWER(replace(:3, :"SYS_B_1", :"SYS_B_2"))

       select count(*) from INVOICE_CHARGES  --1148451
       
       select count(*) from CUSTOMER -- 1
       
       select count(*) from VENDOR -- 609
              
       select count(*) from COMPANY_CODE  -- 799
             
       select count(*) from BAN  -- 2869
       
       select count(*) from INVOICE  -- 56912


The DBA guys are telling INVOICE and BAN tables were stale='YES' and hence the query was taking time earlier.
But I am thinking the data is very small and how it can be the reason.


Below explain plan after gather stats done:
PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------
Plan hash value: 2096554764

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |     1 |    47 |       |  3598   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                       |                           |     1 |    47 |       |            |          |
|*  2 |   HASH JOIN RIGHT OUTER               |                           |   157K|  7241K|  3456K|  3598   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| INVOICE_CHARGES           |   176K|  1380K|       |  1841   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IDX_INV_CHARGES_CHARGE_ID |   176K|       |       |    42   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                       |                           |   101K|  3854K|       |  1636   (1)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID       | CUSTOMER                  |     1 |    12 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                | PK_CUSTOMER               |     1 |       |       |     1   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL                 | INVOICE                   |   101K|  2668K|       |  1635   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------

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

   2 - access("IC"."INVOICE_ID"(+)="I"."INVOICE_ID")
   4 - access("IC"."CHARGE_ID"(+)=TO_NUMBER(:1))
   6 - filter(LOWER("CS"."CUSTOMER_NAME") LIKE LOWER(REPLACE(:3,:SYS_B_1,:SYS_B_2)))
   7 - access("CS"."CUSTOMER_ID"=TO_NUMBER(:2))
   8 - filter("I"."INTERNAL_STATUS" IS NOT NULL AND "I"."INVOICE_NO" IS NOT NULL AND "I"."BAN_ID" IS NOT NULL AND
              "I"."CUSTOMER_ID"=TO_NUMBER(:2))

Note: please ignore earlier tables'WORKFLOW_S' and 'PROVS'. The actual tables are INVOICE and BAN.

Regards,
SRK

[Updated on: Wed, 07 December 2016 09:25] by Moderator

Report message to a moderator

Re: stale stats [message #658253 is a reply to message #658252] Wed, 07 December 2016 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You do not have to convince me about anything.
IMO, unless & until you identify the root cause of the slow query; everything else is just idle speculation.
You can NOT prove a negative. You can not prove the problem is NOT due to poor statistics.

EXPLAIN PLAN reports query should complete in about 1 second.
how long does query really take to complete?

>SELECT COUNT(:"SYS_B_0") INV_NUM
does performance change based upon the value of the bind variable above?

[Updated on: Wed, 07 December 2016 09:37]

Report message to a moderator

Re: stale stats [message #658254 is a reply to message #658252] Wed, 07 December 2016 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't see how that plan can go with that query. Query has 6 tables. Plan has 3.

Also:
You should probably consider changing cursor_sharing from force to exact.
The outer-join on invoice_charges is actually an inner-join since you're matching it to a bind.
The plan thinks there are approx twice as many rows in invoice as you say there are.

Re: stale stats [message #658255 is a reply to message #658253] Wed, 07 December 2016 09:48 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
@BlackSwan,

Sorry!! Yes,we have to find out the root cause. Thanks for the info.

The query took almost 7 hours to complete as per AWR report. Yes, performance change is based on bind values but not much difference.

We are unable to get the values of the bind variables which took 7 hrs.

@Cookie Master,

oh!! sorry. I think may be some part was missing. Let me check and come back.
Thanks for your suggestions.
Re: stale stats [message #658256 is a reply to message #658255] Wed, 07 December 2016 10:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
How long is it taking to complete now?

Is invoice foreign keyed to ban, ban foreign keyed to company_code and company_code to vendor?
Re: stale stats [message #658265 is a reply to message #658256] Wed, 07 December 2016 12:52 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Yes, the relation is correct.

Now, the query is taking around 1 second.
Re: stale stats [message #658266 is a reply to message #658265] Wed, 07 December 2016 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what changed?
Re: stale stats [message #658267 is a reply to message #658266] Wed, 07 December 2016 13:47 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
oh!! I think some misunderstanding here.

The issue was the query took 7 hours to complete earlier and after doing gather stats it is taking 1 second only.

I am investigating why it took 7 hours earlier and I have shared explain plan for latest one where it took only one second.

Please note as of now, it is taking 1 second only.


When checked with DBA guys for explanation on slowness earlier, they are telling its because of stale stats, it took 7 hours.
My question is, staled tables are having very less data and how can it be the reason for query performance.

Regards,
SRK
Re: stale stats [message #658274 is a reply to message #658267] Wed, 07 December 2016 21:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How can SELECT statement that queries SIX tables produce a PLAN that shows only THREE of the six tables?

Does this mean the THREE missing tables can be entirely & completely removed from the SELECT statement?
Re: stale stats [message #658279 is a reply to message #658274] Thu, 08 December 2016 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes, because of the foreign keys oracle knows they make zero difference to the result so it's just ignoring them.
@srinivas.k2005 - remove ban, company_code and vendor from the query they're not doing anything useful.

As for working out why it was so slow - without a plan from when it was running slow all we can do is guess, and since gathering the stats speeded it up massively stale stats is as good guess as any.
Re: stale stats [message #658292 is a reply to message #658279] Thu, 08 December 2016 04:59 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks a lot.
Re: stale stats [message #658294 is a reply to message #658279] Thu, 08 December 2016 05:07 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Yes, you are correct.

Below two queries are giving same results.

SELECT COUNT(1) INV_NUM
FROM INVOICE I,
CUSTOMER CS,
INVOICE_CHARGES IC
WHERE I.BAN_ID IS NOT NULL
AND I.INVOICE_NO IS NOT NULL
AND I.INTERNAL_STATUS IS NOT NULL
AND I.CUSTOMER_ID = CS.CUSTOMER_ID(+)
AND IC.INVOICE_ID(+) = I.INVOICE_ID



SELECT COUNT(1) INV_NUM
FROM INVOICE I,
BAN B,
COMPANY_CODE C,
VENDOR V,
CUSTOMER CS,
INVOICE_CHARGES IC
WHERE I.BAN_ID IS NOT NULL
AND I.INVOICE_NO IS NOT NULL
AND I.INTERNAL_STATUS IS NOT NULL
AND I.BAN_ID = B.BAN_ID(+)
AND B.COMPANY_CODE_ID = C.COMPANY_CODE_ID(+)
AND C.VENDOR_ID = V.VENDOR_ID(+)
AND I.CUSTOMER_ID = CS.CUSTOMER_ID(+)
AND IC.INVOICE_ID(+) = I.INVOICE_ID

Regards,
SRK
Re: stale stats [message #658296 is a reply to message #658294] Thu, 08 December 2016 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Cool. Now removed the (+) on IC. If the query restricts to a given ic.charge_id then having an outer-join is pointless. It's got to find a row in invoice_charges to match the predicate.

Also, you almost certainly want to change the cursor_sharing parameter to exact if you haven't already. Having it set to force is a buggy crutch for code that doesn't use bind variables. You are and force can cause all sorts of performance issues.
Re: stale stats [message #658300 is a reply to message #658294] Thu, 08 December 2016 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Format your posts!

Re: stale stats [message #658409 is a reply to message #658300] Tue, 13 December 2016 03:44 Go to previous message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thank you. Sure... Thanks for the inputs.
Previous Topic: Oracle Advance Queue consumes messages very slowly
Next Topic: Which index to choose local or global for attached scenario
Goto Forum:
  


Current Time: Thu Mar 28 03:42:53 CDT 2024