Home » RDBMS Server » Performance Tuning » improve sql performance (long sql here) (11g)
icon12.gif  improve sql performance (long sql here) [message #649051] Thu, 10 March 2016 21:30 Go to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
Gurus,

I'm learning sql tuning and I'm trying to improve the performance of the following sql. Right now, it took about 15 seconds to run and we want to cut it to less than 5 seconds. Attached please find execution plan and TKPROF trace output(explain_plan_and_sql_trace.txt).

Thanks..
WITH "Rpt"
     AS (  SELECT LIM.COMPANY_CD,
                  LIM.COMPANY_NAME,
                  LIM.PAY_END_DT,
                  LIM.PAYGROUP,
                  LIM.PAYGROUP_DESCR_LONG,
                  LIM.INVOICE_NUMBER,
                  LIM.DEDUCTION_CODE,
                  LIM.DESCR_LONG,
                  LIM.DEDUCTION_CLASS,
                  LIM.PLAN_TYPE,
                  (SELECT PLAN_TYPE_D.BEN_PLAN_TYPE_DESCR_LONG
                     FROM DW.BEN_PLAN_TYPE_D PLAN_TYPE_D
                    WHERE     PLAN_TYPE_D.BEN_PLAN_TYPE_CD = LIM.PLAN_TYPE
                          AND LIM.INVOICE_DT BETWEEN PLAN_TYPE_D.EFF_START_DT
                                                 AND PLAN_TYPE_D.EFF_END_DT
                          AND SYSDATE BETWEEN PLAN_TYPE_D.VALID_START_DT
                                          AND PLAN_TYPE_D.VALID_END_DT)
                     DEDUCTION_DESCRIPTION,
                  LIM.SEPCHK,
                  LIM.EMPLID,
                  LIM.NAME EE_NAME,
                  LIM.TAXABLE_AMOUNT,
                  LIM.NON_TAXABLE_AMOUNT,
                  LIM.CALCULATED_BASE,
                  (CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.MSUM ELSE 0 END)
                     MSUM,
                  (CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.QSUM ELSE 0 END)
                     QSUM,
                  (CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.YSUM ELSE 0 END)
                     YSUM
             FROM (  SELECT COMPANY,
                            EMPLID,
                            EMPL_RCD,
                            CODE,
                            DEDUCTION_CLASS,
                            INVOICE,
                            INV_DT,
                            SUM (
                               CASE
                                  WHEN MNTH_DT = CHK_MNTH_DT THEN AMOUNT
                                  ELSE 0
                               END)
                               MSUM,
                            SUM (
                               CASE
                                  WHEN MNTH_DT = CHK_MNTH_DT THEN HOURS
                                  ELSE 0
                               END)
                               MHRS,
                            SUM (
                               CASE
                                  WHEN QTR_DT = CHK_QTR_DT THEN AMOUNT
                                  ELSE 0
                               END)
                               QSUM,
                            SUM (
                               CASE
                                  WHEN QTR_DT = CHK_QTR_DT THEN HOURS
                                  ELSE 0
                               END)
                               QHRS,
                            SUM (AMOUNT) YSUM,
                            SUM (NVL (HOURS, 0)) YHRS
                       FROM (SELECT PRF.COMPANY,
                                    PRF.PAYGROUP,
                                    PRF.PAY_END_DT PAY_END_DT,
                                    PRF.EMPLID,
                                    PRF.EMPL_RCD,
                                    INV_DT_D.INVOICE_DT INV_DT,
                                    INV_DT_D.CHECK_DT CHK_DT,
                                    REG_D.CODE,
                                    PRF.CODE_TYPE,
                                    DED_D.PAY_DED_CLASS_CD DEDUCTION_CLASS,
                                    PRF.AMOUNT,
                                    PRF.HOURS,
                                    INV_DT_D.INVOICE,
                                    PRF.VALID_FLAG,
                                    TRUNC (INV_DT_D.CHECK_DT, 'MM') MNTH_DT,
                                    TRUNC (INV_DT_D.CHECK_DT, 'Q') QTR_DT,
                                    TRUNC (INV_DT_D.CHECK_DT, 'YEAR') YEAR_DT,
                                    TRUNC (CK_DT_D.CALENDAR_DT, 'MM') CHK_MNTH_DT,
                                    TRUNC (CK_DT_D.CALENDAR_DT, 'Q') CHK_QTR_DT,
                                    TRUNC (CK_DT_D.CALENDAR_DT, 'YEAR')
                                       CHK_YEAR_DT
                               FROM DW.PAY_REGISTER_F PRF,
                                    DW.PAY_PAYROLL_REGISTER_D REG_D,
                                    DW.PAY_DEDUCTION_CLASS_D DED_D,
                                    DW.SYS_CALENDAR_D CK_DT_D,
                                    (SELECT COMPANY,
                                            INVOICE_NUMBER INVOICE,
                                            INVOICE_DT,
                                            (  PRF.CHECK_DT_D_SID
                                             + TO_DATE ('1/1/1900', 'MM/DD/YYYY'))
                                               AS CHECK_DT,
                                            TRUNC (
                                               (  PRF.CHECK_DT_D_SID
                                                + TO_DATE ('1/1/1900',
                                                           'MM/DD/YYYY')),
                                               'YYYY')
                                               AS START_DT,
                                            (  PRF.CHECK_DT_D_SID
                                             + TO_DATE ('1/1/1900', 'MM/DD/YYYY'))
                                               AS END_DT
                                       FROM DW.PAY_REGISTER_F PRF
                                      WHERE     COMPANY = '12C'
                                            AND INVOICE_NUMBER = '1941765'
                                            AND VALID_FLAG = 'Y'
                                            AND ROWNUM = 1) INV_DT_D
                              WHERE     PRF.COMPANY = INV_DT_D.COMPANY
                                    AND '1941765' >= PRF.INVOICE_NUMBER
                                    AND PRF.PAY_PAYROLL_REGISTER_D_SID =
                                           REG_D.PAY_PAYROLL_REGISTER_D_SID
                                    AND PRF.PAY_DEDUCTION_CLASS_D_SID =
                                           DED_D.PAY_DEDUCTION_CLASS_D_SID
                                    AND PRF.CHECK_DT_D_SID =
                                           CK_DT_D.CALENDAR_D_SID
                                    AND CK_DT_D.CALENDAR_DT >= INV_DT_D.START_DT
                                    AND CK_DT_D.CALENDAR_DT <= INV_DT_D.END_DT
                                    AND (DED_D.PAY_DED_CLASS_CD > 'K')) YTD_DEDS
                      WHERE YTD_DEDS.VALID_FLAG = 'Y'
                   GROUP BY COMPANY,
                            EMPLID,
                            EMPL_RCD,
                            CODE,
                            DEDUCTION_CLASS,
                            INVOICE,
                            INV_DT) AGG_DATA,
                  DW.HCM_EMPLOYEE_D EE_D,
                  DW.PAY_PAYROLL_REGISTER_D PAY_REG_D,
                  DW.T2_DW_ADMIN_STDRPT_ACCESS ACC,
                  (  SELECT CO_D.COMPANY_CD,
                            CO_D.COMPANY_NAME,
                            PRF2.INVOICE_NUMBER,
                            PRF2.EMPLID,
                            PRF2.EMPL_RCD,
                            PRF2.SEPCHK,
                            PER_D.NAME,
                            SUBSTR (PPRD.DISPLAY_CODE, 1, 2) PLAN_TYPE,
                            SUBSTR (PPRD.DISPLAY_CODE, 4, 10) DEDUCTION_CODE,
                            PPRD.DISPLAY_CODE CODE,
                            PPRD.DESCR_LONG DESCR_LONG,
                            DC_D.PAY_DED_CLASS_CD DEDUCTION_CLASS,
                            PRF2.PAYGROUP,
                            PG_D.PAYGROUP_DESCR_LONG,
                            PRF2.PAY_END_DT,
                            PRF2.INVOICE_DT,
                            PRF2.CALCULATED_BASE,
                            ROW_NUMBER ()
                            OVER (
                               PARTITION BY PRF2.EMPLID,
                                            SUBSTR (PPRD.DISPLAY_CODE, 4, 10),
                                            DC_D.PAY_DED_CLASS_CD
                               ORDER BY
                                  PRF2.EMPLID,
                                  SUBSTR (PPRD.DISPLAY_CODE, 4, 10),
                                  PRF2.SEPCHK NULLS LAST)
                               ROW_NUMBER,
                            SUM (
                               CASE
                                  WHEN DC_D.PAY_DED_CLASS_CD = 'T'
                                  THEN
                                     PRF2.AMOUNT
                                  ELSE
                                     0
                               END)
                               TAXABLE_AMOUNT,
                            SUM (
                               CASE
                                  WHEN DC_D.PAY_DED_CLASS_CD IN ('N', 'P')
                                  THEN
                                     PRF2.AMOUNT
                                  ELSE
                                     0
                               END)
                               NON_TAXABLE_AMOUNT,
                            SUM (PRF2.AMOUNT) AMOUNT
                       FROM DW.PAY_REGISTER_F PRF2
                            INNER JOIN DW.PAY_PAYROLL_REGISTER_D PPRD
                               ON PRF2.PAY_PAYROLL_REGISTER_D_SID =
                                     PPRD.PAY_PAYROLL_REGISTER_D_SID
                            INNER JOIN DW.HCM_COMPANY_D CO_D
                               ON PRF2.HCM_COMPANY_D_SID = CO_D.HCM_COMPANY_D_SID
                            INNER JOIN DW.PAY_PAYGROUP_D PG_D
                               ON PRF2.PAY_PAYGROUP_D_SID =
                                     PG_D.PAY_PAYGROUP_D_SID
                            INNER JOIN DW.HCM_PERSON_D PER_D
                               ON PRF2.HCM_PERSON_D_SID = PER_D.HCM_PERSON_D_SID
                            INNER JOIN DW.PAY_DEDUCTION_CLASS_D DC_D
                               ON PRF2.PAY_DEDUCTION_CLASS_D_SID =
                                     DC_D.PAY_DEDUCTION_CLASS_D_SID
                            INNER JOIN DW.PAY_DEDUCTION_CLASS_D ded_d
                               ON PRF2.PAY_DEDUCTION_CLASS_D_SID =
                                     DED_D.PAY_DEDUCTION_CLASS_D_SID
                      WHERE     INVOICE_NUMBER = '1941765'
                            AND PRF2.VALID_FLAG = 'Y'
                            AND (DED_D.PAY_DED_CLASS_CD > 'K')
                   GROUP BY CO_D.COMPANY_CD,
                            PRF2.INVOICE_NUMBER,
                            CO_D.COMPANY_NAME,
                            PRF2.EMPLID,
                            PRF2.EMPL_RCD,
                            PRF2.SEPCHK,
                            PER_D.NAME,
                            SUBSTR (PPRD.DISPLAY_CODE, 1, 2),
                            SUBSTR (PPRD.DISPLAY_CODE, 4, 10),
                            PPRD.DISPLAY_CODE,
                            PPRD.DESCR_LONG,
                            DC_D.PAY_DED_CLASS_CD,
                            PRF2.PAYGROUP,
                            PG_D.PAYGROUP_DESCR_LONG,
                            PRF2.PAY_END_DT,
                            PRF2.INVOICE_DT,
                            PRF2.CALCULATED_BASE) LIM
            WHERE     EE_D.EMPLID = AGG_DATA.EMPLID
                  AND EE_D.EMPL_RCD = AGG_DATA.EMPL_RCD
                  AND EE_D.VALID_FLAG = 'Y'
                  AND EE_D.COMPANY_CD = LIM.COMPANY_CD
                  AND AGG_DATA.INV_DT BETWEEN EE_D.EFF_START_DT
                                          AND EE_D.EFF_END_DT
                  AND AGG_DATA.CODE = PAY_REG_D.CODE
                  AND AGG_DATA.INV_DT BETWEEN PAY_REG_D.EFF_START_DT
                                          AND PAY_REG_D.EFF_END_DT
                  AND SYSDATE BETWEEN PAY_REG_D.VALID_START_DT
                                  AND PAY_REG_D.VALID_END_DT
                  AND AGG_DATA.INVOICE = LIM.INVOICE_NUMBER
                  AND AGG_DATA.EMPLID = LIM.EMPLID
                  AND AGG_DATA.EMPL_RCD = LIM.EMPL_RCD
                  AND AGG_DATA.CODE = LIM.CODE
                  AND AGG_DATA.DEDUCTION_CLASS = LIM.DEDUCTION_CLASS
                  AND ACC.COMPANY_ID = AGG_DATA.COMPANY
                  AND ACC.LOGIN_USER = '1357626'
                  AND ACC.REPORT_ID IN ('C10BENREG',
                                        'C10BENREGCAN',
                                        'C10BENREGSB')
                  AND DW.CHECK_EE_REPORT_ACCESS (AGG_DATA.COMPANY,
                                                          '1357626',
                                                          ACC.REPORT_ID,
                                                          EE_D.EMPLID,
                                                          EE_D.DEPTID,
                                                          EE_D.LOCATION_ID) = 1
         ORDER BY LIM.NAME,
                  LIM.EMPLID,
                  LIM.DEDUCTION_CODE,
                  LIM.SEPCHK)
SELECT "Rpt"."EMPLID" "Employee_ID",
       "Rpt"."EE_NAME" "Employee_Name",
       "Rpt"."PLAN_TYPE" "Plan_Type",
       CASE
          WHEN    "Rpt"."PLAN_TYPE" IS NULL
               OR "Rpt"."DEDUCTION_DESCRIPTION" IS NULL
          THEN
             NULL
          ELSE
                "Rpt"."PLAN_TYPE"
             || ' - '
             || "Rpt"."DEDUCTION_DESCRIPTION"
       END
          "Type",
       "Rpt"."DEDUCTION_DESCRIPTION"
          "Benefit_Plan_Description",
       "Rpt"."DEDUCTION_CODE" "Deduction_Code",
       CASE
          WHEN    "Rpt"."DEDUCTION_CODE" IS NULL
               OR "Rpt"."DESCR_LONG" IS NULL
          THEN
             NULL
          ELSE
                "Rpt"."DEDUCTION_CODE"
             || ' - '
             || "Rpt"."DESCR_LONG"
       END
          "Deduction",
       "Rpt"."DESCR_LONG" "Payroll_Description",
       "Rpt"."PAYGROUP_DESCR_LONG" "Pay_Group",
       CAST ("Rpt"."INVOICE_NUMBER" AS VARCHAR (10))
          "Invoice_Number",
       "Rpt"."PAY_END_DT" "Pay_End_Date",
       "Rpt"."CALCULATED_BASE" "Calculated_Benefit_Base",
       "Rpt"."TAXABLE_AMOUNT" "Taxable_Amount",
       "Rpt"."NON_TAXABLE_AMOUNT" "Non_Taxable_Amount",
       "Rpt"."MSUM" "MTD",
       "Rpt"."QSUM" "QTD",
       "Rpt"."YSUM" "YTD"
  FROM "Rpt"

 

Re: improve sql performance (long sql here) [message #649054 is a reply to message #649051] Fri, 11 March 2016 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I'm learning sql tuning and I'm trying to improve the performance of the following sql.


You can read:
- OraFAQ Forum Guide, Detailed OraFAQ Forum Guide section, Performance Tuning paragraph
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

You can also have a look at Database Performance Tuning Guide.

You can also buy the books from Stephane Faroult.

Re: improve sql performance (long sql here) [message #649058 is a reply to message #649051] Fri, 11 March 2016 02:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your trace shows that the query took less than a second. You need to look outside the database for the solution to your problem.
Re: improve sql performance (long sql here) [message #649067 is a reply to message #649058] Fri, 11 March 2016 09:31 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
I guess all these less than a seconds added up since there are lots of joins in this SQL. By looking at SQL alone, is there anything that I can improve?

Thanks..
Re: improve sql performance (long sql here) [message #649068 is a reply to message #649054] Fri, 11 March 2016 09:33 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
Thanks Michel, I start reading these already. Very helpful.
Re: improve sql performance (long sql here) [message #649069 is a reply to message #649067] Fri, 11 March 2016 09:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
marzbuzz wrote on Fri, 11 March 2016 15:31
I guess all these less than a seconds added up since there are lots of joins in this SQL. By looking at SQL alone, is there anything that I can improve?

Thanks..
No. Nothing. The query ran in 0.74 seconds. From your trace:


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.29       0.31          0        108          0           0
Execute      1      0.00       0.00          0         48          0           0
Fetch       61      0.41       0.42          0      44019          0         887
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       63      0.71       0.74          0      44175          0         887

Re: improve sql performance (long sql here) [message #649071 is a reply to message #649069] Fri, 11 March 2016 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You will get a slight improvement changing your array fetching from 14 to 100.

Re: improve sql performance (long sql here) [message #649075 is a reply to message #649071] Fri, 11 March 2016 14:54 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
Sorry Guys.. I think my previous sql trace may have hit the cache, that's why it's less than 1 second. It still took me 15 seconds to get the results back when I ran the sql in the database. Below is the new trace file for the sql without hitting the cache.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.26       0.26          0        108          0           0
Execute      1      0.00       0.00          0         48          0           0
Fetch       61      0.62       3.47       3169      61423          0         898
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       63      0.89       3.74       3169      61579          0         898

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 213  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID BEN_PLAN_TYPE_D (cr=3 pr=0 pw=0 time=49 us cost=2 size=53 card=1)
         4          4          4   INDEX RANGE SCAN BEN_PLAN_TYPE_D_N03 (cr=2 pr=0 pw=0 time=33 us cost=1 size=0 card=1)(object id 407480)
       898        898        898  PX COORDINATOR  (cr=65063 pr=3169 pw=0 time=3500847 us)
       898        898        898   PX SEND QC (ORDER) :TQ10002 (cr=65015 pr=3169 pw=0 time=3499820 us cost=636 size=821 card=1)
       898        898        898    SORT ORDER BY (cr=65015 pr=3169 pw=0 time=3499044 us cost=636 size=821 card=1)
       898        898        898     PX RECEIVE  (cr=65012 pr=3169 pw=0 time=3443499 us)
       898        898        898      PX SEND RANGE :TQ10001 (cr=65012 pr=3169 pw=0 time=3443271 us)
       898        898        898       NESTED LOOPS  (cr=65012 pr=3169 pw=0 time=3443269 us)
     29193      29193      29193        NESTED LOOPS  (cr=61171 pr=3166 pw=0 time=3307286 us cost=635 size=821 card=1)
       898        898        898         NESTED LOOPS  (cr=57264 pr=3160 pw=0 time=3234501 us cost=635 size=763 card=1)
       898        898        898          NESTED LOOPS  (cr=53100 pr=3160 pw=0 time=3220111 us cost=634 size=722 card=1)
       898        898        898           BUFFER SORT (cr=45915 pr=3160 pw=0 time=3193395 us)
       898        898        898            PX RECEIVE  (cr=45915 pr=3160 pw=0 time=3192413 us)
       898        898        898             PX SEND BROADCAST :TQ10000 (cr=45915 pr=3160 pw=0 time=3192075 us)
       898        898        898              HASH JOIN  (cr=45915 pr=3160 pw=0 time=3191962 us cost=633 size=694 card=1)
      1092       1092       1092               VIEW  (cr=27154 pr=722 pw=0 time=2215132 us cost=371 size=747 card=9)
      1092       1092       1092                HASH GROUP BY (cr=27154 pr=722 pw=0 time=2214766 us cost=371 size=1062 card=9)
     20835      20835      20835                 NESTED LOOPS  (cr=27154 pr=722 pw=0 time=2161110 us)
     20835      20835      20835                  NESTED LOOPS  (cr=22481 pr=722 pw=0 time=2131834 us cost=370 size=1062 card=9)
     20835      20835      20835                   NESTED LOOPS  (cr=21526 pr=722 pw=0 time=2077371 us cost=368 size=918 card=9)
      1380       1380       1380                    NESTED LOOPS  (cr=79 pr=3 pw=0 time=17993 us cost=199 size=7992 card=148)
         4          4          4                     MERGE JOIN CARTESIAN (cr=13 pr=3 pw=0 time=16847 us cost=196 size=41 card=1)
         1          1          1                      VIEW  (cr=5 pr=3 pw=0 time=16776 us cost=192 size=36 card=1)
         1          1          1                       COUNT STOPKEY (cr=5 pr=3 pw=0 time=16752 us)
         1          1          1                        TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5 pr=3 pw=0 time=16740 us cost=192 size=25 card=1)
         1          1          1                         INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=4 pr=2 pw=0 time=16310 us cost=5 size=0 card=306)(object id 407596)
         4          4          4                      BUFFER SORT (cr=8 pr=0 pw=0 time=80 us cost=196 size=5 card=1)
         4          4          4                       TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=76 us cost=3 size=5 card=1)
         4          4          4                        INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=62 us cost=2 size=0 card=1)(object id 160855)
      1380       1380       1380                     TABLE ACCESS BY INDEX ROWID SYS_CALENDAR_D (cr=66 pr=0 pw=0 time=1157 us cost=3 size=1560 card=120)
      1380       1380       1380                      INDEX RANGE SCAN SYS_CALENDAR_DIM_N01 (cr=10 pr=0 pw=0 time=342 us cost=0 size=0 card=215)(object id 15315)
     20835      20835      20835                    TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=21447 pr=719 pw=0 time=2232521 us cost=4 size=48 card=1)
     20835      20835      20835                     INDEX RANGE SCAN PAY_REGISTER_F_N05 (cr=674 pr=8 pw=0 time=9316 us cost=3 size=0 card=1)(object id 447234)
     20835      20835      20835                   INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=955 pr=0 pw=0 time=38999 us cost=0 size=0 card=1)(object id 15537)
     20835      20835      20835                  TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=4673 pr=0 pw=0 time=17260 us cost=0 size=16 card=1)
       898        898        898               VIEW  (cr=18761 pr=2438 pw=0 time=969054 us cost=262 size=32994 card=54)
       898        898        898                WINDOW NOSORT (cr=18761 pr=2438 pw=0 time=968940 us cost=262 size=11070 card=54)
       898        898        898                 SORT GROUP BY (cr=18761 pr=2438 pw=0 time=967536 us cost=262 size=11070 card=54)
       898        898        898                  NESTED LOOPS  (cr=18761 pr=2438 pw=0 time=2274452 us)
       898        898        898                   NESTED LOOPS  (cr=17863 pr=2435 pw=0 time=1634960 us cost=261 size=11070 card=54)
       898        898        898                    NESTED LOOPS  (cr=15261 pr=2426 pw=0 time=887615 us cost=249 size=9828 card=54)
       898        898        898                     NESTED LOOPS  (cr=13515 pr=2426 pw=0 time=881752 us cost=237 size=7884 card=54)
       898        898        898                      HASH JOIN  (cr=13506 pr=2426 pw=0 time=877913 us cost=225 size=6372 card=54)
       898        898        898                       NESTED LOOPS  (cr=13482 pr=2426 pw=0 time=858424 us)
       898        898        898                        NESTED LOOPS  (cr=13481 pr=2426 pw=0 time=857071 us cost=218 size=6102 card=54)
       898        898        898                         NESTED LOOPS  (cr=12570 pr=2426 pw=0 time=852003 us cost=194 size=4482 card=54)
         4          4          4                          TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=113 us cost=3 size=5 card=1)
         4          4          4                           INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=85 us cost=2 size=0 card=1)(object id 160855)
       898        898        898                          TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=12562 pr=2426 pw=0 time=862281 us cost=191 size=3432 card=44)
     24256      24256      24256                           INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=110 pr=24 pw=0 time=364728 us cost=4 size=0 card=306)(object id 407596)
       898        898        898                         INDEX RANGE SCAN HCM_COMPANY_D_PK (cr=911 pr=0 pw=0 time=2895 us cost=0 size=0 card=1)(object id 15313)
       898        898        898                        TABLE ACCESS BY INDEX ROWID HCM_COMPANY_D (cr=1 pr=0 pw=0 time=524 us cost=0 size=30 card=1)
         7          7          7                       VIEW  index$_join$_023 (cr=24 pr=0 pw=0 time=3390 us cost=7 size=35 card=7)
         7          7          7                        HASH JOIN  (cr=24 pr=0 pw=0 time=3388 us)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=17 pr=0 pw=0 time=59 us cost=5 size=35 card=7)(object id 160855)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_PK (cr=7 pr=0 pw=0 time=25 us cost=3 size=35 card=7)(object id 160857)
       898        898        898                      TABLE ACCESS BY INDEX ROWID PAY_PAYGROUP_D (cr=9 pr=0 pw=0 time=2610 us cost=0 size=28 card=1)
       898        898        898                       INDEX RANGE SCAN PAY_PAYGROUP_D_PK (cr=8 pr=0 pw=0 time=1743 us cost=0 size=0 card=1)(object id 15499)
       898        898        898                     TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=1746 pr=0 pw=0 time=5729 us cost=0 size=36 card=1)
       898        898        898                      INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=902 pr=0 pw=0 time=3237 us cost=0 size=0 card=1)(object id 15537)
       898        898        898                    PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=2602 pr=9 pw=0 time=56920 us cost=0 size=0 card=1)
       898        898        898                     INDEX UNIQUE SCAN HCM_PERSON_D_PK PARTITION: KEY KEY (cr=2602 pr=9 pw=0 time=55440 us cost=0 size=0 card=1)(object id 4391000)
       898        898        898                   TABLE ACCESS BY GLOBAL INDEX ROWID HCM_PERSON_D PARTITION: ROW LOCATION ROW LOCATION (cr=898 pr=3 pw=0 time=18864 us cost=0 size=23 card=1)
       898        898        898           INLIST ITERATOR  (cr=7185 pr=0 pw=0 time=22834 us)
       898        898        898            PX PARTITION HASH ITERATOR PARTITION: KEY(INLIST) KEY(INLIST) (cr=7185 pr=0 pw=0 time=21097 us cost=1 size=28 card=1)
       898        898        898             INDEX RANGE SCAN T2_DW_ADMIN_STDRPT_ACCESS_N1 PARTITION: KEY(INLIST) KEY(INLIST) (cr=7185 pr=0 pw=0 time=16743 us cost=1 size=28 card=1)(object id 

7065133)
       898        898        898          TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=4164 pr=0 pw=0 time=12984 us cost=3 size=41 card=1)
      2241       2241       2241           INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_N04 (cr=1798 pr=0 pw=0 time=5798 us cost=2 size=0 card=1)(object id 407517)
     29193      29193      29193         PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=3907 pr=6 pw=0 time=144002 us cost=0 size=0 card=1)
     29193      29193      29193          INDEX RANGE SCAN HCM_EMPLOYEE_D_N03 PARTITION: KEY KEY (cr=3907 pr=6 pw=0 time=138138 us cost=0 size=0 card=1)(object id 4391673)
       898        898        898        TABLE ACCESS BY GLOBAL INDEX ROWID HCM_EMPLOYEE_D PARTITION: ROW LOCATION ROW LOCATION (cr=3841 pr=3 pw=0 time=162103 us cost=0 size=58 card=1)

