Home -> Mailing Lists -> Oracle-L -> FW: oracle-l: roon987_at_yahoo.co.uk post needs approval

FW: oracle-l: roon987_at_yahoo.co.uk post needs approval

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Tue, 6 Sep 2011 15:16:33 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F788F7508_at_AAPQMAILBX02V.proque.st> 







-----Original Message-----




From: FreeLists Mailing List Manager [mailto:ecartis_at_freelists.org]
Sent: Tuesday, September 06, 2011 9:37 AM
To: oracle-l-moderators_at_freelists.org
Subject: oracle-l: roon987_at_yahoo.co.uk post needs approval



This message was received for a list you are a moderator on, and was marked for moderation due to the following reason:
Message exceeded allowed body size.



To approve this message and have it go out on the list, forward this to oracle-l-repost_at_freelists.org



If you wish to decline the post, change the 'apppost' below to 'delpost'.
If you wish to edit the post, change it to 'modpost' and edit the message as needed - not all mail programs will work with modpost.



DO NOT DELETE THE FOLLOWING LINE.  Ecartis needs it.
// apppost 4E6621EC:2365.1:benpyry



From roon987_at_yahoo.co.uk  Tue Sep  6 09:36:44 2011
Return-Path: <roon987_at_yahoo.co.uk>


X-Original-To: oracle-l_at_freelists.org


Delivered-To: oracle-l_at_freelists.org
Received: from localhost (localhost [127.0.0.1])

        by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1CFA6E3F1D6
        for <oracle-l_at_freelists.org>; Tue,  6 Sep 2011 09:36:44 -0400 (EDT)


Authentication-Results: turing.freelists.org; dkim=pass (1024-bit key) header.i=_at_yahoo.co.uk
X-Virus-Scanned: Debian amavisd-new at localhost.localdomain
Received: from turing.freelists.org ([127.0.0.1])

        by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024)
        with ESMTP id EMr3ch9v8fR9 for <oracle-l_at_freelists.org>;
        Tue,  6 Sep 2011 09:36:43 -0400 (EDT)
Received: from nm15-vm0.bullet.mail.ukl.yahoo.com (nm15-vm0.bullet.mail.ukl.yahoo.com [217.146.183.252])
        by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id DF9F1E3F1B8
        for <oracle-l_at_freelists.org>; Tue,  6 Sep 2011 09:36:40 -0400 (EDT)
Received: from [217.146.183.217] by nm15.bullet.mail.ukl.yahoo.com with NNFMP; 06 Sep 2011 13:36:39 -0000


Received: from [217.146.183.63] by tm10.bullet.mail.ukl.yahoo.com with NNFMP; 06 Sep 2011 13:36:39 -0000
Received: from [127.0.0.1] by omp1032.mail.ukl.yahoo.com with NNFMP; 06 Sep 2011 13:36:39 -0000
X-Yahoo-Newman-Property: ymail-3


X-Yahoo-Newman-Id: 620850.52571.bm_at_omp1032.mail.ukl.yahoo.com
Received: (qmail 35494 invoked by uid 60001); 6 Sep 2011 13:36:39 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1315316199; bh=2rdP79wv0XN1RyE0jqvMg/leduSwzOsifJF8ZwcvWqQ=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=W+msfhcOjXloO0ixqwUZ6tD6iC6xqwDQxUO6jLe0wMmPcVNiOltvyatx7yxeANtm2BZ8qp+5LxMUtzSqrRrJrte8VtFu8pydqJL/Uq4f+sStywAICQQW6ggR1wHrkiu0tL8ZfAqB+nbMLiqZ2+cGeeAw/bR+8Ya5zBCdZIqKM5A=
DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws;

  s=s1024; d=yahoo.co.uk;
  h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
  b=f+SjIrDpYObW2beQ8cs9b+kZxWQHDH9ZKi39JXd4mZXtx0YMO5br8OyZftmwVUmW9V70Q8wqA617BiOZK9EtABQXMhds3bSqJ16Ep56QxvHD6DSeFKWW5x3hffHk/EmeDShbFhdCG7yqsHi+Z9KtrkKAl2fh6NWiOOJR8dbQYpI=;


