Skip to main content

solving oracle performance issue by using dba_hist_active_sess_history.sql_plan_line_id

 Problem : batch job run duration doubled from ~10 hours to ~20 hours


Diag method : AWR, ASH, SQLSTAT


Finding 1 : from AWR, easy to find out the time consumed mostly by sql_id='ahp911trpvs2w'

Finding 2 : this sql_id is using same query plan 645295920, but performance varies.

Finding 3 : ASH indicates that usually elapse time/execution is normally 0.1 seconds as in date 20210308, but jump up to 10 seconds on 20210505       , 


09:09:45 jsun> @sqlid_exec_history_sum

Enter value for sql_id: ahp911trpvs2w


SQL_ID              PLAN RUNDATE         EXEC START_TIME     END_TIME           BUFFER   PHY_READ     cpu(s)     elp(s)       READ     IOWAIT     DIRECT BUFFER_PER_EXEC ELPS_PER_EXEC

------------- ---------- --------- ---------- -------------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- -------------

ahp911trpvs2w  645295920 20210308         529 1930           0000             22125368          0         91         98      13508    6993203          0      41824.8922    .185255198

ahp911trpvs2w  645295920 20210309       45630 0000           2300           1433734616   88334336       5709       6104     689291  410561497          0       31420.877    .133771641

ahp911trpvs2w  645295920 20210310        7326 0000           2330            262701130   17620992       1096       1110      20703   14546299          0      35858.7401    .151515152

ahp911trpvs2w  645295920 20210311        5503 0000           0000            194456283    8126464        810        844     109591   35723828          0      35336.4134    .153370889

ahp911trpvs2w  645295920 20210312        6071 0030           2200            239443870   45424640        986       1021     159684   36923677          0      39440.5979    .168176577

ahp911trpvs2w  645295920 20210313          34 0230           0300              1495611   58253312          7         18       7111   11453061          0      43988.5588    .529411765

ahp911trpvs2w 1079470532 20210313        2663 0500           0000             89457091 1301544960        374        421     158880   47883707          0      33592.5989    .158092377

ahp911trpvs2w 1079470532 20210314        2954 0000           2330             78318698  958070784        327        358     116952   33013588          0      26512.7617    .121191605

ahp911trpvs2w 1079470532 20210315        4113 0000           0000            158012562   64233472        659        721     261962   65525300          0      38417.8366    .175297836

ahp911trpvs2w 1079470532 20210316        4747 0000           2330            192403562          0        797        888     262848   95125408          0       40531.612    .187065515

ahp911trpvs2w 1079470532 20210317        6734 0000           2300            267850371          0       1120       1273     296503  158393425          0      39775.8199    .189040689

ahp911trpvs2w 1079470532 20210318      142510 0100           0000           3876042188          0      15588      16162    1038416  592984315          0      27198.3874    .113409585

ahp911trpvs2w 1079470532 20210319       43446 0030           0000           8057441971          0      35385      55434     369871  617870737          0      185458.776    1.27592874

ahp911trpvs2w 1079470532 20210320       38528 0000           2300           6621663041    2662400      32534      57683     651229 1093023183          0      171866.254    1.49717089

ahp911trpvs2w 1079470532 20210321         799 0100           1300             37287639          0        161        208      72686   48254345          0      46667.8836    .260325407

ahp911trpvs2w  645295920 20210321        2648 1300           0000           2446316697  274554880       3428       3524     135105   98167747          0      923835.611    1.33081571

ahp911trpvs2w  645295920 20210322       31667 0000           0000           1967101014          0       6391       6717     837954  338541310          0      62118.3255    .212113557

ahp911trpvs2w  645295920 20210323       70188 0000           0000           3269468966          0       8883       8984     105988  104275328          0      46581.5947    .127999088

ahp911trpvs2w  645295920 20210324        6671 0000           0000            240071211          0        991        994       2433    3793548          0      35987.2899    .149003148

ahp911trpvs2w  645295920 20210325       44728 0000           0000           1409034389          0       5649       5906     784608  272326825          0      31502.2891    .132042568

ahp911trpvs2w  645295920 20210326        7234 0000           2300            242964993          0        996       1025      60669   29438303          0      33586.5348     .14169201

ahp911trpvs2w  645295920 20210327          62 0030           0000              3004304          0         13         17       8944    4542290          0      48456.5161    .274193548

ahp911trpvs2w 1079470532 20210327        2360 0400           0000             89159733 1626193920        372        549     446013  183424530          0      37779.5479    .232627119

ahp911trpvs2w 1079470532 20210328        1898 0000           0000             85179562  974266368        363        500     274040  140702595          0       44878.589    .263435195

ahp911trpvs2w 1079470532 20210329        4743 0000           2130            189607887  199204864        794       1007     489136  221015556          0      39976.3624    .212312882

