Monthly Archives: July 2011

The Care and Feeding of Good, Skilled Employees

Posted on by
2

A blog post by Simon Cooper sent up a reminder of a subject that has been at the forefront of my mind for the last couple weeks and is a follow up to my blog post The Superman Conundrum.  I’d had lunch with a previous coworker from years ago about  less than stellar management behavior directed towards him as an employee and also spoke last week to another previous co-worker about challenges in her current workplace.  I’m pretty content with how I am treated at my company.  Even though the company does not comprise of techies, they at least attempt to understand the demands of the IT industry, giving folks off time during the day when appointments and personal issues arise, knowing that we often “pay-it-forward”  within a week or so in after-hours work that is common for most operations support roles.

payday loans lenders online

Simon’s post discusses what should be common sense in a business-   Give employees what they need to be as productive as possible, pick your battles and you will reap the benefit in return for the business.  You want loyal employees-  ones that come to work, look forward to coming to work, drive for new challenges with gusto and always give their best to the company that hired them.  Unfortunately, their morale and loyalty is often tested by management and management practices that have no common sense behind them.

The simple policy of treating your employees well and in return you will receive their best is a rarity in the work place.  Having this policy, with the understanding that if abused, then there was a mistake made in hiring them and then the company has the  opportunity to replace the individual should be in place everywhere when it comes to Information Technology.  Instead we commonly hear of companies mistreating skilled professionals and in return, spend ridiculous amounts of time repeatedly in the recruiting phase due to consistent turnover which is a huge waste of any company’s time and money.

The employees that a company such as this will retain are either:

1.  possessed of little self-esteem, which in turn shows itself in lack of initiative and personal growth, (doesn’t serve any business well in the long run.)

2. Aren’t shining examples of good employees anyway and are perfectly happy mistreating the company as they are mistreated, (also an additional burden to those that have stuck around with the low self-esteems…)

I think most techies will agree with me-  Information Technology is a peculiar industry.  Many of the folks that fall into the field have a broad range of skills, often quite intelligent and unique personality traits vs. other areas of business.  Since most companies prefer to promote from within the department that the group will be managed from,  this results in techies being turned into managers.  Where the problem of management promotion being based on control issues more often than true leadership skills, this is never more apparent than in IT.  Sorry folks, it’s just a major fact as it is a problem in business overall.  We say, “Our Manager is being unfair and effecting morale!” and someone is going to undeniably say, “It’s a job, you’re not supposed to like it, man up, (or woman up, depending on the case)!”

The deal is, most companies should be paying more attention to these types of complaints.  Often employees aren’t too keen to complain in fear of reprisal to begin with, let alone stating what the REAL problem is.  Keeping an employee happy and productive is actually quite easy and involves very little effort than simple respect.  It baffles the mind how often it is more important to a manager to “be right” than to do the right thing for the long haul.

Take the following scenarios:

Jennifer has worked a year for her company and has performed well in her manager’s eyes.  He relies on her often for last minute jobs and she has always come through.  She suddenly needs the afternoon off , her son is sick at school and has to pick him up. Jennifer’s boss asks her to take sick time to do so and follows up with an email asking her to correct the time in the system because it was short what he feels was a 1/2 hr from what she really took off.  Jennifer has only five sick days a year and often performs after-hour support for the company, interrupting her personal life to address issues, losing time with her kids and spouse to address them.  There is no “comp time” that she is given for this work-  it is just expected from employees at her company.

John has been employed for years by the same company and is in good standing with his current manager.  John lost his son in a car accident three months ago.  His family has had a very difficult time with the loss and John has a large cache of vacation time that he is pestered by HR to take.  When his wife has an extra difficult day due to the loss of their son, he has requested to take a vacation day.  John now has an interim manager who refuses to OK the vacation day and chooses to leave  John unpaid for those days.  John is the sole provider for his family and this is causing even more stress in his personal life.  John also performs after hour support and rarely is offered any type of comp time for this time away from his family.

These above scenarios were both opportunities to build employee loyalty but poor management choices, driven by control issues have ended up costing employee loyalty, morale and in the end, company time and money.

The company has taken the time to hire professionals-  treat them as you would wish to be treated if you are their managers.   Make them realize the value they have to the company as they ARE what comprises the company.  I have seen this wonderful pay-it-forward attitude in action when I worked for the Parent Company, formally known as eToys, Inc.  They took four months to hire me and once there, I walked around and was impressed to realize the quality of the folks in every department.  I give high kudos to what a great manager Steve Ridley, the director of technical operations, along with the Manager of the DataWarehouse group, Greg Sitzman and our CTO, Chris Cummings all were.  Steve, as my direct manager, always challenged me, treated me with respect and if I had an emergency come up or needed an afternoon off, he simply stated that my family must come first and he meant it.  I came in everyday wanting to work there, do my best and you better believe I put in any after hours support they needed from me.   I made sure I was always available and had a sense of pride for the company that I belonged to.  This was a direct result of feeling valued and important to the company for the part I played.  They understood that employee loyalty was earned by treating everyone with respect, no matter what part you played on the company ladder.

“Jennifer” and “John’s” scenarios above could easily have been turned into a win for everyone.  If “Jennifer’s” manager had supported her situation, stating, “I always appreciate everything you do for us and the after-hours support you put in, please, go take care of your family and I will see you tomorrow….”  how much time just in emails, time entry and frustration could have been saved to the company?  If “John’s” interim manager, no matter what his personal management style was, had put himself in “John’s” shoes for just a moment and said, “John, I know you’ve had a rough time the past couple months and I know you already have an agreement in place with your previous manager.  Please, go tend to your family’s needs…”  how much could he have earned John’s loyalty if he was interested in making that interim position permanent and in making John a more productive employee the next day when he was back after taking care of his family?

I hear time and time again from different companies and even through recruiters how exhausted certain companies are of finding good people, but I also hear from impressive candidates looking for employment stating that they won’t even phone interview with certain companies due to this type of behavior.  Before any company starts complaining about how lacking the hiring pool is, it might be a good idea to look internally and find out if you are losing perfectly good people due to the lack of care and feeding that should be common sense so that a company is able to retain those valuable resources once originally hired.

 

 

You Know You’re Doing Too Much Database Work When…

Posted on by
Reply

This is when I know I’ve been doing too much database work in one day.  I awoke from a dream, quite early in the morning and was replaying it in my head.  The scenario went like this:

Scene:  City streets, obviously a murder scene with yellow tape drawn and bystanders watching off to the sides as the main characters, two seasoned detectives are discussing the decision to enter the crime scene.

Officer #1:  “Are we sure we covered our bases?  Is there anyway the criminal could have escaped the scene or we could have compromised the evidence when we entered the crime scene?”

Officer #2:  “Nope, positive we are in the clear-  I ran an ASH report before we did anything!”

Sigh… I need a vacation… :)

Addressing a Deadlock

Posted on by
Reply

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.