Re: improve sql performance (long sql here) [message #649076 is a reply to message #649075] Fri, 11 March 2016 14:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So that execution took 3.74 seconds. The other 11.26 seconds are somewhere else. Nothing to do with Oracle.
Re: improve sql performance (long sql here) [message #649077 is a reply to message #649075] Fri, 11 March 2016 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make a trace file with wait events.

And this is the purpose of the cache to the query execution time.
If you execute it once then what's the problem with 4 seconds?
If you execute it many times then the cache makes its work and the 1 second is a valid time.

[Updated on: Fri, 11 March 2016 14:59]

Report message to a moderator

Re: improve sql performance (long sql here) [message #649078 is a reply to message #649076] Fri, 11 March 2016 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Fri, 11 March 2016 21:57
So that execution took 3.74 seconds. The other 11.26 seconds are somewhere else. Nothing to do with Oracle.


Yes, and as I said increasing the array fetching will reduce the number of network round trips and so the global time.
This is what marzbuzz will see if he activates a trace with the wait events.

Re: improve sql performance (long sql here) [message #649080 is a reply to message #649076] Fri, 11 March 2016 16:08 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
I can live with 3.74s. I wondering why I got 15s to run the sql and get the result back in sql plus or toad.
Re: improve sql performance (long sql here) [message #649085 is a reply to message #649080] Sat, 12 March 2016 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do what I said.

Re: improve sql performance (long sql here) [message #649119 is a reply to message #649085] Mon, 14 March 2016 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you selecting any long or lob columns?
They can slow down the rendering of the data in GUIs
Re: improve sql performance (long sql here) [message #649122 is a reply to message #649119] Mon, 14 March 2016 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something the trace I suggested would tell us... Smile

Re: improve sql performance (long sql here) [message #649128 is a reply to message #649051] Mon, 14 March 2016 09:29 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
Do I add wait event information by the following commands?

ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SELECT 'SLOW SQL'
ALTER SESSION SET SQL_TRACE=FALSE.

Thanks..
Re: improve sql performance (long sql here) [message #649129 is a reply to message #649128] Mon, 14 March 2016 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want to use events:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SELECT 'SLOW SQL'
alter session set events '10046 trace name context off';

No "ALTER SESSION SET SQL_TRACE".

Re: improve sql performance (long sql here) [message #649130 is a reply to message #649129] Mon, 14 March 2016 11:55 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
Thanks Michel.. Please see the sql trace with wait event information below:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.35       2.44          2       4658         38           0
Execute      1      0.00       0.00          0         48          0           0
Fetch       60      0.59       0.69       1097      36762          0         872
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       62      2.95       3.14       1099      41468         38         872

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 213  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         7          7          7  TABLE ACCESS BY INDEX ROWID BEN_PLAN_TYPE_D (cr=4 pr=0 pw=0 time=104 us cost=2 size=53 card=1)
         7          7          7   INDEX RANGE SCAN BEN_PLAN_TYPE_D_N03 (cr=3 pr=0 pw=0 time=74 us cost=1 size=0 card=1)(object id 407480)
       872        872        872  PX COORDINATOR  (cr=44848 pr=1099 pw=0 time=2962069 us)
       872        872        872   PX SEND QC (ORDER) :TQ10002 (cr=44800 pr=1099 pw=0 time=2960773 us cost=593 size=821 card=1)
       872        872        872    SORT ORDER BY (cr=44800 pr=1099 pw=0 time=2960474 us cost=593 size=821 card=1)
       872        872        872     PX RECEIVE  (cr=44796 pr=1099 pw=0 time=2576044 us)
       872        872        872      PX SEND RANGE :TQ10001 (cr=44796 pr=1099 pw=0 time=2575914 us)
       872        872        872       NESTED LOOPS  (cr=44796 pr=1099 pw=0 time=2575662 us)
     28276      28276      28276        NESTED LOOPS  (cr=41067 pr=871 pw=0 time=1261076 us cost=592 size=821 card=1)
       872        872        872         NESTED LOOPS  (cr=37153 pr=844 pw=0 time=1228832 us cost=592 size=763 card=1)
       872        872        872          NESTED LOOPS  (cr=33111 pr=840 pw=0 time=1214715 us cost=591 size=722 card=1)
       872        872        872           BUFFER SORT (cr=26134 pr=840 pw=0 time=1186802 us)
       872        872        872            PX RECEIVE  (cr=26134 pr=840 pw=0 time=1186208 us)
       872        872        872             PX SEND BROADCAST :TQ10000 (cr=26134 pr=840 pw=0 time=1186206 us)
       872        872        872              HASH JOIN  (cr=26134 pr=840 pw=0 time=1186079 us cost=590 size=694 card=1)
       984        984        984               VIEW  (cr=15598 pr=26 pw=0 time=287231 us cost=352 size=664 card=8)
       984        984        984                HASH GROUP BY (cr=15598 pr=26 pw=0 time=286859 us cost=352 size=944 card=8)
     11943      11943      11943                 NESTED LOOPS  (cr=15598 pr=26 pw=0 time=114955 us)
     11943      11943      11943                  NESTED LOOPS  (cr=12937 pr=26 pw=0 time=97795 us cost=351 size=944 card=8)
     11943      11943      11943                   NESTED LOOPS  (cr=12373 pr=26 pw=0 time=67148 us cost=350 size=816 card=8)
       764        764        764                    NESTED LOOPS  (cr=63 pr=4 pw=0 time=25011 us cost=181 size=7992 card=148)
         4          4          4                     MERGE JOIN CARTESIAN (cr=13 pr=4 pw=0 time=23463 us cost=178 size=41 card=1)
         1          1          1                      VIEW  (cr=5 pr=4 pw=0 time=23320 us cost=174 size=36 card=1)
         1          1          1                       COUNT STOPKEY (cr=5 pr=4 pw=0 time=23283 us)
         1          1          1                        TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5 pr=4 pw=0 time=23269 us cost=174 size=25 card=1)
         1          1          1                         INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=4 pr=3 pw=0 time=20272 us cost=4 size=0 card=273)(object id 407596)
         4          4          4                      BUFFER SORT (cr=8 pr=0 pw=0 time=154 us cost=178 size=5 card=1)
         4          4          4                       TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=151 us cost=3 size=5 card=1)
         4          4          4                        INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=128 us cost=2 size=0 card=1)(object id 160855)
       764        764        764                     TABLE ACCESS BY INDEX ROWID SYS_CALENDAR_D (cr=50 pr=0 pw=0 time=1328 us cost=3 size=1560 card=120)
       764        764        764                      INDEX RANGE SCAN SYS_CALENDAR_DIM_N01 (cr=10 pr=0 pw=0 time=458 us cost=0 size=0 card=215)(object id 15315)
     11943      11943      11943                    TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=12310 pr=22 pw=0 time=44385 us cost=4 size=48 card=1)
     11943      11943      11943                     INDEX RANGE SCAN PAY_REGISTER_F_N05 (cr=420 pr=0 pw=0 time=4927 us cost=3 size=0 card=1)(object id 447234)
     11943      11943      11943                   INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=564 pr=0 pw=0 time=21257 us cost=0 size=0 card=1)(object id 15537)
     11943      11943      11943                  TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=2661 pr=0 pw=0 time=9760 us cost=0 size=16 card=1)
       872        872        872               VIEW  (cr=10536 pr=814 pw=0 time=888779 us cost=237 size=29328 card=48)
       872        872        872                WINDOW NOSORT (cr=10536 pr=814 pw=0 time=888652 us cost=237 size=9840 card=48)
       872        872        872                 SORT GROUP BY (cr=10536 pr=814 pw=0 time=886478 us cost=237 size=9840 card=48)
       872        872        872                  NESTED LOOPS  (cr=10536 pr=814 pw=0 time=559966 us)
       872        872        872                   NESTED LOOPS  (cr=9664 pr=782 pw=0 time=554200 us cost=236 size=9840 card=48)
       872        872        872                    NESTED LOOPS  (cr=7221 pr=755 pw=0 time=541342 us cost=226 size=8736 card=48)
       872        872        872                     NESTED LOOPS  (cr=5554 pr=755 pw=0 time=528992 us cost=215 size=7008 card=48)
       872        872        872                      HASH JOIN  (cr=5545 pr=753 pw=0 time=510432 us cost=204 size=5664 card=48)
       872        872        872                       NESTED LOOPS  (cr=5521 pr=753 pw=0 time=497155 us)
       872        872        872                        NESTED LOOPS  (cr=5520 pr=752 pw=0 time=492481 us cost=197 size=5424 card=48)
       872        872        872                         NESTED LOOPS  (cr=5507 pr=751 pw=0 time=479584 us cost=176 size=3984 card=48)
         4          4          4                          TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=98 us cost=3 size=5 card=1)
         4          4          4                           INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=71 us cost=2 size=0 card=1)(object id 160855)
       872        872        872                          TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5499 pr=751 pw=0 time=487190 us cost=173 size=3042 card=39)
     22468      22468      22468                           INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=91 pr=19 pw=0 time=6083 us cost=3 size=0 card=273)(object id 407596)
       872        872        872                         INDEX RANGE SCAN HCM_COMPANY_D_PK (cr=13 pr=1 pw=0 time=10528 us cost=0 size=0 card=1)(object id 15313)
       872        872        872                        TABLE ACCESS BY INDEX ROWID HCM_COMPANY_D (cr=1 pr=1 pw=0 time=3773 us cost=0 size=30 card=1)
         7          7          7                       VIEW  index$_join$_023 (cr=24 pr=0 pw=0 time=2900 us cost=7 size=35 card=7)
         7          7          7                        HASH JOIN  (cr=24 pr=0 pw=0 time=2898 us)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=17 pr=0 pw=0 time=73 us cost=5 size=35 card=7)(object id 160855)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_PK (cr=7 pr=0 pw=0 time=54 us cost=3 size=35 card=7)(object id 160857)
       872        872        872                      TABLE ACCESS BY INDEX ROWID PAY_PAYGROUP_D (cr=9 pr=2 pw=0 time=13429 us cost=0 size=28 card=1)
       872        872        872                       INDEX RANGE SCAN PAY_PAYGROUP_D_PK (cr=8 pr=1 pw=0 time=9486 us cost=0 size=0 card=1)(object id 15499)
       872        872        872                     TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=1667 pr=0 pw=0 time=5869 us cost=0 size=36 card=1)
       872        872        872                      INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=864 pr=0 pw=0 time=3328 us cost=0 size=0 card=1)(object id 15537)
       872        872        872                    PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=2443 pr=27 pw=0 time=157443 us cost=0 size=0 card=1)
       872        872        872                     INDEX UNIQUE SCAN HCM_PERSON_D_PK PARTITION: KEY KEY (cr=2443 pr=27 pw=0 time=155922 us cost=0 size=0 card=1)(object id 4391000)
       872        872        872                   TABLE ACCESS BY GLOBAL INDEX ROWID HCM_PERSON_D PARTITION: ROW LOCATION ROW LOCATION (cr=872 pr=32 pw=0 time=194145 us cost=0 size=23 card=1)
       872        872        872           INLIST ITERATOR  (cr=6977 pr=0 pw=0 time=23181 us)
       872        872        872            PX PARTITION HASH ITERATOR PARTITION: KEY(INLIST) KEY(INLIST) (cr=6977 pr=0 pw=0 time=21396 us cost=1 size=28 card=1)
       872        872        872             INDEX RANGE SCAN T2_DW_ADMIN_STDRPT_ACCESS_N1 PARTITION: KEY(INLIST) KEY(INLIST) (cr=6977 pr=0 pw=0 time=16930 us cost=1 size=28 card=1)(object id 7065133)
       872        872        872          TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=4042 pr=4 pw=0 time=43405 us cost=3 size=41 card=1)
      2181       2181       2181           INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_N04 (cr=1747 pr=3 pw=0 time=28974 us cost=2 size=0 card=1)(object id 407517)
     28276      28276      28276         PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=3914 pr=27 pw=0 time=341236 us cost=0 size=0 card=1)
     28276      28276      28276          INDEX RANGE SCAN HCM_EMPLOYEE_D_N03 PARTITION: KEY KEY (cr=3914 pr=27 pw=0 time=335053 us cost=0 size=0 card=1)(object id 4391673)
       872        872        872        TABLE ACCESS BY GLOBAL INDEX ROWID HCM_EMPLOYEE_D PARTITION: ROW LOCATION ROW LOCATION (cr=3729 pr=228 pw=0 time=1473703 us cost=0 size=58 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      60        0.00          0.00
  Disk file operations I/O                       32        0.00          0.00
  db file sequential read                      1099        0.02          2.37
  SQL*Net message from client                    60       29.15         98.04
********************************************************************************
Re: improve sql performance (long sql here) [message #649131 is a reply to message #649130] Mon, 14 March 2016 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Reexecute the same thing. Your trace is not representative, most of the time is spent during parsing which should happen only once.
The elapsed time spent to fetch is only 1/5 of the previous one which tends to prove that the problem is outside the query.

Note the time Oracle spent to wait for the client (last line of wait events).

Re: improve sql performance (long sql here) [message #649132 is a reply to message #649131] Mon, 14 March 2016 12:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The parse is slow because dynamic sampling (according to the first EXPLAIN PLAN) is at level 7. Rather high, I thought. But trivial compared to the time the query is spending outside the database.
Re: improve sql performance (long sql here) [message #649136 is a reply to message #649132] Mon, 14 March 2016 13:03 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
If I re-execute the same sql, isn't it going to hit the cache?
Re: improve sql performance (long sql here) [message #649138 is a reply to message #649136] Mon, 14 March 2016 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
marzbuzz wrote on Mon, 14 March 2016 11:03
If I re-execute the same sql, isn't it going to hit the cache?

maybe yes & maybe no

how often is SQL run in Production?
Re: improve sql performance (long sql here) [message #649139 is a reply to message #649136] Mon, 14 March 2016 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe and then?

Re: improve sql performance (long sql here) [message #649140 is a reply to message #649138] Mon, 14 March 2016 13:12 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
In production, it was run with difference company and different invoice, so it will not hit the cache usually. However, when I ran the sql with the trace file, I am testing with the same sql. Should I change it to different invoice so that it will not hit the cache?
Re: improve sql performance (long sql here) [message #649141 is a reply to message #649140] Mon, 14 March 2016 13:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ALTER SYSTEM FLUSH BUFFER_CACHE; might help.
Re: improve sql performance (long sql here) [message #649165 is a reply to message #649141] Tue, 15 March 2016 08:52 Go to previous messageGo to next message
marzbuzz
Messages: 10
Registered: March 2016
Junior Member
I don't have the privilege to flush buffer cache.
I ran the same SQL with different invoices one after another and below is the trace file with wait event information. The elapsed time(35.75, 7.90) is much longer than the one I posted before (3.14) for both SQLs.
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      2.35       2.44          2       4658         38           0
Execute      3      0.00       0.00          0        144          0           0
Fetch      180      2.47      33.30      10254     119384          0        2616
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      186      4.83      35.75      10256     124186         38        2616

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         7          7          7  TABLE ACCESS BY INDEX ROWID BEN_PLAN_TYPE_D (cr=4 pr=1 pw=0 time=6992 us cost=2 size=53 card=1)
         7          7          7   INDEX RANGE SCAN BEN_PLAN_TYPE_D_N03 (cr=3 pr=0 pw=0 time=2660 us cost=1 size=0 card=1)(object id 407480)
       872        872        872  PX COORDINATOR  (cr=44847 pr=3419 pw=0 time=11935125 us)
       872        872        872   PX SEND QC (ORDER) :TQ10002 (cr=44799 pr=3419 pw=0 time=11933733 us cost=593 size=821 card=1)
       872        872        872    SORT ORDER BY (cr=44799 pr=3419 pw=0 time=11933097 us cost=593 size=821 card=1)
       872        872        872     PX RECEIVE  (cr=44795 pr=3418 pw=0 time=11262944 us)
       872        872        872      PX SEND RANGE :TQ10001 (cr=44795 pr=3418 pw=0 time=11262773 us)
       872        872        872       NESTED LOOPS  (cr=44795 pr=3418 pw=0 time=11262354 us)
     28276      28276      28276        NESTED LOOPS  (cr=41066 pr=3263 pw=0 time=9231713 us cost=592 size=821 card=1)
       872        872        872         NESTED LOOPS  (cr=37152 pr=2987 pw=0 time=9242442 us cost=592 size=763 card=1)
       872        872        872          NESTED LOOPS  (cr=33110 pr=2976 pw=0 time=8769132 us cost=591 size=722 card=1)
       872        872        872           BUFFER SORT (cr=26133 pr=2976 pw=0 time=8738525 us)
       872        872        872            PX RECEIVE  (cr=26133 pr=2976 pw=0 time=8737235 us)
       872        872        872             PX SEND BROADCAST :TQ10000 (cr=26133 pr=2976 pw=0 time=8737068 us)
       872        872        872              HASH JOIN  (cr=26133 pr=2976 pw=0 time=8736983 us cost=590 size=694 card=1)
       984        984        984               VIEW  (cr=15598 pr=2328 pw=0 time=7399171 us cost=352 size=664 card=8)
       984        984        984                HASH GROUP BY (cr=15598 pr=2328 pw=0 time=7398881 us cost=352 size=944 card=8)
     11943      11943      11943                 NESTED LOOPS  (cr=15598 pr=2328 pw=0 time=5259381 us)
     11943      11943      11943                  NESTED LOOPS  (cr=12937 pr=2328 pw=0 time=5238575 us cost=351 size=944 card=8)
     11943      11943      11943                   NESTED LOOPS  (cr=12373 pr=2328 pw=0 time=5196418 us cost=350 size=816 card=8)
       764        764        764                    NESTED LOOPS  (cr=63 pr=5 pw=0 time=34758 us cost=181 size=7992 card=148)
         4          4          4                     MERGE JOIN CARTESIAN (cr=13 pr=5 pw=0 time=32440 us cost=178 size=41 card=1)
         1          1          1                      VIEW  (cr=5 pr=3 pw=0 time=22841 us cost=174 size=36 card=1)
         1          1          1                       COUNT STOPKEY (cr=5 pr=3 pw=0 time=22810 us)
         1          1          1                        TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5 pr=3 pw=0 time=22795 us cost=174 size=25 card=1)
         1          1          1                         INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=4 pr=2 pw=0 time=18826 us cost=4 size=0 card=273)(object id 

407596)
         4          4          4                      BUFFER SORT (cr=8 pr=2 pw=0 time=9605 us cost=178 size=5 card=1)
         4          4          4                       TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=2 pw=0 time=10933 us cost=3 size=5 card=1)
         4          4          4                        INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=2 pw=0 time=10908 us cost=2 size=0 card=1)(object id 

160855)
       764        764        764                     TABLE ACCESS BY INDEX ROWID SYS_CALENDAR_D (cr=50 pr=0 pw=0 time=1973 us cost=3 size=1560 card=120)
       764        764        764                      INDEX RANGE SCAN SYS_CALENDAR_DIM_N01 (cr=10 pr=0 pw=0 time=674 us cost=0 size=0 card=215)(object id 

15315)
     11943      11943      11943                    TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=12310 pr=2323 pw=0 time=8167504 us cost=4 size=48 card=1)
     11943      11943      11943                     INDEX RANGE SCAN PAY_REGISTER_F_N05 (cr=420 pr=39 pw=0 time=24186 us cost=3 size=0 card=1)(object id 

447234)
     11943      11943      11943                   INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=564 pr=0 pw=0 time=33815 us cost=0 size=0 card=1)(object id 

15537)
     11943      11943      11943                  TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=2661 pr=0 pw=0 time=13248 us cost=0 size=16 card=1)
       872        872        872               VIEW  (cr=10535 pr=648 pw=0 time=1328921 us cost=237 size=29328 card=48)
       872        872        872                WINDOW NOSORT (cr=10535 pr=648 pw=0 time=1328749 us cost=237 size=9840 card=48)
       872        872        872                 SORT GROUP BY (cr=10535 pr=648 pw=0 time=1326728 us cost=237 size=9840 card=48)
       872        872        872                  NESTED LOOPS  (cr=10535 pr=648 pw=0 time=1435014 us)
       872        872        872                   NESTED LOOPS  (cr=9663 pr=594 pw=0 time=955506 us cost=236 size=9840 card=48)
       872        872        872                    NESTED LOOPS  (cr=7220 pr=526 pw=0 time=470742 us cost=226 size=8736 card=48)
       872        872        872                     NESTED LOOPS  (cr=5553 pr=526 pw=0 time=461245 us cost=215 size=7008 card=48)
       872        872        872                      HASH JOIN  (cr=5544 pr=524 pw=0 time=444390 us cost=204 size=5664 card=48)
       872        872        872                       NESTED LOOPS  (cr=5520 pr=521 pw=0 time=429197 us)
       872        872        872                        NESTED LOOPS  (cr=5519 pr=521 pw=0 time=426924 us cost=197 size=5424 card=48)
       872        872        872                         NESTED LOOPS  (cr=5506 pr=520 pw=0 time=412816 us cost=176 size=3984 card=48)
         4          4          4                          TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=102 us cost=3 size=5 card=1)
         4          4          4                           INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=75 us cost=2 size=0 card=1)(object id 

160855)
       872        872        872                          TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5498 pr=520 pw=0 time=420455 us cost=173 size=3042 