ahp911trpvs2w 1079470532 20210330        7045 0230           0000            281145913 7447224320       1150       1511     989051  375469781          0      39907.1559    .214478353

ahp911trpvs2w 1079470532 20210331        5450 0000           0000            229288686 4240515072        931       1159     544199  235026514          0      42071.3185     .21266055

ahp911trpvs2w 1079470532 20210401        3985 0000           2200            153315349 1773961216        650        758     228916  111822579          0      38473.1114      .1902133

ahp911trpvs2w 1079470532 20210402        2257 0030           0000             97430232 2614648832        407        533     319171  131544114          0      43168.0248    .236154187

ahp911trpvs2w 1079470532 20210403        3421 0000           0000            144699806 2567487488        606        722     313414  120301875          0      42297.5171    .211049401

ahp911trpvs2w 1079470532 20210404        1106 0000           1230             44975745 1012727808        190        232     123624   43479686          0      40665.2306    .209764919

ahp911trpvs2w  645295920 20210404        1158 1230           0000             46099434 1323810816        191        259     161598   70057559          0      39809.5285    .223661485

ahp911trpvs2w  645295920 20210405        5749 0030           0000            239539645 1058160640        979       1076     365631  100692144          0       41666.315    .187162985

ahp911trpvs2w  645295920 20210406         138 0000           0230              5865642          0         25         27       6242    2266527          0      42504.6522    .195652174

ahp911trpvs2w 1079470532 20210406        5815 0330           0000            247920124  153763840       1037       1111     334200   77790500          0      42634.5871     .19105761

ahp911trpvs2w 1079470532 20210407       57734 0000           0000           1.1087E+10   25698304      53294      55237    1004615 1260047358          0      192042.394    .956749922

ahp911trpvs2w 1079470532 20210408       74990 0000           0000           5042249991      24576      29768      31686     546964 1237253755          0      67238.9651    .422536338

ahp911trpvs2w 1079470532 20210409       51728 0000           2330           1369133571  253345792       5477       5650     303989  182552927          0      26467.9394    .109225178

ahp911trpvs2w 1079470532 20210410        4974 0000           0000            199783867  735895552        811        856      90271   46607304          0      40165.6347    .172094893

ahp911trpvs2w 1079470532 20210411       22621 0000           1230            359468748  158720000       1297       1367     131254   72585278          0      15890.9309    .060430573

ahp911trpvs2w  645295920 20210411        1882 1230           0000             71793768   18644992        300        348      91735   49788087          0      38147.5919    .184909671

ahp911trpvs2w  645295920 20210412       48582 0000           0000           1560907886 3862601728       6443       6728     742751  301488808          0       32129.346    .138487506

ahp911trpvs2w  645295920 20210413         104 0100           0330              5498638          0         24         24        327     287936          0      52871.5192    .230769231

ahp911trpvs2w 1079470532 20210413        7705 0400           0000            293211481 1060806656       1212       1285     137325   76161644          0      38054.7023    .166774822

ahp911trpvs2w 1079470532 20210414       12929 0000           0000            467283284 1673117696       1948       2077     217143  132872780          0      36142.2603    .160646608

ahp911trpvs2w 1079470532 20210415       10370 0030           0000            409492129 1384497152       1682       1769     177261   90230905          0      39488.1513    .170588235


SQL_ID              PLAN RUNDATE         EXEC START_TIME     END_TIME           BUFFER   PHY_READ     cpu(s)     elp(s)       READ     IOWAIT     DIRECT BUFFER_PER_EXEC ELPS_PER_EXEC

------------- ---------- --------- ---------- -------------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- -------------

ahp911trpvs2w 1079470532 20210416         247 0100           0330              8341169   49225728         37         45       6009    8794207          0       33769.915    .182186235

ahp911trpvs2w  645295920 20210416        5627 0330           2300            207562964  473800704        854       1047     441743  199596560          0      36886.9671    .186067176

ahp911trpvs2w  645295920 20210417        2192 0100           0000             89437632 8123670528        384        587     991659  214695966          0      40801.8394    .267791971

ahp911trpvs2w  645295920 20210418         945 0000           1300             44437569 1101668352        191        243     134481   54441381          0      47023.8825    .257142857

ahp911trpvs2w 1079470532 20210418         980 1300           0000             41946219  867041280        176        246     154007   72111128          0      42802.2643    .251020408

ahp911trpvs2w 1079470532 20210419      145438 0000           2330           3950999629   96772096      15619      16088    1303674  490371127          0      27166.2126    .110617583

ahp911trpvs2w 1079470532 20210420       53295 0100           0000           1.0767E+10 2311151616      49852      53324    1867918 3308344124          0      202035.339    1.00054414

ahp911trpvs2w 1079470532 20210421       67082 0000           0000           9909924980 1106165760      47051      54870    2933418 6408308674          0      147728.526    .817954146

