V$ -- RE: Excessive Logical IOs against which Table/Index

Regards,  

Larry G. Elkins

 <mailto:elkinsl_at_verizon.net> elkinsl_at_verizon.net

214.695.8605  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pot'Vin-Gorman Sent: Monday, April 25, 2016 12:40 PM
To: Jessica Mason <jessica.masson85_at_gmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Excessive Logical IOs against which Table/Index  

Well, you're quite "up a creek with only half a paddle"! :(  

I would definitely say, due to the workload type, (so many environments do have monthly or monthend processing) a business case to have them bump it up to five weeks so that your research could be more productive.  

Second, it may be helpful to have this for reference: https://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm#CACGBHBD  

I'll let a couple others chime in and see what other recommendations folks have to locate what changed, but v$sqlarea and v$io* objects sounds like a good start...  

Kellyn  

  <http://d3mod6n032mdiz.cloudfront.net/thumb2/d/b/a/dbakevlar/dbakevlar-105x70.jpg>  

Kellyn Pot'Vin-Gorman

about.me/dbakevlar    

On Mon, Apr 25, 2016 at 11:31 AM, Jessica Mason <jessica.masson85_at_gmail.com <mailto:jessica.masson85_at_gmail.com> > wrote:

Hi Kellyn,  

(1) Historical data is only stored for 21 days in the production database and this is a monthly job. So, there was no optimal plan to compare ie the whole exercise.
 

(2) The information was gathered from v$ views.

I have only SELECT_CATALOG_ROLE granted to me for this database and have to troubleshoot all the issues using v$ views only.  

Thanks

JM  

On Mon, Apr 25, 2016 at 10:52 PM, Kellyn Pot'Vin-Gorman <dbakevlar_at_gmail.com <mailto:dbakevlar_at_gmail.com> > wrote:

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    

  <http://d3mod6n032mdiz.cloudfront.net/thumb2/d/b/a/dbakevlar/dbakevlar-105x70.jpg>  

Kellyn Pot'Vin-Gorman

about.me/dbakevlar    

On Mon, Apr 25, 2016 at 11:10 AM, Jessica Mason <jessica.masson85_at_gmail.com <mailto:jessica.masson85_at_gmail.com> > 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      

--
http://www.freelists.org/webpage/oracle-l