card=39)
     22468      22468      22468                           INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=90 pr=13 pw=0 time=5255 us cost=3 size=0 card=273)(object id 

407596)
       872        872        872                         INDEX RANGE SCAN HCM_COMPANY_D_PK (cr=13 pr=1 pw=0 time=11458 us cost=0 size=0 card=1)(object id 15313)
       872        872        872                        TABLE ACCESS BY INDEX ROWID HCM_COMPANY_D (cr=1 pr=0 pw=0 time=1573 us cost=0 size=30 card=1)
         7          7          7                       VIEW  index$_join$_023 (cr=24 pr=3 pw=0 time=4598 us cost=7 size=35 card=7)
         7          7          7                        HASH JOIN  (cr=24 pr=3 pw=0 time=4596 us)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=17 pr=2 pw=0 time=64 us cost=5 size=35 card=7)

(object id 160855)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_PK (cr=7 pr=1 pw=0 time=168 us cost=3 size=35 card=7)(object 

id 160857)
       872        872        872                      TABLE ACCESS BY INDEX ROWID PAY_PAYGROUP_D (cr=9 pr=2 pw=0 time=14177 us cost=0 size=28 card=1)
       872        872        872                       INDEX RANGE SCAN PAY_PAYGROUP_D_PK (cr=8 pr=1 pw=0 time=10013 us cost=0 size=0 card=1)(object id 15499)
       872        872        872                     TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=1667 pr=0 pw=0 time=6970 us cost=0 size=36 card=1)
       872        872        872                      INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=864 pr=0 pw=0 time=3911 us cost=0 size=0 card=1)(object id 

15537)
       872        872        872                    PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=2443 pr=68 pw=0 time=515623 us cost=0 size=0 card=1)
       872        872        872                     INDEX UNIQUE SCAN HCM_PERSON_D_PK PARTITION: KEY KEY (cr=2443 pr=68 pw=0 time=513903 us cost=0 size=0 

card=1)(object id 4391000)
       872        872        872                   TABLE ACCESS BY GLOBAL INDEX ROWID HCM_PERSON_D PARTITION: ROW LOCATION ROW LOCATION (cr=872 pr=54 pw=0 

time=338787 us cost=0 size=23 card=1)
       872        872        872           INLIST ITERATOR  (cr=6977 pr=0 pw=0 time=25487 us)
       872        872        872            PX PARTITION HASH ITERATOR PARTITION: KEY(INLIST) KEY(INLIST) (cr=6977 pr=0 pw=0 time=23453 us cost=1 size=28 

card=1)
       872        872        872             INDEX RANGE SCAN T2_DW_ADMIN_STDRPT_ACCESS_N1 PARTITION: KEY(INLIST) KEY(INLIST) (cr=6977 pr=0 pw=0 time=18632 us 

cost=1 size=28 card=1)(object id 7065133)
       872        872        872          TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=4042 pr=11 pw=0 time=99245 us cost=3 size=41 card=1)
      2181       2181       2181           INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_N04 (cr=1747 pr=5 pw=0 time=38235 us cost=2 size=0 card=1)(object id 407517)
     28276      28276      28276         PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=3914 pr=276 pw=0 time=3236053 us cost=0 size=0 card=1)
     28276      28276      28276          INDEX RANGE SCAN HCM_EMPLOYEE_D_N03 PARTITION: KEY KEY (cr=3914 pr=276 pw=0 time=3229625 us cost=0 size=0 card=1)