ahp911trpvs2w 1079470532 20210422       27731 0000           2300           5073123584  178126848      21074      33052    2276342 1.1631E+10          0      182940.521    1.19187912

ahp911trpvs2w 1079470532 20210423       14977 0000           0000           3718828095 1164230656      14334      14778     152452  258381405          0      248302.604     .98671296

ahp911trpvs2w 1079470532 20210424       46348 0000           2330           9906534842 1585315840      44495      51241     561297 3609367698          0      213742.445     1.1055709

ahp911trpvs2w 1079470532 20210425        3400 0000           0000            144558926  774037504        600        710     180031  113420052          0      42517.3312    .208823529

ahp911trpvs2w 1079470532 20210426       33975 0000           0000           1460699730   68648960       6037       6271     647242  244188177          0      42993.3695    .184576895

ahp911trpvs2w 1079470532 20210427         328 0000           0300             13765966          0         61         69       7456    8142342          0      41969.4085    .210365854

ahp911trpvs2w  645295920 20210427       47949 0330           0000           1518729576  171229184       6030       6349     676648  333635766          0       31673.853    .132411521

ahp911trpvs2w  645295920 20210428        8227 0030           2330            316532552   68665344       1293       1324      56492   31674114          0      38474.8453    .160933512

ahp911trpvs2w  645295920 20210429        9986 0000           2300            377626013  279715840       1553       1633     302430   83389468          0      37815.5431    .163528941

ahp911trpvs2w  645295920 20210430          12 0000           0030               477411          0          2          3       1342     800053          0        39784.25           .25

ahp911trpvs2w 1079470532 20210430        5128 0530           2330            181134218 3408969728        753        919     440756  171742904          0      35322.5854    .179212168

ahp911trpvs2w 1079470532 20210501        2691 0000           0000             97592034 1595867136        407        495     194808   91595430          0      36266.0847    .183946488

ahp911trpvs2w 1079470532 20210502        1080 0100           1300             49365558 1080246272        210        290     140169   82947886          0        45708.85    .268518519

ahp911trpvs2w  645295920 20210502        3603 1300           2300            151157009 1411063808        637        725     172249   90146241          0      41953.0971    .201221205

ahp911trpvs2w  645295920 20210503      145763 0000           0000           3960423906  750215168      15694      16299    1498037  633648659          0      27170.2963      .1118185

ahp911trpvs2w  645295920 20210504        5153 0030           0000            209058857 2323865600        881       1029     292517  153035322          0      40570.3196    .199689501

ahp911trpvs2w  645295920 20210505       61241 0130           0000           1.0386E+10 2537259008     654603     655522    1222326  905440204          0       169590.18    10.7039728

ahp911trpvs2w  645295920 20210506       44707 0000           0900           1.3499E+10   22290432     611583     613059    1163387 1405827093          0      301952.787     13.712819


72 rows selected.



10:15:02 jsun> @planh


Session altered.


Elapsed: 00:00:00.06

Enter value for sql_id: ahp911trpvs2w


TIMESTAMP            SQL_ID        PLAN_HASH_VALUE

-------------------- ------------- ---------------

04-dec-2019:13:57:36 ahp911trpvs2w      1079470532

20-sep-2020:12:59:48 ahp911trpvs2w       645295920

06-may-2021:09:07:37 ahp911trpvs2w      2030392835


Elapsed: 00:00:00.09


PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID ahp911trpvs2w

--------------------

SELECT /*+  use_nl(target) index(target PAY_RUN_RESULT_VALUES_N50)  */

fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TA

RGET.result_value) * FEED.scale),0) ) FROM  /* Assignment Inception To

Date */                 pay_run_result_values   TARGET

,pay_balance_feeds_f     FEED                ,pay_run_results

RR                ,pay_assignment_actions  ASSACT

,pay_assignment_actions  BAL_ASSACT                ,pay_payroll_actions

    PACT                ,pay_payroll_actions     BACT           where

BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID             and

BACT.payroll_action_id = BAL_ASSACT.payroll_action_id             and

FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null,0,0)

         and FEED.input_value_id = TARGET.input_value_id

and nvl(TARGET.result_value,'0') != '0'             and

TARGET.run_result_id = RR.run_result_id             and

RR.assignment_action_id = ASSACT.assignment_action_id             and

ASSACT.payroll_action_id = PACT.payroll_action_id             and

PACT.effective_date between FEED.effective_start_date

                      and FEED.effective_end_date             and

RR.status in ('P','PA')             and ASSACT.action_sequence <=

BAL_ASSACT.action_sequence             and ASSACT.assignment_id =

BAL_ASSACT.assignment_id


Plan hash value: 645295920


------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                             |                            |       |       |   212 (100)|          |       |       |        |      |            |

|   1 |  SORT AGGREGATE                              |                            |     1 |   130 |            |          |       |       |        |      |            |

