I'm going to take a shot at this with questions instead of answers, (sorry!)
1. Do you have the execution plans for the optimal vs. the plan that was
used during this execution?
2. Did you gather this information from simple V$views or did you trace
the session?
Thanks,
Kellyn
[image: Kellyn Pot'Vin on about.me]
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
<http://about.me/dbakevlar>
On Mon, Apr 25, 2016 at 11:10 AM, Jessica Mason <jessica.masson85@xxxxxxxxx>
wrote:
Hello List,
Last week, I was involved in a production issue, where a data load job,
which normally takes few hours to complete, had been running for more than
48 hours. I tried to take the following systematic approach to identify the
cause -
Step 1 - Identify the session and started profiling it. All the time, the
session was on CPU.
Step 2 - To understand why the session was burning CPU, the v$sesstat view
was queried and below were the top statistics that were changing :
43126075162624 logical read bytes from cache
240440566773 table scan rows gotten
2632208820 session logical reads
2632206511 consistent gets
2632206511 consistent gets from cache
2632205708 consistent gets from cache (fastpath)
Step 3 - Next, I wanted to know the object ( table/index) against which
these logical IOs were happening so that I could focus on the operations,
involving these objects, in the execution plan but didn't know which view
to query.
The above information could have saved us lot to time to identify the
cause ( in this case, an unique index was dropped and Oracle was doing FTS
on a table which was referred 6 times in the query fetching million of
records).
So, my questions to the list is that which v$ view should I have checked?
Or is there a better approach to troubleshoot such issues?
Thanks
JM
