Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Wasim.

Asked: April 04, 2017 - 6:20 am UTC

Last updated: April 11, 2017 - 1:05 am UTC

Version: 11.2.0.2 RAC on HPUX

Viewed 10K+ times! This question is

You Asked

Dear Experts,

I work in a telecom domain, We have AIA and SOA application which we are tunning for increasing the tps. We have observed below even when same session are hitting the database. I would request you to please provide me suggestion or solution to this.

       SID USERNAME     PROGRAM      OSUSER       EVENT                     STATE          SQL                            SQL_ID        SEC_ACTIVE
---------- ------------ ------------ ------------ ------------------------- -------------- ------------------------------ ------------- ----------
       279 SCHEMA_NAME JDBC Thin C  xxxxxxx       SQL*Net message to client WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          0
      3263 SCHEMA_NAME JDBC Thin C  xxxxxxx       SQL*Net message to client WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          0
      2770 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          0
      4787 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          0
      2282 SYS          sqlplus@PRE  oracle       SQL*Net message to client WAITED SHORT   select distinct w.sid,s.userna 1pu0npbh8j535          0
      4288 SCHEMA_NAME JDBC Thin C  xxxxxxx       SQL*Net message to client WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1008 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1535 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3016 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3286 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3521 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      4275 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
         4 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
        36 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1010 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1037 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1275 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1507 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1769 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1790 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2029 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2290 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2531 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2777 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3009 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3031 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3262 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3523 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3539 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3759 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3792 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      4013 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      4756 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      4786 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          1
       522 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      1540 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2004 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2264 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2511 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2534 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2767 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      2785 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3023 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3037 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3268 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3530 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      3770 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
      4537 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          1
       756 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
       791 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      1273 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      3015 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      4521 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      4778 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
       267 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
       760 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      1265 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2036 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2507 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2753 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2756 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      3502 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      3525 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      4014 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      1775 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2271 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2285 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2518 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      2538 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          2
      3038 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          2
       271 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          4
      3279 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED KNOWN   select VALUE from XREF_DATA  w bk2mk4dh179y7          4
      3019 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITED SHORT   select VALUE from XREF_DATA  w bk2mk4dh179y7          4
      3289 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          4
      4281 SCHEMA_NAME JDBC Thin C  xxxxxxx       latch: cache buffers chai WAITING        select VALUE from XREF_DATA  w bk2mk4dh179y7          4

75 rows selected.