|   2 |   PX COORDINATOR FORCED SERIAL               |                            |       |       |            |          |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)                       | :TQ10003                   |     1 |   130 |            |          |       |       |  Q1,03 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE                           |                            |     1 |   130 |            |          |       |       |  Q1,03 | PCWP |            |

|   5 |      NESTED LOOPS                            |                            |     8 |  1040 |   212   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|   6 |       NESTED LOOPS                           |                            |    10 |  1040 |   212   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|   7 |        HASH JOIN                             |                            |    10 |  1160 |   211   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|   8 |         PX RECEIVE                           |                            | 11473 |   324K|     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

|   9 |          PX SEND HASH                        | :TQ10001                   | 11473 |   324K|     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |

|  10 |           PX BLOCK ITERATOR                  |                            | 11473 |   324K|     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |

|  11 |            TABLE ACCESS FULL                 | PAY_BALANCE_FEEDS_F        | 11473 |   324K|     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

|  12 |         PX RECEIVE                           |                            |  9571 |   813K|   209   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|  13 |          PX SEND HASH                        | :TQ10002                   |  9571 |   813K|   209   (0)| 00:00:03 |       |       |  Q1,02 | P->P | HASH       |

|  14 |           NESTED LOOPS                       |                            |  9571 |   813K|   209   (0)| 00:00:03 |       |       |  Q1,02 | PCWP |            |

|  15 |            NESTED LOOPS                      |                            | 15488 |   813K|   209   (0)| 00:00:03 |       |       |  Q1,02 | PCWP |            |

|  16 |             NESTED LOOPS                     |                            |  1936 |   136K|    41   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|  17 |              BUFFER SORT                     |                            |       |       |            |          |       |       |  Q1,02 | PCWC |            |

|  18 |               PX RECEIVE                     |                            |       |       |            |          |       |       |  Q1,02 | PCWP |            |

|  19 |                PX SEND BROADCAST             | :TQ10000                   |       |       |            |          |       |       |        | S->P | BROADCAST  |

|  20 |                 NESTED LOOPS                 |                            |   378 | 21168 |    14   (0)| 00:00:01 |       |       |        |      |            |

|  21 |                  NESTED LOOPS                |                            |     1 |    31 |     3   (0)| 00:00:01 |       |       |        |      |            |

|  22 |                   TABLE ACCESS BY INDEX ROWID| PAY_ASSIGNMENT_ACTIONS     |     1 |    25 |     3   (0)| 00:00:01 |       |       |        |      |            |

|  23 |                    INDEX UNIQUE SCAN         | PAY_ASSIGNMENT_ACTIONS_PK  |     1 |       |     2   (0)| 00:00:01 |       |       |        |      |            |

|  24 |                   INDEX UNIQUE SCAN          | PAY_PAYROLL_ACTIONS_PK     |     1 |     6 |     0   (0)|          |       |       |        |      |            |

|  25 |                  TABLE ACCESS BY INDEX ROWID | PAY_ASSIGNMENT_ACTIONS     |   378 |  9450 |    11   (0)| 00:00:01 |       |       |        |      |            |

|  26 |                   INDEX RANGE SCAN           | PAY_ASSIGNMENT_ACTIONS_N54 |   378 |       |     1   (0)| 00:00:01 |       |       |        |      |            |

|  27 |              PX PARTITION HASH ITERATOR      |                            |     5 |    80 |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWC |            |

|  28 |               TABLE ACCESS BY INDEX ROWID    | PAY_RUN_RESULTS            |     5 |    80 |     0   (0)|          |       |       |  Q1,02 | PCWP |            |

|  29 |                INDEX RANGE SCAN              | XXBCF_PAY_RUN_RESULTS_N12  |    73 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  30 |             PARTITION HASH ITERATOR          |                            |     8 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  31 |              INDEX RANGE SCAN                | PAY_RUN_RESULT_VALUES_N50  |     8 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  32 |            TABLE ACCESS BY GLOBAL INDEX ROWID| PAY_RUN_RESULT_VALUES      |     5 |    75 |     0   (0)|          | ROWID | ROWID |  Q1,02 | PCWP |            |

|  33 |        INDEX UNIQUE SCAN                     | PAY_PAYROLL_ACTIONS_PK     |     1 |       |     0   (0)|          |       |       |  Q1,03 | PCWP |            |

|  34 |       TABLE ACCESS BY INDEX ROWID            | PAY_PAYROLL_ACTIONS        |     1 |    14 |     0   (0)|          |       |       |  Q1,03 | PCWP |            |

------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Note

-----

   - automatic DOP: Computed Degree of Parallelism is 37

   - SQL profile "SYS_SQLPROF_016ed2eb25b10001" used for this statement


SQL_ID ahp911trpvs2w

--------------------

SELECT /*+  use_nl(target) index(target PAY_RUN_RESULT_VALUES_N50)  */

fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TA

