Hi Kellyn, Jonathan, Stefan, Lothar & Carlos
Thanks you all for your suggestions and pointers.
I have already requested the project team to keep the historical data for
atleast 90 days, so that in future, we can compare the optimal plans with
the problematic plan. I'm also pushing the management for more privileges
so that I can leverage all the tools recommended by you and can
troubleshoot issues more efficiently and effectively.
Thanks
JM
On Mon, Apr 25, 2016 at 11:57 PM, Carlos Sierra <carlos.sierra.usa@xxxxxxxxx
wrote:
Jessica,
I would suggest on issues like this, to try TUNAs360 from Mauro Pagano, or
SNAPPER from Tanel Poder. Both free. I see SNAPPER more session centric,
which in your case that is all you needed. In the other hand, TUNAs360 is
easier to use and tells you more about other sessions, and more about the
SQL that you are executing on your resource-intensive session of interest.
Then, once you identify the SQL taking long, use SQLd360, which is also
free and also from Mauro. The benefit of these tools is that you can share
the output for others to help, and you can easily document your findings
out of the same output.
Carlos
On Apr 25, 2016, at 10:10 AM, Jessica Mason <jessica.masson85@xxxxxxxxx>wrote:
which normally takes few hours to complete, had been running for more than
Hello List,
Last week, I was involved in a production issue, where a data load job,
48 hours. I tried to take the following systematic approach to identify the
cause -
the session was on CPU.
Step 1 - Identify the session and started profiling it. All the time,
view was queried and below were the top statistics that were changing :
Step 2 - To understand why the session was burning CPU, the v$sesstat
these logical IOs were happening so that I could focus on the operations,
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
involving these objects, in the execution plan but didn't know which view
to query.
cause ( in this case, an unique index was dropped and Oracle was doing FTS
The above information could have saved us lot to time to identify the
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
