Home » RDBMS Server » Performance Tuning » stale stats (Oracle 12c)
stale stats [message #658242] |
Wed, 07 December 2016 08:34  |
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 #658252 is a reply to message #658247] |
Wed, 07 December 2016 09:23   |
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   |
 |
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 #658294 is a reply to message #658279] |
Thu, 08 December 2016 05:07   |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 04 02:39:09 CDT 2025
|