I am told to check the performance of a AIX BOX with 10.1.0.2.0.
The client tells they run a certain report around 10am & all the users are just unable to use their application as it becomes dead slow, also this process / job takes sometimes 2-3 hours which slows down their business process. The same report when generated at evening will take about 30 -45 mins only.
I had advised them to change the timing as max users will login at 10am & will be using hence the delay, so they generated the report at 6.30am i.e.; at the time when hardly any users would have come, this too took almost 1.45 mins.
I am confused as to where is the problem, I had generated AWR Report & came to know 2 jobs are utilising max time of DB to execute(SQL ORDERED BY ELAPSED TIME i.e.; 51.5% & 19.2% of database time) out of 1 is a job which is the report (many nested queries get executed & it gets executed ONLY ONCE in a day) & the other is also a job ( i got a table name from AWR report, which I verified with developers & got to know that particular table dose not have any index & gets executed about 12700 times in 1 day).
I am not able to get whole query of both the jobs from AWR report...any idea where I will be able to find whole query so that I can get execution plan for the same..
I have informed the client about the status, I want to know is there anything more I need to look at / am I missing anything important?
If I am not clear please let me know.