Addressing a Deadlock
I’ve always had a *thing* for trace files. They serve me well- I like them and they seem to really like me. I have a special affinity for deadlock trace files and someone needs to remind me to pester Cary Millsap about a tool to decipher them so I have even more data to go through…:) Even without any tools, I can easily pull out what is important and work from this data to run queries and reports to drill down to find work-arounds when changes to logic or design are not an option.
Deadlocks, although quite unpleasant, I find occur often in mature environments where the code, logic or design may have become complicated over time. Often it’s not anything that was intentional, but simply due to the natural complexity that can occur in database logic and as objects become, what I like to refer to as “heavy”.
An object, when receiving high traffic , both due to concurrent reporting and/or transactional, can become, what I like to refer to as “heavy” or “weighted”. As a DBA, it’s my job to find ways to lighten the object, by logical or physical tuning, often both. As deadlocks occur when more than one session is waiting on data that is locked by the other and often due to high complexity logic holding locks consistently for long periods of time, (or longer than the logic can tolerate…) I find I can often help eliminate deadlocking by eliminating anything that causes those longer lock times.
One such issue arose this week, was not available for a code rewrite, but I was able to offer some physical tuning. What the trace files and AWR reports below offer is that one of the major pieces of the complex puzzle wasn’t part of the original deadlock query.
__________________________________________________________________________________________________________________________
So here is the process and the fix…
- Step through the deadlock trace file and document the logic “tangle” and waits that lead to the deadlock. If the waits do not correspond to the statement review, then go onto the steps below, (which did occur…)
- Take an AWR for the snapshot in time of the deadlock.
- Take an AWR sql_id specific report for each of the top queries involving the object that also was involved in the deadlock query.
- Remove the “weight” from the objects that are causing the waits in the statements involved in the deadlock snapshot scenario.
- Inspect the improvements in the explain plans, the execution times and monitor for an occurrence of any new deadlocking.
TRACE FILE Example:
Tue Jul 5 09:01:07 2011
ORA-00060: Deadlock detected. More info in file
/u01/app/oracle/admin/xxxx/udump/xxxx_ora_20960.trc.
The first thing anyone will notice who reads a deadlock trace file is the following statement, (or should notice!):
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL.
This is Oracle telling you that it is not responsible for this error and you should automatically assume that something is wrong with the code, design, logic or all three. Now there are some bugs involved that will create a deadlock, but please do not go to Oracle and try to convince them you have one of these bugs unless you have clear proof. It will waste your time and only annoy Oracle Support… 🙂
The trace file will tell you about the deadlocked session and the other waiting session.
Information on the OTHER waiting sessions:
Session 779:
pid=83 serial=41199 audsid=117763439 user: 49/DW_USR
O/S info: user: dw_usr, term: unknown, ospid: 1234, machine: linuxora01
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
DELETE FROM MFG_TBL
WHERE mfg_id=:1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
DELETE FROM MFG_TBL
WHERE mfg_id=:1
The trace file does not stop here- It will take you through the datafiles, objects involved and then take you through the logic in the session that were involved in the deadlock scenario. This allows you to see what occurred in the database, along with the timing and the wait time. This is incredibly valuable to a DBA to see how the logic is flowing, (or not flowing!) and how a code change would benefit and in the case where code changes are not an option, where removing “weight” may help significantly.
Pay attention to the bind variable values that are presented to you in these trace files. They can assist you in finding where bind peek issues may have caused a deadlock or when an explain plan is off and statistics are the cause of your troubles.
Doing a search for “sqltxt” and “Peeked Binds”, you will step through the trace file and see the statement s trapped and the bind variables “peeked” for each.
Peeked Binds
============
Bind variable information
position=1
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=140344 <--This is my value for :1
The plan table for the statement that was utilized for the execution is shown in the trace, too:
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | DELETE STATEMENT | | | | 1 | |
| 1 | DELETE | MFG_TBL | | | | |
| 2 | INDEX UNIQUE SCAN | MFG_PK | 1 | 12 | 1 | |
------------------------------------------------+-----------------------------------+
Was an outline or profile used for the statement? Yes, this is displayed as well..
Outline Data:
/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('10.2.0.4')OPT_PARAM('_optimizer_cost_model' 'io')OPT_PARAM('optimizer_dynamic_sampling' 1)OPT_PARAM('optimizer_index_cost_adj' 10)OPT_PARAM('optimizer_index_caching' 80)ALL_ROWSOUTLINE_LEAF(@"DEL$1")INDEX(@"DEL$1" "MFG_TBL"@"DEL$1" ("MFG_TBL"."MFG_ID"))END_OUTLINE_DATA*/
The query that due to concurrency added “weight” to my deletes that ended up deadlocking on the MFG_TBL was actually a distinct select with joins to a number of tables during that hour, which sourced to another table in the distinct query, we will refer to in this article as FF_TBL, (I’m not telling you what I think FF stands for…J)
Along with the deadlock trace file, I utilized both AWR reports and , (awrsqrpt.sql) to drill down and gather more details about individual sql_id’s from the snapshot period.
__________________________________________________________________________________________________________________________
The AWR report from the time in question shows numerous queries with massive joins involving the table in question:
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
272 222 55 5.0 2.8 55dnjt1894pw0
Module: JDBC Thin Client
SELECT DISTINCT mf.f_id, mf.l_dir, mf.f_name, st.stg_ type_cd,
pe.pr_stp_sts_cd, mfo.ll_dir AS odir, mfo.f_name AS oname, peo.pr_
stp_sts_cd AS ostat FROM MF_TBL mf JOIN STG_TBL st ON mf.b_rec_cd
= st.b_rec_cd AND st.cla_flag = 'Y' JOIN FF_TBL f
152 152 109 1.4 1.6 bc88sd1b2v10q
Module: oracle@server3 (S003)
SELECT DISTINCT NVL("A3"."M_ID",NVL("A5"."M_ID","A1"."M_ID")) FRO
M "MF_TBL" "A5","PRC_TBL" "A4","MF_TBL" "A3","MFG_TBL
" "A2","MF_TBL2" "A1" WHERE NVL("A4"."MFG_ID","A5"."M
FG_ID") IS NOT NULL AND "A1"."MF_ID"(+)="A2"."MF_ID
151 150 7 21.5 1.5 4vqa0hz2qgmr7
Module: oracle@server3 (S011)
SELECT "A1"."RCVD","A1"."MR_TBL","A1"."MIP_TBL","A1"."MA_TBL
","A1"."NC_TBL","A1"."NNC_TBL","A1"."WM_TBL","A1"."CF_TBL
","A1"."WM_TBL","A1"."DAR_TBL","A1"."WH_U_R","A1"."RC
VDS",COUNT(*) OVER () FROM (SELECT "A5"."RCVD" "RCVD",
__________________________________________________________________________________________________________________________
My First concern was the select distinct queries, which one output from below for a larger snapshot in time shows how poorly the first one had been performing.
| Stat Name | Statement | Per Execution | % Snap |
| Elapsed Time (ms) | 924,866 | 2,306.40 | 11.1 |
| CPU Time (ms) | 873,535 | 2,178.40 | 11.3 |
| Executions | 401 | N/A | N/A |
| Buffer Gets | ########## | 752,796.80 | 18.9 |
| Disk Reads | 24 | 0.1 | 8 |
| Parse Calls | 401 | 1 | 8 |
| Rows | 356 | 0.9 | N/A |
| User I/O Wait Time (ms) | 124 | N/A | N/A |
| Cluster Wait Time (ms) | 0 | N/A | N/A |
| Application Wait Time (ms) | 0 | N/A | N/A |
| Concurrency Wait Time (ms) | 64,100 | N/A | N/A |
| Invalidations | 0 | N/A | N/A |
| Version Count | 160 | N/A | N/A |
| Sharable Mem(KB) | 12,611 | N/A | N/A |
Global Parameter that may be affecting the deadlock scenario:
PARAMETER VALUE
_optimizer_cost_model IO
Session parameter changes that may have affected the performance(seen in the outline..):
PARAMETER VALUE
Dynamic_sampling 1
Optimizer_index_cost_adj 1
Optimizer_index_caching 80
Due to hardware options, the parameter, _optimizer_cost_model change offered a huge performance boost, allocating the database with huge I/O constraints to always prefer costs that favored less I/O hits than CPU when disk I/O was costly to the database.
Over time, investments have been made in hardware improvements that now make it pertinent for the DBA team to investigate if this parameter setting is still a good idea. During this exercise, it is valuable to look into it as well.
SQL> explain plan for
SELECT DISTINCT mf.f_id, mf.l_dir, mf.f_name, st.stg_type_cd, pe.pr_stp_cd,
mfo.l_dir AS odir, mfo.f_name AS oname, peo.pr_stp_cd AS ostat FROM MR_TBL mf
JOIN ST_TBL st ON mf.b_rec _cd = st.b_rec_cd AND st.ca_flag = 'Y'
JOIN FF_TBL ff ON ff.fr_type_id = mf.fr_type_id
JOIN PE_TBL pe ON pe.f_id = mf.pf_id
AND (pe.pr_stp_name = ‘<req 1>’ OR pe.pr_stp_name = '<req2>')
LEFT JOIN MF_TBL mfo ON mfo.pf_id = mf.f_id
LEFT JOIN PE_TBL peo
ON peo.f_id = mf.f_id AND peo.pr_stp_name = '<req3>'
WHERE mf.f_id IN ( SELECT f_id FROM MF_TBL mf
WHERE MIF_id IS NULL START WITH f_id
IN ( SELECT mfgi.f_id FROM MFGI_TBL mfgi
WHERE mfgi.mfgi_id = :1)
CONNECT BY PRIOR mf.f_id = mf.pf_id);
Explained.
SQL> set linesize 240
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————
| Operation | Name | Rows | Bytes | Cost |
| SELECT STATEMENT | 1 | 242 | 26 | |
| HASH UNIQUE | 1 | 242 | 26 | |
| HASH JOIN | 1 | 242 | 13 | |
| HASH JOIN | 1 | 237 | 9 | |
| NESTED LOOPS OUTER | 1 | 227 | 6 | |
| NESTED LOOPS | 1 | 147 | 5 | |
| NESTED LOOPS OUTER | 4 | 492 | 4 | |
| NESTED LOOPS | 4 | 396 | 2 | |
| VIEW | VW_NSO_1 | 4 | 20 | 1 |
| FILTER | ||||
| CONNECT BY WITH FILTERING | ||||
| FILTER | ||||
| TABLE ACCESS FULL | MF_TBL | 4 | 48 | 1 |
| INDEX UNIQUE SCAN | MFGI_PK | 1 | 9 | 1 |
| NESTED LOOPS | ||||
| CONNECT BY PUMP | ||||
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 4 | 48 | 1 |
| INDEX RANGE SCAN | MF_TBL_IDX01 | 3 | 1 | |
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 1 | 94 | 1 |
| INDEX UNIQUE SCAN | MF_PK | 1 | 1 | |
| TABLE ACCESS BY INDEX ROWID | PE_TBL | 1 | 24 | 1 |
| INDEX RANGE SCAN | PE__IDX01 | 4 | 1 | |
| TABLE ACCESS BY INDEX ROWID | PE_TBL | 1 | 24 | 1 |
| INDEX RANGE SCAN | PE__IDX01 | 4 | 1 | |
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 1 | 80 | 1 |
| INDEX RANGE SCAN | MF_TBL_IDX01 | 4 | 1 | |
| TABLE ACCESS FULL | ST_TBL | 10 | 100 | 2 |
| TABLE ACCESS FULL | FF_TBL | 9304 | 46520 | 3 |
Plan hash value: 2540126887
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FF"."F_REC_ID"="MF"."F_REC_ID")
3 - access("MF"."BR_TYPE_CD"="ST"."BR_TYPE_CD")
9 - filter("MIF_ID" IS NULL)
10 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
11 - filter( EXISTS (SELECT 0 FROM "DW_USR"."MFG_TBL" "MFGI"
WHERE "MFGI"."F_ID"=:B1 AND "MFGI"."MFG_ID"=TO_NUMBER(:1)))
12 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
13 - access("MFGI"."MFG_ID"=TO_NUMBER(:1) AND "MFGI"."F_ID"=:B1)
17 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
18 - filter("MF"."PF_ID" IS NOT NULL AND "MF"."F_REC_ID" IS
NOT NULL AND "MF"."BR_TYPE_CD" IS NOT NULL)
19 - access("MF"."F_ID"="$nso_col_1")
20 - filter("PEO"."PS_NAME"(+)='<req2>')
21 - access("PEO"."F_ID"(+)="MF"."F_ID")
filter("PEO"."F_ID"(+) IS NOT NULL)
22 - filter("PE"."PS_NAME"='Concatenate' OR "PE"."PS_NAME"='File
Conversion')
23 - access("PE"."F_ID"="MF"."PF_ID")
filter("PE"."F_ID" IS NOT NULL)
25 - access("MFO"."PF_ID"(+)="MF"."F_ID")
filter("MFO"."PF_ID"(+) IS NOT NULL)
26 - filter("ST"."CA_FLAG"='Y')
Note
-----
- cpu costing is off (consider enabling it)
64 rows selected.
CREATE the stupid index for the quantity they run this silly thing for…
SQL> create index DW_USR.ff_idx1 on DW_USR.FF_TBL(F_REC_ID)
tablespace mf_idx;
Index created.
SQL> explain plan for
SELECT /*+ CPU_COSTING */ DISTINCT mf.f_id, mf.l_dir, mf.f_name,
st.stg_type_cd, pe.pr_stp_cd,
mfo.l_dir AS odir, mfo.f_name AS oname, peo.pr_stp_cd AS ostat FROM MR_TBL mf
JOIN ST_TBL st ON mf.b_rec _cd = st.b_rec_cd AND st.ca_flag = 'Y'
JOIN FF_TBL ff ON ff.fr_type_id = mf.fr_type_id
JOIN PE_TBL pe ON pe.f_id = mf.pf_id
AND (pe.pr_stp_name = ‘<req 1>’ OR pe.pr_stp_name = '<req2>')
LEFT JOIN MF_TBL mfo ON mfo.pf_id = mf.f_id
LEFT JOIN PE_TBL peo
ON peo.f_id = mf.f_id AND peo.pr_stp_name = '<req3>'
WHERE mf.f_id IN ( SELECT f_id FROM MF_TBL mf
WHERE MIF_id IS NULL START WITH f_id
IN ( SELECT mfgi.f_id FROM MFGI_TBL mfgi
WHERE mfgi.mfgi_id = :1)
CONNECT BY PRIOR mf.f_id = mf.pf_id);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————
| Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| SELECT STATEMENT | 1 | 242 | 39 (11) | 00:00:01 | |
| HASH UNIQUE | 1 | 242 | 39 (11) | 00:00:01 | |
| NESTED LOOPS OUTER | 1 | 242 | 38 (8) | 00:00:01 | |
| NESTED LOOPS OUTER | 1 | 162 | 37 (9) | 00:00:01 | |
| HASH JOIN | 1 | 138 | 36 (9) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | PE_TBL | 1 | 24 | 1 (0) | 00:00:01 |
| NESTED LOOPS | 1 | 133 | 9 (12) | 00:00:01 | |
| HASH JOIN | 2 | 218 | 8 (13) | 00:00:01 | |
| NESTED LOOPS | 4 | 396 | 2 (0) | 00:00:01 | |
| VIEW | VW_NSO_1 | 4 | 20 | 1 (0) | 00:00:01 |
| FILTER | |||||
| CONNECT BY WITH FILTERING | |||||
| FILTER | |||||
| TABLE ACCESS FULL | MF_TBL | 4 | 48 | 1 (0) | 00:00:01 |
| INDEX UNIQUE SCAN | MFGI_PK | 1 | 9 | 1 (0) | 00:00:01 |
| NESTED LOOPS | |||||
| CONNECT BY PUMP | |||||
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 4 | 48 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | MF_TBL_IDX01 | 3 | 1 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 1 | 94 | 1 (0) | 00:00:01 |
| INDEX UNIQUE SCAN | MF_PK | 1 | 1 (0) | 00:00:01 | |
| TABLE ACCESS FULL | ST_TBL | 10 | 100 | 5 (0) | 00:00:01 |
| INDEX RANGE SCAN | PE__IDX01 | 4 | 1 (0) | 00:00:01 | |
| TABLE ACCESS FULL | FF_TBL | 9304 | 46520 | 26 (4) | 00:00:01 |
| TABLE ACCESS BY INDEX ROWID | PE_TBL | 1 | 24 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | PE__IDX01 | 4 | 1 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 2 | 160 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | MF_TBL_IDX01 | 4 | 1 (0) | 00:00:01 |
Plan hash value: 2202465807
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("FF"."F_REC_ID"="MF"."F_REC_ID")
5 - filter("PE"."PS_NAME"='Concatenate' OR "PE"."PS_NAME"='<req1>')
7 - access("MF"."BR_TYPE_CD"="ST"."BR_TYPE_CD")
10 - filter("MIF_ID" IS NULL)
11 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
12 - filter( EXISTS (SELECT 0 FROM "DW_USR"."MFG_TBL" "MFGI" WHERE
"MFGI"."F_ID"=:B1 AND "MFGI"."MFG_ID"=TO_NUMBER(:1)))
13 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
14 - access("MFGI"."MFG_ID"=TO_NUMBER(:1) AND "MFGI"."F_ID"=:B1)
18 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
19 - filter("MF"."PF_ID" IS NOT NULL AND "MF"."F_REC_ID" IS NOT NULL AND
"MF"."BR_TYPE_CD" IS NOT NULL)
20 - access("MF"."F_ID"="$nso_col_1")
21 - filter("ST"."CA_FLAG"='Y')
22 - access("PE"."F_ID"="MF"."PF_ID")
filter("PE"."F_ID" IS NOT NULL)
24 - filter("PEO"."PS_NAME"(+)='<req2>')
25 - access("PEO"."F_ID"(+)="MF"."F_ID")
filter("PEO"."F_ID"(+) IS NOT NULL)
27 - access("MFO"."PF_ID"(+)="MF"."F_ID")
filter("MFO"."PF_ID"(+) IS NOT NULL)
59 rows selected.
COLUMN STATS are obviously off by the behavior here… just not for the join, but why won’t it look at the index?
| OWNER | TABLE_NAME | COLUMN_NAME | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | HISTOGRAM |
| DW_USR | FF_TBL | FF_ID | 75 | 7/3/2011 18:41 | 4961 | HEIGHT BALANCED |
| DW_USR | FF_TBL | F_REC_ID | 1 | 7/6/2011 11:33 | 4450 | NONE |
| DW_USR | FF_TBL | H_CNT | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | CET_CD | 3 | 9/29/2009 11:22 | 4450 | FREQUENCY |
| DW_USR | FF_TBL | F_SEP | 1 | 9/29/2009 11:22 | 4430 | NONE |
| DW_USR | FF_TBL | F_EXT | 8 | 9/29/2009 11:22 | 4308 | FREQUENCY |
| DW_USR | FF_TBL | FFS_CD | 2 | 9/29/2009 11:22 | 4450 | FREQUENCY |
| DW_USR | FF_TBL | LU_DT | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | LM_BY | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | LM_DT | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | RT_SEP | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | FNP | 1 | 9/29/2009 11:22 | 4423 | NONE |
| DW_USR | FF_TBL | ATM_ID | 254 | 9/29/2009 11:22 | 1075 | HEIGHT BALANCED |
| DW_USR | FF_TBL | FC_CD | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | FC_ARG | 1 | 9/29/2009 11:22 | 160 | NONE |
| DW_USR | FF_TBL | CN_FLAG | 1 | 9/29/2009 11:22 | 4450 | NONE |
| DW_USR | FF_TBL | APTP_FLAG | 1 | 9/29/2009 11:22 | 4450 | NONE |
Yeah, so only the index columns are being updated…not so good as this will start to make the index appear less attractive as the row count gets higher and none of the others do when working with more advance joins…
Fix the stats and all are updated.
EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=> 'DW_USR', tabname=>'FF_TBL', ESTIMATE_PERCENT=>10,
METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE,DEGREE=>6);
EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=> 'DW_USR', tabname=>'MF_TBL', ESTIMATE_PERCENT=>10,
METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE,DEGREE=>6);
EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname=> 'DW_USR', tabname=>'MFG_TBL', ESTIMATE_PERCENT=>10,
METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE,DEGREE=>6);
DB chose to drop the historgram on the FF_ID, too…now… **Note that the histograms that you see here are mostly shadows of their former selves…(so in my opinion is that they are doing more harm than good and I want to be rid of them…)
So how did we do?
SQL> explain plan for SELECT /*+ CPU_COSTING */ DISTINCT mf.f_id, mf.l_dir,
mf.f_name, st.stg_type_cd, pe.pr_stp_cd,
mfo.l_dir AS odir, mfo.f_name AS oname, peo.pr_stp_cd AS ostat FROM MR_TBL mf
JOIN ST_TBL st ON mf.b_rec _cd = st.b_rec_cd AND st.ca_flag = 'Y'
JOIN FF_TBL ff ON ff.fr_type_id = mf.fr_type_id
JOIN PE_TBL pe ON pe.f_id = mf.pf_id
AND (pe.pr_stp_name = ‘<req 1>’ OR pe.pr_stp_name = '<req2>')
LEFT JOIN MF_TBL mfo ON mfo.pf_id = mf.f_id
LEFT JOIN PE_TBL peo
ON peo.f_id = mf.f_id AND peo.pr_stp_name = '<req3>'
WHERE mf.f_id IN ( SELECT f_id FROM MF_TBL mf
WHERE MIF_id IS NULL START WITH f_id
IN ( SELECT mfgi.f_id FROM MFGI_TBL mfgi
WHERE mfgi.mfgi_id = :1)
CONNECT BY PRIOR mf.f_id = mf.pf_id);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| SELECT STATEMENT | 1 | 242 | 13 (16) | 00:00:01 | |
| HASH UNIQUE | 1 | 242 | 13 (16) | 00:00:01 | |
| NESTED LOOPS OUTER | 1 | 242 | 12 (9) | 00:00:01 | |
| NESTED LOOPS OUTER | 1 | 162 | 11 (10) | 00:00:01 | |
| NESTED LOOPS | 1 | 138 | 10 (10) | 00:00:01 | |
| NESTED LOOPS | 2 | 228 | 9 (12) | 00:00:01 | |
| HASH JOIN | 2 | 218 | 8 (13) | 00:00:01 | |
| NESTED LOOPS | 4 | 396 | 2 (0) | 00:00:01 | |
| VIEW | VW_NSO_1 | 4 | 20 | 1 (0) | 00:00:01 |
| FILTER | |||||
| CONNECT BY WITH FILTERING | |||||
| FILTER | |||||
| TABLE ACCESS FULL | MF_TBL | 4 | 48 | 1 (0) | 00:00:01 |
| INDEX UNIQUE SCAN | MFGI_PK | 1 | 9 | 1 (0) | 00:00:01 |
| NESTED LOOPS | |||||
| CONNECT BY PUMP | |||||
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 4 | 48 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | MF_TBL_IDX01 | 3 | 1 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 1 | 94 | 1 (0) | 00:00:01 |
| INDEX UNIQUE SCAN | MF_PK | 1 | 1 (0) | 00:00:01 | |
| TABLE ACCESS FULL | ST_TBL | 10 | 100 | 5 (0) | 00:00:01 |
| INDEX RANGE SCAN | FF_IDX1 | 1 | 5 | 1 (0) | 00:00:01 |
| TABLE ACCESS BY INDEX ROWID | PE_TBL | 1 | 24 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | PE__IDX01 | 4 | 1 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | PE_TBL | 1 | 24 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | PE__IDX01 | 4 | 1 (0) | 00:00:01 | |
| TABLE ACCESS BY INDEX ROWID | MF_TBL | 2 | 160 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | MF_TBL_IDX01 | 4 | 1 (0) | 00:00:01 |
Plan hash value: 1233783205
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("MF"."BR_TYPE_CD"="ST"."BR_TYPE_CD")
9 - filter("MIF_ID" IS NULL)
10 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
11 - filter( EXISTS (SELECT 0 FROM "DW_USR"."MFG_TBL" "MFGI" WHERE
"MFGI"."F_ID"=:B1 AND "MFGI"."MFG_ID"=TO_NUMBER(:1)))
12 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
13 - access("MFGI"."MFG_ID"=TO_NUMBER(:1) AND "MFGI"."F_ID"=:B1)
17 - access("MF"."PF_ID"=PRIOR "MF"."F_ID")
18 - filter("MF"."PF_ID" IS NOT NULL AND "MF"."F_REC_ID" IS NOT NULL AND
"MF"."BR_TYPE_CD" IS NOT NULL)
19 - access("MF"."F_ID"="$nso_col_1")
20 - filter("ST"."CA_FLAG"='Y')
21 - access("FF"."F_REC_ID"="MF"."F_REC_ID")
22 - filter("PE"."PS_NAME"='Concatenate' OR "PE"."PS_NAME"='<req1>')
23 - access("PE"."F_ID"="MF"."PF_ID")
filter("PE"."F_ID" IS NOT NULL)
24 - filter("PEO"."PS_NAME"(+)='<req2>')
25 - access("PEO"."F_ID"(+)="MF"."F_ID")
filter("PEO"."F_ID"(+) IS NOT NULL)
27 - access("MFO"."PF_ID"(+)="MF"."F_ID")
filter("MFO"."PF_ID"(+) IS NOT NULL)
59 rows selected.
Yes, 1 row is so much better than 9213…even with good stats… This will take a lot more “weight” off the MF_TBL we are seeing due to concurrency by this query when joining to FF_TBL, as well as the ones involving the MFG_TBL.
I will now follow up in the next couple weeks monitoring for any new deadlocks, as well as performance gains through execution times.