(object id 4391673)
       872        872        872        TABLE ACCESS BY GLOBAL INDEX ROWID HCM_EMPLOYEE_D PARTITION: ROW LOCATION ROW LOCATION (cr=3729 pr=155 pw=0 time=1251493 

us cost=0 size=58 card=1)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      7   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
              'BEN_PLAN_TYPE_D' (TABLE)
      7    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'BEN_PLAN_TYPE_D_N03' 
               (INDEX)
    872   PX COORDINATOR
    872    PX SEND (QC (ORDER)) OF ':TQ10002' [:Q1002]
    872     SORT (ORDER BY) [:Q1002]
    872      PX RECEIVE [:Q1002]
    872       PX SEND (RANGE) OF ':TQ10001' [:Q1001]
    872        NESTED LOOPS [:Q1001]
  28276         NESTED LOOPS [:Q1001]
    872          NESTED LOOPS [:Q1001]
    872           NESTED LOOPS [:Q1001]
    872            BUFFER (SORT) [:Q1001]
    872             PX RECEIVE [:Q1001]
    872              PX SEND (BROADCAST) OF ':TQ10000'
    872               HASH JOIN
    984                VIEW
    984                 HASH (GROUP BY)
  11943                  NESTED LOOPS
  11943                   NESTED LOOPS
  11943                    NESTED LOOPS
    764                     NESTED LOOPS
      4                      MERGE JOIN (CARTESIAN)
      1                       VIEW
      1                        COUNT (STOPKEY)
      1                         TABLE ACCESS   MODE: 
                                  ANALYZED (BY INDEX ROWID) OF 
                                    'PAY_REGISTER_F' (TABLE)
      1                          INDEX   MODE: 
                                   ANALYZED (RANGE SCAN) OF 
                                     'PAY_REGISTER_F_N04' (INDEX)
      4                       BUFFER (SORT)
      4                        TABLE ACCESS   MODE: 
                                 ANALYZED (BY INDEX ROWID) OF 
                                   'PAY_DEDUCTION_CLASS_D' (TABLE)
      4                         INDEX   MODE: 
                                  ANALYZED (RANGE SCAN) OF 
                                    'PAY_DEDUCTION_CLASS_D_N01' (INDEX)
    764                      TABLE ACCESS   MODE: 
                               ANALYZED (BY INDEX ROWID) OF 
                                 'SYS_CALENDAR_D' (TABLE)
    764                       INDEX   MODE: ANALYZED 
                                (RANGE SCAN) OF 'SYS_CALENDAR_DIM_N01' 
                                  (INDEX)
  11943                     TABLE ACCESS   MODE: 
                              ANALYZED (BY INDEX ROWID) OF 'PAY_REGISTER_F' 
                                (TABLE)
  11943                      INDEX   MODE: ANALYZED 
                               (RANGE SCAN) OF 'PAY_REGISTER_F_N05' (INDEX)

  11943                    INDEX   MODE: ANALYZED (RANGE 
                               SCAN) OF 'PAY_PAYROLL_REGISTER_D_PK' (INDEX)
  11943                   TABLE ACCESS   MODE: ANALYZED 
                            (BY INDEX ROWID) OF 'PAY_PAYROLL_REGISTER_D' 
                              (TABLE)
    872                VIEW
    872                 WINDOW (NOSORT)
    872                  SORT (GROUP BY)
    872                   NESTED LOOPS
    872                    NESTED LOOPS
    872                     NESTED LOOPS
    872                      NESTED LOOPS
    872                       HASH JOIN
    872                        NESTED LOOPS
    872                         NESTED LOOPS
    872                          NESTED LOOPS
      4                           TABLE ACCESS   
                                    MODE: ANALYZED (BY INDEX ROWID) OF 
                                      'PAY_DEDUCTION_CLASS_D' (TABLE)
      4                            INDEX   MODE: 
                                     ANALYZED (RANGE SCAN) OF 
                                       'PAY_DEDUCTION_CLASS_D_N01' (INDEX)
    872                           TABLE ACCESS   
                                    MODE: ANALYZED (BY INDEX ROWID) OF 
                                      'PAY_REGISTER_F' (TABLE)
  22468                            INDEX   MODE: 
                                     ANALYZED (RANGE SCAN) OF 
                                       'PAY_REGISTER_F_N04' (INDEX)
    872                          INDEX   MODE: 
                                   ANALYZED (RANGE SCAN) OF 
                                     'HCM_COMPANY_D_PK' (INDEX)
    872                         TABLE ACCESS   MODE: 
                                  ANALYZED (BY INDEX ROWID) OF 
                                    'HCM_COMPANY_D' (TABLE)
      7                        VIEW OF 
                                   'index$_join$_023' (VIEW)
      7                         HASH JOIN
      7                          INDEX   MODE: 
                                   ANALYZED (FAST FULL SCAN) OF 
                                     'PAY_DEDUCTION_CLASS_D_N01' (INDEX)
      7                          INDEX   MODE: 
                                   ANALYZED (FAST FULL SCAN) OF 
                                   'PAY_DEDUCTION_CLASS_D_PK' (INDEX 
                                     (UNIQUE))
    872                       TABLE ACCESS   MODE: 
                                ANALYZED (BY INDEX ROWID) OF 
                                  'PAY_PAYGROUP_D' (TABLE)
    872                        INDEX   MODE: ANALYZED 
                                 (RANGE SCAN) OF 'PAY_PAYGROUP_D_PK' (INDEX)

    872                      TABLE ACCESS   MODE: 
                               ANALYZED (BY INDEX ROWID) OF 
                                 'PAY_PAYROLL_REGISTER_D' (TABLE)
    872                       INDEX   MODE: ANALYZED 
                                (RANGE SCAN) OF 'PAY_PAYROLL_REGISTER_D_PK' 
                                  (INDEX)
    872                     PARTITION HASH (ITERATOR) 
                                PARTITION:KEYKEY
    872                      INDEX   MODE: ANALYZED 
                               (UNIQUE SCAN) OF 'HCM_PERSON_D_PK' (INDEX 
                                 (UNIQUE)) PARTITION:KEYKEY
    872                    TABLE ACCESS   MODE: ANALYZED 
                             (BY GLOBAL INDEX ROWID) OF 'HCM_PERSON_D' 
                               (TABLE) PARTITION:ROW LOCATION
    872            INLIST ITERATOR [:Q1001]
    872             PX PARTITION HASH (ITERATOR) [:Q1001] 
                        PARTITION:KEY(INLIST)KEY(INLIST)
    872              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                       'T2_DW_ADMIN_STDRPT_ACCESS_N1' (INDEX (UNIQUE)) 
                         [:Q1001] PARTITION:KEY(INLIST)KEY(INLIST)
    872           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 
                      OF 'PAY_PAYROLL_REGISTER_D' (TABLE) [:Q1001]
   2181            INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                       'PAY_PAYROLL_REGISTER_D_N04' (INDEX) [:Q1001]
  28276          PARTITION HASH (ITERATOR) [:Q1001] 
                     PARTITION:KEYKEY
  28276           INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                      'HCM_EMPLOYEE_D_N03' (INDEX) [:Q1001] PARTITION:KEYKEY
    872         TABLE ACCESS   MODE: ANALYZED (BY GLOBAL INDEX ROWID)
                   OF 'HCM_EMPLOYEE_D' (TABLE) [:Q1001] PARTITION:ROW 
                    LOCATION


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     180        0.00          0.00
  Disk file operations I/O                       59        0.00          0.00
  db file sequential read                     10249        0.09         33.40
  SQL*Net message from client                   180       64.21        435.23
  db file scattered read                          2        0.00          0.00
