Re: Excessive Logical IOs against which Table/Index
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 26 Apr 2016 07:41:22 -0400
On 04/25/2016 01:10 PM, Jessica Mason 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)
OK, did you identify the most CPU intensive process? What was it running
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.
Hmmmm, I have a problem with the logic here: if something is burning
your CPU, the first step would be to find out what process it is and
what SQL it is running.
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?
Jessica, my approach would be this:
1. Identify process(es) responsible for the most of the CPU consumption.
2. Activate tracing using DBMS_MONITOR or ALTER SYSTEM SET EVENTS,
using new 11g syntax. Make sure that trace files can get big enough.
3. Run it with the trace on for some time.
4. Analyze the trace file with Trivadis tvdxtat, Egor Stavrostin's
OraSRP or something similar. The original tkprof should be the last
and least desirable option as it doesn't provide as much detail as
tvdxtat or orasrp. My personal preference is the Trivadis tool,
because OraSRP has not been updated since 2013. Price is the same,
in all cases. Also, Christian Antognini, the principal author of the
tvdxtat tool has written an excellent performance tuning book.
The result of the trace file analysis is an HTML page, with all the
necessary details. It will reveal what SQL is using the most of CPU,
what events is it waiting on (in your case it would be db file scattered
read) and what is the most time consuming step in the execution plan.
That should provide enough information to fix the problem.
Regards
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
Other related posts:
