Forum Stats

  • 3,780,464 Users
  • 2,254,398 Discussions
  • 7,879,338 Comments

Discussions

Find Logical Reads cause

User_DUYMH
User_DUYMH Member Posts: 31 Blue Ribbon

Hi

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

We have a sql(7dhuru7uqj84a) which has started to appear in the top sql when ordered by gets. The sql hasn't been changed for a long time. How to identify the reason which is causing this.

SQL Ordered by Gets (Both top 2 queries seems to be related, as described in ADDM below)

Buffer Gets  Executions Gets per Exec %Total Elapsed Time (s) %CPU %IO SQL Id  

301,985,273  13,057     23,128.23    53.68 1,729.67      99    0 7dhuru7uqj84a  

175,801,888  43,959,060 4.00      31.25 205.97        99.8 0 55mvjphntfnnt


The explain plain for first query:

 ------------------------------------------------------------------------------------------

| Id | Operation                  | Name           | E-Rows |

------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT               |             |    |

|  1 | COUNT STOPKEY                |             |    |

|  2 |  TABLE ACCESS BY INDEX ROWID BATCHED    | EER           |   2 |

|  3 |  INDEX RANGE SCAN             | ERE_UK1         |    |

|  4 | SORT ORDER BY                |             |   1 |

|  5 |  NESTED LOOPS OUTER             |             |   1 |

|  6 |  NESTED LOOPS               |             |   1 |

|  7 |   NESTED LOOPS               |             |   1 |

|  8 |   NESTED LOOPS              |             |   2 |

|  9 |    NESTED LOOPS              |             |   2 |

| 10 |    NESTED LOOPS             |             |   1 |

| 11 |     NESTED LOOPS             |             |   1 |

| 12 |     NESTED LOOPS            |             |   1 |

| 13 |      TABLE ACCESS BY INDEX ROWID BATCHED| SPG           |   1 |

| 14 |      INDEX RANGE SCAN         | SPG_SU_FK_I       |   1 |

| 15 |      TABLE ACCESS BY INDEX ROWID    | RDT           |   1 |

| 16 |      INDEX UNIQUE SCAN         | RDT_UK1         |   1 |

| 17 |     TABLE ACCESS BY INDEX ROWID     | RES           |   1 |

| 18 |      INDEX UNIQUE SCAN         | RES_UK1         |   1 |

| 19 |     INDEX UNIQUE SCAN          | GRP_UK1         |   1 |

| 20 |    TABLE ACCESS FULL           | EER           |   22 |

| 21 |    TABLE ACCESS BY INDEX ROWID      | EE            |   1 |

| 22 |    INDEX UNIQUE SCAN           | EE_PK          |   1 |

| 23 |   TABLE ACCESS BY INDEX ROWID BATCHED   | EUN           |   1 |

| 24 |    INDEX RANGE SCAN            | EUN_EE_FK_I       |  1154 |

| 25 |   TABLE ACCESS BY INDEX ROWID       | RE_R           |   1 |

| 26 |   INDEX UNIQUE SCAN            | RRE_PK          |   1 |

| 27 |  TABLE ACCESS BY INDEX ROWID        | C            |   1 |

| 28 |   INDEX UNIQUE SCAN            | C_PK           |   1 |

------------------------------------------------------------------------------------------

The ADDM for the query shows:

 Rationale
      SQL statement with SQL_ID "7dhuru7uqj84a" was executed 13057 times and
      had an average elapsed time of 0.13 seconds.
   Rationale
      Top level calls to execute the SELECT statement with SQL_ID
      "55mvjphntfnnt" are responsible for 61% of the database time spent on
      the SELECT statement with SQL_ID "7dhuru7uqj84a".
      Related Object
	 SQL statement with SQL_ID 55mvjphntfnnt.

Checking for the other query mentioned:

-------------------------------------------------------------------------

| Id | Operation          | Name          | E-Rows |

-------------------------------------------------------------------------

|  0 | SELECT STATEMENT       |             |    |

|  1 | NESTED LOOPS        |             |   1 |

|  2 |  TABLE ACCESS BY INDEX ROWID| EAT           |   1 |

|  3 |  INDEX UNIQUE SCAN     | EAT_UK1         |   1 |

|  4 |  TABLE ACCESS BY INDEX ROWID| EAV           |   1 |

|  5 |  INDEX UNIQUE SCAN     | EAV_UK1         |   1 |

-------------------------------------------------------------------------

What further info can be checked to find if it is the first query or the latter that is causing the issue.


Thanks.

 
Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,216 Blue Diamond
    Accepted Answer

    What further info can be checked to find if it is the first query or the latter that is causing the issue.


    From what you described, it appears that the second query is called from the first query - so you are asking the wrong question. You can't point to one OR the other, when one is responsible for the other.

    What does the first query do? It seems as though somewhere it must call a function, which in turn executes the second query - in a loop, since the second query is executed 44 million times while the first one is executed 13,000 times. If my math is right, this means that the second query is executed more than 3,000 times for each execution of the first query.

    Anyway - I am just speculating here (and rambling), and I may be 100% on the wrong path. Check (and perhaps share with us, if you need help) what the first query actually does. Just seeing the plan is not enough.

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 810 Silver Trophy

    appear in the top sql when ordered by gets.

    Something will always be on top.

  • User_DUYMH
    User_DUYMH Member Posts: 31 Blue Ribbon

    @User_H3J7U, Yes but this one didn't use to be in the past.

  • mathguy
    mathguy Member Posts: 10,216 Blue Diamond
    Accepted Answer

    What further info can be checked to find if it is the first query or the latter that is causing the issue.


    From what you described, it appears that the second query is called from the first query - so you are asking the wrong question. You can't point to one OR the other, when one is responsible for the other.

    What does the first query do? It seems as though somewhere it must call a function, which in turn executes the second query - in a loop, since the second query is executed 44 million times while the first one is executed 13,000 times. If my math is right, this means that the second query is executed more than 3,000 times for each execution of the first query.

    Anyway - I am just speculating here (and rambling), and I may be 100% on the wrong path. Check (and perhaps share with us, if you need help) what the first query actually does. Just seeing the plan is not enough.