RGET.result_value) * FEED.scale),0) ) FROM  /* Assignment Inception To

Date */                 pay_run_result_values   TARGET

,pay_balance_feeds_f     FEED                ,pay_run_results

RR                ,pay_assignment_actions  ASSACT

,pay_assignment_actions  BAL_ASSACT                ,pay_payroll_actions

    PACT                ,pay_payroll_actions     BACT           where

BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID             and

BACT.payroll_action_id = BAL_ASSACT.payroll_action_id             and

FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null,0,0)

         and FEED.input_value_id = TARGET.input_value_id

and nvl(TARGET.result_value,'0') != '0'             and

TARGET.run_result_id = RR.run_result_id             and

RR.assignment_action_id = ASSACT.assignment_action_id             and

ASSACT.payroll_action_id = PACT.payroll_action_id             and

PACT.effective_date between FEED.effective_start_date

                      and FEED.effective_end_date             and

RR.status in ('P','PA')             and ASSACT.action_sequence <=

BAL_ASSACT.action_sequence             and ASSACT.assignment_id =

BAL_ASSACT.assignment_id


Plan hash value: 1079470532


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                             |                           |       |       |   230 (100)|          |       |       |        |      |            |

|   1 |  SORT AGGREGATE                              |                           |     1 |   130 |            |          |       |       |        |      |            |

|   2 |   PX COORDINATOR FORCED SERIAL               |                           |       |       |            |          |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)                       | :TQ10003                  |     1 |   130 |            |          |       |       |  Q1,03 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE                           |                           |     1 |   130 |            |          |       |       |  Q1,03 | PCWP |            |

|   5 |      NESTED LOOPS                            |                           |     7 |   910 |   230   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|   6 |       NESTED LOOPS                           |                           |     9 |   910 |   230   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|   7 |        HASH JOIN                             |                           |     9 |  1044 |   230   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|   8 |         PX RECEIVE                           |                           | 11353 |   321K|     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

|   9 |          PX SEND HASH                        | :TQ10001                  | 11353 |   321K|     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |

|  10 |           PX BLOCK ITERATOR                  |                           | 11353 |   321K|     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |

|  11 |            TABLE ACCESS FULL                 | PAY_BALANCE_FEEDS_F       | 11353 |   321K|     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

|  12 |         PX RECEIVE                           |                           |  7961 |   676K|   228   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

|  13 |          PX SEND HASH                        | :TQ10002                  |  7961 |   676K|   228   (0)| 00:00:03 |       |       |  Q1,02 | P->P | HASH       |

|  14 |           NESTED LOOPS                       |                           |  7961 |   676K|   228   (0)| 00:00:03 |       |       |  Q1,02 | PCWP |            |

|  15 |            NESTED LOOPS                      |                           | 12776 |   676K|   228   (0)| 00:00:03 |       |       |  Q1,02 | PCWP |            |

|  16 |             NESTED LOOPS                     |                           |  1597 |   112K|    44   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|  17 |              NESTED LOOPS                    |                           |   313 | 17528 |    15   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|  18 |               BUFFER SORT                    |                           |       |       |            |          |       |       |  Q1,02 | PCWC |            |

|  19 |                PX RECEIVE                    |                           |       |       |            |          |       |       |  Q1,02 | PCWP |            |

|  20 |                 PX SEND BROADCAST            | :TQ10000                  |       |       |            |          |       |       |        | S->P | BROADCAST  |

|  21 |                  NESTED LOOPS                |                           |     1 |    31 |     3   (0)| 00:00:01 |       |       |        |      |            |

|  22 |                   TABLE ACCESS BY INDEX ROWID| PAY_ASSIGNMENT_ACTIONS    |     1 |    25 |     3   (0)| 00:00:01 |       |       |        |      |            |

|  23 |                    INDEX UNIQUE SCAN         | PAY_ASSIGNMENT_ACTIONS_PK |     1 |       |     2   (0)| 00:00:01 |       |       |        |      |            |

|  24 |                   INDEX UNIQUE SCAN          | PAY_PAYROLL_ACTIONS_PK    |     1 |     6 |     0   (0)|          |       |       |        |      |            |

|  25 |               PX PARTITION HASH ITERATOR     |                           |   313 |  7825 |    12   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWC |            |

|  26 |                TABLE ACCESS BY INDEX ROWID   | PAY_ASSIGNMENT_ACTIONS    |   313 |  7825 |    12   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|  27 |                 INDEX RANGE SCAN             | XXBCF_RETRO001            |   313 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  28 |              PARTITION HASH ITERATOR         |                           |     5 |    80 |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  29 |               TABLE ACCESS BY INDEX ROWID    | PAY_RUN_RESULTS           |     5 |    80 |     0   (0)|          |       |       |  Q1,02 | PCWP |            |

