Originally Posted by
mahajanakhil198
1. This 3 hour duration of AWR is non peak hour duration. The query itself takes more than 30 minutes to execute. For 45 minute duration, I will have to take remote connectivity of client again. I will check if it is possible.
OK.
2. No, I took AWR report after the query finished execution in SQLPLUS.
May be you are not aware and some other SQL executing. YOu may check V$SQL to get those SQLs.
3. The query I have mentioned in attached file 'problematic_query.txt' already refers to tables MAM_ASSET_ATTR_STRING_VALUES and MAM_ASSET_ATTR_DOMAIN_VALUES.
Can you look for the exact table name. They are not there.
4. Yes, I will see if it is possible to increase SGA_TARGET.
5. CPU cores, CPU usage and SAN related is unknow as of now. I will try to capture it. But it won't be of much help as same query was running good few days back and it has been working fine for last 5 years. I know that the data growth patterns have to be taken into account but still it works fine after restart.
May be it's time for histograms then.
6. I don't know much about latches. Did you find anything about latches from AWR that may alleviate the problem?
No there are no latch issue. All IO reated issue, that's why i asked for CPU usage info?