********************************************************************************







------------------Ran the same sql with different invoice right away------------------------




call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.27       0.27          0        108          0           0
Execute      1      0.00       0.00          0         48          0           0
Fetch       60      0.82       7.62       3826      50908          0         878
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       62      1.09       7.90       3826      51064          0         878

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         8          8          8  TABLE ACCESS BY INDEX ROWID BEN_PLAN_TYPE_D (cr=4 pr=0 pw=0 time=111 us cost=2 size=53 card=1)
         8          8          8   INDEX RANGE SCAN BEN_PLAN_TYPE_D_N03 (cr=3 pr=0 pw=0 time=77 us cost=1 size=0 card=1)(object id 407480)
       878        878        878  PX COORDINATOR  (cr=54468 pr=3826 pw=0 time=7738912 us)
       878        878        878   PX SEND QC (ORDER) :TQ10002 (cr=54420 pr=3826 pw=0 time=7737951 us cost=593 size=821 card=1)
       878        878        878    SORT ORDER BY (cr=54420 pr=3826 pw=0 time=7737070 us cost=593 size=821 card=1)
       878        878        878     PX RECEIVE  (cr=54416 pr=3826 pw=0 time=8182217 us)
       878        878        878      PX SEND RANGE :TQ10001 (cr=54416 pr=3826 pw=0 time=8181962 us)
       878        878        878       NESTED LOOPS  (cr=54416 pr=3826 pw=0 time=8181583 us)
     28293      28293      28293        NESTED LOOPS  (cr=50661 pr=3774 pw=0 time=7005588 us cost=592 size=821 card=1)
       878        878        878         NESTED LOOPS  (cr=46739 pr=3766 pw=0 time=6968123 us cost=592 size=763 card=1)
       878        878        878          NESTED LOOPS  (cr=42666 pr=3765 pw=0 time=6950767 us cost=591 size=722 card=1)
       878        878        878           BUFFER SORT (cr=35641 pr=3765 pw=0 time=6921414 us)
       878        878        878            PX RECEIVE  (cr=35641 pr=3765 pw=0 time=6920292 us)
       878        878        878             PX SEND BROADCAST :TQ10000 (cr=35641 pr=3765 pw=0 time=6920292 us)
       878        878        878              HASH JOIN  (cr=35641 pr=3765 pw=0 time=6920291 us cost=590 size=694 card=1)
      1001       1001       1001               VIEW  (cr=18989 pr=1851 pw=0 time=5695487 us cost=353 size=747 card=9)
      1001       1001       1001                HASH GROUP BY (cr=18989 pr=1851 pw=0 time=5695359 us cost=353 size=1062 card=9)
     14570      14570      14570                 NESTED LOOPS  (cr=18989 pr=1851 pw=0 time=2642461 us)
     14570      14570      14570                  NESTED LOOPS  (cr=15754 pr=1851 pw=0 time=2621751 us cost=352 size=1062 card=9)
     14570      14570      14570                   NESTED LOOPS  (cr=15072 pr=1851 pw=0 time=2578800 us cost=350 size=918 card=9)
       932        932        932                    NESTED LOOPS  (cr=67 pr=4 pw=0 time=44801 us cost=181 size=7992 card=148)
         4          4          4                     MERGE JOIN CARTESIAN (cr=13 pr=4 pw=0 time=43384 us cost=178 size=41 card=1)
         1          1          1                      VIEW  (cr=5 pr=4 pw=0 time=43252 us cost=174 size=36 card=1)
         1          1          1                       COUNT STOPKEY (cr=5 pr=4 pw=0 time=43236 us)
         1          1          1                        TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5 pr=4 pw=0 time=43223 us cost=174 size=25 card=1)
         1          1          1                         INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=4 pr=3 pw=0 time=37674 us cost=4 size=0 card=273)(object id 

407596)
         4          4          4                      BUFFER SORT (cr=8 pr=0 pw=0 time=135 us cost=178 size=5 card=1)
         4          4          4                       TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=125 us cost=3 size=5 card=1)
         4          4          4                        INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=97 us cost=2 size=0 card=1)(object id 

160855)
       932        932        932                     TABLE ACCESS BY INDEX ROWID SYS_CALENDAR_D (cr=54 pr=0 pw=0 time=1080 us cost=3 size=1560 card=120)
       932        932        932                      INDEX RANGE SCAN SYS_CALENDAR_DIM_N01 (cr=10 pr=0 pw=0 time=129 us cost=0 size=0 card=215)(object id 

15315)
     14570      14570      14570                    TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=15005 pr=1847 pw=0 time=4631295 us cost=4 size=48 card=1)
     14570      14570      14570                     INDEX RANGE SCAN PAY_REGISTER_F_N05 (cr=490 pr=22 pw=0 time=8088 us cost=3 size=0 card=1)(object id 447234)
     14570      14570      14570                   INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=682 pr=0 pw=0 time=35461 us cost=0 size=0 card=1)(object id 

15537)
     14570      14570      14570                  TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=3235 pr=0 pw=0 time=14429 us cost=0 size=16 card=1)
       878        878        878               VIEW  (cr=16652 pr=1914 pw=0 time=1217360 us cost=237 size=29328 card=48)
       878        878        878                WINDOW NOSORT (cr=16652 pr=1914 pw=0 time=1217106 us cost=237 size=9840 card=48)
       878        878        878                 SORT GROUP BY (cr=16652 pr=1914 pw=0 time=1215309 us cost=237 size=9840 card=48)
       878        878        878                  NESTED LOOPS  (cr=16652 pr=1914 pw=0 time=1094588 us)
       878        878        878                   NESTED LOOPS  (cr=15774 pr=1909 pw=0 time=1089799 us cost=236 size=9840 card=48)
       878        878        878                    NESTED LOOPS  (cr=13228 pr=1900 pw=0 time=1077603 us cost=226 size=8736 card=48)
       878        878        878                     NESTED LOOPS  (cr=11515 pr=1900 pw=0 time=1068522 us cost=215 size=7008 card=48)
       878        878        878                      HASH JOIN  (cr=11506 pr=1898 pw=0 time=1032421 us cost=204 size=5664 card=48)
       878        878        878                       NESTED LOOPS  (cr=11482 pr=1898 pw=0 time=955377 us)
       878        878        878                        NESTED LOOPS  (cr=11481 pr=1898 pw=0 time=953989 us cost=197 size=5424 card=48)
       878        878        878                         NESTED LOOPS  (cr=11468 pr=1898 pw=0 time=950712 us cost=176 size=3984 card=48)
         4          4          4                          TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=135 us cost=3 size=5 card=1)
         4          4          4                           INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=112 us cost=2 size=0 card=1)(object 

id 160855)
       878        878        878                          TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=11460 pr=1898 pw=0 time=967294 us cost=173 size=3042 