|  30 |                INDEX RANGE SCAN              | XXBCF_PAY_RUN_RESULTS_N12 |    72 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  31 |             PARTITION HASH ITERATOR          |                           |     8 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  32 |              INDEX RANGE SCAN                | PAY_RUN_RESULT_VALUES_N50 |     8 |       |     0   (0)|          |   KEY |   KEY |  Q1,02 | PCWP |            |

|  33 |            TABLE ACCESS BY GLOBAL INDEX ROWID| PAY_RUN_RESULT_VALUES     |     5 |    75 |     0   (0)|          | ROWID | ROWID |  Q1,02 | PCWP |            |

|  34 |        INDEX UNIQUE SCAN                     | PAY_PAYROLL_ACTIONS_PK    |     1 |       |     0   (0)|          |       |       |  Q1,03 | PCWP |            |

|  35 |       TABLE ACCESS BY INDEX ROWID            | PAY_PAYROLL_ACTIONS       |     1 |    14 |     0   (0)|          |       |       |  Q1,03 | PCWP |            |

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------


Note

-----

   - automatic DOP: Computed Degree of Parallelism is 28

   - SQL profile "SYS_SQLPROF_016ed2eb25b10001" used for this statement


SQL_ID ahp911trpvs2w

--------------------

SELECT /*+  use_nl(target) index(target PAY_RUN_RESULT_VALUES_N50)  */

fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TA

RGET.result_value) * FEED.scale),0) ) FROM  /* Assignment Inception To

Date */                 pay_run_result_values   TARGET

,pay_balance_feeds_f     FEED                ,pay_run_results

RR                ,pay_assignment_actions  ASSACT

,pay_assignment_actions  BAL_ASSACT                ,pay_payroll_actions

    PACT                ,pay_payroll_actions     BACT           where

BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID             and

BACT.payroll_action_id = BAL_ASSACT.payroll_action_id             and

FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null,0,0)

         and FEED.input_value_id = TARGET.input_value_id

and nvl(TARGET.result_value,'0') != '0'             and

TARGET.run_result_id = RR.run_result_id             and

RR.assignment_action_id = ASSACT.assignment_action_id             and

ASSACT.payroll_action_id = PACT.payroll_action_id             and

PACT.effective_date between FEED.effective_start_date

                      and FEED.effective_end_date             and

RR.status in ('P','PA')             and ASSACT.action_sequence <=

BAL_ASSACT.action_sequence             and ASSACT.assignment_id =

BAL_ASSACT.assignment_id


Plan hash value: 2030392835


--------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                        |                            |       |       |  7310 (100)|          |       |       |

|   1 |  SORT AGGREGATE                         |                            |     1 |   130 |            |          |       |       |

|   2 |   NESTED LOOPS                          |                            |     9 |  1170 |  7310   (1)| 00:01:28 |       |       |

|   3 |    NESTED LOOPS                         |                            |    12 |  1170 |  7310   (1)| 00:01:28 |       |       |

|   4 |     HASH JOIN                           |                            |    12 |  1392 |  7298   (1)| 00:01:28 |       |       |

|   5 |      TABLE ACCESS FULL                  | PAY_BALANCE_FEEDS_F        | 11480 |   325K|    32   (0)| 00:00:01 |       |       |

|   6 |      NESTED LOOPS                       |                            | 10791 |   916K|  7266   (1)| 00:01:28 |       |       |

|   7 |       NESTED LOOPS                      |                            | 19107 |   916K|  7266   (1)| 00:01:28 |       |       |

|   8 |        NESTED LOOPS                     |                            |  2123 |   149K|  1375   (1)| 00:00:17 |       |       |

|   9 |         NESTED LOOPS                    |                            |   419 | 23464 |   405   (0)| 00:00:05 |       |       |

|  10 |          NESTED LOOPS                   |                            |     1 |    31 |     3   (0)| 00:00:01 |       |       |

|  11 |           TABLE ACCESS BY INDEX ROWID   | PAY_ASSIGNMENT_ACTIONS     |     1 |    25 |     3   (0)| 00:00:01 |       |       |

|  12 |            INDEX UNIQUE SCAN            | PAY_ASSIGNMENT_ACTIONS_PK  |     1 |       |     2   (0)| 00:00:01 |       |       |

|  13 |           INDEX UNIQUE SCAN             | PAY_PAYROLL_ACTIONS_PK     |     1 |     6 |     0   (0)|          |       |       |

|  14 |          TABLE ACCESS BY INDEX ROWID    | PAY_ASSIGNMENT_ACTIONS     |   419 | 10475 |   402   (0)| 00:00:05 |       |       |

|  15 |           INDEX RANGE SCAN              | PAY_ASSIGNMENT_ACTIONS_N54 |   419 |       |    22   (0)| 00:00:01 |       |       |

|  16 |         PARTITION HASH ITERATOR         |                            |     5 |    80 |     6   (0)| 00:00:01 |   KEY |   KEY |

