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

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 ...
Post a Comment
Read more

oracle dba_hist_sysmetric_summary

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350...
Post a Comment
Read more

ORA_RMAN_SGA_TARGET

assume that we lost all the files of oracle database but we do have rman backup, when trying to bring up a dummy database before restore start, I get this error. RMAN> startup nomount force; WARNING: cannot translate ORA_RMAN_SGA_TARGET value startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/PROD/spfilePROD.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/PROD/spfilePROD.ora ORA-15056: additional error message ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/prod/spfileprod.ora ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 starting Oracle instance without parameter file for retrival of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =================================...
2 comments
Read more