X-YMail-OSG: UyDK96EVM1mA_vvQB5GL6JRjRQDFqff2fKC7Phr6OVByzZp
 OYzFqUESVvyFOspb3WSkea_EXiyoump2MSZI2pDDMWOLs9bDoFqUmrtS81J_
 orX9bbwgIcle8T9PUqQt07wOg6QBfMc2nd5DyVdLyGhpIQo4UH_0.MzdSE8A
 3X3fAtbAeGknrczLQnQULwS.WFZPTRu1AJ_Y8NYfS3ie.1u04cI4y4av2FGR
 8y8q9.t2buUhHypcogevOA.oPlWPyCrvHpa77qPY9k0Iq7.3dyv31ENVhPp1
 pVJ1.p_LIThRkMRpmMHBh9stTYBxtKfetkn4NTrh4xBsw_2UCdhBIHyMLwum
 AqtO9iZeYFbioa35zjcHnyoVW09E8zoP6zrmCn5DFb2up7LJ8hnq3lAfjxXk
 Lh34q712a.zpmPBfgnRbz01dXuHNbIApXlS2uavdxDgsCjX4xnyMfbM5nI3v
 6s8MTXzVzpC2CeEDDkGNdgROPu.zGqDG0caVrm7HJxTO2O38QmHbI472nwFs
 9


Received: from [194.221.40.145] by web25403.mail.ukl.yahoo.com via HTTP; Tue, 06 Sep 2011 14:36:39 BST
X-Mailer: YahooMailClassic/14.0.5 YahooMailWebService/0.8.113.315625
Message-ID: <1315316199.32570.YahooMailClassic_at_web25403.mail.ukl.yahoo.com>
Date: Tue, 6 Sep 2011 14:36:39 +0100 (BST)
From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Subject: RE: tuning sql queries....


To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
In-Reply-To: <C5533BD628A9524496D63801704AE56D6A323799CC_at_SPOBMEXC14.adprod.directory>
MIME-Version: 1.0


Content-type: text/plain


Content-Transfer-Encoding: 8bit



Thank you for those who have replied.....
Â


The 2 databases are running on 2 AIX servers...
Â


One of the envionments is PRE-PRODUCTION (IHR45R)Â and the other one is Functional Testing (IHR42T)...
Â


