SQL PROFILE IN ORACLE

[Arsalan]

Dear sir.

we are facing with performance issue in our live oracle database, we checked the issue through SQL adviser
SQL divisor recommend us the solution as attached to snap shots.
please share your views with us to solve the mentioned issue.








Best Regards,

[ErmanArslansOracleBlog]

Hi,

I can't comment on this, without seeing the execution plan.
Try what the advisor says.

Check with compare explain plan button. You will see the difference. If it is acceptable, accept it.

[Arsalan]

Hi Arsalan

I would upload snapshot of sql plan kindly check and give me your suggestion.



and

[ErmanArslansOracleBlog]

The Total cost and IO cost of the new execution plan is lower than the original/current one.
I don't see the whole exeuction plan though ..The screenshot you provided is only a portion of it, but it can be seen that your query has a complex execution plan. I don't see the route cause of these big costs, it must be in other steps down there.. Anyways it seems acceptable. (low IO, low cost) At least give the suggestion a try.

On the other hand, the cost is really huge. Check with your developer to tune the sql statement if it can be tuned in somehow.. (less full tablescans, less loops etc...)

[Arsalan]

Thanks Arsalan

Sure i tolled many time to developer to tune SQL Statement it always scan full table.

so i attached snapshot of SQL PROFILE Which are disable, what would you recommend it must be enable or
disable.

[ErmanArslansOracleBlog]

This is not an easy thing Arsala.
I need to see the full sql, understand it, see the execution plan as a whole, speak with your developer and so. I don't have any references to check. What is good and what is bad, is not certain for this.
what I can suggest you with this limited info is, try the suggested Execution plan (as it has lower cost and lower IO cost)