stale stats [message #658242] |
Wed, 07 December 2016 08:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #658247 is a reply to message #658243] |
Wed, 07 December 2016 09:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8964 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/136107.jpg) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 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 #658267 is a reply to message #658266] |
Wed, 07 December 2016 13:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #658279 is a reply to message #658274] |
Thu, 08 December 2016 03:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 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 #658294 is a reply to message #658279] |
Thu, 08 December 2016 05:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 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.
|
|
|
|
|