RE: Excessive Logical IOs against which Table/Index

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 25 Apr 2016 17:33:38 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282CF9EE_at_EXMBX01.thus.corp>

If you're stuck with command line queries then you could start with:

SQL> select seq#, state, sql_id, sql_child_number, prev_sql_id, prev_child_number from v$session where status = 'ACTIVE' and sid = 18;

      SEQ# STATE SQL_ID SQL_CHILD_NUMBER PREV_SQL_ID PREV_CHILD_NUMBER

---------- ------------------- ------------- ---------------- ------------- -----------------
       391 WAITED SHORT TIME   1xs82mq9vdg23                0 9babjv8yq8ru3                 0

Repeat 20 or 30 times in rapid succession. There's a good chance that the SQL_ID/SQL_CHILD_NUMBER would have identified the very expensive statement. In other circumstances you might see two or 3 sql IDs cycling back and forth between the SQL_ID and PREV_SQL_ID; and if there were no obvious pattern then you could query v$open_cursor for the session to get a list of many SQL ids with their child number.

With an SQL_ID/child_number query v$sql a few times looking at columns like: executions, rows_processed, buffer_gets, disk_reads and some of the time columns - again a repeated query will give you an idea of the workload and help you decide if that's a critical statement - then you can call dbms_xplan.display_cursor() for the sql_id and child_Number to get the plan.

(Another option, if you're licensed, would be to use the SQL Monitor facility to identify current long running queries for the session and see their plans, current operation, and workload per operation.) (dbms_sqltune.report_sql_monitor for a command-line method).

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jessica Mason [jessica.masson85_at_gmail.com] Sent: 25 April 2016 18:10
To: oracle-l_at_freelists.org
Subject: Excessive Logical IOs against which Table/Index

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

Received on Mon Apr 25 2016 - 19:33:38 CEST