card=39)
     22300      22300      22300                           INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=108 pr=23 pw=0 time=5479 us cost=3 size=0 card=273)(object id 

407596)
       878        878        878                         INDEX RANGE SCAN HCM_COMPANY_D_PK (cr=13 pr=0 pw=0 time=2872 us cost=0 size=0 card=1)(object id 15313)
       878        878        878                        TABLE ACCESS BY INDEX ROWID HCM_COMPANY_D (cr=1 pr=0 pw=0 time=508 us cost=0 size=30 card=1)
         7          7          7                       VIEW  index$_join$_023 (cr=24 pr=0 pw=0 time=3728 us cost=7 size=35 card=7)
         7          7          7                        HASH JOIN  (cr=24 pr=0 pw=0 time=3713 us)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=17 pr=0 pw=0 time=65 us cost=5 size=35 card=7)

(object id 160855)
         7          7          7                         INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_PK (cr=7 pr=0 pw=0 time=23 us cost=3 size=35 card=7)(object 

id 160857)
       878        878        878                      TABLE ACCESS BY INDEX ROWID PAY_PAYGROUP_D (cr=9 pr=2 pw=0 time=33681 us cost=0 size=28 card=1)
       878        878        878                       INDEX RANGE SCAN PAY_PAYGROUP_D_PK (cr=8 pr=1 pw=0 time=18184 us cost=0 size=0 card=1)(object id 15499)
       878        878        878                     TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=1713 pr=0 pw=0 time=6426 us cost=0 size=36 card=1)
       878        878        878                      INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=882 pr=0 pw=0 time=3603 us cost=0 size=0 card=1)(object id 

15537)
       878        878        878                    PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=2546 pr=9 pw=0 time=93768 us cost=0 size=0 card=1)
       878        878        878                     INDEX UNIQUE SCAN HCM_PERSON_D_PK PARTITION: KEY KEY (cr=2546 pr=9 pw=0 time=92080 us cost=0 size=0 

card=1)(object id 4391000)
       878        878        878                   TABLE ACCESS BY GLOBAL INDEX ROWID HCM_PERSON_D PARTITION: ROW LOCATION ROW LOCATION (cr=878 pr=5 pw=0 

time=42977 us cost=0 size=23 card=1)
       878        878        878           INLIST ITERATOR  (cr=7025 pr=0 pw=0 time=26233 us)
       878        878        878            PX PARTITION HASH ITERATOR PARTITION: KEY(INLIST) KEY(INLIST) (cr=7025 pr=0 pw=0 time=24138 us cost=1 size=28 

card=1)
       878        878        878             INDEX RANGE SCAN T2_DW_ADMIN_STDRPT_ACCESS_N1 PARTITION: KEY(INLIST) KEY(INLIST) (cr=7025 pr=0 pw=0 time=18738 us 

cost=1 size=28 card=1)(object id 7065133)
       878        878        878          TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=4073 pr=1 pw=0 time=20924 us cost=3 size=41 card=1)
      2196       2196       2196           INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_N04 (cr=1760 pr=1 pw=0 time=11369 us cost=2 size=0 card=1)(object id 407517)
     28293      28293      28293         PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=3922 pr=8 pw=0 time=154119 us cost=0 size=0 card=1)
     28293      28293      28293          INDEX RANGE SCAN HCM_EMPLOYEE_D_N03 PARTITION: KEY KEY (cr=3922 pr=8 pw=0 time=148069 us cost=0 size=0 card=1)(object 

id 4391673)
       878        878        878        TABLE ACCESS BY GLOBAL INDEX ROWID HCM_EMPLOYEE_D PARTITION: ROW LOCATION ROW LOCATION (cr=3755 pr=52 pw=0 time=643393 

us cost=0 size=58 card=1)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      8   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
              'BEN_PLAN_TYPE_D' (TABLE)
      8    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'BEN_PLAN_TYPE_D_N03' 
               (INDEX)
    878   PX COORDINATOR
    878    PX SEND (QC (ORDER)) OF ':TQ10002' [:Q1002]
    878     SORT (ORDER BY) [:Q1002]
    878      PX RECEIVE [:Q1002]
    878       PX SEND (RANGE) OF ':TQ10001' [:Q1001]
    878        NESTED LOOPS [:Q1001]
  28293         NESTED LOOPS [:Q1001]
    878          NESTED LOOPS [:Q1001]
    878           NESTED LOOPS [:Q1001]
    878            BUFFER (SORT) [:Q1001]
    878             PX RECEIVE [:Q1001]
    878              PX SEND (BROADCAST) OF ':TQ10000'
    878               HASH JOIN
   1001                VIEW
   1001                 HASH (GROUP BY)
  14570                  NESTED LOOPS
  14570                   NESTED LOOPS
  14570                    NESTED LOOPS
    932                     NESTED LOOPS
      4                      MERGE JOIN (CARTESIAN)
      1                       VIEW
      1                        COUNT (STOPKEY)
      1                         TABLE ACCESS   MODE: 
                                  ANALYZED (BY INDEX ROWID) OF 
                                    'PAY_REGISTER_F' (TABLE)
      1                          INDEX   MODE: 
                                   ANALYZED (RANGE SCAN) OF 
                                     'PAY_REGISTER_F_N04' (INDEX)
      4                       BUFFER (SORT)
      4                        TABLE ACCESS   MODE: 
                                 ANALYZED (BY INDEX ROWID) OF 
                                   'PAY_DEDUCTION_CLASS_D' (TABLE)
      4                         INDEX   MODE: 
                                  ANALYZED (RANGE SCAN) OF 
                                    'PAY_DEDUCTION_CLASS_D_N01' (INDEX)
    932                      TABLE ACCESS   MODE: 
                               ANALYZED (BY INDEX ROWID) OF 
                                 'SYS_CALENDAR_D' (TABLE)
    932                       INDEX   MODE: ANALYZED 
                                (RANGE SCAN) OF 'SYS_CALENDAR_DIM_N01' 
                                  (INDEX)
  14570                     TABLE ACCESS   MODE: 
                              ANALYZED (BY INDEX ROWID) OF 'PAY_REGISTER_F' 
                                (TABLE)
  14570                      INDEX   MODE: ANALYZED 
                               (RANGE SCAN) OF 'PAY_REGISTER_F_N05' (INDEX)

  14570                    INDEX   MODE: ANALYZED (RANGE 
                               SCAN) OF 'PAY_PAYROLL_REGISTER_D_PK' (INDEX)
  14570                   TABLE ACCESS   MODE: ANALYZED 
                            (BY INDEX ROWID) OF 'PAY_PAYROLL_REGISTER_D' 
                              (TABLE)
    878                VIEW
    878                 WINDOW (NOSORT)
    878                  SORT (GROUP BY)
    878                   NESTED LOOPS
    878                    NESTED LOOPS
    878                     NESTED LOOPS
    878                      NESTED LOOPS
    878                       HASH JOIN
    878                        NESTED LOOPS
    878                         NESTED LOOPS
    878                          NESTED LOOPS
      4                           TABLE ACCESS   
                                    MODE: ANALYZED (BY INDEX ROWID) OF 
                                      'PAY_DEDUCTION_CLASS_D' (TABLE)
      4                            INDEX   MODE: 
                                     ANALYZED (RANGE SCAN) OF 
                                       'PAY_DEDUCTION_CLASS_D_N01' (INDEX)
    878                           TABLE ACCESS   
                                    MODE: ANALYZED (BY INDEX ROWID) OF 
                                      'PAY_REGISTER_F' (TABLE)
  22300                            INDEX   MODE: 
                                     ANALYZED (RANGE SCAN) OF 
                                       'PAY_REGISTER_F_N04' (INDEX)
    878                          INDEX   MODE: 
                                   ANALYZED (RANGE SCAN) OF 
                                     'HCM_COMPANY_D_PK' (INDEX)
    878                         TABLE ACCESS   MODE: 
                                  ANALYZED (BY INDEX ROWID) OF 
                                    'HCM_COMPANY_D' (TABLE)
      7                        VIEW OF 
                                   'index$_join$_023' (VIEW)
      7                         HASH JOIN
      7                          INDEX   MODE: 
                                   ANALYZED (FAST FULL SCAN) OF 
                                     'PAY_DEDUCTION_CLASS_D_N01' (INDEX)
      7                          INDEX   MODE: 
                                   ANALYZED (FAST FULL SCAN) OF 
                                   'PAY_DEDUCTION_CLASS_D_PK' (INDEX 
                                     (UNIQUE))
    878                       TABLE ACCESS   MODE: 
                                ANALYZED (BY INDEX ROWID) OF 
                                  'PAY_PAYGROUP_D' (TABLE)
    878                        INDEX   MODE: ANALYZED 
                                 (RANGE SCAN) OF 'PAY_PAYGROUP_D_PK' (INDEX)

    878                      TABLE ACCESS   MODE: 
                               ANALYZED (BY INDEX ROWID) OF 
                                 'PAY_PAYROLL_REGISTER_D' (TABLE)
    878                       INDEX   MODE: ANALYZED 
                                (RANGE SCAN) OF 'PAY_PAYROLL_REGISTER_D_PK' 
                                  (INDEX)
    878                     PARTITION HASH (ITERATOR) 
                                PARTITION:KEYKEY
    878                      INDEX   MODE: ANALYZED 
                               (UNIQUE SCAN) OF 'HCM_PERSON_D_PK' (INDEX 
                                 (UNIQUE)) PARTITION:KEYKEY
    878                    TABLE ACCESS   MODE: ANALYZED 
                             (BY GLOBAL INDEX ROWID) OF 'HCM_PERSON_D' 
                               (TABLE) PARTITION:ROW LOCATION
    878            INLIST ITERATOR [:Q1001]
    878             PX PARTITION HASH (ITERATOR) [:Q1001] 
                        PARTITION:KEY(INLIST)KEY(INLIST)
    878              INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                       'T2_DW_ADMIN_STDRPT_ACCESS_N1' (INDEX (UNIQUE)) 
                         [:Q1001] PARTITION:KEY(INLIST)KEY(INLIST)
    878           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 
                      OF 'PAY_PAYROLL_REGISTER_D' (TABLE) [:Q1001]
   2196            INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                       'PAY_PAYROLL_REGISTER_D_N04' (INDEX) [:Q1001]
  28293          PARTITION HASH (ITERATOR) [:Q1001] 
                     PARTITION:KEYKEY
  28293           INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                      'HCM_EMPLOYEE_D_N03' (INDEX) [:Q1001] PARTITION:KEYKEY
    878         TABLE ACCESS   MODE: ANALYZED (BY GLOBAL INDEX ROWID)
                   OF 'HCM_EMPLOYEE_D' (TABLE) [:Q1001] PARTITION:ROW 
                    LOCATION


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      60        0.00          0.00
  db file sequential read                      3826        0.05          6.84
  Disk file operations I/O                        2        0.00          0.00
  SQL*Net message from client                    60       21.05        246.06
********************************************************************************

Re: improve sql performance (long sql here) [message #649168 is a reply to message #649165] Tue, 15 March 2016 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The elapsed time(35.75, 7.90) is much longer than the one I posted before (3.14) for both SQLs.


You now retrieve much more rows and this requires much more IO (logical and physical).
As I said (3 times), if you want to decrease the most important event (SQL*Net message from client) you have to increase your array fetching.

And As we also already said, the problem is NOT in the database, it is outside it.

Re: improve sql performance (long sql here) [message #649317 is a reply to message #649168] Tue, 22 March 2016 09:22 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Can you run it without PARALLEL and post the results?
Previous Topic: Long running Query
Next Topic: dynamic sql
Goto Forum:
  


Current Time: Thu Mar 28 08:51:23 CDT 2024