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 |
|
VIP2013
Messages: 91 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 #594684 is a reply to message #594669] |
Sun, 01 September 2013 13:48 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
VIP2013 wrote on Sun, 01 September 2013 16:16And 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 |
|
VIP2013
Messages: 91 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 #594876 is a reply to message #594871] |
Tue, 03 September 2013 13:36 |
John Watson
Messages: 8938 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 |
|
VIP2013
Messages: 91 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.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 23:26:38 CDT 2024
|