Home » RDBMS Server » Performance Tuning » Performance degrades suddenly. (10.2.0.4/Solaris 10)
Performance degrades suddenly. [message #394867] Mon, 30 March 2009 11:32 Go to next message
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 #394876 is a reply to message #394867] Mon, 30 March 2009 12:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>We are analyzing the tables and indexes used in queries using compute option on regular basis.
You are supposed to use DBMS_STATS.
Are you?
Post the exact command you are using.

What kind of indexes on what columns?
>>a.mobile_no = '96560000'
Is this a string?
Re: Performance degrades suddenly. [message #394886 is a reply to message #394867] Mon, 30 March 2009 13:18 Go to previous messageGo to next message
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 #394887 is a reply to message #394867] Mon, 30 March 2009 13:21 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Also,

Index type is BTree index and composite index.
Re: Performance degrades suddenly. [message #394889 is a reply to message #394886] Mon, 30 March 2009 13:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Analyze table prep_comp_master compute statistics;
That will collect stats for tables. Not for indexes.
You are in 10g.
You are supposed to use DBMS_STATS to collect stats and histograms on tables/indexes and partitions.

>>And datatype for mobile_no is number(10).
if that is true, then
why are you treating it like a string?
>>a.mobile_no = '96560000'
Re: Performance degrades suddenly. [message #394894 is a reply to message #394867] Mon, 30 March 2009 13:37 Go to previous messageGo to next message
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 #394900 is a reply to message #394894] Mon, 30 March 2009 14:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>why after flushing the buffer cache and shared pool queries work fine

In the older versions, the only way to flush the buffer cache is to bounce the database Smile.
By flushing the buffer cache, you are almost doing it, with respect to buffers. If you go this way,
You will have to do this all your lifetime.

By flushing shared pool, you are just temporarily consolidating memory, with a cost. For example, the database sequences will be flushed away ending up with gaps.
Most probably your application is using bad code and not using bind variables where it should be.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#sthref1372
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2053602
Re: Performance degrades suddenly. [message #395022 is a reply to message #394900] Tue, 31 March 2009 02:00 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: bad index choose when using rownum < 9 or less
Next Topic: contention on temprary tablespace
Goto Forum:
  


Current Time: Tue Dec 06 00:04:48 CST 2016

Total time taken to generate the page: 0.13427 seconds