| Performance degrades suddenly. [message #394867] |
Mon, 30 March 2009 11:32  |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
We have a strange problem of performance degradation...
Our application works fine, but suddenly the performance degrades.
The scenario is like this:-
When the application is working fine, the query takes 5 secs to execute and suddenly the performance degrades and the same query takes 3mins(180 secs) to execute or at times soesnt return any resturns and hungs up.
Nothing has changes at the database, table, index application level.
We are analyzing the tables and indexes used in queries using compute option on regular basis.
And Whenever the performance degrades, we have to analyze the tables and indexes again and then flush the shared pool and buffer cache, doing so resolves the issue for 2hrs to 3hrs time [the query again performs well] and then the same issue pops up again[query performance degrades] no matter wheather it is PEAK or OFFPEAK business hours.
The query takes the same amount of time even if we run it locally at SQLPLUS prompt.
At times when we even flush the shared pool and buffer cache the issue is resolved for time being and then the same issue pops up again.
I am really stunned and unable to understand what and how it is happening internally, and how to get rid of this.
| Quote: | Cursor_sharring is set to Exact
SGA_MAX_SIZE is 15G and dynamically configured.
|
Below is the query for your reference:-
SELECT a.docketno AS docketno, a.mobile_no AS mobileno,
TO_CHAR (a.ccdate, 'dd/Mon/yy hh:mi:ss AM') AS cc_date,
a.COUNT AS COUNT, a.currentstatus AS status, a.TYPE AS TYPE,
b.CATEGORY AS CATEGORY, NVL (c.description, '-') AS subcategory,
d.description subsubcategory, a.resolution_type AS rs_type,
(SELECT username
FROM users
WHERE userid = a.usercode) AS sender,
a.SOURCE AS registrationdetails, a.feedback AS feedback,
(SELECT NAME
FROM msc
WHERE msccode = a.msccode) AS msc, a.msccode
FROM prep_comp_master a LEFT OUTER JOIN cccategory b
ON a.categorycode = b.categoryno
LEFT OUTER JOIN masters c
ON b.categoryno = c.categoryno AND a.csno = c.sno
LEFT OUTER JOIN crm_sst_master d ON d.sst_code = a.ssct
WHERE a.mobile_no = '96560000'
ORDER BY ccdate DESC;
Your suggestion will be highly appreciated.
Thanks
|
|
|
|
|
|
| Re: Performance degrades suddenly. [message #394886 is a reply to message #394867] |
Mon, 30 March 2009 13:18  |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Thanks Mahesh,
For analyzing we use:
| Quote: | Analyze table prep_comp_master compute statistics;
And datatype for mobile_no is number(10).
|
The tables are non partitioned tables and size is approx 1.5GB.
Please suggest.
Thanks.
|
|
|
|
|
|
|
|
| Re: Performance degrades suddenly. [message #394894 is a reply to message #394867] |
Mon, 30 March 2009 13:37 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Thanks Mahesh,
Well the query is hardcoded in application and I have copied it from the TOAD.
I will look into the mobile number clause.
And we are seperately analyzing the tables and indexes using.
| Quote: | ANALYZE TABLE PREP_COMP_MASTER COMPUTE STATISTICS;
ANALYZE INDEX PREP_COMP_MAS_IND COMPUTE STATISTICS;
|
Also, as suggested by you, I will try to gather the stats using DBMS_STATS for all the tables in the queries with cascade = true for gathering the associated index stats also.
Will surely update you once the stat gather is done and there is any relief.
But one thing which confuses me a lot is why after flushing the buffer cache and shared pool queries work fine till 2 hrs to 3 hrs no matter wheather it is PEAK or OFFPEAK hours, and I have to flush the buffer and shared pool again and again, which I know is not a good practice.
Regards
Maddy.
|
|
|
|
|
|
| Re: Performance degrades suddenly. [message #395022 is a reply to message #394900] |
Tue, 31 March 2009 02:00 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Curses! Browser crapped-out when I submitted my response - apols if this info appears twice...
I reckon this is a case of Bind Variable Peeking.
The optimizer peeks once at the bind variable when you FIRST run the query and optimises accordingly. Then every subsequent run uses the same plan regardless of the bind variable value.
If you don't use the SQL for a while, the plan is aged out of the shared pool and is re-parsed on the next execution. If the next execution uses a different bind variable value, you might get a different plan. The different plan may be good for THAT ONE value, but terrible for every other value. Regardless, every subsequent run will use the same bad plan.
Use Plan Stability / Outlines to lock in a preferred plan.
Or use hints to force a preferred plan.
Or upgrade to 11g which will peek on every execution.
Ross Leishman
|
|
|
|
| Re: Performance degrades suddenly. [message #395271 is a reply to message #394867] |
Wed, 01 April 2009 00:12 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Hi Appreciate your suggestions!!
But I have compared the execution plans during good performance and bad performance, in both the times execution plan is same.
Should I still go for the plan stability, if please suggest how can I go for it.
Below are the execution plans for your reference:-
Execution PLan during Good performance
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1320 | 8382 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID | USERS | 1 | 12 | 2 (0)|
| 2 | INDEX UNIQUE SCAN | USERS_PK | 1 | | 1 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | MSC | 1 | 15 | 1 (0)|
| 4 | INDEX UNIQUE SCAN | SYS_CMSC3759 | 1 | | 0 (0)|
| 5 | SORT ORDER BY | | 5 | 1320 | 8382 (1)|
| 6 | NESTED LOOPS OUTER | | 5 | 1320 | 8381 (1)|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 7 | NESTED LOOPS OUTER | | 5 | 1185 | 8376 (1)|
| 8 | VIEW | | 5 | 1045 | 8371 (1)|
| 9 | NESTED LOOPS OUTER | | 5 | 455 | 8371 (1)|
| 10 | TABLE ACCESS FULL | PREP_COMP_MASTER | 5 | 375 | 8366 (1)|
| 11 | TABLE ACCESS BY INDEX ROWID| CCCATEGORY | 1 | 16 | 1 (0)|
| 12 | INDEX UNIQUE SCAN | PK_CCCATEGORY | 1 | | 0 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID | MASTERS | 1 | 28 | 1 (0)|
| 14 | INDEX UNIQUE SCAN | SNOPKMASTERS | 1 | | 0 (0)|
| 15 | TABLE ACCESS BY INDEX ROWID | CRM_SST_MASTER | 1 | 27 | 1 (0)|
| 16 | INDEX UNIQUE SCAN | PK_SST | 1 | | 0 (0)|
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Execution PLan during bad performance
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1320 | 8382 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID | USERS | 1 | 12 | 2 (0)|
| 2 | INDEX UNIQUE SCAN | USERS_PK | 1 | | 1 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | MSC | 1 | 15 | 1 (0)|
| 4 | INDEX UNIQUE SCAN | SYS_CMSC3759 | 1 | | 0 (0)|
| 5 | SORT ORDER BY | | 5 | 1320 | 8382 (1)|
| 6 | NESTED LOOPS OUTER | | 5 | 1320 | 8381 (1)|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 7 | NESTED LOOPS OUTER | | 5 | 1185 | 8376 (1)|
| 8 | VIEW | | 5 | 1045 | 8371 (1)|
| 9 | NESTED LOOPS OUTER | | 5 | 455 | 8371 (1)|
| 10 | TABLE ACCESS FULL | PREP_COMP_MASTER | 5 | 375 | 8366 (1)|
| 11 | TABLE ACCESS BY INDEX ROWID| CCCATEGORY | 1 | 16 | 1 (0)|
| 12 | INDEX UNIQUE SCAN | PK_CCCATEGORY | 1 | | 0 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID | MASTERS | 1 | 28 | 1 (0)|
| 14 | INDEX UNIQUE SCAN | SNOPKMASTERS | 1 | | 0 (0)|
| 15 | TABLE ACCESS BY INDEX ROWID | CRM_SST_MASTER | 1 | 27 | 1 (0)|
| 16 | INDEX UNIQUE SCAN | PK_SST | 1 | | 0 (0)|
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
26 rows selected.
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)|
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | |
----------------------------------------------------------------
Any suggestions will be highly appreciated.
Thanks
Maddy
|
|
|
|
| Re: Performance degrades suddenly. [message #395338 is a reply to message #394867] |
Wed, 01 April 2009 03:13 |
dingwei
Messages: 3
Registered: April 2009
|
Junior Member |
|
|
What's your actual plan for executing the SQL.
You can
set serverout off
alter session set statistics_level = ALL, then
execute the SQL
then
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
|
|
|
|
| Re: Performance degrades suddenly. [message #395393 is a reply to message #395338] |
Wed, 01 April 2009 05:12 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
How did you get those plans?
Did you get them out of V$SQLPLAN? Or did you get them from SQL*Plus?
If you got them from SQL*Plus, did you edit the SQL? For example, did you replace the bind variables with literal values?
If so, these are not the actual plans. You need to get the plan without changing the bind variables. You cannot do that with AUTOTRACE, you have to use the DBMS_XPLAN.DISPLAY_CURSOR package to get the real plan.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.