Oracle

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…

  1. 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…)
  2. Take an AWR for the snapshot in time of the deadlock.
  3. Take an AWR sql_id specific report for each of the top queries involving the object that also was involved in the deadlock query.
  4. Remove the “weight” from the objects that are causing the waits in the statements involved in the deadlock snapshot scenario.
  5. 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_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_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_ROWS
      OUTLINE_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.