|  17 |          TABLE ACCESS BY INDEX ROWID    | PAY_RUN_RESULTS            |     5 |    80 |     6   (0)| 00:00:01 |       |       |

|  18 |           INDEX RANGE SCAN              | XXBCF_PAY_RUN_RESULTS_N12  |    74 |       |     2   (0)| 00:00:01 |   KEY |   KEY |

|  19 |        PARTITION HASH ITERATOR          |                            |     9 |       |     2   (0)| 00:00:01 |   KEY |   KEY |

|  20 |         INDEX RANGE SCAN                | PAY_RUN_RESULT_VALUES_N50  |     9 |       |     2   (0)| 00:00:01 |   KEY |   KEY |

|  21 |       TABLE ACCESS BY GLOBAL INDEX ROWID| PAY_RUN_RESULT_VALUES      |     5 |    75 |     3   (0)| 00:00:01 | ROWID | ROWID |

|  22 |     INDEX UNIQUE SCAN                   | PAY_PAYROLL_ACTIONS_PK     |     1 |       |     0   (0)|          |       |       |

|  23 |    TABLE ACCESS BY INDEX ROWID          | PAY_PAYROLL_ACTIONS        |     1 |    14 |     1   (0)| 00:00:01 |       |       |

--------------------------------------------------------------------------------------------------------------------------------------



198 rows selected.


Finding 4 : most of the time is spending on step 26 when performance is bad, but not the same case when normal performance


select sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id , count(*)

from dba_hist_active_sess_history

where sql_id = 'ahp911trpvs2w'

and sample_time > sysdate-2

group by sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id

order by sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id ,count(*) desc


SAMPLE_TIME                                                                 SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID   COUNT(*)

--------------------------------------------------------------------------- ------------------- ---------------- ----------

05-MAY-21 10.12.04.108 PM                                                             645295920               26         19

05-MAY-21 10.12.04.108 PM                                                             645295920               31          1

05-MAY-21 10.12.14.124 PM                                                             645295920               26         17

05-MAY-21 10.12.24.172 PM                                                             645295920               25          1

05-MAY-21 10.12.24.172 PM                                                             645295920               26         14

05-MAY-21 10.12.24.172 PM                                                             645295920               29          1

05-MAY-21 10.12.24.172 PM                                                             645295920               31          1

05-MAY-21 10.12.34.227 PM                                                             645295920                4          1

05-MAY-21 10.12.34.227 PM                                                             645295920                7          1



so SQL_PLAN_LINE_ID=26 is taking most of the time


line 26 is this:

|  26 |                   INDEX RANGE SCAN           | PAY_ASSIGNMENT_ACTIONS_N54 


other time, the count(*) is not big as shown below:  



select sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id , count(*)

from dba_hist_active_sess_history

where sql_id = 'ahp911trpvs2w'

and sample_time between sysdate-5 and sysdate-4

group by sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id

order by sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id ,count(*) desc



SAMPLE_TIME                                                                 SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID   COUNT(*)

--------------------------------------------------------------------------- ------------------- ---------------- ----------

01-MAY-21 10.18.23.256 AM                                                            1079470532               33          1

01-MAY-21 10.45.37.392 AM                                                            1079470532                7          1

01-MAY-21 10.49.38.012 AM                                                            1079470532               32          1

01-MAY-21 10.55.18.872 AM                                                            1079470532               32          1

01-MAY-21 11.09.31.020 AM                                                            1079470532               33          1

01-MAY-21 11.09.41.042 AM                                                            1079470532               33          1

01-MAY-21 11.10.11.130 AM                                                            1079470532               32          1

01-MAY-21 11.13.11.566 AM                                                            1079470532               32          1

01-MAY-21 11.39.15.461 AM                                                            1079470532               32          1

01-MAY-21 11.39.45.518 AM                                                            1079470532               33          1

01-MAY-21 11.44.06.188 AM                                                            1079470532               33          1

01-MAY-21 11.58.08.278 AM                                                            1079470532               32          1

01-MAY-21 11.58.18.285 AM                                                            1079470532               32          1

01-MAY-21 11.58.18.285 AM                                                            1079470532               33          1

01-MAY-21 12.25.22.255 PM                                                            1079470532               33          1

01-MAY-21 01.04.48.010 PM                                                            1079470532               33          1

01-MAY-21 01.04.58.075 PM                                                            1079470532               33          1




select sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id , count(*)

from dba_hist_active_sess_history

where sql_id = 'ahp911trpvs2w' and SQL_PLAN_HASH_VALUE='645295920'

and to_char(sample_time,'yyyymmdd') ='20210427'

group by sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id

order by sample_time,SQL_PLAN_HASH_VALUE,sql_plan_line_id ,count(*) desc


SAMPLE_TIME                                                                 SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID   COUNT(*)

--------------------------------------------------------------------------- ------------------- ---------------- ----------

27-APR-21 03.30.04.235 AM                                                             645295920                7          1