01:35:49 SQL> select
      count(*),
      sql_id,
      nvl(o.object_name,ash.current_obj#) objn,
      substr(o.object_type,0,10) otype,
      CURRENT_FILE# fn,
      CURRENT_BLOCK# blockn
01:35:49   2  from  v$active_session_history ash
01:35:49   3      , all_objects o
01:35:49   4  01:35:49   5  where event like 'latch: cache buffers chains'
01:35:49   6  01:35:49   7  01:35:49   8    and o.object_id (+)= ash.CURRENT_OBJ#
01:35:49   9  01:35:49  10  01:35:49  11  01:35:49  12  group by sql_id, current_obj#, current_file#,
               current_block#, o.object_name,o.object_type
01:35:49  13  01:35:49  14  order by count(*)
/    01:35:49  15

  COUNT(*) SQL_ID        OBJN                                     OTYPE                                            FN     BLOCKN
---------- ------------- ---------------------------------------- ---------------------------------------- ---------- ----------
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218782
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    2131102
         1 bk2mk4dh179y7 SYS_C0036790                             INDEX                                             4     567326
         1 b2rmqka1g2b24 XREF_VALUE                               INDEX                                             4    1793582
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218976
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2219081
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    2152098
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218339
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    1792285
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    1882844
         1 bk2mk4dh179y7 XREF_DATA                                TABLE                                             4    2226047
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218504
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4      58174
         1 bk2mk4dh179y7 XREF_DATA                                TABLE                                             4    2227904
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4     282955
         1 bk2mk4dh179y7 XREF_DATA                                TABLE                                             4    2227942
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218987
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218564
         1 bk2mk4dh179y7 XREF_DATA                                TABLE                                             4    2228194
         1 bk2mk4dh179y7 XREF_DATA                                TABLE                                             4    2225593
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218827
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4     267427
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4     348478
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    1897072
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218538
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218409
         1 bk2mk4dh179y7 SYS_C0036790                             INDEX                                             4     655379
         1 bk2mk4dh179y7 SYS_C0036790                             INDEX                                             4     272285
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    2131168
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4     347768
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218541
         1 bk2mk4dh179y7 SYS_C0036790                             INDEX                                             4     313616
         1 8qku71rhyp5u3 MEDIATOR_INSTANCE                        TABLE PART                                       89     863180
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4    1977727
         1 bk2mk4dh179y7 XREF_VALUE                               INDEX                                             4     159889
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218170
         1 b2rmqka1g2b24 XREF_VALUE                               INDEX                                             4    1621897
         1 b2rmqka1g2b24 XREF_VALUE                               INDEX                                             4     165478
         1 b2rmqka1g2b24 XREF_VALUE                               INDEX                                             4    1773458
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2219091
         1 bk2mk4dh179y7 XREF_ROW_NUMBER                          INDEX                                             4    2218806


    EVENT#   EVENT_ID NAME                              PARAMETER1        PARAMETER2        PARAMETER3        WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- --------------------------------- ----------------- ----------------- ----------------- ------------- ----------- -----------------
        86 2779959231 latch: cache buffers chains       address           number            tries                3875070507           4 Concurrency

SQL_ID                        : bk2mk4dh179y7
SHARABLE_MEM                  : 38022
PERSISTENT_MEM                : 60216
RUNTIME_MEM                   : 59104
SORTS                         : 0
LOADED_VERSIONS               : 1
OPEN_VERSIONS                 : 1
USERS_OPENING                 : 39
FETCHES                       : 166519
EXECUTIONS                    : 166534
PX_SERVERS_EXECUTIONS         : 0
END_OF_FETCH_COUNT            : 166517
USERS_EXECUTING               : 1
LOADS                         : 1
FIRST_LOAD_TIME               : 2017-03-14/18:51:46
INVALIDATIONS                 : 0
PARSE_CALLS                   : 278
DISK_READS                    : 1041
DIRECT_WRITES                 : 0
BUFFER_GETS                   : 1633139203
APPLICATION_WAIT_TIME         : 0
CONCURRENCY_WAIT_TIME         : 1367866445
CLUSTER_WAIT_TIME             : 0
USER_IO_WAIT_TIME             : 3605616
PLSQL_EXEC_TIME               : 0
JAVA_EXEC_TIME                : 0
ROWS_PROCESSED                : 108263
COMMAND_TYPE                  : 3
OPTIMIZER_MODE                : ALL_ROWS
OPTIMIZER_COST                : 10
OPTIMIZER_ENV                 : E289FB89E226A8003D011000AEF9C3E2CFFA331056414555
519521105555551545545558591555449665851D5511058555555155515122555415A0EA0C555145
4265455454449081566E001696C6A35545150102541550441669899BA00214500800000800000040
0000000400008000000820401F00000000F6000033310004040000E0E0031000008099180000007B
00889BA002949191090890C908C8000008000C0828281414285000080000D0070000402928282890
A10F00009001030E0800080000FCFF0300A050C80001
OPTIMIZER_ENV_HASH_VALUE      : 3952539766
PARSING_USER_ID               : 171
PARSING_SCHEMA_ID             : 171
PARSING_SCHEMA_NAME           : PRODAIA_XREF
KEPT_VERSIONS                 : 0
ADDRESS                       : C0000004789A4B88
TYPE_CHK_HEAP                 : 00
HASH_VALUE                    : 1611900871
OLD_HASH_VALUE                : 1804870301
PLAN_HASH_VALUE               : 1427186440
CHILD_NUMBER                  : 2
SERVICE                       : aiaprepn.mti.abscbn.com
SERVICE_HASH                  : 0
MODULE                        : JDBC Thin Client
MODULE_HASH                   : -1748072636
ACTION                        :
ACTION_HASH                   : 0
SERIALIZABLE_ABORTS           : 0
OUTLINE_CATEGORY              :
CPU_TIME                      : 11348680000
ELAPSED_TIME                  : 15818792444
OUTLINE_SID                   :
CHILD_ADDRESS                 : C0000002D1DFB520
SQLTYPE                       : 6
REMOTE                        : N
OBJECT_STATUS                 : VALID
LITERAL_HASH_VALUE            : 0
LAST_LOAD_TIME                : 2017-04-04/02:30:24
IS_OBSOLETE                   : N
IS_BIND_SENSITIVE             : Y
IS_BIND_AWARE                 : N
IS_SHAREABLE                  : Y
CHILD_LATCH                   : 0
SQL_PROFILE                   :
SQL_PATCH                     :
SQL_PLAN_BASELINE             :
PROGRAM_ID                    : 0
PROGRAM_LINE#                 : 0
EXACT_MATCHING_SIGNATURE      : 1705577883262083921
FORCE_MATCHING_SIGNATURE      : 1705577883262083921
LAST_ACTIVE_TIME              : 04-apr-2017 21:19:13
BIND_DATA                     : BEDA0B20020058E399EC00051FF0012003690642524D5F30
31F40107D003693A6F72616D64733A2F617070732F4149414D657461446174612F787265662F4355
53544F4D455250415254595F4143434F554E5449442E78726566F00120036906434F4D4D4F4EF401
07D003693A6F72616D64733A2F617070732F4149414D657461446174612F787265662F435553544F
4D455250415254595F4143434F554E5449442E78726566F40107D003692032643331333933313335
33333334333633303335333733323334333233313337
TYPECHECK_MEM                 : 0
IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 0
IO_INTERCONNECT_BYTES         : 8527872
PHYSICAL_READ_REQUESTS        : 1041
PHYSICAL_READ_BYTES           : 8527872
PHYSICAL_WRITE_REQUESTS       : 0
PHYSICAL_WRITE_BYTES          : 0
OPTIMIZED_PHY_READ_REQUESTS   : 0
LOCKED_TOTAL                  : 338
PINNED_TOTAL                  : 166539
IO_CELL_UNCOMPRESSED_BYTES    : 0
IO_CELL_OFFLOAD_RETURNED_BYTES: 0
-----------------

PL/SQL procedure successfully completed.




and Connor said...

OK - you chopped out the sql text :-( so that's not so useful, but anyway, look at this:

EXECUTIONS                    : 166534
BUFFER_GETS                   : 1633139203
DISK_READS                    : 1041


1.6 billion logical I/O's for that 1 sql statement and virtually no disk activity. So it's likely accessing a small set of data (and hence most likely concentrated into a few blocks) over and over and over and over again.

Hence you've got contention for those few blocks.

From the other data you've provided, your course of action looks like being:

a) tune the SQL to read less blocks, and/or
b) look at changing at storage parameters of XREF_DATA,XREF_ROW_NUMBER to spread the blocks out over a wider set to ease the contention

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Explain plan and table details..