At a very high level, the 2 databases are at the same level of Oracle 11.2.0.1 (same patchsets etc; same level of OS (AIX 6.1 - same patchset), and also accessing the same SAN.
Am looking at the number of rows in each environment just to make sure that we have similar amount to data...
Â


Thanks


Â



    
  
  •   On Tue, 6/9/11, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com> wrote:







From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Subject: RE: tuning sql queries....


To: "'roon987_at_yahoo.co.uk'" <roon987_at_yahoo.co.uk>, "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
Date: Tuesday, 6 September, 2011, 14:08




What are the environments like?



Meaning: Is one production and another test? Which environment is experiencing the problem?  How are the systems different (at a high level)?



It may be that you just need to use dbms_stats to gather statistics on the tables & indexes used by the optimizer in the environment that is experiencing the slow performance.




Chris Taylor


Sr. Oracle DBA


Ingram Barge Company


Nashville, TN 37205


Office: 615-517-3355


Cell: 615-663-1673


Email: chris.taylor_at_ingrambarge.com



CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.




-----Original Message-----




From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zabair Ahmed
Sent: Tuesday, September 06, 2011 7:59 AM
To: oracle-l_at_freelists.org


Subject: tuning sql queries....



I've just been given some trace files, to examine as to why a piece of sql is taking a long time to run in one evironment and not taking so much time in another environment.



Both the databases are running 11.2.0.1 on AIX 6.1 - am also told that the databases are running on the same SAN.




First piece of SQL......





TKPROF: Release 11.2.0.1.0 - Development on Thu Sep 1 20:09:55 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Trace file: IHR45R_ora_25035006_APPS.trc Sort options: prsela  exeela  fchela




count    = number of times OCI procedure was executed cpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executing disk     = number of physical reads of buffers from disk query    = number of buffers gotten for consistent read current  = number of buffers gotten in current mode (usually for update) rows     = number of rows processed by the fetch or execute call




select per.EMPLOYEE_NUMBER,


           hou.NAME ORG_NAME,
           rpaa.assignment_action_id run_assignment_action_id,            per.FULL_NAME from hr_organization_units hou,        per_all_assignments_f asf,          per_people_f per,          pay_assignment_actions paa,          pay_payroll_actions ppa  ,          pay_assignment_actions rpaa,          pay_action_interlocks il,          per_time_periods    ptp,          pay_payroll_actions rppa where rppa.PAYROLL_ID=:P_PAYROLL_ID AND   rppa.time_period_id=:P_TIME_PERIOD_ID
AND   asf.ORGANIZATION_ID = hou.ORGANIZATION_ID and    paa.payroll_action_id = ppa.payroll_action_id and     ppa.action_type in ('U','P') and     ppa.action_status = 'C'
and     ppa.payroll_id = rppa.payroll_id and     ppa.effective_date >= rppa.effective_date and     rpaa.assignment_id = paa.assignment_id and    per.person_id  = asf.person_id and    paa.assignment_id = asf.assignment_id and    rppa.date_earned between              asf.effective_start_date and asf.effective_end_date and    ptp.time_period_id = rppa.time_period_id and    rppa.date_earned between                  per.effective_start_date and per.effective_end_date and    rppa.payroll_action_id = rpaa.payroll_action_id and    rpaa.assignment_action_id = il.locked_action_id and    paa.assignment_action_id = il.locking_action_id and il.rowid        (select             substr(max(lpad(aa.action_sequence,15,0)||loc.rowid),
                   -length(loc.rowid)) latest_act         from   pay_assignment_actions aa,                pay_action_interlocks loc         where loc.locked_action_id = aa.assignment_action_id         and   loc.locking_action_id = paa.assignment_action_id         group by length(loc.rowid)) ORDER BY per.last_name call     count       cpu    elapsed       disk      query    current        rows


------- ------Â  -------- ---------- ---------- ---------- ----------Â  ----------





Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1    809.49    1632.05      84914  263024953          0           0


------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3    809.49    1632.05      84914  263024953          0           0





Misses in library cache during parse: 0



Optimizer mode: ALL_ROWS


Parsing user id: 193  (APPS) Rows     Row Source Operation -------  ---------------------------------------------------


      0  SORT ORDER BY (cr&3024953 pr„914 pw=0 time=0 us cost590 size&2 card=1)       0   NESTED LOOPS  (cr&3024953 pr„914 pw=0 time=0 us)       0    NESTED LOOPS  (cr&3024953 pr„914 pw=0 time=0 us cost582 size&2 card=1)       0     NESTED LOOPS  (cr&3024953 pr„914 pw=0 time=0 us cost577 size9 card=1) 16970762      NESTED LOOPS  (cr"0440177 pr„900 pw=0 time30098816 us cost576 size0 card=1) 16970762       NESTED LOOPS  (cr9369827 pr„900 pw=0 time73923328 us cost543 sizes272 cardQ6) 16970762        HASH JOIN  (cr5526 prG472 pw=0 timeƒ105720 us cost032 sizee408 cardQ1)   10831         TABLE ACCESS FULL HR_ALL_ORGANIZATION_UNITS_TL (cr2 pr=0 pw=0 timeS640 us costG sizeH60 card8) 16970762         HASH JOIN  (cr5394 prG472 pw=0 timeG942036 us cost™84 size!18824 card%528)   10831          TABLE ACCESS FULL HR_ALL_ORGANIZATION_UNITS (cr'7 pr=0 pw=0 time89370 us cost† sizeC328 cardT16) 16970762          HASH JOIN  (cr5117 prG472 pw=0 time311112 us cost˜97 size'00000 card6000) 1182286           NESTED LOOPS  (crp268 pr626 pw=0 time`283736 us) 1182286            NESTED LOOPS  (cr405 pr061 pw=0 timeh51288 us cost03 size&2020 cardY55)     119             NESTED LOOPS  (cr3 pr pw=0 time16 us cost sizeg6 card&)       1              INDEX UNIQUE SCAN PER_TIME_PERIODS_PK (cr=2 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 42704)     119              INLIST ITERATOR  (cr1 pr pw=0 time80 us)     119               TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_ACTIONS (cr1 pr pw=0 time62 us cost sizeW2 card&)     119                INDEX RANGE SCAN PAY_PAYROLL_ACTIONS_N51 (cr=7 pr=2 pw=0 time#6 us cost=3 size=0 cardw)(object id 41293) 1182286             INDEX RANGE SCAN PAY_ASSIGNMENT_ACTIONS_N50 (cr282 pr046 pw=0 timed07830 us cost=2 size=0 card#1)(object id 40857) 1182286            TABLE ACCESS BY INDEX ROWID PAY_ASSIGNMENT_ACTIONS (crf863 pr•65 pw=0 time=0 us cost$ sizeA58 card#1) 1160908           TABLE ACCESS FULL PER_ALL_ASSIGNMENTS_F (cr4849 pr4846 pw=0 time"56153 us cost•82 size5881818 card57478) 16970762        TABLE ACCESS BY USER ROWID PAY_ACTION_INTERLOCKS (cr9264301 pr7428 pw=0 time=0 us cost=1 size card=1) 16964706         SORT GROUP BY (cr2293910 pr1545 pw=0 time=0 us cost=7 sizeV card=2) 32113805          NESTED LOOPS  (cr2293910 pr1545 pw=0 time(0380896 us) 32113805           NESTED LOOPS  (cr0180105 pr259 pw=0 time3230016 us cost=6 sizeV card=2) 32113805            INDEX RANGE SCAN PAY_ACTION_INTERLOCKS_PK (crU952660 pre36 pw=0 time2551526 us cost=3 size( card=2)(object id 40837) 32113805            INDEX UNIQUE SCAN PAY_ASSIGNMENT_ACTIONS_PK (crd227445 prG23 pw=0 time=0 us cost=1 size=0 card=1)(object id 40859) 32113805           TABLE ACCESS BY INDEX ROWID PAY_ASSIGNMENT_ACTIONS (cr2113805 pr 286 pw=0 time=0 us cost=2 size card=1) 16970762       TABLE ACCESS BY INDEX ROWID PAY_ASSIGNMENT_ACTIONS (crQ070350 pr=0 pw=0 time=0 us cost=2 size card=1) 16970762        INDEX UNIQUE SCAN PAY_ASSIGNMENT_ACTIONS_PK (cr4099588 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 40859)       0      TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_ACTIONS (crB584776 pr pw=0 time=0 us cost=1 size) card=1) 16970762       INDEX UNIQUE SCAN PAY_PAYROLL_ACTIONS_PK (cr&761707 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 41295)       0     INDEX RANGE SCAN PER_PEOPLE_F_PK (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=3)(object id 41790)       0    TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F (cr=0 pr=0 pw=0 time=0 us cost=5 sizes card=1)



Rows     Execution Plan


-------Â  ---------------------------------------------------


      0  SELECT STATEMENT   MODE: ALL_ROWS       0   SORT (ORDER BY)       0    NESTED LOOPS       0     NESTED LOOPS       0      NESTED LOOPS 16970762       NESTED LOOPS 16970762        NESTED LOOPS 16970762         HASH JOIN   10831          TABLE ACCESS   MODE: ANALYZED (FULL) OF                      'HR_ALL_ORGANIZATION_UNITS_TL' (TABLE) 16970762          HASH JOIN   10831           TABLE ACCESS   MODE: ANALYZED (FULL) OF                       'HR_ALL_ORGANIZATION_UNITS' (TABLE) 16970762           HASH JOIN 1182286            NESTED LOOPS 1182286             NESTED LOOPS     119              NESTED LOOPS       1               INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                           'PER_TIME_PERIODS_PK' (INDEX (UNIQUE))     119               INLIST ITERATOR     119                TABLE ACCESS   MODE: ANALYZED (BY                            INDEX ROWID) OF 'PAY_PAYROLL_ACTIONS' (TABLE)     119                 INDEX   MODE: ANALYZED (RANGE SCAN)                             OF 'PAY_PAYROLL_ACTIONS_N51' (INDEX) 1182286              INDEX   MODE: ANALYZED (RANGE SCAN) OF                          'PAY_ASSIGNMENT_ACTIONS_N50' (INDEX) 1182286             TABLE ACCESS   MODE: ANALYZED (BY INDEX                         ROWID) OF 'PAY_ASSIGNMENT_ACTIONS' (TABLE) 1160908            TABLE ACCESS   MODE: ANALYZED (FULL) OF                        'PER_ALL_ASSIGNMENTS_F' (TABLE) 16970762         TABLE ACCESS   MODE: ANALYZED (BY USER ROWID) OF                     'PAY_ACTION_INTERLOCKS' (TABLE) 16964706          SORT (GROUP BY) 32113805           NESTED LOOPS 32113805            NESTED LOOPS 32113805             INDEX   MODE: ANALYZED (RANGE SCAN) OF                         'PAY_ACTION_INTERLOCKS_PK' (INDEX (UNIQUE)) 32113805             INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                         'PAY_ASSIGNMENT_ACTIONS_PK' (INDEX (UNIQUE)) 32113805            TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)                        OF 'PAY_ASSIGNMENT_ACTIONS' (TABLE) 16970762        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF                    'PAY_ASSIGNMENT_ACTIONS' (TABLE) 16970762         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                     'PAY_ASSIGNMENT_ACTIONS_PK' (INDEX (UNIQUE))       0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF                   'PAY_PAYROLL_ACTIONS' (TABLE) 16970762        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                    'PAY_PAYROLL_ACTIONS_PK' (INDEX (UNIQUE))       0      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'PER_PEOPLE_F_PK'

                 (INDEX (UNIQUE))       0     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF                 'PER_ALL_PEOPLE_F' (TABLE)


Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       1        0.00          0.00   Disk file operations I/O                       16        0.00          0.00   db file sequential read                     50068        0.52        284.33   asynch descriptor resize                        6        0.00          0.00   direct path read                              195        0.04          1.40   SQL*Net message from client                     1        0.00          0.00
********************************************************************************







Second piece of SQL.....






select per.EMPLOYEE_NUMBER,


           hou.NAME ORG_NAME,
           rpaa.assignment_action_id run_assignment_action_id,            per.FULL_NAME from hr_organization_units hou,        per_all_assignments_f asf,          per_people_f per,          pay_assignment_actions paa,          pay_payroll_actions ppa  ,          pay_assignment_actions rpaa,          pay_action_interlocks il,          per_time_periods    ptp,          pay_payroll_actions rppa where rppa.PAYROLL_ID=:P_PAYROLL_ID AND   rppa.time_period_id=:P_TIME_PERIOD_ID
AND   asf.ORGANIZATION_ID = hou.ORGANIZATION_ID and    paa.payroll_action_id = ppa.payroll_action_id and     ppa.action_type in ('U','P') and     ppa.action_status = 'C'
and     ppa.payroll_id = rppa.payroll_id and     ppa.effective_date >= rppa.effective_date and     rpaa.assignment_id = paa.assignment_id and    per.person_id  = asf.person_id and    paa.assignment_id = asf.assignment_id and    rppa.date_earned between              asf.effective_start_date and asf.effective_end_date and    ptp.time_period_id = rppa.time_period_id and    rppa.date_earned between                  per.effective_start_date and per.effective_end_date and    rppa.payroll_action_id = rpaa.payroll_action_id and    rpaa.assignment_action_id = il.locked_action_id and    paa.assignment_action_id = il.locking_action_id and il.rowid        (select             substr(max(lpad(aa.action_sequence,15,0)||loc.rowid),

                   -length(loc.rowid)) latest_act         from   pay_assignment_actions aa,                pay_action_interlocks loc         where loc.locked_action_id = aa.assignment_action_id         and   loc.locking_action_id = paa.assignment_action_id         group by length(loc.rowid)) ORDER BY per.last_name call     count       cpu    elapsed       disk      query    current        rows




------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        0      0.00       0.00          0          0          0           0




    
  
  •   ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.00       0.00          0          0          0           0 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 193  (APPS) Rows     Execution Plan -------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS       0   SORT (ORDER BY)       0    NESTED LOOPS       0     NESTED LOOPS       0      NESTED LOOPS       0       NESTED LOOPS       0        NESTED LOOPS       0         HASH JOIN       0          TABLE ACCESS   MODE: ANALYZED (FULL) OF                      'HR_ALL_ORGANIZATION_UNITS_TL' (TABLE)       0          HASH JOIN       0           TABLE ACCESS   MODE: ANALYZED (FULL) OF                       'HR_ALL_ORGANIZATION_UNITS' (TABLE)       0           HASH JOIN       0            NESTED LOOPS       0             NESTED LOOPS       0              NESTED LOOPS       0               INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                           'PER_TIME_PERIODS_PK' (INDEX (UNIQUE))       0               INLIST ITERATOR       0                TABLE ACCESS   MODE: ANALYZED (BY                            INDEX ROWID) OF 'PAY_PAYROLL_ACTIONS' (TABLE)       0                 INDEX   MODE: ANALYZED (RANGE SCAN)                             OF 'PAY_PAYROLL_ACTIONS_N51' (INDEX)       0              INDEX   MODE: ANALYZED (RANGE SCAN) OF                          'PAY_ASSIGNMENT_ACTIONS_N50' (INDEX)       0             TABLE ACCESS   MODE: ANALYZED (BY INDEX                         ROWID) OF 'PAY_ASSIGNMENT_ACTIONS' (TABLE)       0            TABLE ACCESS   MODE: ANALYZED (FULL) OF                        'PER_ALL_ASSIGNMENTS_F' (TABLE)       0         TABLE ACCESS   MODE: ANALYZED (BY USER ROWID) OF                     'PAY_ACTION_INTERLOCKS' (TABLE)       0          SORT (GROUP BY)       0           NESTED LOOPS       0            NESTED LOOPS       0             INDEX   MODE: ANALYZED (RANGE SCAN) OF                         'PAY_ACTION_INTERLOCKS_PK' (INDEX (UNIQUE))       0             INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                         'PAY_ASSIGNMENT_ACTIONS_PK' (INDEX (UNIQUE))       0            TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)                        OF 'PAY_ASSIGNMENT_ACTIONS' (TABLE)       0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF                    'PAY_ASSIGNMENT_ACTIONS' (TABLE)       0         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                     'PAY_ASSIGNMENT_ACTIONS_PK' (INDEX (UNIQUE))       0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF                   'PAY_PAYROLL_ACTIONS' (TABLE)       0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF                    'PAY_PAYROLL_ACTIONS_PK' (INDEX (UNIQUE))       0      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'PER_PEOPLE_F_PK'

                 (INDEX (UNIQUE))       0     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF                 'PER_ALL_PEOPLE_F' (TABLE)





Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       1        0.00          0.00   Disk file operations I/O                        9        0.00          0.00   db file sequential read                   4518987        0.74      10002.88   db file scattered read                         59        0.05          0.87   asynch descriptor resize                        6        0.00          0.00   direct path read                               75        0.03          0.58   latch: cache buffers chains                    14        0.00          0.00   latch: object queue header operation            1        0.00          0.00
********************************************************************************






Any pointers/guidance as to what is going on here , how to diagnose the problem, what I should be looking etc will be much appreciated.



Thanks




--




http://www.freelists.org/webpage/oracle-l








// eompost 4E6621EC:2365.1:benpyry





†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^
Received on Tue Sep 06 2011 - 14:16:33 CDT












Original text of this message