27-APR-21 05.15.20.655 AM                                                             645295920               32          1

27-APR-21 05.16.50.904 AM                                                             645295920               32          1

27-APR-21 05.17.20.957 AM                                                             645295920               25          1

27-APR-21 05.17.30.980 AM                                                             645295920               31          1

27-APR-21 05.18.41.180 AM                                                             645295920               14          1

27-APR-21 05.19.21.222 AM                                                             645295920               15          1

27-APR-21 05.35.03.492 AM                                                             645295920               31          1

27-APR-21 05.46.05.116 AM                                                             645295920               32          1

27-APR-21 06.01.47.459 AM                                                             645295920               32          1

27-APR-21 06.12.39.098 AM                                                             645295920               32          1

27-APR-21 06.13.19.209 AM                                                             645295920               32          1

27-APR-21 06.22.20.571 AM                                                             645295920               32          1

27-APR-21 06.28.01.430 AM                                                             645295920               32          1

27-APR-21 06.37.22.824 AM                                                             645295920               28          1

27-APR-21 06.45.44.118 AM                                                             645295920                7          1


Finding 5 : the index segment size is 3 times bigger than table


SQL> @segmentsize

Enter value for tablename: PAY_ASSIGNMENT_ACTIONS

Enter value for ownername: hr

old   1: select sum(bytes)/1024/1024 MB from dba_segments where segment_name=upper('&tablename') and owner=upper('&ownername')

new   1: select sum(bytes)/1024/1024 MB from dba_segments where segment_name=upper('PAY_ASSIGNMENT_ACTIONS') and owner=upper('hr')


        MB

----------

     563.5


SQL> @segmentsize

Enter value for tablename: PAY_ASSIGNMENT_ACTIONS_N54

Enter value for ownername: hr

old   1: select sum(bytes)/1024/1024 MB from dba_segments where segment_name=upper('&tablename') and owner=upper('&ownername')

new   1: select sum(bytes)/1024/1024 MB from dba_segments where segment_name=upper('PAY_ASSIGNMENT_ACTIONS_N54') and owner=upper('hr')


        MB

----------

   1664.75


Fix: after recreate the index, performance back to normal.




Comments

  1. walkthroughMay 20, 2021 at 11:31 AM

    https://richardfoote.wordpress.com/2016/01/14/indexes-and-initrans-blackstar/

    ReplyDelete
  2. walkthroughMay 20, 2021 at 11:51 AM

    https://titanwolf.org/Network/Articles/Article?AID=61b687e3-bc74-4325-a765-717c6fd28c1b#gsc.tab=0

    ReplyDelete
  3. walkthroughMay 20, 2021 at 3:57 PM

    https://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/

    ReplyDelete
  4. walkthroughMay 20, 2021 at 6:12 PM

    https://asktom.oracle.com/pls/apex/asktom.search?tag=delayed-block-cleanout

    ReplyDelete
  5. walkthroughMay 20, 2021 at 6:20 PM

    https://www.databasejournal.com/features/oracle/delayed-block-cleanout-in-oracle.html#:~:text=Delayed%20Block%20Cleanout%20is%20the,that%20modified%20the%20affected%20blocks.

    ReplyDelete
  6. walkthroughMay 20, 2021 at 6:29 PM

    https://hemantoracledba.blogspot.com/2008/10/delayed-block-cleanout.html

    ReplyDelete
  7. walkthroughMay 20, 2021 at 9:01 PM

    This comment has been removed by the author.

    ReplyDelete

Post a Comment

Popular posts from this blog

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k
3 comments
Read more

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory for the given Oracle Home. LsInventorySession failed: Unable to create patchObject Possible ca
Post a Comment
Read more

shell script to clean up oracle dumpfile

https://github.com/iacosta/personal/blob/master/shells/cleanhouse.sh #!/bin/ksh # # Script used to cleanup any Oracle environment. # # Cleans:      audit_file_dest #              background_dump_dest #              core_dump_dest #              user_dump_dest #              Clusterware logs # # Rotates:     Alert Logs #              Listener Logs # # Scheduling:  00 00 * * * /networkdrive/dba/scripts/purge/cleanup.sh -d 7 > /u01/dba/bin/cleanup.log 2>&1 # # Created By:  Lei Dao # # # RM="rm -f" RMDIR="rm -rf" LS="ls -l" MV="mv" TOUCH="touch" TESTTOUCH="echo touch" TESTMV="echo mv" TESTRM=$LS TESTRMDIR=$LS SUCCESS=0 FAILURE=1 TEST=0 HOSTNAME=`hostname` ORAENV="oraenv" TODAY=`date +%Y%m%d` ORIGPATH=/usr/local/bin:$PATH ORIGLD=$LD_LIBRARY_PATH export PATH=$ORIGPATH # Usage function. f_usage(){   echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [
Post a Comment
Read more