DETAILED ADDM REPORT FOR TASK 'TASK_6341' WITH ID 6341
------------------------------------------------------
Analysis Period: 22-OCT-2007 from 13:00:41 to 17:30:43
Database ID/Instance: 1581744463/1
Database/Instance Names: PSMIS/psmis
Host Name: psmis_p0
Database Version: 10.2.0.3.0
Snapshot Range: from 5653 to 5662
Database Time: 208990 seconds
Average Database Load: 12.9 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 56% impact (117416 seconds)
--------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 15% benefit (32194 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "gn9sxf3bbqznp". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID gn9sxf3bbqznp
BEGIN Pack_Ec_Report_Main.pro_ec_account_month0(:1,:2,:3,:4,:5,:6,:7)
; END;
RATIONALE: SQL statement with SQL_ID "gn9sxf3bbqznp" was executed 1
times and had an average elapsed time of 32193 seconds.
RECOMMENDATION 2: SQL Tuning, 15% benefit (32086 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"445p9438cbkpy".
RELEVANT OBJECT: SQL statement with SQL_ID 445p9438cbkpy and
PLAN_HASH 3518493936
SELECT /*+index(tb,IDX_EC_ACC_FEE_BACK11)*/ COUNT(DISTINCT TA.ID),
COUNT(DISTINCT DECODE(TA.MR_STATE_W, '1', TA.ID, NULL)),
COUNT(DISTINCT DECODE(TA.MR_STATE_W, '0', NULL, '1', NULL, TA.ID))
FROM EC_MR_USER_DATA_BACK TA, EC_ACC_FEE_BACK TB WHERE TA.ZONE_CODE
|| '' = :B2 AND TA.ELECTRI_FEE_MONTH = :B1 AND TA.RUSH_RED_NUM = 0
AND TA.USER_ID = TB.USER_ID AND TA.MR_TIMES = TB.FACT_MR_TIMES AND
NVL(TB.RUSH_RED_NUM, 0) = 0 AND NVL(TB.USER_TYPE, '0') IN ('0', '1',
'8', '14', '15') AND SUBSTR(TB.ELECTRIC_POWER_CT, 1, 1) = :B4 AND
NVL(TB.TIME_SHAR_FLAG, 0) LIKE DECODE(:B3 , NULL, '%', :B3 ) AND
TB.ELEC_NUMBER IN (SELECT ELEC_NUMBER FROM EC_REPORT_ITEM_SORT_EV T1
WHERE EXISTS (SELECT 'X' FROM EC_REPORT_ITEM_SORT T2 WHERE T2.ID =
T1.MAIN_ID AND ELECTRIC_POWER_CT = :B4 AND POWER_SV = :B6 AND
TIME_SHAR_FLAG IS NULL AND ESPECIAL_FLAG = :B5 )) AND TB.ZONE_CODE =
:B2 AND TB.ACC_FEE_DATE LIKE :B1 || '%'
RATIONALE: SQL statement with SQL_ID "445p9438cbkpy" was executed 513
times and had an average elapsed time of 62 seconds.
RECOMMENDATION 3: SQL Tuning, 10% benefit (21529 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "auhpf33d4g5m1". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID auhpf33d4g5m1
BEGIN PACK_EC_ACCOUNT.PRO_AFFIRM_CHARGE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:1
0,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24) ; END;
RATIONALE: SQL statement with SQL_ID "auhpf33d4g5m1" was executed 6803
times and had an average elapsed time of 3.1 seconds.
RECOMMENDATION 4: SQL Tuning, 9.2% benefit (19251 seconds)
ACTION: Investigate the SQL statement with SQL_ID "bbmcbx5x3uq1c" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID bbmcbx5x3uq1c and
PLAN_HASH 2355517755
update EC_BILL_PRINT set INV_TYPE=:"SYS_B_0" where
ELECTRI_FEE_MONTH=:"SYS_B_1" and USER_CODE=:"SYS_B_2"
RATIONALE: SQL statement with SQL_ID "bbmcbx5x3uq1c" was executed 11154
times and had an average elapsed time of 1.6 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 "bbmcbx5x3uq1c".
RECOMMENDATION 5: SQL Tuning, 6% benefit (12598 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "1p76srxy3ty8u". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 1p76srxy3ty8u
BEGIN PACK_EC_ACCOUNT.PRO_CANCEL_CHARGE(:1,:2,:3,:4,:5) ; END;
RATIONALE: SQL statement with SQL_ID "1p76srxy3ty8u" was executed 1368
times and had an average elapsed time of 9.2 seconds.
FINDING 2: 28% impact (59367 seconds)
-------------------------------------
SQL statements were found waiting for row lock waits.
RECOMMENDATION 1: Application Analysis, 17% benefit (34750 seconds)
ACTION: Significant row contention was detected in the TABLE
"PSMIS.EC_BILL_PRINT" with object id 17508. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 17508
RATIONALE: The SQL statement with SQL_ID "bbmcbx5x3uq1c" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID bbmcbx5x3uq1c
update EC_BILL_PRINT set INV_TYPE=:"SYS_B_0" where
ELECTRI_FEE_MONTH=:"SYS_B_1" and USER_CODE=:"SYS_B_2"
RATIONALE: The SQL statement with SQL_ID "461zgv3c5mq45" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 461zgv3c5mq45
UPDATE /*+INDEX(EC_BILL_PRINT,IDX_EC_BILL_PRINT1)*/ EC_BILL_PRINT SET
WILL_RECEIVE_LATE_FEE = NVL(:B6 , 0), CHECK_ID = :B5 WHERE USER_CODE
= :B4 AND ELECTRI_FEE_MONTH = :B3 AND MR_TIMES = :B2 AND
NVL(RUSH_RED_NUM, 0) = :B1
RATIONALE: The SQL statement with SQL_ID "29asjs4c59ck8" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 29asjs4c59ck8
update EC_BILL_PRINT set WILL_RECEIVE_LATE_FEE=(:b0/:"SYS_B_0")
where ((USER_CODE=:b1 and ELECTRI_FEE_MONTH=:b2) and MR_TIMES=:b3)
RATIONALE: The SQL statement with SQL_ID "fq653rf7b054r" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID fq653rf7b054r
UPDATE EC_BILL_PRINT SET PRINT_FLAG = '0', TAX_INVOICE_PRINTER =
NULL, TAX_INVOICE_TIME = NULL, TAX_INVOICE_CODE = NULL WHERE ID = :B1
RATIONALE: The SQL statement with SQL_ID "5w4g3askbyr2u" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 5w4g3askbyr2u
update EC_BILL_PRINT set
CHECK_ACCTS_DATE=to_char(sysdate,:"SYS_B_0") where (((USER_CODE=:b0
and ELECTRI_FEE_MONTH=:b1) and MR_TIMES=:b2) and
CREDENCE_CLASS=:"SYS_B_1")
RATIONALE: Session with ID "2224", User ID "40", Program "" and Module
"" was the blocking session responsible for 42% of this
recommendation's benefit.
RATIONALE: Session with ID "2142", User ID "40", Program "" and Module
"" was the blocking session responsible for 37% of this
recommendation's benefit.
RECOMMENDATION 2: Application Analysis, 5.6% benefit (11788 seconds)
ACTION: Significant row contention was detected in the TABLE
"PSMIS.EC_CHARGE_PRINT_TMP" with object id 14583. Trace the cause of
row contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 14583
RATIONALE: The SQL statement with SQL_ID "dqu0hxgpv9bzv" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID dqu0hxgpv9bzv
DELETE EC_CHARGE_PRINT_TMP WHERE CHARGE_MEN = :B1
RATIONALE: Session with ID "2599", User ID "40", Program "" and Module
"" was the blocking session responsible for 76% of this
recommendation's benefit.
RATIONALE: Session with ID "3359", User ID "40", Program "" and Module
"" was the blocking session responsible for 23% of this
recommendation's benefit.
RECOMMENDATION 3: Application Analysis, 3.2% benefit (6692 seconds)
ACTION: Significant row contention was detected in the TABLE
"PSMIS.EC_CHARGE_TMP" with object id 14601. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 14601
RATIONALE: The SQL statement with SQL_ID "c88zzg1hymtjk" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID c88zzg1hymtjk
DELETE EC_CHARGE_TMP WHERE CHARGE_PEPO = :B1
RATIONALE: Session with ID "3437", User ID "40", Program "" and Module
"" was the blocking session responsible for 40% of this
recommendation's benefit.
RATIONALE: Session with ID "2087", User ID "40", Program "" and Module
"" was the blocking session responsible for 36% of this
recommendation's benefit.
RATIONALE: Session with ID "2145", User ID "40", Program "" and Module
"" was the blocking session responsible for 22% of this
recommendation's benefit.
RECOMMENDATION 4: Application Analysis, 2.7% benefit (5641 seconds)
ACTION: Significant row contention was detected in the TABLE
"PSMIS.EC_ACCOUNT" with object id 17507. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 17507
RATIONALE: The SQL statement with SQL_ID "1gj6avsqvcvqy" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 1gj6avsqvcvqy
UPDATE /*+INDEX(EC_ACCOUNT,IDX_EC_ACCOUNT3)*/ EC_ACCOUNT SET
PROCESS_STATE = '0', CHARGE_MAN = NULL, CHARGE_DATE = NULL,
TOTAL_MONEY = NVL(TOTAL_ELECTRI_FEE, 0) + NVL(PROPHASE_LATE_FEE, 0)
WHERE ZONE_CODE IS NOT NULL AND PROCESS_STATE = '9' AND CHARGE_MAN =
:B1
RATIONALE: Session with ID "2599", User ID "40", Program "" and Module
"" was the blocking session responsible for 54% of this
recommendation's benefit.
RATIONALE: Session with ID "3359", User ID "40", Program "" and Module
"" was the blocking session responsible for 35% of this
recommendation's benefit.
RATIONALE: Session with ID "2822", User ID "40", Program "" and Module
"" was the blocking session responsible for 10% of this
recommendation's benefit.
RECOMMENDATION 5: Application Analysis, 0.17% benefit (347 seconds)
ACTION: Significant row contention was detected in the TABLE
"PSMIS.EC_YHYBDZQD" with object id 15090. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 15090
RATIONALE: The SQL statement with SQL_ID "4xnx2fv83s3jg" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 4xnx2fv83s3jg
DELETE /*+INDEX(Ec_Yhybdzqd, IND_EC_YHYBDZQD3)*/ FROM EC_YHYBDZQD
WHERE ELECTRI_FEE_MONTH = :B7 AND ZONE_CODE = :B6 AND MR_TIMES = :B5
AND OPERATER = :B4 AND DECODE(:B3 , 1, USER_CODE, 2, LINE_CODE, 3,
ACCOUNT_ID) BETWEEN :B2 AND :B1
RATIONALE: Session with ID "2711", User ID "40", Program "" and Module
"" was the blocking session responsible for 100% of this
recommendation's benefit.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (28% impact [59373 seconds])
FINDING 3: 21% impact (42910 seconds)
-------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: SQL Tuning, 15% benefit (32086 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"445p9438cbkpy".
RELEVANT OBJECT: SQL statement with SQL_ID 445p9438cbkpy and
PLAN_HASH 3518493936
SELECT /*+index(tb,IDX_EC_ACC_FEE_BACK11)*/ COUNT(DISTINCT TA.ID),
COUNT(DISTINCT DECODE(TA.MR_STATE_W, '1', TA.ID, NULL)),
COUNT(DISTINCT DECODE(TA.MR_STATE_W, '0', NULL, '1', NULL, TA.ID))
FROM EC_MR_USER_DATA_BACK TA, EC_ACC_FEE_BACK TB WHERE TA.ZONE_CODE
|| '' = :B2 AND TA.ELECTRI_FEE_MONTH = :B1 AND TA.RUSH_RED_NUM = 0
AND TA.USER_ID = TB.USER_ID AND TA.MR_TIMES = TB.FACT_MR_TIMES AND
NVL(TB.RUSH_RED_NUM, 0) = 0 AND NVL(TB.USER_TYPE, '0') IN ('0', '1',
'8', '14', '15') AND SUBSTR(TB.ELECTRIC_POWER_CT, 1, 1) = :B4 AND
NVL(TB.TIME_SHAR_FLAG, 0) LIKE DECODE(:B3 , NULL, '%', :B3 ) AND
TB.ELEC_NUMBER IN (SELECT ELEC_NUMBER FROM EC_REPORT_ITEM_SORT_EV T1
WHERE EXISTS (SELECT 'X' FROM EC_REPORT_ITEM_SORT T2 WHERE T2.ID =
T1.MAIN_ID AND ELECTRIC_POWER_CT = :B4 AND POWER_SV = :B6 AND
TIME_SHAR_FLAG IS NULL AND ESPECIAL_FLAG = :B5 )) AND TB.ZONE_CODE =
:B2 AND TB.ACC_FEE_DATE LIKE :B1 || '%'
RATIONALE: SQL statement with SQL_ID "445p9438cbkpy" was executed 513
times and had an average elapsed time of 62 seconds.
RATIONALE: Average CPU used per execution was 62 seconds.
RECOMMENDATION 2: SQL Tuning, 5.2% benefit (10826 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"7jn23tuhndbb6".
RELEVANT OBJECT: SQL statement with SQL_ID 7jn23tuhndbb6 and
PLAN_HASH 357127085
select * from ( select ecuserbank0_.USER_ID as USER1_246_,
ecuserbank0_.TRANSFER_TIME as TRANSFER2_246_, ecuserbank0_.ZONE_CODE
as ZONE3_246_, ecuserbank0_.ACCOUNT_ID as ACCOUNT4_246_,
ecuserbank0_.ELECTRICITY_WORDS_NUMBER as ELECTRIC5_246_,
ecuserbank0_.ACCOUNT_NAME as ACCOUNT6_246_,
ecuserbank0_.ACCOUNT_BANK_NO as ACCOUNT7_246_,
ecuserbank0_.ACCOUNT_BANK_ACCOUNTS as ACCOUNT8_246_,
ecuserbank0_.TAX_NUMBER as TAX9_246_,
ecuserbank0_.TAX_PAYMENT_ADDRESS as TAX10_246_,
ecuserbank0_.CHARGE_WAY as CHARGE11_246_, ecuserbank0_.POST_ADDR as
POST12_246_, ecuserbank0_.SPNAME as SPNAME246_,
ecuserbank0_.CONTACT_PHONE_NUMBER as CONTACT14_246_,
ecuserbank0_.PROPOSER_ID_CARD as PROPOSER15_246_,
ecuserbank0_.AGENT_NAME as AGENT16_246_, ecuserbank0_.AENT_ID_CARD as
AENT17_246_, ecuserbank0_.USER_CODE as USER18_246_,
ecuserbank0_.TRANS_BANK_NO as TRANS19_246_, ecuserbank0_.PACT_CODE as
PACT20_246_, ecuserbank0_.JJ_BANK_NO as JJ21_246_,
ecuserbank0_.CONTACT_PERSON as CONTACT22_246_, ecuserbank0_.SFYJ as
SFYJ246_ from EC_USER_BANK_ACCOUNT_FILE ecuserbank0_ where
:"SYS_B_0"=:"SYS_B_1" and (ecuserbank0_.ACCOUNT_BANK_ACCOUNTS like
:"SYS_B_2") and (ecuserbank0_.ZONE_CODE like :"SYS_B_3") ) where
rownum <= :1
RATIONALE: SQL statement with SQL_ID "7jn23tuhndbb6" was executed 1514
times and had an average elapsed time of 7.1 seconds.
RATIONALE: Average CPU used per execution was 7.1 seconds.
FINDING 4: 8.9% impact (18613 seconds)
--------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 8.9% benefit (18613 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 50176 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "28672 M" during the analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(15% impact [30582 seconds])
FINDING 5: 4.9% impact (10194 seconds)
--------------------------------------
Individual database segments responsible for significant user I/O wait were
found.
RECOMMENDATION 1: Segment Tuning, 2.9% benefit (5964 seconds)
ACTION: Investigate application logic involving I/O on TABLE
"PSMIS.EC_ACCOUNT" with object id 17507.
RELEVANT OBJECT: database object with id 17507
RATIONALE: The I/O usage statistics for the object are: 46 full object
scans, 2654059 physical reads, 144716 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "fgd6u8ngb8424" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID fgd6u8ngb8424
select * from ( select count(*) as y0_ from EC_ACCOUNT this_ where
this_.CHARGE_WAY=:1 ) where rownum <= :2
RATIONALE: The SQL statement with SQL_ID "ambdxqc5j3cp5" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID ambdxqc5j3cp5
select * from ( select count(*) as y0_ from EC_ACCOUNT this_ where
this_.TOTAL_MONEY=:1 ) where rownum <= :2
RATIONALE: The SQL statement with SQL_ID "83s6tcfzbh8p9" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 83s6tcfzbh8p9
SELECT /*+INDEX(EC_ACCOUNT,IDX_EC_ACCOUNT2)*/
SUM(TA.TOTAL_ELECTRI_FEE), COUNT(TA.ELECTRI_FEE_MONTH ||
TA.USER_CODE) FROM EC_ACCOUNT TA WHERE TA.ZONE_CODE = :B3 AND
TA.CREDENCE_CLASS = '1' AND TA.ELECTRI_FEE_MONTH BETWEEN :B2 AND :B1
AND TA.MR_TIMES IS NOT NULL AND EXISTS (SELECT
/*+INDEX(BE_USER,IDX_BE_USER6)*/ 'X' FROM BE_USER TB WHERE
TB.ZONE_CODE = :B3 AND TB.LINE_CODE = :B4 AND TB.USER_CODE =
TA.USER_CODE)
RATIONALE: The SQL statement with SQL_ID "0274uypz7q51x" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 0274uypz7q51x
select * from ( select this_.ACCOUNT_RECE_CODE as ACCOUNT1_268_0_,
this_.ZONE_CODE as ZONE2_268_0_, this_.PSB_ACCOUNTS as PSB3_268_0_,
this_.PSB_ACCOUNTS_NAME as PSB4_268_0_, this_.MR_PLAN_ID as
MR5_268_0_, this_.LINE_CODE_ID as LINE6_268_0_, this_.LINE_CODE as
LINE7_268_0_, this_.ELECTRI_FEE_MONTH as ELECTRI8_268_0_,
this_.MR_TIMES as MR9_268_0_, this_.ACCOUNT_ID as ACCOUNT10_268_0_,
this_.ELECTRICITY_WORDS_NUMBER as ELECTRI11_268_0_, this_.GEN_USR_NO
as GEN12_268_0_, this_.USER_ID as USER13_268_0_, this_.USER_CODE as
USER14_268_0_, this_.ELECT_ID as ELECT15_268_0_, this_.RUSH_RED_NUM
as RUSH16_268_0_, this_.CHARGE_WAY as CHARGE17_268_0_,
this_.CREDENCE_CLASS as CREDENCE18_268_0_, this_.ACCOUNT_BANK_CODE as
ACCOUNT19_268_0_, this_.ACCOUNT_BANK_NAME as ACCOUNT20_268_0_,
this_.ACCOUNT_BANK_NO as ACCOUNT21_268_0_,
this_.ACCOUNT_BANK_ACCOUNTS as ACCOUNT22_268_0_,
this_.SEND_SHEET_DATE as SEND23_268_0_, this_.ELECTRI_QUANTITY as
ELECTRI24_268_0_, this_.TOTAL_ELECTRI_FEE as TOTAL25_268_0_,
this_.PROPHASE_LATE_FEE as PROPHASE26_268_0_, this_.IMPREST as
IMPREST268_0_, this_.TOTAL_MONEY as TOTAL28_268_0_,
this_.RECEIVED_MONEY as RECEIVED29_268_0_, this_.PROCESS_STATE as
PROCESS30_268_0_, this_.CHARGE_FLAG as CHARGE31_268_0_,
this_.CHECK_ACC_FLAG as CHECK32_268_0_, this_.CANCEL_ACC_FLAG as
CANCEL33_268_0_, this_.ACC_TYPE as ACC34_268_0_, this_.USER_PROP as
USER35_268_0_, this_.COUNTRY_CODE as COUNTRY36_268_0_,
this_.ACC_SHAPE_TIME as ACC37_268_0_, this_.TRANS_BANK_NAME as
TRANS38_268_0_, this_.TRANS_BANK_NO as TRANS39_268_0_,
this_.JJ_BANK_NAME as JJ40_268_0_, this_.JJ_BANK_NO as JJ41_268_0_,
this_.VIP_FLAG as VIP42_268_0_, this_.CHARGE_NUM as CHARGE43_268_0_,
this_.ELEC_NUMBER as ELEC44_268_0_, this_.USER_NAME as USER45_268_0_,
this_.POWER_ADDR as POWER46_268_0_, this_.PACT_CODE as PACT47_268_0_,
this_.TRANS_REC_ELEC as TRANS48_268_0_, this_.TRANS_REC_LATE_FEE as
TRANS49_268_0_, this_.WORK_SHEET_NO as WORK50_268_0_,
this_.CHARGE_DATE as CHARGE51_268_0_, this_.CHARGE_MAN as
CHARGE52_268_0_, this_.TRANS_MODE as TRANS53_268_0_,
this_.TRANS_BANK_CODE as TRANS54_268_0_, this_.TRANS_CURR_LATE_FEE as
TRANS55_268_0_, this_.ACCOUNT_NAME as ACCOUNT56_268_0_ from
EC_ACCOUNT this_ where this_.TOTAL_MONEY=:1 ) where rownum <= :2
RATIONALE: The SQL statement with SQL_ID "ag1g0qy54ns64" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID ag1g0qy54ns64
select * from ( select row_.*, rownum rownum_ from ( select
this_.ACCOUNT_RECE_CODE as ACCOUNT1_268_0_, this_.ZONE_CODE as
ZONE2_268_0_, this_.PSB_ACCOUNTS as PSB3_268_0_,
this_.PSB_ACCOUNTS_NAME as PSB4_268_0_, this_.MR_PLAN_ID as
MR5_268_0_, this_.LINE_CODE_ID as LINE6_268_0_, this_.LINE_CODE as
LINE7_268_0_, this_.ELECTRI_FEE_MONTH as ELECTRI8_268_0_,
this_.MR_TIMES as MR9_268_0_, this_.ACCOUNT_ID as ACCOUNT10_268_0_,
this_.ELECTRICITY_WORDS_NUMBER as ELECTRI11_268_0_, this_.GEN_USR_NO
as GEN12_268_0_, this_.USER_ID as USER13_268_0_, this_.USER_CODE as
USER14_268_0_, this_.ELECT_ID as ELECT15_268_0_, this_.RUSH_RED_NUM
as RUSH16_268_0_, this_.CHARGE_WAY as CHARGE17_268_0_,
this_.CREDENCE_CLASS as CREDENCE18_268_0_, this_.ACCOUNT_BANK_CODE as
ACCOUNT19_268_0_, this_.ACCOUNT_BANK_NAME as ACCOUNT20_268_0_,
this_.ACCOUNT_BANK_NO as ACCOUNT21_268_0_,
this_.ACCOUNT_BANK_ACCOUNTS as ACCOUNT22_268_0_,
this_.SEND_SHEET_DATE as SEND23_268_0_, this_.ELECTRI_QUANTITY as
ELECTRI24_268_0_, this_.TOTAL_ELECTRI_FEE as TOTAL25_268_0_,
this_.PROPHASE_LATE_FEE as PROPHASE26_268_0_, this_.IMPREST as
IMPREST268_0_, this_.TOTAL_MONEY as TOTAL28_268_0_,
this_.RECEIVED_MONEY as RECEIVED29_268_0_, this_.PROCESS_STATE as
PROCESS30_268_0_, this_.CHARGE_FLAG as CHARGE31_268_0_,
this_.CHECK_ACC_FLAG as CHECK32_268_0_, this_.CANCEL_ACC_FLAG as
CANCEL33_268_0_, this_.ACC_TYPE as ACC34_268_0_, this_.USER_PROP as
USER35_268_0_, this_.COUNTRY_CODE as COUNTRY36_268_0_,
this_.ACC_SHAPE_TIME as ACC37_268_0_, this_.TRANS_BANK_NAME as
TRANS38_268_0_, this_.TRANS_BANK_NO as TRANS39_268_0_,
this_.JJ_BANK_NAME as JJ40_268_0_, this_.JJ_BANK_NO as JJ41_268_0_,
this_.VIP_FLAG as VIP42_268_0_, this_.CHARGE_NUM as CHARGE43_268_0_,
this_.ELEC_NUMBER as ELEC44_268_0_, this_.USER_NAME as USER45_268_0_,
this_.POWER_ADDR as POWER46_268_0_, this_.PACT_CODE as PACT47_268_0_,
this_.TRANS_REC_ELEC as TRANS48_268_0_, this_.TRANS_REC_LATE_FEE as
TRANS49_268_0_, this_.WORK_SHEET_NO as WORK50_268_0_,
this_.CHARGE_DATE as CHARGE51_268_0_, this_.CHARGE_MAN as
CHARGE52_268_0_, this_.TRANS_MODE as TRANS53_268_0_,
this_.TRANS_BANK_CODE as TRANS54_268_0_, this_.TRANS_CURR_LATE_FEE as
TRANS55_268_0_, this_.ACCOUNT_NAME as ACCOUNT56_268_0_ from
EC_ACCOUNT this_ where this_.CHARGE_WAY=:1 ) row_ where rownum <= :2)
where rownum_ > :3
RECOMMENDATION 2: Segment Tuning, 2% benefit (4230 seconds)
ACTION: Run "Segment Advisor" on TABLE "PSMIS.EC_BILL_PRINT" with object
id 17508.
RELEVANT OBJECT: database object with id 17508
ACTION: Investigate application logic involving I/O on TABLE
"PSMIS.EC_BILL_PRINT" with object id 17508.
RELEVANT OBJECT: database object with id 17508
RATIONALE: The I/O usage statistics for the object are: 17 full object
scans, 1883947 physical reads, 64587 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "5bvkyj5cccwra" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 5bvkyj5cccwra
select count(*) from Ec_Bill_Print a where
substr(zone_code,:"SYS_B_00",:"SYS_B_01")=substr(:"SYS_B_02",:"SYS_B_
03",:"SYS_B_04") and check_Accts_Date is not null and
inv_Type=:"SYS_B_05" and print_Flag=:"SYS_B_06" and
:"SYS_B_07"=:"SYS_B_08" and
nvl(credence_Valid_Flag,:"SYS_B_09")=:"SYS_B_10" and
electricity_Words_Number >=:"SYS_B_11" and electricity_Words_Number
<=:"SYS_B_12" and electri_Fee_Month =:"SYS_B_13"
RATIONALE: The SQL statement with SQL_ID "31u0k4b6r5zh5" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 31u0k4b6r5zh5
select count(*) from Ec_Bill_Print a where
substr(zone_code,:"SYS_B_00",:"SYS_B_01")=substr(:"SYS_B_02",:"SYS_B_
03",:"SYS_B_04") and check_Accts_Date is not null and
inv_Type=:"SYS_B_05" and print_Flag=:"SYS_B_06" and
:"SYS_B_07"=:"SYS_B_08" and
nvl(credence_Valid_Flag,:"SYS_B_09")=:"SYS_B_10"
RATIONALE: The SQL statement with SQL_ID "21bm668u89c7j" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 21bm668u89c7j
select count(*) from Ec_Bill_Print a where
substr(zone_code,:"SYS_B_00",:"SYS_B_01")=substr(:"SYS_B_02",:"SYS_B_
03",:"SYS_B_04") and check_Accts_Date is not null and
inv_Type=:"SYS_B_05" and print_Flag=:"SYS_B_06" and
:"SYS_B_07"=:"SYS_B_08" and
nvl(credence_Valid_Flag,:"SYS_B_09")=:"SYS_B_10" and
electricity_Words_Number >=:"SYS_B_11"
RATIONALE: The SQL statement with SQL_ID "62awpufmv0ssk" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 62awpufmv0ssk
select count(*) from Ec_Bill_Print a where
substr(zone_code,:"SYS_B_00",:"SYS_B_01")=substr(:"SYS_B_02",:"SYS_B_
03",:"SYS_B_04") and check_Accts_Date is not null and
inv_Type=:"SYS_B_05" and print_Flag=:"SYS_B_06" and
:"SYS_B_07"=:"SYS_B_08" and
nvl(credence_Valid_Flag,:"SYS_B_09")=:"SYS_B_10" and tax_Invo_Deli
=:"SYS_B_11"
RATIONALE: The SQL statement with SQL_ID "102vyc1zx46r3" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 102vyc1zx46r3
select * from ( select ecbillprin0_.ID as ID288_,
ecbillprin0_.USER_NAME as USER2_288_, ecbillprin0_.MR_PLAN_ID as
MR3_288_, ecbillprin0_.LINE_CODE_ID as LINE4_288_,
ecbillprin0_.LINE_CODE as LINE5_288_, ecbillprin0_.CHECK_ID as
CHECK6_288_, ecbillprin0_.ELECTRI_FEE_MONTH as ELECTRI7_288_,
ecbillprin0_.MR_TIMES as MR8_288_, ecbillprin0_.LINE_CODE_NO as
LINE9_288_, ecbillprin0_.GEN_USR_NO as GEN10_288_,
ecbillprin0_.ACCOUNT_ID as ACCOUNT11_288_,
ecbillprin0_.ELECTRICITY_WORDS_NUMBER as ELECTRI12_288_,
ecbillprin0_.USER_ID as USER13_288_, ecbillprin0_.USER_CODE as
USER14_288_, ecbillprin0_.ZIP_CODE as ZIP15_288_,
ecbillprin0_.POST_ADDR as POST16_288_, ecbillprin0_.FEE_REQU_DELI as
FEE17_288_, ecbillprin0_.TAX_INVO_DELI as TAX18_288_,
ecbillprin0_.USER_PRINT_SIGN as USER19_288_,
ecbillprin0_.INFO_SHED_PRINT_SIGN as INFO20_288_,
ecbillprin0_.MR_CYCLE as MR21_288_, ecbillprin0_.RUSH_RED_NUM as
RUSH22_288_, ecbillprin0_.CHARGE_WAY as CHARGE23_288_,
ecbillprin0_.INV_TYPE as INV24_288_,
ecbillprin0_.REAL_ELECTRI_QUANTITY as REAL25_288_,
ecbillprin0_.TOTAL_ELECTRI_FEE as TOTAL26_288_,
ecbillprin0_.PROPHASE_LATE_FEE as PROPHASE27_288_,
ecbillprin0_.WILL_RECEIVE_LATE_FEE as WILL28_288_,
ecbillprin0_.CANCEL_AAC_FLAG as CANCEL29_288_,
ecbillprin0_.CANCEL_ACC_DATE as CANCEL30_288_,
ecbillprin0_.INVOICE_PRINT_SEQU_NO as INVOICE31_288_,
ecbillprin0_.PRINT_FLAG as PRINT32_288_,
ecbillprin0_.TAX_INVOICE_PRINTER as TAX33_288_,
ecbillprin0_.TAX_INVOICE_TIME as TAX34_288_,
ecbillprin0_.CREDENCE_VALID_FLAG as CREDENCE35_288_,
ecbillprin0_.TAX_INVOICE_CODE as TAX36_288_, ecbillprin0_.USER_PROP
as USER37_288_, ecbillprin0_.USER_TYPE as USER38_288_,
ecbillprin0_.CREDENCE_CLASS as CREDENCE39_288_,
ecbillprin0_.CHECK_ACCTS_DATE as CHECK40_288_, ecbillprin0_.ZONE_CODE
as ZONE41_288_, ecbillprin0_.ACCOUNT_BANK_ACCOUNTS as ACCOUNT42_288_,
ecbillprin0_.DUN_USER_PRINT_SIGN as DUN43_288_ from EC_BILL_PRINT
ecbillprin0_ where ecbillprin0_.INV_TYPE=:"SYS_B_00" and
nvl(ecbillprin0_.CREDENCE_VALID_FLAG, :"SYS_B_01")=:"SYS_B_02" and
ecbillprin0_.PRINT_FLAG=:"SYS_B_03" and
(ecbillprin0_.CHECK_ACCTS_DATE is not null) and
:"SYS_B_04"=:"SYS_B_05" and substr(ecbillprin0_.ZONE_CODE,
:"SYS_B_06", :"SYS_B_07")=substr(:"SYS_B_08", :"SYS_B_09",
:"SYS_B_10") and ecbillprin0_.TAX_INVO_DELI=:"SYS_B_11" ) where
rownum <= :1
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(15% impact [30582 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
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.
Hard parsing of SQL statements was not consuming significant database time.
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.