Wasim Shaikh, April 06, 2017 - 7:16 am UTC

please find the details for sql and its explain plan .

21:43:42 SQL> @cur
Enter value for sql: bk2mk4dh179y7
old   1: select * from table(dbms_xplan.display_cursor('&sql'))
new   1: select * from table(dbms_xplan.display_cursor('bk2mk4dh179y7'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bk2mk4dh179y7, child number 0
-------------------------------------
select VALUE from XREF_DATA  where XREF_COLUMN_NAME = :1   and
XREF_TABLE_NAME = :2   and ROW_NUMBER in  ( select ROW_NUMBER from
XREF_DATA    where XREF_COLUMN_NAME = :3     and XREF_TABLE_NAME = :4
  and VALUE = :5     and IS_DELETED = 'N' )   and IS_DELETED = 'N'

Plan hash value: 1427186440

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |       |       |    10 (100)|          |
|   1 |  NESTED LOOPS                 |                 |       |       |            |          |
|   2 |   NESTED LOOPS                |                 |     1 |   248 |    10   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XREF_DATA       |     1 |   124 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | XREF_VALUE      |     1 |       |     4   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | XREF_ROW_NUMBER |     2 |       |     3   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | XREF_DATA       |     1 |   124 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("XREF_TABLE_NAME"=:4 AND "XREF_COLUMN_NAME"=:3 AND "IS_DELETED"='N'))
   4 - access("VALUE"=:5)
   5 - access("ROW_NUMBER"="ROW_NUMBER")
   6 - filter(("XREF_TABLE_NAME"=:2 AND "XREF_COLUMN_NAME"=:1 AND "IS_DELETED"='N'))


29 rows selected.

Elapsed: 00:00:00.09
21:43:47 SQL> @ind
Enter value for tb: XREF_DATA
Enter value for owner: PRODAIA_XREF
old   1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('&TB') and index_owner=upper('&OWNER') order by INDEX_NAME,COLUMN_POSITION
new   1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('XREF_DATA') and index_owner=upper('PRODAIA_XREF') order by INDEX_NAME,COLUMN_POSITION

INDEX_OWNER                    TABLE_NAME                     COLUMN_NAME               INDEX_NAME                     COLUMN_POSITION
------------------------------ ------------------------------ ------------------------- ------------------------------ ---------------
PRODAIA_XREF                   XREF_DATA                      XREF_TABLE_NAME           SYS_C0036790                                 1
PRODAIA_XREF                   XREF_DATA                      XREF_COLUMN_NAME          SYS_C0036790                                 2
PRODAIA_XREF                   XREF_DATA                      VALUE                     SYS_C0036790                                 3
PRODAIA_XREF                   XREF_DATA                      ROW_NUMBER                XREF_ROW_NUMBER                              1
PRODAIA_XREF                   XREF_DATA                      VALUE                     XREF_VALUE                                   1

Elapsed: 00:00:00.12
21:44:44 SQL> @tab_de
Enter table_name: XREF_DATA

OWNER                          OBJECT_NAME                                                  OBJECT_TYPE
------------------------------ ------------------------------------------------------------ -------------------
AIA_SOAINFRA                   XREF_DATA                                                    TABLE
EAI_SOAINFRA                   XREF_DATA                                                    TABLE
PRODAIA_XREF                   XREF_DATA                                                    TABLE
PUBLIC                         XREF_DATA                                                    SYNONYM
SOA_SOAINFRA                   XREF_DATA                                                    TABLE

Elapsed: 00:00:00.11
Enter table owner: PRODAIA_XREF
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 XREF_TABLE_NAME                                                          NOT NULL VARCHAR2(2000)
 XREF_COLUMN_NAME                                                         NOT NULL VARCHAR2(2000)
 ROW_NUMBER                                                               NOT NULL VARCHAR2(48)
 VALUE                                                                    NOT NULL VARCHAR2(2000)
 IS_DELETED                                                               NOT NULL VARCHAR2(1)
 LAST_MODIFIED                                                            NOT NULL TIMESTAMP(6)


INDEX_NAME                     UNIQUENES COLUMN_NAME                              COLUMN_POSITION
------------------------------ --------- ---------------------------------------- ---------------
SYS_C0036790                   UNIQUE    XREF_TABLE_NAME                                        1
SYS_C0036790                   UNIQUE    XREF_COLUMN_NAME                                       2
SYS_C0036790                   UNIQUE    VALUE                                                  3
XREF_ROW_NUMBER                NONUNIQUE ROW_NUMBER                                             1
XREF_VALUE                     NONUNIQUE VALUE                                                  1

Elapsed: 00:00:00.20

COLUMN_NAME                              NUM_DISTINCT  NUM_NULLS NUM_BUCKETS    DENSITY SAMPLE_SIZE
---------------------------------------- ------------ ---------- ----------- ---------- -----------
IS_DELETED                                          1          0           1          1    23970128
LAST_MODIFIED                                13055143          0           1 7.6598E-08    23970128
ROW_NUMBER                                   10798967          0           1 9.2601E-08    23970128
VALUE                                        21888279          0           1 4.5687E-08    23970128
XREF_COLUMN_NAME                                    5          0           1         .2    23970128
XREF_TABLE_NAME                                    21          0           1 .047619048    23970128

6 rows selected.

Elapsed: 00:00:00.22

COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- --------------- --------------
IS_DELETED                                             0     4.0500E+35
IS_DELETED                                             1     4.0500E+35
LAST_MODIFIED                                          0     2456428.76
LAST_MODIFIED                                          1     2457788.89
ROW_NUMBER                                             0     2.5021E+35
ROW_NUMBER                                             1     3.6489E+35
VALUE                                                  0     2.4988E+35
VALUE                                                  1     4.3231E+35
XREF_COLUMN_NAME                                       0     3.4436E+35
XREF_COLUMN_NAME                                       1     4.3237E+35
XREF_TABLE_NAME                                        0     5.7866E+35
XREF_TABLE_NAME                                        1     5.7866E+35

12 rows selected.

Elapsed: 00:00:00.06

TABLE_NAME                       NUM_ROWS DEGREE                                   LAST_ANAL
------------------------------ ---------- ---------------------------------------- ---------
XREF_DATA                        23970128          1                               23-FEB-17

Elapsed: 00:00:00.06

SEGMENT_NAME                                       SEGMENT_TYPE          SIZE_MB
-------------------------------------------------- ------------------ ----------
XREF_ROW_NUMBER                                    INDEX                    1856
XREF_DATA                                          TABLE                    3703
SYS_C0036790                                       INDEX                    3779
XREF_VALUE                                         INDEX                    1472

Elapsed: 00:00:06.43

Please also suggest more about the storage parameter change

Thanks
Wasim

explain plan with actual and estimated cardinalities

Rajeshwaran, April 06, 2017 - 9:29 am UTC

Could you post the plan with both actual and estimated cardinality ? that could help us the verify the optimizer access paths, join methods and join orders in the plan.

you could look into the below link to see, how we could get the actual and estimated cardinality listed in the execution plan.
https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Both the plans as requested...

Wasim Shaikh, April 06, 2017 - 11:16 am UTC

Please find the plan requested please let me know if any information is missing.

19:06:11 SQL> @awr
Enter value for sql_id: bk2mk4dh179y7
old   1: select * from table(dbms_xplan.display_awr('&sql_id'))
new   1: select * from table(dbms_xplan.display_awr('bk2mk4dh179y7'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bk2mk4dh179y7
--------------------
select VALUE from XREF_DATA  where XREF_COLUMN_NAME = :1   and
XREF_TABLE_NAME = :2   and ROW_NUMBER in  ( select ROW_NUMBER from
XREF_DATA    where XREF_COLUMN_NAME = :3     and XREF_TABLE_NAME = :4
  and VALUE = :5     and IS_DELETED = 'N' )   and IS_DELETED = 'N'

Plan hash value: 1427186440

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |       |       |    10 (100)|          |
|   1 |  NESTED LOOPS                 |                 |       |       |            |          |
|   2 |   NESTED LOOPS                |                 |     1 |   248 |    10   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XREF_DATA       |     1 |   124 |     5   (0)| 00:00:01 |
|   4 |     INDEX RANGE SCAN          | XREF_VALUE      |     1 |       |     4   (0)| 00:00:01 |
|   5 |    INDEX RANGE SCAN           | XREF_ROW_NUMBER |     2 |       |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XREF_DATA       |     1 |   124 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


21 rows selected.

Elapsed: 00:00:00.08

19:12:14 SQL> var a VARCHAR2(32);
var b VARCHAR2(2000);
var c VARCHAR2(32);
var d VARCHAR2(2000);
var e VARCHAR2(2000);

exec :a := 'BRM_01';
19:12:15 SQL> exec :b := 'oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_ACCOUNTID.xref';
19:12:15 SQL> exec :c := 'COMMON';
exec :d := 'oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_ACCOUNTID.xref';
19:12:15 SQL> exec :e := '2d363531353333353533343533393030';
19:12:15 SQL> 19:12:15 SQL> 19:12:15 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
19:12:15 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
19:12:15 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
19:12:15 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
19:12:15 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
19:12:15 SQL>
19:12:16 SQL>
19:12:16 SQL>
19:12:21 SQL> set autotrace on;
select VALUE from XREF_DATA  where XREF_COLUMN_NAME = :a   and
XREF_TABLE_NAME = :b   and ROW_NUMBER in  ( select ROW_NUMBER from
XREF_DATA    where XREF_COLUMN_NAME = :c     and XREF_TABLE_NAME = :d
  and VALUE = :e     and IS_DELETED = 'N' )   and IS_DELETED = 'N';19:12:22 SQL> 19:12:22   2  19:12:22   3  19:12:22   4

no rows selected

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 57299837

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |  6068 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     1 |  6068 |     0   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| XREF_DATA    |     1 |  3034 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0012924 |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| XREF_DATA    |     1 |  3034 |     0   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | SYS_C0012924 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("IS_DELETED"='N')
   3 - access("XREF_TABLE_NAME"=:D AND "XREF_COLUMN_NAME"=:C AND "VALUE"=:E)
   4 - filter("IS_DELETED"='N' AND "ROW_NUMBER"="ROW_NUMBER")
   5 - access("XREF_TABLE_NAME"=:B AND "XREF_COLUMN_NAME"=:A)


Statistics
----------------------------------------------------------
        141  recursive calls
          0  db block gets
        163  consistent gets
          8  physical reads
          0  redo size
        333  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          0  rows processed



Regards
wasim

Used to work as a SOA Suite administrator

Mikhail Velikikh, April 07, 2017 - 2:15 am UTC

Hi Wasim,

I used to work with Oracle SOA Suite 10 years ago and I remember using the XREF_DATA table there.
I do not know for what purpose the XREF_VALUE index is. AFAIK, there is no XREF operation that should use this index.
Both plan hash value 1427186440 and 57299837 are not optimal.
I would rather use a combination of both with an execution plan like below (ignore the oddity in the line numbers - I simply combined both of your plans together taking the lines that I needed):
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT | | 1 | 6068 | 0 (0)| 00:00:01 |
| 1  | NESTED LOOPS | | 1 | 6068 | 0 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| XREF_DATA | 1 | 3034 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0012924 | 1 | | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | XREF_ROW_NUMBER | 2 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | XREF_DATA | 1 | 124 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("IS_DELETED"='N')
3 - access("XREF_TABLE_NAME"=:D AND "XREF_COLUMN_NAME"=:C AND "VALUE"=:E)
5 - access("ROW_NUMBER"="ROW_NUMBER")
6 - filter(("XREF_TABLE_NAME"=:2 AND "XREF_COLUMN_NAME"=:1 AND "IS_DELETED"='N')) 


So what you can do is to identify the root cause why Oracle comes up with your plans or you can fix the plan using SQL Plan Baselines, for instance.

Both the plans as requested... | To Wasim

Rajeshwaran, April 07, 2017 - 7:43 am UTC

we don't need the plans from AWR or Autotrace output, instead we need the plans from the cursor cache after the execution.

something like this.

demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ gather_plan_statistics */ d.deptno,sum(e.sal)
  2  from emp e, dept d
  3  where e.deptno = d.deptno
  4  group by d.deptno;

    DEPTNO SUM(E.SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4ssay4d2ta7u, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ d.deptno,sum(e.sal) from emp e,
dept d where e.deptno = d.deptno group by d.deptno

Plan hash value: 2006461124

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      3 |00:00:00.06 |      21 |      4 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |      3 |      3 |00:00:00.06 |      21 |      4 |  1214K|  1214K| 1243K (0)|
|*  2 |   HASH JOIN         |      |      1 |     14 |     14 |00:00:00.06 |      21 |      4 |  2545K|  2545K| 1106K (0)|
|   3 |    TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.05 |       9 |      2 |       |       |          |
|   4 |    TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       9 |      2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPTNO"="D"."DEPTNO")


22 rows selected.

demo@ORA12C>

Plan as requested

Wasim Shaikh, April 10, 2017 - 4:15 am UTC

Plan 1: As requested
---------------
---------------

12:08:20 SQL> set serveroutput off
12:08:51 SQL>
12:08:59 SQL>
var a VARCHAR2(32);
var b VARCHAR2(2000);
var c VARCHAR2(32);
var d VARCHAR2(2000);
var e VARCHAR2(2000);

exec :a := 'BRM_01';
exec :b := 'oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_ACCOUNTID.xref';
exec :c := 'COMMON';
exec :d := 'oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_ACCOUNTID.xref';
exec :e := '2d363531353333353533343533393030';12:08:59 SQL> 12:08:59 SQL> 12:08:59 SQL> 12:08:59 SQL> 12:08:59 SQL> 12:08:59 SQL> 12:08:59 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
12:08:59 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
12:08:59 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
12:08:59 SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
12:08:59 SQL>

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
12:09:00 SQL>
12:09:00 SQL>
12:09:10 SQL>
12:09:11 SQL> select /*+ gather_plan_statistics */ VALUE
from PRODAIA_XREF.XREF_DATA
where XREF_COLUMN_NAME = :a
and XREF_TABLE_NAME = :b
and ROW_NUMBER in  ( select ROW_NUMBER
12:09:11   2  12:09:11   3                                              from PRODAIA_XREF.XREF_DATA
                                                where XREF_COLUMN_NAME = :c
12:09:11   4  12:09:11   5  12:09:11   6  12:09:11   7  12:09:11   8                                            and XREF_TABLE_NAME = :d
                                                and VALUE = :e
12:09:11   9  12:09:11  10                                              and IS_DELETED = 'N' )
and IS_DELETED = 'N';
  12:09:11  11
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.0.0.1 /account 7704153845 0

Elapsed: 00:00:00.00
12:09:11 SQL>
12:09:11 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2xngqvr0jt64n, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ VALUE from PRODAIA_XREF.XREF_DATA
where XREF_COLUMN_NAME = :a and XREF_TABLE_NAME = :b and ROW_NUMBER in
( select ROW_NUMBER       from PRODAIA_XREF.XREF_DATA       where
XREF_COLUMN_NAME = :c       and XREF_TABLE_NAME = :d       and VALUE =
:e       and IS_DELETED = 'N' ) and IS_DELETED = 'N'

Plan hash value: 1427186440

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  NESTED LOOPS                 |                 |      1 |        |      1 |00:00:00.01 |      14 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |      3 |00:00:00.01 |      12 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XREF_DATA       |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  4 |     INDEX RANGE SCAN          | XREF_VALUE      |      1 |      1 |      2 |00:00:00.01 |       5 |
|*  5 |    INDEX RANGE SCAN           | XREF_ROW_NUMBER |      1 |      2 |      3 |00:00:00.01 |       5 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | XREF_DATA       |      3 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("XREF_TABLE_NAME"=:D AND "XREF_COLUMN_NAME"=:C AND "IS_DELETED"='N'))
   4 - access("VALUE"=:E)
   5 - access("ROW_NUMBER"="ROW_NUMBER")
   6 - filter(("XREF_TABLE_NAME"=:B AND "XREF_COLUMN_NAME"=:A AND "IS_DELETED"='N'))


30 rows selected.

Elapsed: 00:00:00.06
12:09:26 SQL>


Plan with Advance Options;;
------------------------------

12:02:10 SQL> select * from table(dbms_xplan.display_cursor('bk2mk4dh179y7',format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bk2mk4dh179y7, child number 0
-------------------------------------
select VALUE from XREF_DATA  where XREF_COLUMN_NAME = :1   and
XREF_TABLE_NAME = :2   and ROW_NUMBER in  ( select ROW_NUMBER from
XREF_DATA    where XREF_COLUMN_NAME = :3     and XREF_TABLE_NAME = :4
  and VALUE = :5     and IS_DELETED = 'N' )   and IS_DELETED = 'N'

Plan hash value: 1427186440

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |       |       |    10 (100)|          |
|   1 |  NESTED LOOPS                 |                 |       |       |            |          |
|   2 |   NESTED LOOPS                |                 |     1 |   248 |    10   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| XREF_DATA       |     1 |   124 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | XREF_VALUE      |     1 |       |     4   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | XREF_ROW_NUMBER |     2 |       |     3   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | XREF_DATA       |     1 |   124 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / XREF_DATA@SEL$2
   4 - SEL$5DA710D3 / XREF_DATA@SEL$2
   5 - SEL$5DA710D3 / XREF_DATA@SEL$1
   6 - SEL$5DA710D3 / XREF_DATA@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$5DA710D3" "XREF_DATA"@"SEL$2" ("XREF_DATA"."VALUE"))
      INDEX(@"SEL$5DA710D3" "XREF_DATA"@"SEL$1" ("XREF_DATA"."ROW_NUMBER"))
      LEADING(@"SEL$5DA710D3" "XREF_DATA"@"SEL$2" "XREF_DATA"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "XREF_DATA"@"SEL$1")
      NLJ_BATCHING(@"SEL$5DA710D3" "XREF_DATA"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'COMMON'
   2 - :2 (VARCHAR2(30), CSID=873): 'oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_BILLPROFILEID.x
       ref'
   3 - :3 (VARCHAR2(30), CSID=873): 'SEBL_01'
   4 - :4 (VARCHAR2(30), CSID=873): 'oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_BILLPROFILEID.x
       ref'
   5 - :5 (VARCHAR2(30), CSID=873): '1-1B2ZVBX'

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("XREF_TABLE_NAME"=:4 AND "XREF_COLUMN_NAME"=:3 AND "IS_DELETED"='N'))
   4 - access("VALUE"=:5)
   5 - access("ROW_NUMBER"="ROW_NUMBER")
   6 - filter(("XREF_TABLE_NAME"=:2 AND "XREF_COLUMN_NAME"=:1 AND "IS_DELETED"='N'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "VALUE"[VARCHAR2,2000]
   2 - "XREF_DATA".ROWID[ROWID,10]
   3 - "ROW_NUMBER"[VARCHAR2,48]
   4 - "XREF_DATA".ROWID[ROWID,10]
   5 - "XREF_DATA".ROWID[ROWID,10]
   6 - "VALUE"[VARCHAR2,2000]


80 rows selected.

Elapsed: 00:00:00.16

Regards
Wasim

Connor McDonald
April 11, 2017 - 1:05 am UTC

I'm sceptical of the values being a good representative here, because from the initial data you gave us:

EXECUTIONS                    : 166534
BUFFER_GETS                   : 1633139203
DISK_READS                    : 1041

SQL> select 1633139203 / 166534 from dual;

1633139203/166534
-----------------
        9806.6413



we're looking at around 9800 buffer gets per execution, whereas your most recent example suggests far less. It may well be the case the *some* special values are causing a much high buffer get count.

So the reason this is critical is because we might opt for different strategies

1) if the buffer count is low per execution, then the plan is fine - and we probably need to alter the storage to ease contention

2) if the buffer count is actually *not* low, then the plan might need work, and hence there is a good chance the contention is due to excessive buffers per execution.

So lets look at each - but *you* will need to decide on which is more appropriate for your setup

1) If the buffer count is low, you can "spread out" the table blocks by either

a) setting a larger pctfree, or
b) perhaps hash partitioning on appropriate column

2) If the plan is poor, then from the SQL, a more sensible index looks to be potentially SYS_C0036790. So consider that a plan with that, and use a baselines/outline/etc to switch in that plan.


Adaptive cursor sharing

Rajeshwaran, April 11, 2017 - 2:03 pm UTC

Sound like a bind variable peeking issue, By any chance does this SQL get invoked from PL/SQL ?

if so adaptive cursor sharing introduced in Oracle 11g to address this bind variable peeking issue, but unfortunately doesn't work (doesn't adapt cursors) for sql's present inside PL/SQL.

Randolf, Geist describe them in detail at this blog post.
http://oracle-randolf.blogspot.in/2011/01/adaptive-cursor-sharing.html

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.