DETAILED ADDM REPORT FOR TASK 'TASK_159215' WITH ID 159215
----------------------------------------------------------
Analysis Period: from 25-MAY-2011 22:00 to 27-MAY-2011 11:00
Database ID/Instance: 4127787440/2
Database/Instance Names: CAPGENT/capgent2
Host Name: rac2.capgent.com
Database Version: 10.2.0.4.0
Snapshot Range: from 21093 to 21130
Database Time: 169169 seconds
Average Database Load: 1.3 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 66% impact (111382 seconds)
--------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 39% benefit (66429 seconds)
ACTION: Investigate the SQL statement with SQL_ID "1h18jnuurhb8k" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 1h18jnuurhb8k and
PLAN_HASH 3733913279
select invtxn0_.IV_ID as IV1_36_0_, invtxn0_.IV_DATE as IV2_36_0_,
invtxn0_.IV_PINVID as IV3_36_0_, invtxn0_.IV_SBID as IV4_36_0_,
invtxn0_.IV_CURR as IV5_36_0_, invtxn0_.IV_CHGS as IV6_36_0_,
invtxn0_.IV_TLAMT as IV7_36_0_, invtxn0_.IV_SHPADD as IV8_36_0_,
invtxn0_.IV_BLNO as IV9_36_0_, invtxn0_.IV_SHMODE as IV10_36_0_,
invtxn0_.IV_CARR as IV11_36_0_, invtxn0_.IV_BILLADD as IV12_36_0_,
invtxn0_.IV_CRTRMS as IV13_36_0_, invtxn0_.IV_TERMS as IV14_36_0_,
invtxn0_.IV_ATTDTLS as IV15_36_0_, invtxn0_.IV_TEXT as IV16_36_0_,
invtxn0_.IV_BTCHDTE as IV17_36_0_, invtxn0_.IV_ADVPAID as IV18_36_0_,
invtxn0_.IV_ADVADJ as IV19_36_0_, invtxn0_.IV_PRNTD as IV20_36_0_,
invtxn0_.IV_MODE as IV21_36_0_, invtxn0_.IV_DUEDATE as IV22_36_0_,
invtxn0_.IV_TARIFF as IV23_36_0_, invtxn0_.IV_TXNDTE as IV24_36_0_,
invtxn0_.IV_GPINVNO as IV25_36_0_, invtxn0_.IV_CRTDBY as IV26_36_0_,
invtxn0_.IV_MODBY as IV27_36_0_, invtxn0_.IV_CRTDDT as IV28_36_0_,
invtxn0_.IV_MODDT as IV29_36_0_, invtxn0_.IV_CUSNO as IV30_36_0_,
invtxn0_.IV_ASSGTPE as IV31_36_0_, invtxn0_.IV_ASSGNTO as IV32_36_0_,
invtxn0_.IV_STR1 as IV33_36_0_, invtxn0_.IV_STR2 as IV34_36_0_,
invtxn0_.IV_STR3 as IV35_36_0_, invtxn0_.IV_STR4 as IV36_36_0_,
invtxn0_.IV_STR5 as IV37_36_0_, invtxn0_.IV_STR6 as IV38_36_0_,
invtxn0_.IV_STR7 as IV39_36_0_, invtxn0_.IV_STR8 as IV40_36_0_,
invtxn0_.IV_STR9 as IV41_36_0_, invtxn0_.IV_STR10 as IV42_36_0_,
invtxn0_.IV_STR11 as IV43_36_0_, invtxn0_.IV_STR12 as IV44_36_0_,
invtxn0_.IV_STR13 as IV45_36_0_, invtxn0_.IV_STR14 as IV46_36_0_,
invtxn0_.IV_STR15 as IV47_36_0_, invtxn0_.IV_STR16 as IV48_36_0_,
invtxn0_.IV_STR17 as IV49_36_0_, invtxn0_.IV_STR18 as IV50_36_0_,
invtxn0_.IV_STR19 as IV51_36_0_, invtxn0_.IV_STR20 as IV52_36_0_,
invtxn0_.IV_STR21 as IV53_36_0_, invtxn0_.IV_STR22 as IV54_36_0_,
invtxn0_.IV_STR23 as IV55_36_0_, invtxn0_.IV_STR24 as IV56_36_0_,
invtxn0_.IV_STR25 as IV57_36_0_, invtxn0_.IV_NUM1 as IV58_36_0_,
invtxn0_.IV_NUM2 as IV59_36_0_, invtxn0_.IV_NUM3 as IV60_36_0_,
invtxn0_.IV_NUM4 as IV61_36_0_, invtxn0_.IV_NUM5 as IV62_36_0_,
invtxn0_.IV_NUM6 as IV63_36_0_, invtxn0_.IV_NUM7 as IV64_36_0_,
invtxn0_.IV_NUM8 as IV65_36_0_, invtxn0_.IV_NUM9 as IV66_36_0_,
invtxn0_.IV_NUM10 as IV67_36_0_, invtxn0_.IV_DATE1 as IV68_36_0_,
invtxn0_.IV_DATE2 as IV69_36_0_, invtxn0_.IV_DATE3 as IV70_36_0_,
invtxn0_.IV_DATE4 as IV71_36_0_, invtxn0_.IV_DATE5 as IV72_36_0_,
invtxn0_.IV_DATE6 as IV73_36_0_, invtxn0_.IV_DATE8 as IV74_36_0_,
invtxn0_.IV_DATE9 as IV75_36_0_, invtxn0_.IV_DATE10 as IV76_36_0_,
invtxn0_.IV_ORDNO as IV77_36_0_, invtxn0_.IV_FOR as IV78_36_0_,
invtxn0_.IV_REMTO as IV79_36_0_, invtxn0_.IV_PRODCDE as IV80_36_0_,
invtxn0_.IV_CNTRLNO as IV81_36_0_, invtxn0_.IV_IONUM as IV82_36_0_,
invtxn0_.IV_AUTHNO as IV83_36_0_, invtxn0_.IV_DEPTNO as IV84_36_0_,
invtxn0_.IV_LOC as IV85_36_0_, invtxn0_.IV_CLOSED as IV86_36_0_,
invtxn0_.TID as TID36_0_, invtxn0_.AID as AID36_0_,
invtxn0_.IV_SOURCE as IV89_36_0_, invtxn0_.IV_RECTYPE as IV90_36_0_,
invtxn0_.IV_SLSORDNO as IV91_36_0_, invtxn0_.IV_GAMT as IV92_36_0_,
invtxn0_.IV_TXAMT as IV93_36_0_, invtxn0_.IV_PDAMT as IV94_36_0_,
invtxn0_.IV_BU as IV95_36_0_, invtxn0_.IV_SHREF as IV96_36_0_,
invtxn0_.IV_SHCLASS as IV97_36_0_, invtxn0_.IV_SHDT as IV98_36_0_,
invtxn0_.IV_PODATE as IV99_36_0_, invtxn0_.IV_PROREF as IV100_36_0_,
invtxn0_.IV_BLDATEFRO as IV101_36_0_, invtxn0_.IV_BLDATETO as
IV102_36_0_, invtxn0_.IV_BATCH as IV103_36_0_, invtxn0_.IV_SLDATE as
IV104_36_0_, invtxn0_.IV_GROUP as IV105_36_0_, invtxn0_.IV_SHADDDET
as IV106_36_0_, invtxn0_.IV_RESP as IV107_36_0_, invtxn0_.IV_REMDT as
IV108_36_0_, invtxn0_.IV_PROB as IV109_36_0_, invtxn0_.IV_STID as
IV110_36_0_, invtxn0_.rowid as rowid111_36_0_, invtxn0_.EN_ID as
EN112_36_0_, invtxn0_.STE_ID as STE113_36_0_, invtxn0_.MTH_SETID as
MTH114_36_0_, invtxn0_.CS_ID as CS115_36_0_, invtxn0_.IV_TYPE as
IV116_36_0_ from INV_TXN invtxn0_ where invtxn0_.IV_ID=:1 and
invtxn0_.IV_DATE=:2 for update
RATIONALE: SQL statement with SQL_ID "1h18jnuurhb8k" was executed 853
times and had an average elapsed time of 77 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "1h18jnuurhb8k".
RECOMMENDATION 2: SQL Tuning, 24% benefit (40380 seconds)
ACTION: Investigate the SQL statement with SQL_ID "0z5rhvh9swhz0" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 0z5rhvh9swhz0 and
PLAN_HASH 4006440212
select IV_ID from INV_TXN where IV_ID =:1 and IV_DATE =:2 for update
RATIONALE: SQL statement with SQL_ID "0z5rhvh9swhz0" was executed 132
times and had an average elapsed time of 305 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 100% of the database time spent in
processing the SQL statement with SQL_ID "0z5rhvh9swhz0".
RECOMMENDATION 3: SQL Tuning, 2.9% benefit (4815 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"cfcrvy77bpw5a".
RELEVANT OBJECT: SQL statement with SQL_ID cfcrvy77bpw5a and
PLAN_HASH 3059921105
update INV_TXN set IV_MODBY='System', IV_MODDT=sysdate,
IV_ASSGTPE=:1, IV_ASSGNTO=:2 where IV_ID in (:3 , :4 , :5 , :6 , :7 ,
:8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 ,
:20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31
, :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 ,
:43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54
, :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 ,
:66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77
, :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 ,
:89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 ,
:100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 , :108 , :109 ,
:110 , :111 , :112 , :113 , :114 , :115 , :116 , :117 , :118 , :119 ,
:120 , :121 , :122 , :123 , :124 , :125 , :126 , :127 , :128 , :129 ,
:130 , :131 , :132 , :133 , :134 , :135 , :136 , :137 , :138 , :139 ,
:140 , :141 , :142 , :143 , :144 , :145 , :146 , :147 , :148 , :149 ,
:150 , :151 , :152 , :153 , :154 , :155 , :156 , :157 , :158 , :159 ,
:160 , :161 , :162 , :163 , :164 , :165 , :166 , :167 , :168 , :169 ,
:170 , :171 , :172 , :173 , :174 , :175 , :176 , :177 , :178 , :179 ,
:180 , :181 , :182 , :183 , :184 , :185 , :186 , :187 , :188 , :189 ,
:190 , :191 , :192 , :193 , :194 , :195 , :196 , :197 , :198 , :199 ,
:200 , :201 , :202 , :203 , :204 , :205 , :206 , :207 , :208 , :209 ,
:210 , :211 , :212 , :213 , :214 , :215 , :216 , :217 , :218 , :219 ,
:220 , :221 , :222 , :223 , :224 , :225 , :226 , :227 , :228 , :229 ,
:230 , :231 , :232 , :233 , :234 , :235 , :236 , :237 , :238 , :239 ,
:240 , :241 , :242 , :243 , :244 , :245 , :246 , :247 , :248 , :249 ,
:250 , :251 , :252 , :253 , :254 , :255 , :256 , :257 , :258 , :259 ,
:260 , :261 , :262 , :263 , :264 , :265 , :266 , :267 , :268 , :269 ,
:270 , :271 , :272 , :273 , :274 , :275 , :276 , :277 , :278 , :279 ,
:280 , :281 , :282 , :283 , :284 , :285 , :286 , :287 , :288 , :289 ,
:290 , :291 , :292 , :293 , :294 , :295 , :296 , :297 , :298 , :299 ,
:300 , :301 , :302 , :303 , :304 , :305 , :306 , :307 , :308 , :309 ,
:310 , :311 , :312 , :313 , :314 , :315 , :316 , :317 , :318 , :319 ,
:320 , :321 , :322 , :323 , :324 , :325 , :326 , :327 , :328 , :329 ,
:330 , :331 , :332 , :333 , :334 , :335 , :336 , :337 , :338 , :339 ,
:340 , :341 , :342 , :343 , :344 , :345 , :346 , :347 , :348 , :349 ,
:350 , :351 , :352 , :353 , :354 , :355 , :356 , :357 , :358 , :359 ,
:360 , :361 , :362 , :363 , :364 , :365 , :366 , :367 , :368 , :369 ,
:370 , :371 , :372 , :373 , :374 , :375 , :376 , :377 , :378 , :379 ,
:380 , :381 , :382 , :383 , :384 , :385 , :386 , :387 , :388 , :389 ,
:390 , :391 , :392 , :393 , :394 , :395 , :396 , :397 , :398 , :399 ,
:400 , :401 , :402 , :403 , :404 , :405 , :406 , :407 , :408 , :409 ,
:410 , :411 , :412 , :413 , :414 , :415 , :416 , :417 , :418 , :419 ,
:420 , :421 , :422 , :423 , :424 , :425 , :426 , :427 , :428 , :429 ,
:430 , :431 , :432 , :433 , :434 , :435 , :436 , :437 , :438 , :439 ,
:440 , :441 , :442 , :443 , :444 , :445 , :446 , :447 , :448 , :449 ,
:450 , :451 , :452 , :453 , :454 , :455 , :456 , :457 , :458 , :459 ,
:460 , :461 , :462 , :463 , :464 , :465 , :466 , :467 , :468 , :469 ,
:470 , :471 , :472 , :473 , :474 , :475 , :476 , :477 , :478 , :479 ,
:480 , :481 , :482 , :483 , :484 , :485 , :486 , :487 , :488 , :489 ,
:490 , :491 , :492 , :493 , :494 , :495 , :496 , :497 , :498 , :499 ,
:500 , :501 , :502 , :503 , :504 , :505 , :506 , :507 , :508 , :509 ,
:510 , :511 , :512 , :513 , :514 , :515 , :516 , :517 , :518 , :519 ,
:520 , :521 , :522 , :523 , :524 , :525 , :526 , :527 , :528 , :529 ,
:530 , :531 , :532 , :533 , :534 , :535 , :536 , :537 , :538 , :539 ,
:540 , :541 , :542 , :543 , :544 , :545 , :546 , :547 , :548 , :549 ,
:550 , :551 , :552 , :553 , :554 , :555 , :556 , :557 , :558 , :559 ,
:560 , :561 , :562 , :563 , :564 , :565 , :566 , :567 , :568 , :569 ,
:570 , :571 , :572 , :573 , :574 , :575 , :576 , :577 , :578 , :579 ,
:580 , :581 , :582 , :583 , :584 , :585 , :586 , :587 , :588 , :589 ,
:590 , :591 , :592 , :593 , :594 , :595 , :596 , :597 , :598 , :599 ,
:600 , :601 , :602 , :603 , :604 , :605 , :606 , :607 , :608 , :609 ,
:610 , :611 , :612 , :613 , :614 , :615 , :616 , :617 , :618 , :619 ,
:620 , :621 , :622 , :623 , :624 , :625 , :626 , :627 , :628 , :629 ,
:630 , :631 , :632 , :633 , :634 , :635 , :636 , :637 , :638 , :639 ,
:640 , :641 , :642 , :643 , :644 , :645 , :646 , :647 , :648 , :649 ,
:650 , :651 , :652 , :653 , :654 , :655 , :656 , :657 , :658 , :659 ,
:660 , :661 , :662 , :663 , :664 , :665 , :666 , :667 , :668 , :669 ,
:670 , :671 , :672 , :673 , :674 , :675 , :676 , :677 , :678 , :679 ,
:680 , :681 , :682 , :683 , :684 , :685 , :686 , :687 , :688 , :689 ,
:690 , :691 , :692 , :693 , :694 , :695 , :696 , :697 , :698 , :699 ,
:700 , :701 , :702 , :703 , :704 , :705 , :706 , :707 , :708 , :709 ,
:710 , :711 , :712 , :713 , :714 , :715 , :716 , :717 , :718 , :719 ,
:720 , :721 , :722 , :723 , :724 , :725 , :726 , :727 , :728 , :729 ,
:730 , :731 , :732 , :733 , :734 , :735 , :736 , :737 , :738 , :739 ,
:740 , :741 , :742 , :743 , :744 , :745 , :746 , :747 , :748 , :749 ,
:750 , :751 , :752 , :753 , :754 , :755 , :756 , :757 , :758 , :759 ,
:760 , :761 , :762 , :763 , :764 , :765 , :766 , :767 , :768 , :769 ,
:770 , :771 , :772 , :773 , :774 , :775 , :776 , :777 , :778 , :779 ,
:780 , :781 , :782 , :783 , :784 , :785 , :786 , :787 , :788 , :789 ,
:790 , :791 , :792 , :793 , :794 , :795 , :796 , :797 , :798 , :799 ,
:800 , :801 , :802 , :803 , :804 , :805 , :806 , :807 , :808 , :809 ,
:810 , :811 , :812 , :813 , :814 , :815 , :816 , :817 , :818 , :819 ,
:820 , :821 , :822 , :823 , :824 , :825 , :826 , :827 , :828 , :829 ,
:830 , :831 , :832 , :833 , :834 , :835 , :836 , :837 , :838 , :839 ,
:840 , :841 , :842 , :843 , :844 , :845 , :846 , :847 , :848 , :849 ,
:850 , :851 , :852 , :853 , :854 , :855 , :856 , :857 , :858 , :859 ,
:860 , :861 , :862 , :863 , :864 , :865 , :866 , :867 , :868 , :869 ,
:870 , :871 , :872 , :873 , :874 , :875 , :876 , :877 , :878 , :879 ,
:880 , :881 , :882 , :883 , :884 , :885 , :886 , :887 , :888 , :889 ,
:890 , :891 , :892 , :893 , :894 , :895 , :896 , :897 , :898 , :899 ,
:900 , :901 , :902 , :903 , :904 , :905 , :906 , :907 , :908 , :909 ,
:910 , :911 , :912 , :913 , :914 , :915 , :916 , :917 , :918 , :919 ,
:920 , :921 , :922 , :923 , :924 , :925 , :926 , :927 , :928 , :929 ,
:930 , :931 , :932 , :933 , :934 , :935 , :936 , :937 , :938 , :939 ,
:940 , :941 , :942 , :943 , :944 , :945 , :946 , :947 , :948 , :949 ,
:950 , :951 , :952 , :953 , :954 , :955 , :956 , :957 , :958 , :959 ,
:960 , :961 , :962 , :963 , :964 , :965 , :966 , :967 , :968 , :969 ,
:970 , :971 , :972 , :973 , :974 , :975 , :976 , :977 , :978 , :979 ,
:980 , :981 , :982 , :983 , :984 , :985 , :986 , :987 , :988 , :989 ,
:990 , :991 , :992 , :993 , :994 , :995 , :996 , :997 , :998 , :999 ,
:1000 , :1001)
RATIONALE: SQL statement with SQL_ID "cfcrvy77bpw5a" was executed 7
times and had an average elapsed time of 687 seconds.
FINDING 2: 63% impact (106634 seconds)
--------------------------------------
SQL statements were found waiting for row lock waits.
RECOMMENDATION 1: Application Analysis, 63% benefit (106566 seconds)
ACTION: Significant row contention was detected in the TABLE
"DNBI.INV_TXN" with object id 56469. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 56469
RATIONALE: The SQL statement with SQL_ID "1h18jnuurhb8k" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 1h18jnuurhb8k
select invtxn0_.IV_ID as IV1_36_0_, invtxn0_.IV_DATE as IV2_36_0_,
invtxn0_.IV_PINVID as IV3_36_0_, invtxn0_.IV_SBID as IV4_36_0_,
invtxn0_.IV_CURR as IV5_36_0_, invtxn0_.IV_CHGS as IV6_36_0_,
invtxn0_.IV_TLAMT as IV7_36_0_, invtxn0_.IV_SHPADD as IV8_36_0_,
invtxn0_.IV_BLNO as IV9_36_0_, invtxn0_.IV_SHMODE as IV10_36_0_,
invtxn0_.IV_CARR as IV11_36_0_, invtxn0_.IV_BILLADD as IV12_36_0_,
invtxn0_.IV_CRTRMS as IV13_36_0_, invtxn0_.IV_TERMS as IV14_36_0_,
invtxn0_.IV_ATTDTLS as IV15_36_0_, invtxn0_.IV_TEXT as IV16_36_0_,
invtxn0_.IV_BTCHDTE as IV17_36_0_, invtxn0_.IV_ADVPAID as IV18_36_0_,
invtxn0_.IV_ADVADJ as IV19_36_0_, invtxn0_.IV_PRNTD as IV20_36_0_,
invtxn0_.IV_MODE as IV21_36_0_, invtxn0_.IV_DUEDATE as IV22_36_0_,
invtxn0_.IV_TARIFF as IV23_36_0_, invtxn0_.IV_TXNDTE as IV24_36_0_,
invtxn0_.IV_GPINVNO as IV25_36_0_, invtxn0_.IV_CRTDBY as IV26_36_0_,
invtxn0_.IV_MODBY as IV27_36_0_, invtxn0_.IV_CRTDDT as IV28_36_0_,
invtxn0_.IV_MODDT as IV29_36_0_, invtxn0_.IV_CUSNO as IV30_36_0_,
invtxn0_.IV_ASSGTPE as IV31_36_0_, invtxn0_.IV_ASSGNTO as IV32_36_0_,
invtxn0_.IV_STR1 as IV33_36_0_, invtxn0_.IV_STR2 as IV34_36_0_,
invtxn0_.IV_STR3 as IV35_36_0_, invtxn0_.IV_STR4 as IV36_36_0_,
invtxn0_.IV_STR5 as IV37_36_0_, invtxn0_.IV_STR6 as IV38_36_0_,
invtxn0_.IV_STR7 as IV39_36_0_, invtxn0_.IV_STR8 as IV40_36_0_,
invtxn0_.IV_STR9 as IV41_36_0_, invtxn0_.IV_STR10 as IV42_36_0_,
invtxn0_.IV_STR11 as IV43_36_0_, invtxn0_.IV_STR12 as IV44_36_0_,
invtxn0_.IV_STR13 as IV45_36_0_, invtxn0_.IV_STR14 as IV46_36_0_,
invtxn0_.IV_STR15 as IV47_36_0_, invtxn0_.IV_STR16 as IV48_36_0_,
invtxn0_.IV_STR17 as IV49_36_0_, invtxn0_.IV_STR18 as IV50_36_0_,
invtxn0_.IV_STR19 as IV51_36_0_, invtxn0_.IV_STR20 as IV52_36_0_,
invtxn0_.IV_STR21 as IV53_36_0_, invtxn0_.IV_STR22 as IV54_36_0_,
invtxn0_.IV_STR23 as IV55_36_0_, invtxn0_.IV_STR24 as IV56_36_0_,
invtxn0_.IV_STR25 as IV57_36_0_, invtxn0_.IV_NUM1 as IV58_36_0_,
invtxn0_.IV_NUM2 as IV59_36_0_, invtxn0_.IV_NUM3 as IV60_36_0_,
invtxn0_.IV_NUM4 as IV61_36_0_, invtxn0_.IV_NUM5 as IV62_36_0_,
invtxn0_.IV_NUM6 as IV63_36_0_, invtxn0_.IV_NUM7 as IV64_36_0_,
invtxn0_.IV_NUM8 as IV65_36_0_, invtxn0_.IV_NUM9 as IV66_36_0_,
invtxn0_.IV_NUM10 as IV67_36_0_, invtxn0_.IV_DATE1 as IV68_36_0_,
invtxn0_.IV_DATE2 as IV69_36_0_, invtxn0_.IV_DATE3 as IV70_36_0_,
invtxn0_.IV_DATE4 as IV71_36_0_, invtxn0_.IV_DATE5 as IV72_36_0_,
invtxn0_.IV_DATE6 as IV73_36_0_, invtxn0_.IV_DATE8 as IV74_36_0_,
invtxn0_.IV_DATE9 as IV75_36_0_, invtxn0_.IV_DATE10 as IV76_36_0_,
invtxn0_.IV_ORDNO as IV77_36_0_, invtxn0_.IV_FOR as IV78_36_0_,
invtxn0_.IV_REMTO as IV79_36_0_, invtxn0_.IV_PRODCDE as IV80_36_0_,
invtxn0_.IV_CNTRLNO as IV81_36_0_, invtxn0_.IV_IONUM as IV82_36_0_,
invtxn0_.IV_AUTHNO as IV83_36_0_, invtxn0_.IV_DEPTNO as IV84_36_0_,
invtxn0_.IV_LOC as IV85_36_0_, invtxn0_.IV_CLOSED as IV86_36_0_,
invtxn0_.TID as TID36_0_, invtxn0_.AID as AID36_0_,
invtxn0_.IV_SOURCE as IV89_36_0_, invtxn0_.IV_RECTYPE as IV90_36_0_,
invtxn0_.IV_SLSORDNO as IV91_36_0_, invtxn0_.IV_GAMT as IV92_36_0_,
invtxn0_.IV_TXAMT as IV93_36_0_, invtxn0_.IV_PDAMT as IV94_36_0_,
invtxn0_.IV_BU as IV95_36_0_, invtxn0_.IV_SHREF as IV96_36_0_,
invtxn0_.IV_SHCLASS as IV97_36_0_, invtxn0_.IV_SHDT as IV98_36_0_,
invtxn0_.IV_PODATE as IV99_36_0_, invtxn0_.IV_PROREF as IV100_36_0_,
invtxn0_.IV_BLDATEFRO as IV101_36_0_, invtxn0_.IV_BLDATETO as
IV102_36_0_, invtxn0_.IV_BATCH as IV103_36_0_, invtxn0_.IV_SLDATE as
IV104_36_0_, invtxn0_.IV_GROUP as IV105_36_0_, invtxn0_.IV_SHADDDET
as IV106_36_0_, invtxn0_.IV_RESP as IV107_36_0_, invtxn0_.IV_REMDT as
IV108_36_0_, invtxn0_.IV_PROB as IV109_36_0_, invtxn0_.IV_STID as
IV110_36_0_, invtxn0_.rowid as rowid111_36_0_, invtxn0_.EN_ID as
EN112_36_0_, invtxn0_.STE_ID as STE113_36_0_, invtxn0_.MTH_SETID as
MTH114_36_0_, invtxn0_.CS_ID as CS115_36_0_, invtxn0_.IV_TYPE as
IV116_36_0_ from INV_TXN invtxn0_ where invtxn0_.IV_ID=:1 and
invtxn0_.IV_DATE=:2 for update
RATIONALE: The SQL statement with SQL_ID "0z5rhvh9swhz0" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 0z5rhvh9swhz0
select IV_ID from INV_TXN where IV_ID =:1 and IV_DATE =:2 for update
RATIONALE: Session with ID "254", User ID "65", Program "JDBC Thin
Client" and Module "JDBC Thin Client" was the blocking session
responsible for 100% of this recommendation's benefit.
RECOMMENDATION 2: Application Analysis, 0.03% benefit (49 seconds)
ACTION: Significant row contention was detected in the INDEX
"DNBI.CST_REF1_INDEX" with object id 93601. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 93601
RATIONALE: The SQL statement with SQL_ID "5raa597dgcwnu" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 5raa597dgcwnu
insert into CST_HDR (CS_PID, CS_NAME, CS_ACTIVE, CS_ALIAS, CS_REF1,
CS_REF2, CS_OREF, CS_OSUPP, CS_CDSCT, CS_DELM, CS_SLPN, CS_RESP,
CS_VATC, CS_CURR, CS_XCHTPE, CS_DTEFRM, CS_PAYR, CS_STATS, CS_CRLM1,
CS_CRLM2, CS_CRLM3, CS_STOP, CS_OSINAMT, CS_ODINAMT, CS_LINVDTE,
CS_EDTE, CS_SUMINV, CS_INTINV, CS_INTRUL, CS_CRATING, CS_CREVIEW,
CS_CRUPDTE, CS_DB, CS_LANG, CS_PSTID, CS_REGION, CS_RMKS, CS_STMT,
CS_CRTDBY, CS_MODBY, CS_CRTDDT, CS_MODDT, CS_CSGID, EN_ID, STE_ID,
CS_STR1, CS_STR2, CS_STR3, CS_STR4, CS_STR5, CS_STR6, CS_STR7,
CS_STR8, CS_STR9, CS_STR10, CS_STR11, CS_STR12, CS_STR13, CS_STR14,
CS_STR15, CS_STR16, CS_STR17, CS_STR18, CS_STR19, CS_STR20, CS_STR21,
CS_STR22, CS_STR23, CS_STR24, CS_STR25, CS_NUM1, CS_NUM2, CS_NUM3,
CS_NUM4, CS_NUM5, CS_NUM6, CS_NUM7, CS_NUM8, CS_NUM9, CS_NUM10,
CS_NUM11, CS_NUM12, CS_NUM13, CS_NUM14, CS_NUM15, CS_NUM16, CS_NUM17,
CS_NUM18, CS_NUM19, CS_NUM20, CS_DATE1, CS_DATE2, CS_DATE3, CS_DATE4,
CS_DATE5, CS_DATE6, CS_DATE7, CS_DATE8, CS_DATE9, CS_DATE10,
CS_TERMS, CS_KEYMARK, AID, CS_IND, CS_URL, AC_TYPE, CS_AVGDP,
CS_BKT12, CS_ERAM, PY_CODE, PC_ID, CY_ID, PR_ID, CS_TYPE, DT_ID,
CS_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12,
:13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,
:27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40,
:41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54,
:55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68,
:69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82,
:83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96,
:97, :98, :99, :100, :101, :102, :103, :104, :105, :106, :107, :108,
:109, :110, :111, :112, :113, :114, :115, :116)
RATIONALE: Sessions with Service "capgent" and Module "JDBC Thin Client"
were the blocking sessions responsible for 100% of this
recommendation's benefit.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (63% impact [106735 seconds])
FINDING 3: 16% impact (27486 seconds)
-------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 16% benefit (27486 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals.
ACTION: Alternatively, you may set the parameter "cursor_sharing" to
"force".
RATIONALE: At least 43 SQL statements with PLAN_HASH_VALUE 2919750023
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 33 SQL statements with PLAN_HASH_VALUE 1928032458
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 28 SQL statements with PLAN_HASH_VALUE 2437727371
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 27 SQL statements with PLAN_HASH_VALUE 2239883860
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 24 SQL statements with PLAN_HASH_VALUE 2819997204
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (16% impact [27510 seconds])
FINDING 4: 6.9% impact (11671 seconds)
--------------------------------------
Individual database segments responsible for significant user I/O wait were
found.
RECOMMENDATION 1: Segment Tuning, 4.4% benefit (7356 seconds)
ACTION: Investigate application logic involving I/O on TABLE
"DNBI.INV_TXN" with object id 56469.
RELEVANT OBJECT: database object with id 56469
RATIONALE: The I/O usage statistics for the object are: 47 full object
scans, 861517 physical reads, 45446 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "3jcjhmx9z4jgs" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 3jcjhmx9z4jgs
select * from ( select /*+ INDEX(CSTHDR0_
CST_HDR_ENID_TYPE_ACTIVE_IDX) */ stshdr3_.ST_NAME as col_0_0_,
sum(cstagehdr1_.CS_AGEBUCKET1) as col_1_0_,
sum(cstagehdr1_.CS_AGEBUCKET2) as col_2_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_8_0_, stshdr3_.ST_ID as
col_9_0_ from CST_HDR csthdr0_, CST_AGEHDR cstagehdr1_, INV_TXN
invtxns2_, STS_HDR stshdr3_ where csthdr0_.CS_ID=cstagehdr1_.CS_ID(+)
and csthdr0_.CS_ID=invtxns2_.CS_ID(+) and
invtxns2_.IV_STID=stshdr3_.ST_ID(+) and (csthdr0_.EN_ID in (4060))
and cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account'
and csthdr0_.CS_ACTIVE=1 group by stshdr3_.ST_NAME , stshdr3_.ST_ID )
where rownum <= :1
RATIONALE: The SQL statement with SQL_ID "30xt6anv0qu60" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 30xt6anv0qu60
DELETE FROM CST_AGEHDR WHERE CS_ID IN ( SELECT DISTINCT CS_ID FROM
INV_TXN WHERE IV_MODDT >= :B2 AND EN_ID=:B1 AND IV_TYPE='Invoice')
RATIONALE: The SQL statement with SQL_ID "66x402s6xbgp8" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 66x402s6xbgp8
select * from ( select /*+ ALL_ROWS */ stshdr3_.ST_NAME as col_0_0_,
sum(csthdr0_.CS_NUM9) as col_1_0_, sum(cstagehdr1_.CS_AGEBUCKET1) as
col_2_0_, sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_, stshdr3_.ST_ID as
col_10_0_ from CST_HDR csthdr0_, CST_AGEHDR cstagehdr1_, INV_TXN
invtxns2_, STS_HDR stshdr3_ where csthdr0_.CS_ID=cstagehdr1_.CS_ID(+)
and csthdr0_.CS_ID=invtxns2_.CS_ID(+) and
invtxns2_.IV_STID=stshdr3_.ST_ID(+) and (csthdr0_.EN_ID in (4060))
and cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account'
and csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
stshdr3_.ST_NAME , stshdr3_.ST_ID ) where rownum <= :1
RATIONALE: The SQL statement with SQL_ID "7rj25xdy54jb6" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 7rj25xdy54jb6
select * from ( select /*+ INDEX(CSTHDR0_
CST_HDR_ENID_TYPE_ACTIVE_IDX) */ invtxns2_.IV_ASSGNTO as col_0_0_,
sum(csthdr0_.CS_NUM9) as col_1_0_, sum(cstagehdr1_.CS_AGEBUCKET1) as
col_2_0_, sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_ from CST_HDR csthdr0_,
CST_AGEHDR cstagehdr1_, INV_TXN invtxns2_ where
csthdr0_.CS_ID=cstagehdr1_.CS_ID(+) and
csthdr0_.CS_ID=invtxns2_.CS_ID(+) and (csthdr0_.EN_ID in (4060)) and
cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account' and
csthdr0_.CS_ACTIVE=1 group by invtxns2_.IV_ASSGNTO ) where rownum <=
:1
RATIONALE: The SQL statement with SQL_ID "3yutmadbnc2p7" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 3yutmadbnc2p7
select * from ( select /*+ INDEX(CSTHDR0_
CST_HDR_ENID_TYPE_ACTIVE_IDX) */ invtxns2_.IV_ASSGNTO as col_0_0_,
sum(csthdr0_.CS_NUM9) as col_1_0_, sum(cstagehdr1_.CS_AGEBUCKET1) as
col_2_0_, sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_ from CST_HDR csthdr0_,
CST_AGEHDR cstagehdr1_, INV_TXN invtxns2_ where
csthdr0_.CS_ID=cstagehdr1_.CS_ID(+) and
csthdr0_.CS_ID=invtxns2_.CS_ID(+) and (csthdr0_.EN_ID in (4060)) and
cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account' and
csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
invtxns2_.IV_ASSGNTO ) where rownum <= :1
RECOMMENDATION 2: Segment Tuning, 2.6% benefit (4315 seconds)
ACTION: Investigate application logic involving I/O on TABLE
"DNBI.CMTS_LOG" with object id 56446.
RELEVANT OBJECT: database object with id 56446
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 72699 physical reads, 2787 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID "65f4pm9xf7fb4" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 65f4pm9xf7fb4
select * from ( select cmtslog0_.CM_CRTDT as col_0_0_,
cmtslog0_.CM_ACTION as col_1_0_, cmtslog0_.CM_TEXT as col_2_0_,
csthdr2_.CS_REF1 as col_3_0_, csthdr2_.CS_NAME as col_4_0_,
invtxn1_.IV_ASSGNTO as col_5_0_, invtxn1_.IV_STR8 as col_6_0_,
invtxn1_.IV_GPINVNO as col_7_0_, cmtslog0_.CM_IVDATE as col_8_0_,
invtxn1_.IV_TLAMT as col_9_0_, accrtxn10_.AR_OUTAMT as col_10_0_,
cmtslog0_.CM_CRTDBY as col_11_0_, invtxn1_.IV_RECTYPE as col_12_0_,
invtxn1_.IV_STR4 as col_13_0_, cmtslog0_.CM_IVID as col_14_0_ from
CMTS_LOG cmtslog0_, INV_TXN invtxn1_, CST_HDR csthdr2_, ACCR_TXN
accrtxn10_ where cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and
invtxn1_.CS_ID=csthdr2_.CS_ID and invtxn1_.IV_ID=accrtxn10_.IV_ID and
invtxn1_.IV_DATE=accrtxn10_.IV_DATE and (invtxn1_.EN_ID in (4060))
and invtxn1_.IV_CLOSED=0 order by cmtslog0_.CM_CRTDT DESC,
cmtslog0_.CM_IVID DESC, cmtslog0_.CM_IVDATE DESC ) where rownum <= :1
RATIONALE: The SQL statement with SQL_ID "aq0tr1bvzkkqj" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID aq0tr1bvzkkqj
select * from ( select cmtslog0_.CM_CRTDT as col_0_0_,
cmtslog0_.CM_ACTION as col_1_0_, cmtslog0_.CM_TEXT as col_2_0_,
csthdr2_.CS_REF1 as col_3_0_, csthdr2_.CS_NAME as col_4_0_,
invtxn1_.IV_ASSGNTO as col_5_0_, invtxn1_.IV_STR8 as col_6_0_,
invtxn1_.IV_GPINVNO as col_7_0_, cmtslog0_.CM_IVDATE as col_8_0_,
invtxn1_.IV_TLAMT as col_9_0_, accrtxn10_.AR_OUTAMT as col_10_0_,
cmtslog0_.CM_CRTDBY as col_11_0_, invtxn1_.IV_RECTYPE as col_12_0_,
invtxn1_.IV_STR4 as col_13_0_, cmtslog0_.CM_IVID as col_14_0_ from
CMTS_LOG cmtslog0_, INV_TXN invtxn1_, CST_HDR csthdr2_, ACCR_TXN
accrtxn10_ where cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and
invtxn1_.CS_ID=csthdr2_.CS_ID and invtxn1_.IV_ID=accrtxn10_.IV_ID and
invtxn1_.IV_DATE=accrtxn10_.IV_DATE and (invtxn1_.EN_ID in (4060))
and invtxn1_.IV_CLOSED=0 order by cmtslog0_.CM_CRTDT ASC,
cmtslog0_.CM_IVID ASC, cmtslog0_.CM_IVDATE ASC ) where rownum <= :1
RATIONALE: The SQL statement with SQL_ID "26qtwk3ghht5d" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 26qtwk3ghht5d
select * from ( select cmtslog0_.CM_CRTDT as col_0_0_,
cmtslog0_.CM_ACTION as col_1_0_, cmtslog0_.CM_TEXT as col_2_0_,
csthdr2_.CS_REF1 as col_3_0_, csthdr2_.CS_NAME as col_4_0_,
invtxn1_.IV_ASSGNTO as col_5_0_, invtxn1_.IV_STR8 as col_6_0_,
invtxn1_.IV_GPINVNO as col_7_0_, cmtslog0_.CM_IVDATE as col_8_0_,
invtxn1_.IV_TLAMT as col_9_0_, accrtxn10_.AR_OUTAMT as col_10_0_,
cmtslog0_.CM_CRTDBY as col_11_0_, invtxn1_.IV_RECTYPE as col_12_0_,
invtxn1_.IV_STR4 as col_13_0_, cmtslog0_.CM_IVID as col_14_0_ from
CMTS_LOG cmtslog0_, INV_TXN invtxn1_, CST_HDR csthdr2_, ACCR_TXN
accrtxn10_ where cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and
invtxn1_.CS_ID=csthdr2_.CS_ID and invtxn1_.IV_ID=accrtxn10_.IV_ID and
invtxn1_.IV_DATE=accrtxn10_.IV_DATE and (invtxn1_.EN_ID in (4060))
and invtxn1_.IV_CLOSED=0 and
trunc(cmtslog0_.CM_CRTDT)>=to_date('11-05-26', 'yy-mm-dd') ) where
rownum <= :1
RATIONALE: The SQL statement with SQL_ID "5qg2fqx79yc9q" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 5qg2fqx79yc9q
select * from ( select invtxn1_.IV_GPINVNO as col_0_0_,
cmtslog0_.CM_TEXT as col_1_0_, cmtslog0_.CM_CRTDT as col_2_0_,
cmtslog0_.CLOG_ID as col_3_0_, invtxn1_.CS_ID as col_4_0_,
cmtslog0_.CM_IVID as col_5_0_, cmtslog0_.CM_IVDATE as col_6_0_ from
CMTS_LOG cmtslog0_, INV_TXN invtxn1_ where
cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and (invtxn1_.EN_ID in (3910))
and invtxn1_.IV_TYPE='Invoice' order by cmtslog0_.CM_CRTDT DESC )
where rownum <= :1
RATIONALE: The SQL statement with SQL_ID "f0k8fqpkdxk7u" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID f0k8fqpkdxk7u
select * from ( select csthdr2_.CS_REF1 as col_0_0_, csthdr2_.CS_NAME
as col_1_0_, cmtslog0_.CM_CRTDT as col_2_0_, cmtslog0_.CM_ACTION as
col_3_0_, cmtslog0_.CM_CRTDBY as col_4_0_, cmtslog0_.CM_TEXT as
col_5_0_, cmtslog0_.CM_IVID as col_6_0_, cmtslog0_.CM_IVDATE as
col_7_0_ from CMTS_LOG cmtslog0_, INV_TXN invtxn1_, CST_HDR csthdr2_
where cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and
invtxn1_.CS_ID=csthdr2_.CS_ID and (invtxn1_.EN_ID in (4160)) and
invtxn1_.IV_TYPE='Account' order by cmtslog0_.CM_CRTDT DESC ) where
rownum <= :1
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(15% impact [24774 seconds])
FINDING 5: 6.2% impact (10437 seconds)
--------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 6.2% benefit (10437 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using the
SAME methodology. You might also need to increase the number of disks
for better performance. Alternatively, consider using Oracle's
Automatic Storage Management solution.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 104 K per second for reads and 21 K per second for
writes. The average response time for single block reads was 17
milliseconds.
RECOMMENDATION 2: Host Configuration, 4.8% benefit (8122 seconds)
ACTION: The performance of file +DATA/capgent/capgent_data.dbf was
significantly worse than other files. If striping all files using the
SAME methodology is not possible, consider striping this file over
multiple disks.
RELEVANT OBJECT: database file
"+DATA/capgent/capgent_data.dbf"
RATIONALE: The average response time for single block reads for this
file was 17 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(15% impact [24774 seconds])
FINDING 6: 2.8% impact (4799 seconds)
-------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: SQL Tuning, 2.9% benefit (4815 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"cfcrvy77bpw5a".
RELEVANT OBJECT: SQL statement with SQL_ID cfcrvy77bpw5a and
PLAN_HASH 3059921105
update INV_TXN set IV_MODBY='System', IV_MODDT=sysdate,
IV_ASSGTPE=:1, IV_ASSGNTO=:2 where IV_ID in (:3 , :4 , :5 , :6 , :7 ,
:8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 ,
:20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31
, :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 ,
:43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54
, :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 ,
:66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77
, :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 ,
:89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 ,
:100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 , :108 , :109 ,
:110 , :111 , :112 , :113 , :114 , :115 , :116 , :117 , :118 , :119 ,
:120 , :121 , :122 , :123 , :124 , :125 , :126 , :127 , :128 , :129 ,
:130 , :131 , :132 , :133 , :134 , :135 , :136 , :137 , :138 , :139 ,
:140 , :141 , :142 , :143 , :144 , :145 , :146 , :147 , :148 , :149 ,
:150 , :151 , :152 , :153 , :154 , :155 , :156 , :157 , :158 , :159 ,
:160 , :161 , :162 , :163 , :164 , :165 , :166 , :167 , :168 , :169 ,
:170 , :171 , :172 , :173 , :174 , :175 , :176 , :177 , :178 , :179 ,
:180 , :181 , :182 , :183 , :184 , :185 , :186 , :187 , :188 , :189 ,
:190 , :191 , :192 , :193 , :194 , :195 , :196 , :197 , :198 , :199 ,
:200 , :201 , :202 , :203 , :204 , :205 , :206 , :207 , :208 , :209 ,
:210 , :211 , :212 , :213 , :214 , :215 , :216 , :217 , :218 , :219 ,
:220 , :221 , :222 , :223 , :224 , :225 , :226 , :227 , :228 , :229 ,
:230 , :231 , :232 , :233 , :234 , :235 , :236 , :237 , :238 , :239 ,
:240 , :241 , :242 , :243 , :244 , :245 , :246 , :247 , :248 , :249 ,
:250 , :251 , :252 , :253 , :254 , :255 , :256 , :257 , :258 , :259 ,
:260 , :261 , :262 , :263 , :264 , :265 , :266 , :267 , :268 , :269 ,
:270 , :271 , :272 , :273 , :274 , :275 , :276 , :277 , :278 , :279 ,
:280 , :281 , :282 , :283 , :284 , :285 , :286 , :287 , :288 , :289 ,
:290 , :291 , :292 , :293 , :294 , :295 , :296 , :297 , :298 , :299 ,
:300 , :301 , :302 , :303 , :304 , :305 , :306 , :307 , :308 , :309 ,
:310 , :311 , :312 , :313 , :314 , :315 , :316 , :317 , :318 , :319 ,
:320 , :321 , :322 , :323 , :324 , :325 , :326 , :327 , :328 , :329 ,
:330 , :331 , :332 , :333 , :334 , :335 , :336 , :337 , :338 , :339 ,
:340 , :341 , :342 , :343 , :344 , :345 , :346 , :347 , :348 , :349 ,
:350 , :351 , :352 , :353 , :354 , :355 , :356 , :357 , :358 , :359 ,
:360 , :361 , :362 , :363 , :364 , :365 , :366 , :367 , :368 , :369 ,
:370 , :371 , :372 , :373 , :374 , :375 , :376 , :377 , :378 , :379 ,
:380 , :381 , :382 , :383 , :384 , :385 , :386 , :387 , :388 , :389 ,
:390 , :391 , :392 , :393 , :394 , :395 , :396 , :397 , :398 , :399 ,
:400 , :401 , :402 , :403 , :404 , :405 , :406 , :407 , :408 , :409 ,
:410 , :411 , :412 , :413 , :414 , :415 , :416 , :417 , :418 , :419 ,
:420 , :421 , :422 , :423 , :424 , :425 , :426 , :427 , :428 , :429 ,
:430 , :431 , :432 , :433 , :434 , :435 , :436 , :437 , :438 , :439 ,
:440 , :441 , :442 , :443 , :444 , :445 , :446 , :447 , :448 , :449 ,
:450 , :451 , :452 , :453 , :454 , :455 , :456 , :457 , :458 , :459 ,
:460 , :461 , :462 , :463 , :464 , :465 , :466 , :467 , :468 , :469 ,
:470 , :471 , :472 , :473 , :474 , :475 , :476 , :477 , :478 , :479 ,
:480 , :481 , :482 , :483 , :484 , :485 , :486 , :487 , :488 , :489 ,
:490 , :491 , :492 , :493 , :494 , :495 , :496 , :497 , :498 , :499 ,
:500 , :501 , :502 , :503 , :504 , :505 , :506 , :507 , :508 , :509 ,
:510 , :511 , :512 , :513 , :514 , :515 , :516 , :517 , :518 , :519 ,
:520 , :521 , :522 , :523 , :524 , :525 , :526 , :527 , :528 , :529 ,
:530 , :531 , :532 , :533 , :534 , :535 , :536 , :537 , :538 , :539 ,
:540 , :541 , :542 , :543 , :544 , :545 , :546 , :547 , :548 , :549 ,
:550 , :551 , :552 , :553 , :554 , :555 , :556 , :557 , :558 , :559 ,
:560 , :561 , :562 , :563 , :564 , :565 , :566 , :567 , :568 , :569 ,
:570 , :571 , :572 , :573 , :574 , :575 , :576 , :577 , :578 , :579 ,
:580 , :581 , :582 , :583 , :584 , :585 , :586 , :587 , :588 , :589 ,
:590 , :591 , :592 , :593 , :594 , :595 , :596 , :597 , :598 , :599 ,
:600 , :601 , :602 , :603 , :604 , :605 , :606 , :607 , :608 , :609 ,
:610 , :611 , :612 , :613 , :614 , :615 , :616 , :617 , :618 , :619 ,
:620 , :621 , :622 , :623 , :624 , :625 , :626 , :627 , :628 , :629 ,
:630 , :631 , :632 , :633 , :634 , :635 , :636 , :637 , :638 , :639 ,
:640 , :641 , :642 , :643 , :644 , :645 , :646 , :647 , :648 , :649 ,
:650 , :651 , :652 , :653 , :654 , :655 , :656 , :657 , :658 , :659 ,
:660 , :661 , :662 , :663 , :664 , :665 , :666 , :667 , :668 , :669 ,
:670 , :671 , :672 , :673 , :674 , :675 , :676 , :677 , :678 , :679 ,
:680 , :681 , :682 , :683 , :684 , :685 , :686 , :687 , :688 , :689 ,
:690 , :691 , :692 , :693 , :694 , :695 , :696 , :697 , :698 , :699 ,
:700 , :701 , :702 , :703 , :704 , :705 , :706 , :707 , :708 , :709 ,
:710 , :711 , :712 , :713 , :714 , :715 , :716 , :717 , :718 , :719 ,
:720 , :721 , :722 , :723 , :724 , :725 , :726 , :727 , :728 , :729 ,
:730 , :731 , :732 , :733 , :734 , :735 , :736 , :737 , :738 , :739 ,
:740 , :741 , :742 , :743 , :744 , :745 , :746 , :747 , :748 , :749 ,
:750 , :751 , :752 , :753 , :754 , :755 , :756 , :757 , :758 , :759 ,
:760 , :761 , :762 , :763 , :764 , :765 , :766 , :767 , :768 , :769 ,
:770 , :771 , :772 , :773 , :774 , :775 , :776 , :777 , :778 , :779 ,
:780 , :781 , :782 , :783 , :784 , :785 , :786 , :787 , :788 , :789 ,
:790 , :791 , :792 , :793 , :794 , :795 , :796 , :797 , :798 , :799 ,
:800 , :801 , :802 , :803 , :804 , :805 , :806 , :807 , :808 , :809 ,
:810 , :811 , :812 , :813 , :814 , :815 , :816 , :817 , :818 , :819 ,
:820 , :821 , :822 , :823 , :824 , :825 , :826 , :827 , :828 , :829 ,
:830 , :831 , :832 , :833 , :834 , :835 , :836 , :837 , :838 , :839 ,
:840 , :841 , :842 , :843 , :844 , :845 , :846 , :847 , :848 , :849 ,
:850 , :851 , :852 , :853 , :854 , :855 , :856 , :857 , :858 , :859 ,
:860 , :861 , :862 , :863 , :864 , :865 , :866 , :867 , :868 , :869 ,
:870 , :871 , :872 , :873 , :874 , :875 , :876 , :877 , :878 , :879 ,
:880 , :881 , :882 , :883 , :884 , :885 , :886 , :887 , :888 , :889 ,
:890 , :891 , :892 , :893 , :894 , :895 , :896 , :897 , :898 , :899 ,
:900 , :901 , :902 , :903 , :904 , :905 , :906 , :907 , :908 , :909 ,
:910 , :911 , :912 , :913 , :914 , :915 , :916 , :917 , :918 , :919 ,
:920 , :921 , :922 , :923 , :924 , :925 , :926 , :927 , :928 , :929 ,
:930 , :931 , :932 , :933 , :934 , :935 , :936 , :937 , :938 , :939 ,
:940 , :941 , :942 , :943 , :944 , :945 , :946 , :947 , :948 , :949 ,
:950 , :951 , :952 , :953 , :954 , :955 , :956 , :957 , :958 , :959 ,
:960 , :961 , :962 , :963 , :964 , :965 , :966 , :967 , :968 , :969 ,
:970 , :971 , :972 , :973 , :974 , :975 , :976 , :977 , :978 , :979 ,
:980 , :981 , :982 , :983 , :984 , :985 , :986 , :987 , :988 , :989 ,
:990 , :991 , :992 , :993 , :994 , :995 , :996 , :997 , :998 , :999 ,
:1000 , :1001)
RATIONALE: SQL statement with SQL_ID "cfcrvy77bpw5a" was executed 7
times and had an average elapsed time of 687 seconds.
RATIONALE: Average CPU used per execution was 685 seconds.
FINDING 7: 1.9% impact (3282 seconds)
-------------------------------------
Buffer cache writes due to setting of the obsolete parameters
"fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout"
were consuming significant database time.
RECOMMENDATION 1: DB Configuration, 1.9% benefit (3282 seconds)
ACTION: Oracle's recommended solution is to control MTTR setting using
the "fast_start_mttr_target" parameter instead of the
"fast_start_io_target", "log_checkpoint_interval" and
"log_checkpoint_timeout" parameters.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (6.2% impact [10437 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (15% impact [24774 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Cluster" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The database's maintenance windows were active during 43% of the analysis
period.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.