Hari ini saya happy!!!

Sebab apa saya happy? Sebab saya telah berjaya solve satu issue yg sgt penting..ewah.. ada satu report ni dah 2 hari run lebih dr 10 jam.. so dorang ni suh saya check, dia running ker hang? So saya pun menggunakan lah segala kepakaran ninja hikmat tenaga solar buat analysis.. dan u ols tau tak apa?? Lepas saya buat analysis & bagi recommendation, malam tadi time US dorang implement recommendation yg saya buat tu.. tetiba report tu complete tak sampai 15 minit.. biar betik? Wuuhuuu... suka2.. happy2 lalalalala...

Hi Srinivas,

I have checked and found that :

1.) No other sessions blocking this concurrent request session at the moment.
2.) There is contention happening in hot table i.e AR.RA_CUSTOMER_TRX_LINES_ALL (i/o wait on table AR.RA_CUSTOMER_TRX_LINES_ALL)
3.) This concurrent request is running the following sql statement at the moment

SELECT A.TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL A, RA_CUSTOMER_TRX_LINES_ALL B WHERE A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID AND B.INTERFACE_LINE_ATTRIBUTE6 = :B1

4.) Current wait event is db_file_scattered_read, Current wait Clas is User I/O

Please find the details on the database session in attached file:
[attachment "Database session for Concurrent Request id 3605626.doc" deleted by Surfraz Mitegar/IT/Alticor]

The following file is the AWR report run between 9PM to 10 PM EST
[attachment "awr_report_22-Dec-09 9PMto10PM_EST.htm" deleted by Surfraz Mitegar/IT/Alticor]

I have run sql tuning thru ADDM and below is the recommendation:
[attachment "Recommendation.doc" deleted by Surfraz Mitegar/IT/Alticor]

Basically the session does not hang. It is running but there is i/o contention on table AR.RA_CUSTOMER_TRX_LINES_ALL. This statement could be doing high disk reads.

Thanks,
Edrina

*************************************************************************************

Thanks, Edrina for the analysis.

Bernard/Hajireen/Rajesh,
I'm leaving this issue to you as from Functional perspective there is nothing we can do except verifying the results. I would suggest to include Bir Singh and Santanu from Oracle to resolve this immediately. You can contact Rahul Sarin to get Oracle resources.

I think this has become a Critical issue now as this functionality is not working since Saturday. Changed the incident INC000000233029 to Critical and assigned to Surfraz, the developer.

Just fyi:
This is working fine in all lower instances.

I can be reached on my cell# 616-916-3568. Feel free to call me at any time if you need my assistance.

Hamid/Isobel,
Please wait for our word to start clearing the receipts again. Unless this issue is resolved, no point in clearing additional receipts. Last count we have about 300 orders to be processed.

Thanks and Regards
Srinivas Bolla
Sr.IT Business Systems Analyst – CoE
Desk: 616-787-5463; Cell: 616-916-3568
SBolla@Amway.com

*************************************************************************************
Hi All ,

We are in process of migrating the Index suggested by Edrina as per ADDM into TEST . Also modified the query which is causing performance issue .

FYI - This program was running fine from last couple of months . We haven't done any code changes recentely.

I am working on this issue and i will keep posting the updates .

Thanks
Surfraz Mitegar

*************************************************************************************

Surfraz Mitegar/... Hisham ..Edrina told "Basically the session does not hang. It is running but there is i/o contention on table AR.RA_CUSTOMER_TRX_LINES_ALL. This statement could be doing high disk reads"
Hisham Salih/Su... Yes .. We are seeing this since the last 2 days .. Let us wait and see what will happen ..
Surfraz Mitegar/... After 15 - 20 min .. Can you also check whether any contention is there or not ... this time ..
Hisham Salih/Su... No need man .. It is finished
Srinivas Bolla/IT... YOU ARE KIDDING!
Surfraz Mitegar/... what is the output ..
Hisham Salih/Su... For 3621563 ... conc request ..
Srinivas Bolla/IT... unbelievable - it processed ALL
Hisham Salih/Su... Can u check from ur end ..
Srinivas Bolla/IT... YES MAN - it worked
Cathy Baker/IT/... YEAH!!!
Srinivas Bolla/IT... this is real Christmas
Surfraz Mitegar/... Index was the Culprit
Hisham Salih/Su... Happy holiday and vacation ..
Craig Abbott/IT... so can we check to see if pv/bv is showing up?
Hisham Salih/Su... I noticed during the run the way it ran is different ..
Srinivas Bolla/IT... it is already going Craig, several went already
Craig Abbott/IT... excellent
Craig Abbott/IT... ok - i'm just afraid it is too good to be true
Michael Tucker/... exactly
Srinivas Bolla/IT... it should be good, Craid for all the torture we had for the last two days on this
Michael Tucker/... hamid, think we have addressed issue
Hisham Salih/Su...
Hamid Abdullah/... cheking a few ords now
Srinivas Bolla/IT... it processed 186 orders in 8 mins
Hamid Abdullah/... Chekd a few orders and 'good as Gold'

weehuuu... report yg run lebih 10 jam complete dlm masa kurang dr 15 minit..yeszaaa...i loike!!

Email dr bos saya steve willard:

I want to express my deep appreciation for your dedication once again. I realize that you have spent many long hours on this and understand the effort it took to convince the customers that this course of action was in their best interest, but it paid off in the end for the business.

So the quote for the year is: "No need man..it is finished"