Hello,
My analysis on a particular job trace file drew my attention towards:
1) High rate of Parses instead of Bind variables usage.
2) High fetches and poor number/ low number of rows being processed
Please let me kno as to how the performance degradation can be minimised, Perhaps the high number of SQL* Net Client wait events may be due to multiple fetches and transactions with the client.
EXPLAIN PLAN FOR SELECT /*+ FIRST_ROWS (1) */ * FROM SAPNXP.INOB
WHERE MANDT = :A0
AND KLART = :A1
AND OBTAB = :A2
AND OBJEK LIKE :A3 AND ROWNUM <= :A4;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 119 0.00 0.00 0 0 0 0
Execute 239 0.16 0.13 0 0 0 0
Fetch 239 2069.31 2127.88 0 13738804 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 597 2069.47 2128.01 0 13738804 0 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1235313998
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 268 | 1 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| INOB | 2 | 268 | 1 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | INOB~2 | 7514 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(:A4))
2 - filter("OBJEK" LIKE :A3 AND "KLART"=:A1)
3 - access("MANDT"=:A0 AND "OBTAB"=:A2)
filter("OBTAB"=:A2)
18 rows selected.
SQL> SELECT INDEX_NAME,TABLE_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE INDEX_OWNER='SAPNXP' AND INDEX_NAME='INOB~2';
INDEX_NAME TABLE_NAME COLUMN_NAME
--------------- ------------------------------ --------------------
INOB~2 INOB MANDT
INOB~2 INOB CLINT
INOB~2 INOB OBTAB
Is it possible to Maximise the rows/fetch
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 163 0.03 0.00 0 0 0 0
Execute 163 0.01 0.03 0 0 0 0
Fetch 174899 55.26 59.14 0 1387649 0 4718932
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 175225 55.30 59.19 0 1387649 0 4718932
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
28952 TABLE ACCESS BY INDEX ROWID EDIDC (cr=8505 pr=0 pw=0 time=202797 us)
28952 INDEX RANGE SCAN EDIDC~1 (cr=1457 pr=0 pw=0 time=29112 us)(object id 202995)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 174899 0.00 0.16
SQL*Net more data to client 155767 0.01 5.69
SQL*Net message from client 174899 0.11 208.21
latch: cache buffers chains 2 0.00 0.00
latch free 4 0.00 0.00
********************************************************************************