Home » RDBMS Server » Performance Tuning » Database crash Due To CPU Starvation (11.2.0.3 Version Oracle.)
Database crash Due To CPU Starvation [message #594669] Sun, 01 September 2013 05:46 Go to next message
VIP2013
Messages: 84
Registered: June 2013
Member
hi, i am using 11.2.0.3 version of oracle. We have recently migrated to 11g, after 1 month of smooth and comparatively better performance, we are suddenly facing performance issues with our database and it got crashed twice within 5 days. even we didnt push any new code to our database in recent past, atleast after the 11G migration. And after getting feedback from the ORACLE corporation guys , they pointed out about the default database stats gathering job, which was eating most of the CPU, because of the default degree mentioned So it was running in 160 parallel threads causing resource starvation.so we reduce the degree of the stats gathering job to 8 .

But the database crashed again two days back, and rebooted within 3 mins to back to normal, even after this default degree changed to 8. And i am observing around some specific time its happening,dont know if its just a coincidence!!! So if you can provide some help , how to dig down to the base , if this is happening due to any specific application related sql or anything else.
Re: Database crash Due To CPU Starvation [message #594670 is a reply to message #594669] Sun, 01 September 2013 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does AWR/Statspack say?

Regards
Michel
Re: Database crash Due To CPU Starvation [message #594674 is a reply to message #594670] Sun, 01 September 2013 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
real crash generates crash dump file that needs to be analyzed by Oracle Support.
The last time I saw a complete Oracle crash was while running V7.3 Oracle.
What is Operating System name & Version?

post excerpt from alert_SID including an example of the crash event
Re: Database crash Due To CPU Starvation [message #594684 is a reply to message #594669] Sun, 01 September 2013 13:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2346
Registered: May 2013
Location: World Wide on the Web
Senior Member
VIP2013 wrote on Sun, 01 September 2013 16:16
And i am observing around some specific time its happening,dont know if its just a coincidence!!!


Nothing in Oracle could be a coincidence. It has to have a reason behind anything. When you have observed that it crashes at a particular time of the day, then you would have the information logged in the crash dump file. But it is important to know the OS as already pointed out by Blackswan. Even if you reach out to Oracle support, they would ask you to provide all such information. You need to know the crash dump configuration to dig into the information hidden int the crash dump file.
Re: Database crash Due To CPU Starvation [message #594871 is a reply to message #594684] Tue, 03 September 2013 12:24 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Actually we have got below sql reported by Oracle corporation guys, which was consuming much the database resources and cause of resource starvation.

I have Changed some of the table names/aliases. Now the issue is that, this query is taking ~14hrs for complete execution resulting into 1027 records for company-1.
So then i tried executing the query for a different company - 2, it resulted into 3 records and completed within few seconds. Then i tried forcing the same plan with company-1, and it completes within ~20 minutes. So ideally the optimizer should follow the optimal path for company-1 too, but its not doing somehow due to some reason. Less optimal plan ,using index id2 for scanning table IV rather Id1. i want to identify the reason. given below is the query + test details. Here xmvl is the views. having below inline query. Id1 is on column (companypk,c2,inorg,dt1) of IV. and Id2 is on column (payeeid,companypk).


--Query for inline view xmvl
SELECT *
     FROM XMVL_B XB, CP CC
    WHERE XB.bcmpnypk = CC.pcmpnypk
          OR XB.bcmpnypk = CC.ccmpnypk;
		  
Below is the stats for both the indexes: Id1 and Id2:

leaf_blocks, blevel,   distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor,num_rows
445090	      3	       4018676	            1	                 18	                    42811230	      83166160
869410	      4	       141335	            24	                 1239	                43912510	       82126740	

Column Companypk of IV is having frequency histogram on it.

-- Below is the sql query causing issue 		  
		  
SELECT 
       IV.pk,
         IV.PN pid,
         IV.IPK,
         IV.INUM,
         IV.IDT,
         IV.VDNM,
         IV.ivst1 ivstat,
         IV.ivgvl,
         IV.ivnpk ivnpk1,
         IV.intc,
         IV.sc
    FROM IV IV
   WHERE     IV.COMPANYPK = 1
         AND IV.intc NOT IN ('C')
         AND IV.INORG IN ('APP', 'IMG')
         AND IV.pk IS NULL
         AND IV.INUM LIKE '147%'
         AND IV.ivnpk IN
                ('J1',
                 'J2',
                 'J3',
                 'J4',
                 'J5',
                 'j6')
         AND EXISTS
                (SELECT 1
                   FROM xmvl xsl, pi pi
                  WHERE     xsl.suplpk = pi.paypk
                        AND xsl.supplstpk = IV.stpk
                        AND xsl.bcmpnypk = IV.companypk
                        AND pi.pid = IV.payeeid
                        AND pi.payn = IV.payeen)
ORDER BY IDT DESC;
	   
	   
1027 rows selected.

Elapsed: 14:25:26.44
SQL> set termout on
SQL>  spool plan.lst
SQL>  select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


Plan hash value: 3052094360

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |      1 |        |   1027 |00:06:12.65 |      17M|     13M|   2400 |       |       |          |         |
|   1 |  SORT ORDER BY                       |                        |      1 |      1 |   1027 |00:06:12.65 |      17M|     13M|   2400 |   178K|   178K|  158K (0)|         |
|   2 |   NESTED LOOPS                       |                        |      1 |        |   1027 |13:22:51.84 |      17M|     13M|   2400 |       |       |          |         |
|   3 |    NESTED LOOPS                      |                        |      1 |      1 |     40M|00:29:57.97 |    2323K|    657K|   2400 |       |       |          |         |
|   4 |     VIEW                             | VW_SQ_1                |      1 |   2516 |    214K|00:00:06.15 |     457K|   2400 |   2400 |       |       |          |         |
|   5 |      HASH UNIQUE                     |                        |      1 |   2516 |    214K|00:00:05.87 |     457K|   2400 |   2400 |    23M|  4152K| 3731K (1)|   20480 |
|   6 |       CONCATENATION                  |                        |      1 |        |    214K|00:00:02.53 |     457K|      0 |      0 |       |       |          |         |
|   7 |        NESTED LOOPS                  |                        |      1 |        |    214K|00:00:02.40 |     457K|      0 |      0 |       |       |          |         |
|   8 |         NESTED LOOPS                 |                        |      1 |   2434 |    214K|00:00:01.50 |     242K|      0 |      0 |       |       |          |         |
|   9 |          NESTED LOOPS                |                        |      1 |   2434 |    214K|00:00:00.55 |    3599 |      0 |      0 |       |       |          |         |
|  10 |           TABLE ACCESS BY INDEX ROWID| CP                     |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|* 11 |            INDEX UNIQUE SCAN         | CP_p1                  |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  12 |           TABLE ACCESS BY INDEX ROWID| XMVL_B                 |      1 |   2434 |    214K|00:00:00.47 |    3596 |      0 |      0 |       |       |          |         |
|* 13 |            INDEX RANGE SCAN          | ID_BCOMPNYPK           |      1 |   2434 |    214K|00:00:00.15 |     552 |      0 |      0 |       |       |          |         |
|* 14 |          INDEX UNIQUE SCAN           | PI_P1                  |    214K|      1 |    214K|00:00:00.72 |     239K|      0 |      0 |       |       |          |         |
|  15 |         TABLE ACCESS BY INDEX ROWID  | pi                     |    214K|      1 |    214K|00:00:00.68 |     214K|      0 |      0 |       |       |          |         |
|  16 |        NESTED LOOPS                  |                        |      1 |        |      0 |00:00:00.05 |     555 |      0 |      0 |       |       |          |         |
|  17 |         NESTED LOOPS                 |                        |      1 |     82 |      0 |00:00:00.05 |     555 |      0 |      0 |       |       |          |         |
|  18 |          NESTED LOOPS                |                        |      1 |     82 |      0 |00:00:00.05 |     555 |      0 |      0 |       |       |          |         |
|  19 |           TABLE ACCESS BY INDEX ROWID| CP                     |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|* 20 |            INDEX UNIQUE SCAN         | CP_p1                  |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  21 |           TABLE ACCESS BY INDEX ROWID| XMVL_B                 |      1 |     82 |      0 |00:00:00.05 |     552 |      0 |      0 |       |       |          |         |
|* 22 |            INDEX RANGE SCAN          | ID_BCOMPNYPK           |      1 |   2434 |      0 |00:00:00.05 |     552 |      0 |      0 |       |       |          |         |
|* 23 |          INDEX UNIQUE SCAN           | PI_P1                  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |
|  24 |         TABLE ACCESS BY INDEX ROWID  | pi                     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |
|* 25 |     INDEX RANGE SCAN                 | id2                    |    214K|      3 |     40M|00:29:24.10 |    1865K|    655K|      0 |       |       |          |         |
|* 26 |    TABLE ACCESS BY GLOBAL INDEX ROWID| IV                     |     40M|      1 |   1027 |13:54:31.57 |      15M|     12M|      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  11 - access("CC"."ccmpnypk"=1)
  13 - access("XB"."bcmpnypk"="CC"."ccmpnypk")
  14 - access("XB"."suplpk"="PI"."PAYPK")
  20 - access("CC"."ccmpnypk"=1)
  22 - access("XB"."bcmpnypk"="CC"."pcmpnypk")
       filter(LNNVL("XB"."bcmpnypk"="CC"."ccmpnypk"))
  23 - access("XB"."suplpk"="PI"."PAYPK")
  25 - access("ITEM_3"="IV"."payeeid" AND "ITEM_2"="IV"."COMPANYPK")
       filter("IV"."COMPANYPK"=1)
  26 - filter(("IV"."pk" IS NULL AND "IV"."INUM" LIKE '147%' AND INTERNAL_FUNCTION("IV"."INORG") AND
              INTERNAL_FUNCTION("IV"."ivnpk") AND "IV"."intc"<>'C' AND "ITEM_1"="IV"."stpk" AND "ITEM_4"="IV"."payeen"))


68 rows selected.
	   

--- Below is the plan for a different company - companypk -- 2	   
	   
	   
Execution Plan
----------------------------------------------------------
Plan hash value: 1295920100

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |     1 |   182 |     6  (17)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                       |                        |     1 |   182 |     6  (17)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS SEMI                  |                        |     1 |   182 |     5   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| IV                     |     1 |   174 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                 | id1                    |     2 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    VIEW PUSHED PREDICATE             | VW_SQ_1                |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   6 |     NESTED LOOPS                     |                        |     1 |   107 |     3   (0)| 00:00:01 |       |       |
|   7 |      NESTED LOOPS                    |                        |     1 |    84 |     2   (0)| 00:00:01 |       |       |
|   8 |       TABLE ACCESS BY INDEX ROWID    | pi                     |     1 |    68 |     1   (0)| 00:00:01 |       |       |
|*  9 |        INDEX UNIQUE SCAN             | PI_UQ                  |     1 |       |     1   (0)| 00:00:01 |       |       |
|  10 |       TABLE ACCESS BY INDEX ROWID    | CP                     |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|* 11 |        INDEX UNIQUE SCAN             | CP_p1                  |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 12 |      INDEX RANGE SCAN                | IDX_SPLLLIST           |     1 |    23 |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("IV"."pk" IS NULL AND "IV"."INUM" LIKE '147%' AND
              ("IV"."ivnpk"='j6' OR "IV"."ivnpk"='J2' OR
              "IV"."ivnpk"='J1' OR "IV"."ivnpk"='J5' OR
              "IV"."ivnpk"='J3' OR "IV"."ivnpk"='J4') AND
              "IV"."intc"<>'C')
   4 - access("IV"."COMPANYPK"=2)
       filter("IV"."INORG"='APP' OR "IV"."INORG"='IMG')
   9 - access("PI"."pid"="IV"."payeeid" AND "PI"."payn"="IV"."payeen")
  11 - access("CC"."ccmpnypk"="IV"."COMPANYPK")
  12 - access("XB"."suplpk"="PI"."PAYPK" AND "XB"."supplstpk"="IV"."stpk")
       filter("XB"."bcmpnypk"="CC"."pcmpnypk" OR "XB"."bcmpnypk"="CC"."ccmpnypk")




-- Then i force the plan outline for company-1 , and got the below result.

SELECT      /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 30)
      OPT_PARAM('optimizer_index_caching' 90)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$38CB8147")
      PUSH_PRED(@"SEL$629EDCDD" "VW_SQ_1"@"SEL$F5A55792" 10 9 8 7)
      OUTLINE_LEAF(@"SEL$629EDCDD")
      UNNEST(@"SEL$335DD26A")
      OUTLINE(@"SEL$833EDA65")
      OUTLINE(@"SEL$629EDCDD")
      UNNEST(@"SEL$335DD26A")
      OUTLINE(@"SEL$F5A55792")
      OUTLINE(@"SEL$335DD26A")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      INDEX_RS_ASC(@"SEL$629EDCDD" "IV"@"SEL$1" ("IV"."COMPANYPK" "IV"."INVOICESOURCE" 
              "IV"."INORG" "IV"."AUDIT_CREATE_DATE"))
      NO_ACCESS(@"SEL$629EDCDD" "VW_SQ_1"@"SEL$F5A55792")
      LEADING(@"SEL$629EDCDD" "IV"@"SEL$1" "VW_SQ_1"@"SEL$F5A55792")
      USE_NL(@"SEL$629EDCDD" "VW_SQ_1"@"SEL$F5A55792")
      INDEX_RS_ASC(@"SEL$38CB8147" "PI"@"SEL$2" ("pi"."pid" "pi"."payn"))
      INDEX_RS_ASC(@"SEL$38CB8147" "CC"@"SEL$3" ("CP"."ccmpnypk"))
      INDEX(@"SEL$38CB8147" "XB"@"SEL$3" ("XMVL_B"."suplpk" "XMVL_B"."supplstpk" 
              "XMVL_B"."bcmpnypk"))
      LEADING(@"SEL$38CB8147" "PI"@"SEL$2" "CC"@"SEL$3" "XB"@"SEL$3")
      USE_NL(@"SEL$38CB8147" "CC"@"SEL$3")
      USE_NL(@"SEL$38CB8147" "XB"@"SEL$3")
      END_OUTLINE_DATA
  */      /* xign.buyerwebinvclient.search.InvoiceCloneSearchData */
        IV.pk,
         IV.PN pid,
         IV.IPK,
         IV.INUM,
         IV.IDT,
         IV.VDNM,
         IV.ivst1 ivstat,
         IV.ivgvl,
         IV.ivnpk ivnpk1,
         IV.intc,
         IV.sc
    FROM IV IV
   WHERE     IV.COMPANYPK = 1
         AND IV.intc NOT IN ('C')
         AND IV.INORG IN ('APP', 'IMG')
         AND IV.pk IS NULL
         AND IV.INUM LIKE '147%'
         AND IV.ivnpk IN
                ('J1',
                 'J2',
                 'J3',
                 'J4',
                 'J5',
                 'j6')
         AND EXISTS
                (SELECT 1
                   FROM xmvl xsl, pi pi
                  WHERE     xsl.suplpk = pi.paypk
                        AND xsl.supplstpk = IV.stpk
                        AND xsl.bcmpnypk = IV.companypk
                        AND pi.pid = IV.payeeid
                        AND pi.payn = IV.payeen)
ORDER BY IDT DESC;


1031 rows selected.

Elapsed: 00:19:14.60

Plan hash value: 1295920100

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |      1 |        |   1031 |00:13:31.14 |    1132K|    628K|       |       |          |
|   1 |  SORT ORDER BY                       |                        |      1 |      1 |   1031 |00:13:31.14 |    1132K|    628K|   160K|   160K|  142K (0)|
|   2 |   NESTED LOOPS SEMI                  |                        |      1 |      1 |   1031 |00:13:31.13 |    1132K|    628K|       |       |          |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| IV                     |      1 |     29 |   1031 |00:13:26.26 |    1127K|    627K|       |       |          |
|*  4 |     INDEX RANGE SCAN                 | id1                    |      1 |     15M|    938K|00:02:01.25 |     208K|    205K|       |       |          |
|   5 |    VIEW PUSHED PREDICATE             | VW_SQ_1                |   1031 |      1 |   1031 |00:00:04.87 |    5748 |    881 |       |       |          |
|   6 |     NESTED LOOPS                     |                        |   1031 |      1 |   1031 |00:00:04.86 |    5748 |    881 |       |       |          |
|   7 |      NESTED LOOPS                    |                        |   1031 |      1 |   1031 |00:00:03.71 |    3918 |    583 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID    | pi                     |   1031 |      1 |   1031 |00:00:03.70 |    2878 |    583 |       |       |          |
|*  9 |        INDEX UNIQUE SCAN             | PI_UQ                  |   1031 |      1 |   1031 |00:00:01.70 |    1847 |    326 |       |       |          |
|  10 |       TABLE ACCESS BY INDEX ROWID    | CP                     |   1031 |      1 |   1031 |00:00:00.01 |    1040 |      0 |       |       |          |
|* 11 |        INDEX UNIQUE SCAN             | CP_p1                  |   1031 |      1 |   1031 |00:00:00.01 |       9 |      0 |       |       |          |
|* 12 |      INDEX RANGE SCAN                | IDX_SPLLLIST           |   1031 |      1 |   1031 |00:00:01.15 |    1830 |    298 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("IV"."pk" IS NULL AND "IV"."INUM" LIKE '147%' AND INTERNAL_FUNCTION("IV"."ivnpk") AND
              "IV"."intc"<>'C'))
   4 - access("IV"."COMPANYPK"=1)
       filter(("IV"."INORG"='APP' OR "IV"."INORG"='IMG'))
   9 - access("PI"."pid"="IV"."payeeid" AND "PI"."payn"="IV"."payeen")
  11 - access("CC"."ccmpnypk"="IV"."COMPANYPK")
  12 - access("XB"."suplpk"="PI"."PAYPK" AND "XB"."supplstpk"="IV"."stpk")
       filter(("XB"."bcmpnypk"="CC"."pcmpnypk" OR "XB"."bcmpnypk"="CC"."ccmpnypk"))


51 rows selected.

Elapsed: 00:00:01.58
SQL>  spool off
SQL>
	   


Re: Database crash Due To CPU Starvation [message #594872 is a reply to message #594871] Tue, 03 September 2013 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
OK, so now what you expect/desire by posting these details here?
Re: Database crash Due To CPU Starvation [message #594874 is a reply to message #594872] Tue, 03 September 2013 12:47 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Few things want to understand...
what is the exact stat which drives the optimizer for picking up the less optimal plan or index id2 for querying iv.
if any wrong cardinality estimation due to wrong stats or any missing histogram etc..
Re: Database crash Due To CPU Starvation [message #594875 is a reply to message #594874] Tue, 03 September 2013 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
TRACE event 10053 will show what/how/why the CBO does

does Bind Variable Peeking impact your results?
Re: Database crash Due To CPU Starvation [message #594876 is a reply to message #594871] Tue, 03 September 2013 13:36 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
The cardinality estimate are bad, look at these examples:
* 13 |            INDEX RANGE SCAN          | ID_BCOMPNYPK           |      1 |   2434 |    214K|

* 25 |     INDEX RANGE SCAN                 | id2                    |    214K|      3 |     40M|


So I would start by gathering statistics on the tables and indexes, 100% sample.
Re: Database crash Due To CPU Starvation [message #594999 is a reply to message #594876] Wed, 04 September 2013 14:04 Go to previous message
VIP2013
Messages: 84
Registered: June 2013
Member
yes. I have updated the DBAs to gather stats(100%) on tables IV and XMVL_B and indexes ID_BCOMPNYPK,ID1,ID2.Will verify the plan after the stats gets updated. Will ask for TRACE event 10053 files.

Just thinking, about the course of action, if even after updating the stats, the plan remains same. Forcing index hint, but it might impact performance for other companies asinput.
Previous Topic: Parallel hints with cursor queries
Next Topic: Call from specified Machine not assigned to Database resource consumer.
Goto Forum:
  


Current Time: Sat Sep 20 18:00:53 CDT 2014

Total time taken to generate the page: 0.08753 seconds