http://javeedkaleem.blogspot.com/2010/02/what-to-do-when-high-cpu-utilization.html
b564ggw7zxtxz
SQL> !uname -a
AIX splmpmmbd1 1 7 00F7CF974C00
SQL> !topas
Exiting from topas
SQL> !topas
Exiting from topas
SQL> select count(1) from v$process;
COUNT(1)
----------
2711
SQL> select count(*) from v$session where status='ACTIVE';
COUNT(*)
----------
124
SQL> select count(*) from v$session where status='INACTIVE';
COUNT(*)
----------
2589
select rownum as rank, a.*
from (
SELECT sess.username,sess.sql_id,v.sid, program, v.value/(100*60) CPUMins,sess.machine
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;
RANK USERNAME SQL_ID SID Program CPU Mins MACHINE
---------- -------------- ----------------- ---------- -------------------------------------- -------- -------------------------
1 PRDI 8knnucfkt0zay 236 sqlplus@WP03INCT (TNS V1-V3) 984 WP03INCT
2 PRDI 222 rating_srvr@WP03INCT (TNS V1-V3) 43 WP03INCT
3 127 oracle@WP03INCT (MMNL) 29 WP03INCT
4 129 oracle@WP03INCT (CJQ0) 15 WP03INCT
5 131 oracle@WP03INCT (SMON) 12 WP03INCT
6 4gd6b1r53yt88 128 oracle@WP03INCT (MMON) 8 WP03INCT
7 PRDI 201 merge_srvr@WP03INCT (TNS V1-V3) 6 WP03INCT
8 PRDI 180 merge_srvr@WP03INCT (TNS V1-V3) 5 WP03INCT
9 PRDI 85 merge_srvr@WP03INCT (TNS V1-V3) 5 WP03INCT
10 PRDI 171 merge_srvr@WP03INCT (TNS V1-V3) 5 WP03INCT
select ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.status='ACTIVE'
and ss.username is not null
and rownum < 20
order by VALUE desc
/
USERNAME SID CPU_USAGE_SECONDS
-------------- ---------- -----------------
PRDI 236 59066.34
PRDI 37 117.71
EMSMON 173 13.05
SYS 47 .67
SYS 105 .02
SYS 47 0
SYS 105 0
SYS 105 0
SYS 105 0
select current_utilization, limit_value from v$resource_limit where resource_name='sessions';
select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');
SQL> !topas
Exiting from topas
set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a23
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_sec
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username !='SYS' and
ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);
At times pid using more cpu in top command will be an inactive session.
The below query is to find the inactive session and their details
set pages 1000
set lines 1000
col SPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a30
col MODULE for a18
col OSUSER for a15
col MACHINE for a25
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine
from v$session s, v$process p where s.paddr=p.addr and s.status like '%INACTIVE%';
If you want to track the session details by using PID listing in top output when cpu load is high, you can use below query only one time. After getting output, you just put / and enter then it'll ask enter your ospid then paste your PID alone and enter. You'll get the output. You can track as many as you can by viewing top output in parallel in the another session.
set pages 100
set lines 1000
col SPID heading 'SPID' for a06
col SID heading 'SID' for 99999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a17
col MODULE for a08
col OSUSER for a07
col MACHINE for a20
col TRACEFILE for a40
select p.spid,s.sid,s.serial#,s.username,s.status,s.sql_id,s.program,s.module,s.osuser,s.machine,
s.EVENT from v$session s, v$process p where s.paddr=p.addr and p.spid=&ospid;
SQL> select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser from v$session s,v$process p Where s.paddr = p.addr and p.spid=&pid;
Enter value for pid: 22217944
old 1: select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser from v$session s,v$process p Where s.paddr = p.addr and p.spid=&pid
new 1: select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser from v$session s,v$process p Where s.paddr = p.addr and p.spid=22217944
SID/SERIAL
--------------------------------------------------------------------------------
USERNAME OSUSER
------------------------------ ------------------------------
4631,61185
SINTERFACE operspsa
SQL> set lines 200 pages 300;
SQL> /
Enter value for pid: 22217944
old 1: select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser from v$session s,v$process p Where s.paddr = p.addr and p.spid=&pid
new 1: select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser from v$session s,v$process p Where s.paddr = p.addr and p.spid=22217944
SID/SERIAL USERNAME OSUSER
--------------------------------------------------------------------------------- ------------------------------ ------------------------------
4631,61185 SINTERFACE operspsa
SQL> SELECT SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid);
Enter value for sid: 4631
old 1: SELECT SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid)
new 1: SELECT SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=4631)
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT VPC.VENDOR_NUMBER, ORG.ORG_LVL_NUMBER, SUBVENDOR, IFHC.VPC_TECH_KEY, MIN(IFHC.PRD_LVL_CHILD) PRD_LVL_CHILD, MIN(IFHC.VPC_PRD_TECH_KEY) VPC_PRD_TECH_KEY FROM IFHSUGEE IFHC, BASVALEE BAS, ORGMSTE
E ORG, VPCMSTEE VPC WHERE IFHC.RPL_DIST_METHOD = 2 AND (:B2 = 0 OR :B2 = 2) AND IFHC.PRD_LVL_CHILD IN (SELECT PRD_LVL_CHILD FROM PRDMSTEE WHERE PRD_LVL_ID < 2 AND PRD_STYLE_IND = 'F' START WITH PRD_LV
L_CHILD = :B3 CONNECT BY PRIOR PRD_LVL_CHILD = PRD_LVL_PARENT) AND IFHC.DATE_CREATED = TO_DATE(:B1 ,'YYYYMMDD') AND IFHC.STATUS = 'L' AND IFHC.ORG_LVL_CHILD = BAS.TECH_KEY1 AND ORG.ORG_LVL_CHILD = IFH
C.ORG_LVL_CHILD AND VPC.VPC_TECH_KEY = IFHC.VPC_TECH_KEY AND BAS.VALUE = 'T' AND BAS.FIELD_CODE = 'TP' AND BAS.ENTITY_NAME = 'ORGMSTEE' AND IFHC.REQUEST_QTY > 0 GROUP BY VPC.VENDOR_NUMBER, ORG.ORG_LVL
_NUMBER, SUBVENDOR, IFHC.VPC_TECH_KEY
SQL> !topas
Exiting from topas
SQL> select a.sid,b.status,rpad(a.opname,15),a.sofar,a.totalwork, to_char(a.START_TIME,'dd-MON-YY HH24:MI') START_TIME, a.TIME_REMAINING,a.ELAPSED_SECONDS,((a.sofar)*100)/a.totalwork "%_completed", rpad(a.username,10) username,a.SQL_HASH_VALUE, B.STATUS from V$SESSION_LONGOPS a, v$session b where a.sid = b.sid and b.status='ACTIVE' AND a.sofar<> a.totalwork and a.totalwork>0 ;
SID STATUS RPAD(A.OPNAME,1 SOFAR TOTALWORK START_TIME TIME_REMAINING ELAPSED_SECONDS %_completed USERNAME SQL_HASH_VALUE STATUS
---------- -------- --------------- ---------- ---------- --------------- -------------- --------------- ----------- ---------- -------------- --------
2340 ACTIVE Table Scan 5608 10864 06-SEP-18 15:52 1527 1629 51.6200295 SINTERFACE 1127605985 ACTIVE
498 ACTIVE Table Scan 352026 362350 06-SEP-18 16:17 4 141 97.150821 1960610601 ACTIVE
1019 ACTIVE Index Fast Full 219 34321 06-SEP-18 15:57 204301 1312 .638093296 SINTERFACE 3764707120 ACTIVE
SQL> SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid)
2 ;
Enter value for sid: 2340
old 1: SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid)
new 1: SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=2340)
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f1ct7mj1mbtr1
PRD.PRDM_ALTERNATE_CODE AS "Codigo" , ,
--DECODE(TRF.TRF_STATUS AS "Cantidad" ,
-- NVL(TRF.TRF_SHP_CARTON, 0) +
TO_DATE(SUBSTR(SLD.EXPIRATION_DATE,1,8),'YYYY-MM-DD')
PRD.DES_CAT AS "Desc. Linea" , ,
SQL> /
Enter value for sid: 4631
old 1: SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid)
new 1: SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=4631)
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2tc8nkb17sg64
SELECT VPC.VENDOR_NUMBER, ORG.ORG_LVL_NUMBER, SUBVENDOR, IFHC.VPC_TECH_KEY, MIN(IFHC.PRD_LVL_CHILD) PRD_LVL_CHILD, MIN(IFHC.VPC_PRD_TECH_KEY) VPC_PRD_TECH_KEY FROM IFHSUGEE IFHC, BASVALEE BAS, ORGMSTE
E ORG, VPCMSTEE VPC WHERE IFHC.RPL_DIST_METHOD = 2 AND (:B2 = 0 OR :B2 = 2) AND IFHC.PRD_LVL_CHILD IN (SELECT PRD_LVL_CHILD FROM PRDMSTEE WHERE PRD_LVL_ID < 2 AND PRD_STYLE_IND = 'F' START WITH PRD_LV
L_CHILD = :B3 CONNECT BY PRIOR PRD_LVL_CHILD = PRD_LVL_PARENT) AND IFHC.DATE_CREATED = TO_DATE(:B1 ,'YYYYMMDD') AND IFHC.STATUS = 'L' AND IFHC.ORG_LVL_CHILD = BAS.TECH_KEY1 AND ORG.ORG_LVL_CHILD = IFH
C.ORG_LVL_CHILD AND VPC.VPC_TECH_KEY = IFHC.VPC_TECH_KEY AND BAS.VALUE = 'T' AND BAS.FIELD_CODE = 'TP' AND BAS.ENTITY_NAME = 'ORGMSTEE' AND IFHC.REQUEST_QTY > 0 GROUP BY VPC.VENDOR_NUMBER, ORG.ORG_LVL
_NUMBER, SUBVENDOR, IFHC.VPC_TECH_KEY
SQL> set lines 300 pages 4000
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'2tc8nkb17sg64',format=>'ALLSTATS LAST +cost +bytes'));SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2tc8nkb17sg64, child number 0
-------------------------------------
SELECT VPC.VENDOR_NUMBER, ORG.ORG_LVL_NUMBER, SUBVENDOR,
IFHC.VPC_TECH_KEY, MIN(IFHC.PRD_LVL_CHILD) PRD_LVL_CHILD,
MIN(IFHC.VPC_PRD_TECH_KEY) VPC_PRD_TECH_KEY FROM IFHSUGEE IFHC,
BASVALEE BAS, ORGMSTEE ORG, VPCMSTEE VPC WHERE IFHC.RPL_DIST_METHOD = 2
AND (:B2 = 0 OR :B2 = 2) AND IFHC.PRD_LVL_CHILD IN (SELECT
PRD_LVL_CHILD FROM PRDMSTEE WHERE PRD_LVL_ID < 2 AND PRD_STYLE_IND =
'F' START WITH PRD_LVL_CHILD = :B3 CONNECT BY PRIOR PRD_LVL_CHILD =
PRD_LVL_PARENT) AND IFHC.DATE_CREATED = TO_DATE(:B1 ,'YYYYMMDD') AND
IFHC.STATUS = 'L' AND IFHC.ORG_LVL_CHILD = BAS.TECH_KEY1 AND
ORG.ORG_LVL_CHILD = IFHC.ORG_LVL_CHILD AND VPC.VPC_TECH_KEY =
IFHC.VPC_TECH_KEY AND BAS.VALUE = 'T' AND BAS.FIELD_CODE = 'TP' AND
BAS.ENTITY_NAME = 'ORGMSTEE' AND IFHC.REQUEST_QTY > 0 GROUP BY
VPC.VENDOR_NUMBER, ORG.ORG_LVL_NUMBER, SUBVENDOR, IFHC.VPC_TECH_KEY
Plan hash value: 1777566233
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 292 (100)| | | |
| 1 | HASH GROUP BY | | 1 | 120 | 292 (2)| 810K| 810K| |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 120 | 291 (1)| | | |
| 4 | NESTED LOOPS | | 1 | 120 | 291 (1)| | | |
|* 5 | HASH JOIN | | 1 | 84 | 290 (1)| 866K| 866K| |
| 6 | JOIN FILTER CREATE | :BF0000 | 1 | 79 | 267 (1)| | | |
| 7 | NESTED LOOPS | | 1 | 79 | 267 (1)| | | |
| 8 | NESTED LOOPS | | 162 | 79 | 267 (1)| | | |
| 9 | MERGE JOIN CARTESIAN | | 1 | 44 | 13 (0)| | | |
|* 10 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
| 11 | BUFFER SORT | | 480 | 3840 | 10 (0)| 73728 | 73728 | |
| 12 | TABLE ACCESS FULL | ORGMSTEE | 480 | 3840 | 10 (0)| | | |
|* 13 | INDEX RANGE SCAN | IDX$$_AABB0002 | 162 | | 192 (1)| | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | IFHSUGEE | 1 | 35 | 254 (1)| | | |
| 15 | VIEW | VW_NSO_1 | 48 | 240 | 23 (5)| | | |
| 16 | HASH UNIQUE | | 48 | 1632 | 23 (5)| 1056K| 1056K| |
|* 17 | FILTER | | | | | | | |
| 18 | JOIN FILTER USE | :BF0000 | | | | | | |
|* 19 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | 73728 | 73728 | |
| 20 | TABLE ACCESS BY INDEX ROWID | PRDMSTEE | 1 | 15 | 2 (0)| | | |
|* 21 | INDEX UNIQUE SCAN | PRDMSTEEP1 | 1 | | 1 (0)| | | |
| 22 | NESTED LOOPS | | 47 | 1316 | 20 (0)| | | |
| 23 | CONNECT BY PUMP | | | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | PRDMSTEE | 47 | 705 | 18 (0)| | | |
|* 25 | INDEX RANGE SCAN | PRDMSTEEI3 | 47 | | 1 (0)| | | |
|* 26 | INDEX UNIQUE SCAN | VPCMSTEEP1 | 1 | | 0 (0)| | | |
| 27 | TABLE ACCESS BY INDEX ROWID | VPCMSTEE | 1 | 36 | 1 (0)| | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((:B2=0 OR :B2=2))
5 - access("IFHC"."PRD_LVL_CHILD"="PRD_LVL_CHILD")
10 - access("BAS"."FIELD_CODE"=U'TP' AND "BAS"."ENTITY_NAME"=U'ORGMSTEE' AND "BAS"."VALUE"=U'T')
13 - access("ORG"."ORG_LVL_CHILD"="IFHC"."ORG_LVL_CHILD")
filter("IFHC"."ORG_LVL_CHILD"="BAS"."TECH_KEY1")
14 - filter(("IFHC"."VPC_TECH_KEY" IS NOT NULL AND "IFHC"."REQUEST_QTY">0 AND "IFHC"."RPL_DIST_METHOD"=2 AND
"IFHC"."DATE_CREATED"=TO_DATE(TO_CHAR(:B1),'YYYYMMDD') AND "IFHC"."STATUS"='L'))
17 - filter(("PRD_LVL_ID"<2 AND "PRD_STYLE_IND"='F'))
19 - access("PRD_LVL_PARENT"=PRIOR NULL)
21 - access("PRD_LVL_CHILD"=:B3)
25 - access("connect$_by$_pump$_006"."PRIOR PRD_LVL_CHILD "="PRD_LVL_PARENT")
26 - access("VPC"."VPC_TECH_KEY"="IFHC"."VPC_TECH_KEY")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
73 rows selected.
SQL> select a.sid,b.status,rpad(a.opname,15),a.sofar,a.totalwork, to_char(a.START_TIME,'dd-MON-YY HH24:MI') START_TIME, a.TIME_REMAINING,a.ELAPSED_SECONDS,((a.sofar)*100)/a.totalwork "%_completed", rpad(a.username,10) username,a.SQL_HASH_VALUE, B.STATUS from V$SESSION_LONGOPS a, v$session b where a.sid = b.sid and b.status='ACTIVE' AND a.sofar<> a.totalwork and a.totalwork>0 ;
SID STATUS RPAD(A.OPNAME,1 SOFAR TOTALWORK START_TIME TIME_REMAINING ELAPSED_SECONDS %_completed USERNAME SQL_HASH_VALUE STATUS
---------- -------- --------------- ---------- ---------- --------------- -------------- --------------- ----------- ---------- -------------- --------
2340 ACTIVE Table Scan 6452 10864 06-SEP-18 15:52 1436 2100 59.3888071 SINTERFACE 1127605985 ACTIVE
2454 ACTIVE Table Scan 50977 121020 06-SEP-18 16:27 37 27 42.1227896 EINTERFACE 689089202 ACTIVE
1019 ACTIVE Index Fast Full 297 34321 06-SEP-18 15:57 204373 1784 .865359401 SINTERFACE 3764707120 ACTIVE
SQL> SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid)
2 ;
Enter value for sid: 2340
old 1: SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid)
new 1: SELECT sql_id,SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=2340)
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f1ct7mj1mbtr1
PRD.COD_BAR AS "EAN" , ,
-- NVL(TRF.TRF_SHP_CARTON, 0) +
PRD.DES_SEC AS "Fecha de Caducidad" , )
PRD.DES_CAT AS "Desc. Linea" ,
SQL> set lines 300 pages 4000
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'f1ct7mj1mbtr1',format=>'ALLSTATS LAST +cost +bytes'));SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f1ct7mj1mbtr1, child number 0
-------------------------------------
SELECT SLH.LOAD_MANIFEST_NBR AS "Nro. Carga" ,
SLD.PALLET_NBR AS "Unidad Logistica",
SLD.ITEM_ALTERNATE_CODE AS "Codigo" ,
PRD.PRD_FULL_NAME AS "Descrip. Artic",
PRD.COD_BAR AS "EAN" ,
SLD.SHIPPED_QTY AS "Cantidad" ,
--DECODE(TRF.TRF_STATUS,5,NVL(TRF.TRF_QTY_REQ,0),(NVL(TRF.TRF_QTY_SHIP,
--
--CARTON, 0) +
-- NVL(TRF.TRF_SHP_MANIFEST, 0)))
AS "Cant. Recibida",
TO_DATE(SUBSTR(SLD.EXPIRATION_DATE,1,8),'YYYY-MM-DD')
AS "Fecha de Caducidad" ,
PRD.DES_SEC AS "Desc. Seccion" ,
PRD.DES_CAT AS "Desc. Linea" ,
Plan hash value: 845846070
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 26595 (100)| | | |
| 1 | UNION-ALL | | | | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS OUTER | | 13 | 6422 | 13318 (1)| | | |
| 4 | VIEW | | 13 | 5902 | 13279 (1)| | | |
| 5 | NESTED LOOPS | | 13 | 5083 | 13279 (1)| | | |
| 6 | NESTED LOOPS | | 13 | 5083 | 13279 (1)| | | |
|* 7 | HASH JOIN OUTER | | 13 | 4056 | 13240 (1)| 743K| 743K| 172K (0)|
|* 8 | HASH JOIN OUTER | | 13 | 3094 | 13224 (1)| 2616K| 2616K| 192K (0)|
| 9 | NESTED LOOPS | | 13 | 2132 | 13208 (1)| | | |
| 10 | NESTED LOOPS | | 56823 | 2132 | 13208 (1)| | | |
|* 11 | TABLE ACCESS FULL | WMS_SHP_LOAD_HDR | 1457 | 87420 | 5957 (1)| | | |
|* 12 | INDEX RANGE SCAN | PK_WMS_SHP_LOAD_DTL | 39 | | 2 (0)| | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_DTL | 1 | 104 | 5 (0)| | | |
| 14 | VIEW | VI_ORGMSTEE | 469 | 34706 | 16 (0)| | | |
|* 15 | HASH JOIN RIGHT OUTER | | 469 | 53935 | 16 (0)| 2293K| 2293K| 1261K (0)|
|* 16 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 27 | 3 (0)| | | |
|* 17 | HASH JOIN RIGHT OUTER | | 469 | 41272 | 13 (0)| 1599K| 1599K| 1293K (0)|
|* 18 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 19 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 10 (0)| | | |
| 20 | VIEW | VI_ORGMSTEE | 469 | 34706 | 16 (0)| | | |
|* 21 | HASH JOIN RIGHT OUTER | | 469 | 53935 | 16 (0)| 1148K| 1148K| |
|* 22 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 27 | 3 (0)| | | |
|* 23 | HASH JOIN RIGHT OUTER | | 469 | 41272 | 13 (0)| 1048K| 1048K| |
|* 24 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 25 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 10 (0)| | | |
|* 26 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 2 (0)| | | |
| 27 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 3 (0)| | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | WMS_RCV_ASN_HDR | 1 | 40 | 3 (0)| | | |
|* 29 | INDEX RANGE SCAN | WMS_RCV_ASN_HDR_IDX01 | 1 | | 2 (0)| | | |
| 30 | NESTED LOOPS | | 1 | 72 | 5 (0)| | | |
|* 31 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 32 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| | | |
| 33 | NESTED LOOPS | | 1 | 72 | 5 (0)| | | |
|* 34 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 35 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| | | |
|* 36 | FILTER | | | | | | | |
| 37 | NESTED LOOPS | | 1 | 384 | 13240 (1)| | | |
| 38 | NESTED LOOPS | | 1 | 384 | 13240 (1)| | | |
|* 39 | HASH JOIN OUTER | | 1 | 305 | 13237 (1)| 730K| 730K| |
| 40 | NESTED LOOPS OUTER | | 1 | 301 | 13218 (1)| | | |
| 41 | NESTED LOOPS OUTER | | 1 | 234 | 13213 (1)| | | |
| 42 | NESTED LOOPS | | 1 | 167 | 13208 (1)| | | |
|* 43 | TABLE ACCESS FULL | WMS_SHP_LOAD_HDR | 1457 | 91791 | 5957 (1)| | | |
|* 44 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_DTL | 1 | 104 | 5 (0)| | | |
|* 45 | INDEX RANGE SCAN | PK_WMS_SHP_LOAD_DTL | 39 | | 2 (0)| | | |
| 46 | VIEW PUSHED PREDICATE | VI_ORGMSTEE | 1 | 67 | 5 (0)| | | |
| 47 | NESTED LOOPS OUTER | | 1 | 115 | 5 (0)| | | |
| 48 | NESTED LOOPS | | 1 | 88 | 4 (0)| | | |
|* 49 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 50 | TABLE ACCESS BY INDEX ROWID| ORGMSTEE | 1 | 52 | 1 (0)| | | |
|* 51 | INDEX UNIQUE SCAN | ORGMSTEEP1 | 1 | | 0 (0)| | | |
|* 52 | INDEX UNIQUE SCAN | BASVALEEP1 | 1 | 27 | 1 (0)| | | |
| 53 | VIEW PUSHED PREDICATE | VI_ORGMSTEE | 1 | 67 | 5 (0)| | | |
| 54 | NESTED LOOPS OUTER | | 1 | 115 | 5 (0)| | | |
| 55 | NESTED LOOPS | | 1 | 88 | 4 (0)| | | |
|* 56 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 57 | TABLE ACCESS BY INDEX ROWID | ORGMSTEE | 1 | 52 | 1 (0)| | | |
|* 58 | INDEX UNIQUE SCAN | ORGMSTEEP1 | 1 | | 0 (0)| | | |
|* 59 | INDEX UNIQUE SCAN | BASVALEEP1 | 1 | 27 | 1 (0)| | | |
| 60 | TABLE ACCESS FULL | WMS_ERROR_INT | 41 | 164 | 19 (0)| | | |
|* 61 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 2 (0)| | | |
| 62 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 3 (0)| | | |
| 63 | NESTED LOOPS | | 1 | 72 | 5 (0)| | | |
|* 64 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 65 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| | | |
| 66 | NESTED LOOPS | | 1 | 72 | 5 (0)| | | |
|* 67 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| | | |
|* 68 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(( IS NOT NULL OR IS NOT NULL))
7 - access("ORGT"."CENTRO_SAP"=SYS_OP_C2C("SLD"."SHIPTO_FACILITY_CODE"))
8 - access("ORGC"."CENTRO_SAP"=SYS_OP_C2C("SLH"."FACILITY_CODE"))
11 - filter((TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')>=TO_DATE(' 2017-09-05 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')<=TO_DATE(' 2018-09-06 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
12 - access("SLH"."HDR_GROUP_NBR"="SLD"."HDR_GROUP_NBR")
13 - filter(("SLD"."PALLET_NBR"='01PLF00700477509' AND (SUBSTR("ORDER_NBR",1,3) OR
"SLH"."FACILITY_CODE"=SUBSTR("SLD"."ORDER_NBR",1,4))))
15 - access("P"."TECH_KEY1"="O"."ORG_LVL_CHILD")
16 - access("P"."FIELD_CODE"=U'TP' AND "P"."ENTITY_NAME"=U'ORGMSTEE')
17 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
18 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE')
19 - filter("O"."ORG_LVL_ID"=1)
21 - access("P"."TECH_KEY1"="O"."ORG_LVL_CHILD")
22 - access("P"."FIELD_CODE"=U'TP' AND "P"."ENTITY_NAME"=U'ORGMSTEE')
23 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
24 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE')
25 - filter("O"."ORG_LVL_ID"=1)
26 - access("PRD"."PRD_LVL_NUMBER"=LTRIM("SLD"."ITEM_ALTERNATE_CODE",'0'))
28 - filter(("HDR"."ERR_CODE"=0 AND "SLD"."SHIPTO_FACILITY_CODE"="HDR"."FACILITY_CODE"))
29 - access("SLH"."LOAD_MANIFEST_NBR"="HDR"."SHIPMENT_NBR")
31 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
32 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
34 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
35 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
36 - filter(( IS NOT NULL OR IS NOT NULL))
39 - access("SLH"."ERR_CODE"="WEI"."ERR_CODE")
43 - filter((TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')>=TO_DATE(' 2017-09-05 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')<=TO_DATE(' 2018-09-06 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
44 - filter(("SLD"."PALLET_NBR"='01PLF00700477509' AND SUBSTR("ORDER_NBR",1,3)='RTV'))
45 - access("SLH"."HDR_GROUP_NBR"="SLD"."HDR_GROUP_NBR")
49 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE' AND
"S"."VALUE"=SYS_OP_C2C("SLH"."FACILITY_CODE"))
50 - filter("O"."ORG_LVL_ID"=1)
51 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
52 - access("P"."ENTITY_NAME"=U'ORGMSTEE' AND "P"."TECH_KEY1"="O"."ORG_LVL_CHILD" AND "P"."FIELD_CODE"=U'TP')
56 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE' AND
"S"."VALUE"=SYS_OP_C2C("SLD"."SHIPTO_FACILITY_CODE"))
57 - filter("O"."ORG_LVL_ID"=1)
58 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
59 - access("P"."ENTITY_NAME"=U'ORGMSTEE' AND "P"."TECH_KEY1"="O"."ORG_LVL_CHILD" AND "P"."FIELD_CODE"=U'TP')
61 - access("PRD"."PRD_LVL_NUMBER"=LTRIM("SLD"."ITEM_ALTERNATE_CODE",'0'))
64 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
65 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
67 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
68 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
156 rows selected.
SQL> !topas
Exiting from topas
SQL> show parameter control_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> cl scr
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'f1ct7mj1mbtr1',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'f1ct7mj1mbtr1_tuning_task11',
description => 'Tuning task1 for statement f1ct7mj1mbtr1');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/ 2 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'f1ct7mj1mbtr1_tuning_task11');
PL/SQL procedure successfully completed.
SQL> set long 65536
set longchunksize 65536
select dbms_sqltune.report_tuning_task('f1ct7mj1mbtr1_tuning_task11') from dual;SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('F1CT7MJ1MBTR1_TUNING_TASK11')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : f1ct7mj1mbtr1_tuning_task11
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 09/06/2018 16:39:12
Completed at : 09/06/2018 16:41:57
-------------------------------------------------------------------------------
Schema Name: SINTERFACE
SQL ID : f1ct7mj1mbtr1
SQL Text : SELECT SLH.LOAD_MANIFEST_NBR AS "Nro. Carga" ,
SLD.PALLET_NBR AS "Unidad Logistica",
SLD.ITEM_ALTERNATE_CODE AS "Codigo" ,
PRD.PRD_FULL_NAME AS "Descrip. Artic",
PRD.COD_BAR AS "EAN" ,
SLD.SHIPPED_QTY AS "Cantidad" ,
--DECODE(TRF.TRF_STATUS,5,NVL(TRF.TRF_QTY_REQ,0),(NV
L(TRF.TRF_QTY_SHIP, 0) +
--
NVL(TRF.TRF_SHP_CARTON, 0) +
--
NVL(TRF.TRF_SHP_MANIFEST, 0)))
-- AS "Cant. Recibida",
TO_DATE(SUBSTR(SLD.EXPIRATION_DATE,1,8),'YYYY-MM-DD'
)
AS "Fecha de Caducidad" ,
PRD.DES_SEC AS "Desc. Seccion" ,
PRD.DES_CAT AS "Desc. Linea" ,
TO_CHAR(SLD.DOWNLOAD_DATE_1,'DD/MM/YYYY
HH24:MI:SS')
AS "Fecha Hora Envio LF"
,
TO_CHAR(HDR.CREATE_DATE,'DD/MM/YYYY HH24:MI:SS')
AS "Fecha Hora Recepcion
Lf" ,
TO_CHAR(HDR.DOWNLOAD_DATE_1,'DD/MM/YYYY
HH24:MI:SS')
AS "Fecha Hora Recepcion
PMM" ,
SLH.FACILITY_CODE AS "CD" ,
ORGC.ORG_NAME_FULL AS "Desc. CD" ,
SLD.SHIPTO_FACILITY_CODE AS "Tienda" ,
ORGT.ORG_NAME_FULL AS "Desc. Tienda" ,
SLH.HDR_BOL_NBR AS "Guia" ,
SLH.TRAILER_NBR AS "Camion" ,
SLD.OB_LPN_NBR AS "Carton" ,
SLD.ORDER_NBR AS "Nro Orden" ,
TP_PKG_GEN_FUNCIONES.FN_OBTIENE_COSTO(PRD.PRD_LVL_NU
MBER,
ORGC.ORG_LVL_NUMBER)
AS "Costo Promedio",
(SLD.SHIPPED_QTY * TP_PKG_GEN_FUNCIONES.FN_OBTIENE_C
OSTO(PRD.PRD_LVL_NUMBER,
ORGC.ORG_LVL_NUMBER))
AS "Total Carga"
FROM WMS_SHP_LOAD_HDR SLH
INNER JOIN WMS_SHP_LOAD_DTL SLD ON SLH.HDR_GROUP_NBR =
SLD.HDR_GROUP_NBR
INNER JOIN IFHPRDMST PRD ON LTRIM(SLD.ITEM_ALTERNATE_CODE,'0')
= PRD.PRD_LVL_NUMBER
LEFT JOIN VI_ORGMSTEE ORGC ON SLH.FACILITY_CODE =
ORGC.CENTRO_SAP
LEFT JOIN VI_ORGMSTEE ORGT ON SLD.SHIPTO_FACILITY_CODE =
ORGT.CENTRO_SAP
--LEFT JOIN WMS_ERROR_INT WEI ON SLH.ERR_CODE = WEI.ERR_CODE
--LEFT JOIN TRFDTLEE TRF ON WMS_PKG_UTIL.FN_GET_VAL_PEDIDO(SLD
.ORDER_NBR ,
--
PRD.PRD_LVL_CHILD ,
--
ORGC.ORG_LVL_CHILD ,
--
ORGT.ORG_LVL_CHILD ,
--
SLH.FACILITY_CODE) = TRF.TRF_NUMBER
-- AND PRD.PRD_LVL_CHILD =
TRF.PRD_LVL_CHILD
--LEFT JOIN TRFHDREE TH ON TRF.TRF_NUMBER = TH.TRF_NUMBER
--LEFT JOIN TRFSTSCD ST ON TH.TRF_STATUS = ST.TRF_STATUS_ID
LEFT JOIN WMS_RCV_ASN_HDR HDR ON SLH.LOAD_MANIFEST_NBR =
HDR.SHIPMENT_NBR
AND SLD.SHIPTO_FACILITY_CODE =
HDR.FACILITY_CODE
AND HDR.ERR_CODE = 0
WHERE (SUBSTR(SLD.ORDER_NBR, 1, 3) IN ('TRF', 'TFC',
'ORD', 'OST', 'SDD','SDE','SDR')
OR SUBSTR(SLD.ORDER_NBR, 1, 4) = SLH.FACILITY_CODE)
AND TO_DATE(SUBSTR(SLH.SHIP_DATE,1,8),'YYYY-MM-DD')
BETWEEN TO_DATE('20170905','YYYYMMDD')
AND TO_DATE('20180906','YYYYMMDD')
AND ((DECODE('','','ALL_ITEMS',ORGC.ORG_LVL_NUMBER)
LIKE DECODE('','','ALL_ITEMS','')) OR ORGC.ORG_LVL_NUMBER IN
(''))
AND ((DECODE('','','ALL_ITEMS',ORGT.ORG_LVL_NUMBER)
LIKE DECODE('','','ALL_ITEMS','')) OR ORGT.ORG_LVL_NUMBER IN
(''))
AND ((DECODE('' ,'','ALL_ITEMS',SLH.LOAD_MANIFEST_NBR)
LIKE DECODE('' ,'','ALL_ITEMS','' )) OR SLH.LOAD_MANIFEST_NBR
IN ('' ))
AND ((DECODE('01PLF00700477509','','ALL_ITEMS',SLD.PALLE
T_NBR) LIKE DECODE('01PLF00700477509','','ALL_ITEMS','01PLF007004
77509')) OR SLD.PALLET_NBR IN ('01PLF00700477509'))
AND ((DECODE('','','ALL_ITEMS',PRD.PRD_LVL_NUMBER) LIKE
DECODE('','','ALL_ITEMS','')) OR PRD.PRD_LVL_NUMBER IN (''))
AND ((SLH.FACILITY_CODE IN (SELECT C.VALUE
FROM BASVALEE B,
BASVALEE C
WHERE B.ENTITY_NAME =
'ORGMSTEE'
AND B.FIELD_CODE = 'TP'
AND B.VALUE = 'T'
AND C.FIELD_CODE = '01'
AND C.ENTITY_NAME =
'ORGMSTEE'
AND C.TECH_KEY1 =
B.TECH_KEY1)
) OR
(SLD.SHIPTO_FACILITY_CODE IN (SELECT C.VALUE
FROM BASVALEE B,
BASVALEE C
WHERE B.ENTITY_NAME =
'ORGMSTEE'
AND B.FIELD_CODE =
'TP'
AND B.VALUE =
'T'
AND C.FIELD_CODE =
'01'
AND C.ENTITY_NAME =
'ORGMSTEE'
AND C.TECH_KEY1 =
B.TECH_KEY1)
))
UNION ALL
SELECT SLH.LOAD_MANIFEST_NBR AS "Nro. Carga" ,
SLD.PALLET_NBR AS "Unidad Logistica",
SLD.ITEM_ALTERNATE_CODE AS "Codigo" ,
PRD.PRD_FULL_NAME AS "Descrip. Artic",
PRD.COD_BAR AS "EAN" ,
SLD.SHIPPED_QTY AS "Cantidad" ,
--NVL(RTV.RTV_QTY_PICK,0) AS "Cant. Recibida",
TO_DATE(SUBSTR(SLD.EXPIRATION_DATE,1,8),'YYYY-MM-DD'
)
AS "Fecha de Caducidad" ,
PRD.DES_SEC AS "Desc. Seccion" ,
PRD.DES_CAT AS "Desc. Linea" ,
TO_CHAR(SLD.DOWNLOAD_DATE_1,'DD/MM/YYYY
HH24:MI:SS')
AS "Fecha Hora Envio LF"
,
NULL AS "Fecha Hora Recepcion
Lf" ,
NULL AS "Fecha Hora Recepcion
PMM" ,
SLH.FACILITY_CODE AS "CD" ,
ORGC.ORG_NAME_FULL AS "Desc. CD" ,
SLD.SHIPTO_FACILITY_CODE AS "Tienda" ,
ORGT.ORG_NAME_FULL AS "Desc. Tienda" ,
SLH.HDR_BOL_NBR AS "Guia" ,
SLH.TRAILER_NBR AS "Camion" ,
SLD.OB_LPN_NBR AS "Carton" ,
SLD.ORDER_NBR AS "Nro Orden" ,
TP_PKG_GEN_FUNCIONES.FN_OBTIENE_COSTO(PRD.PRD_LVL_NU
MBER,
ORGC.ORG_LVL_NUMBER)
AS "Costo Promedio",
(SLD.SHIPPED_QTY * TP_PKG_GEN_FUNCIONES.FN_OBTIENE_C
OSTO(PRD.PRD_LVL_NUMBER,
ORGC.ORG_LVL_NUMBER))
AS "Total Carga"
FROM WMS_SHP_LOAD_HDR SLH
INNER JOIN WMS_SHP_LOAD_DTL SLD ON SLH.HDR_GROUP_NBR =
SLD.HDR_GROUP_NBR
INNER JOIN IFHPRDMST PRD ON LTRIM(SLD.ITEM_ALTERNATE_CODE,'0')
= PRD.PRD_LVL_NUMBER
LEFT JOIN VI_ORGMSTEE ORGC ON SLH.FACILITY_CODE =
ORGC.CENTRO_SAP
LEFT JOIN VI_ORGMSTEE ORGT ON SLD.SHIPTO_FACILITY_CODE =
ORGT.CENTRO_SAP
LEFT JOIN WMS_ERROR_INT WEI ON SLH.ERR_CODE = WEI.ERR_CODE
--LEFT JOIN RTVDTLEE RTV ON TO_NUMBER(SUBSTR(SLD.ORDER_NBR,
8)) = RTV.RTV_NUMBER
-- AND PRD.PRD_LVL_CHILD =
RTV.PRD_LVL_CHILD
--LEFT JOIN RTVSTSCD RST ON RTV.RTV_STATUS =
RST.RTV_STATUS_ID
WHERE (SUBSTR(SLD.ORDER_NBR, 1, 3) IN ('RTV') )
AND TO_DATE(SUBSTR(SLH.SHIP_DATE,1,8),'YYYY-MM-DD')
BETWEEN TO_DATE('20170905','YYYYMMDD')
AND TO_DATE('20180906','YYYYMMDD')
AND ((DECODE('','','ALL_ITEMS',ORGC.ORG_LVL_NUMBER)
LIKE DECODE('','','ALL_ITEMS','')) OR ORGC.ORG_LVL_NUMBER IN
(''))
AND ((DECODE('','','ALL_ITEMS',ORGT.ORG_LVL_NUMBER)
LIKE DECODE('','','ALL_ITEMS','')) OR ORGT.ORG_LVL_NUMBER IN
(''))
AND ((DECODE('' ,'','ALL_ITEMS',SLH.LOAD_MANIFEST_NBR)
LIKE DECODE('' ,'','ALL_ITEMS','' )) OR SLH.LOAD_MANIFEST_NBR
IN ('' ))
AND ((DECODE('01PLF00700477509','','ALL_ITEMS',SLD.PALLE
T_NBR) LIKE DECODE('01PLF00700477509','','ALL_ITEMS','01PLF007004
77509')) OR SLD.PALLET_NBR IN ('01PLF00700477509'))
AND ((DECODE('','','ALL_ITEMS',PRD.PRD_LVL_NUMBER) LIKE
DECODE('','','ALL_ITEMS','')) OR PRD.PRD_LVL_NUMBER IN (''))
AND ((SLH.FACILITY_CODE IN (SELECT C.VALUE
FROM BASVALEE B,
BASVALEE C
WHERE B.ENTITY_NAME =
'ORGMSTEE'
AND B.FIELD_CODE = 'TP'
AND B.VALUE = 'T'
AND C.FIELD_CODE = '01'
AND C.ENTITY_NAME =
'ORGMSTEE'
AND C.TECH_KEY1 =
B.TECH_KEY1)
) OR
(SLD.SHIPTO_FACILITY_CODE IN (SELECT C.VALUE
FROM BASVALEE B,
BASVALEE C
WHERE B.ENTITY_NAME =
'ORGMSTEE'
AND B.FIELD_CODE =
'TP'
AND B.VALUE =
'T'
AND C.FIELD_CODE =
'01'
AND C.ENTITY_NAME =
'ORGMSTEE'
AND C.TECH_KEY1 =
B.TECH_KEY1)
))
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 87.67%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'f1ct7mj1mbtr1_tuning_task11', task_owner => 'SYS', replace =>
TRUE);
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.98%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EINTERFACE.IDX$$_132240001 on
EINTERFACE.WMS_SHP_LOAD_DTL("PALLET_NBR");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EINTERFACE.IDX$$_132240002 on
EINTERFACE.WMS_RCV_ASN_HDR("FACILITY_CODE","SHIPMENT_NBR");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EDSR.IDX$$_132240003 on EDSR.WMS_ERROR_INT("ERR_CODE");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 845846070
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 13677 | 5054K (1)| 00:02:54 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 13 | 6422 | 2524K (1)| 00:01:27 |
| 4 | VIEW | | 13 | 5902 | 2524K (1)| 00:01:27 |
| 5 | NESTED LOOPS | | 13 | 5083 | 2524K (1)| 00:01:27 |
| 6 | NESTED LOOPS | | 13 | 5083 | 2524K (1)| 00:01:27 |
|* 7 | HASH JOIN OUTER | | 13 | 4056 | 2524K (1)| 00:01:27 |
|* 8 | HASH JOIN OUTER | | 13 | 3094 | 2524K (1)| 00:01:27 |
| 9 | NESTED LOOPS | | 13 | 2132 | 2524K (1)| 00:01:27 |
| 10 | NESTED LOOPS | | 19M| 2132 | 2524K (1)| 00:01:27 |
|* 11 | TABLE ACCESS FULL | WMS_SHP_LOAD_HDR | 506K| 28M| 5959 (1)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | PK_WMS_SHP_LOAD_DTL | 39 | | 2 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_DTL | 1 | 104 | 5 (0)| 00:00:01 |
| 14 | VIEW | VI_ORGMSTEE | 8464 | 611K| 23 (0)| 00:00:01 |
|* 15 | HASH JOIN RIGHT OUTER | | 8464 | 950K| 23 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | BASVALEE_2 | 340 | 9180 | 6 (0)| 00:00:01 |
|* 17 | HASH JOIN RIGHT OUTER | | 8464 | 727K| 17 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | BASVALEE_2 | 483 | 17388 | 7 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 10 (0)| 00:00:01 |
| 20 | VIEW | VI_ORGMSTEE | 8464 | 611K| 23 (0)| 00:00:01 |
|* 21 | HASH JOIN RIGHT OUTER | | 8464 | 950K| 23 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | BASVALEE_2 | 340 | 9180 | 6 (0)| 00:00:01 |
|* 23 | HASH JOIN RIGHT OUTER | | 8464 | 727K| 17 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | BASVALEE_2 | 483 | 17388 | 7 (0)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 10 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 3 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | WMS_RCV_ASN_HDR | 1 | 40 | 3 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | WMS_RCV_ASN_HDR_IDX01 | 1 | | 2 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 72 | 650 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | BASVALEE_2 | 322 | 11592 | 6 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 72 | 650 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | BASVALEE_2 | 322 | 11592 | 6 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
|* 36 | FILTER | | | | | |
| 37 | NESTED LOOPS | | 1 | 384 | 2524K (1)| 00:01:27 |
| 38 | NESTED LOOPS | | 1 | 384 | 2524K (1)| 00:01:27 |
|* 39 | HASH JOIN OUTER | | 1 | 305 | 2524K (1)| 00:01:27 |
| 40 | NESTED LOOPS OUTER | | 1 | 301 | 2524K (1)| 00:01:27 |
| 41 | NESTED LOOPS OUTER | | 1 | 234 | 2524K (1)| 00:01:27 |
| 42 | NESTED LOOPS | | 1 | 167 | 2524K (1)| 00:01:27 |
|* 43 | TABLE ACCESS FULL | WMS_SHP_LOAD_HDR | 506K| 30M| 5959 (1)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_DTL | 1 | 104 | 5 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | PK_WMS_SHP_LOAD_DTL | 39 | | 2 (0)| 00:00:01 |
| 46 | VIEW PUSHED PREDICATE | VI_ORGMSTEE | 1 | 67 | 22 (0)| 00:00:01 |
| 47 | NESTED LOOPS OUTER | | 18 | 2070 | 22 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 18 | 1584 | 4 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID| ORGMSTEE | 18 | 936 | 1 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | ORGMSTEEP1 | 1 | | 0 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | BASVALEEP1 | 1 | 27 | 1 (0)| 00:00:01 |
| 53 | VIEW PUSHED PREDICATE | VI_ORGMSTEE | 1 | 67 | 22 (0)| 00:00:01 |
| 54 | NESTED LOOPS OUTER | | 18 | 2070 | 22 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 18 | 1584 | 4 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | ORGMSTEE | 18 | 936 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | ORGMSTEEP1 | 1 | | 0 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | BASVALEEP1 | 1 | 27 | 1 (0)| 00:00:01 |
| 60 | TABLE ACCESS FULL | WMS_ERROR_INT | 41 | 164 | 19 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 2 (0)| 00:00:01 |
| 62 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 3 (0)| 00:00:01 |
| 63 | NESTED LOOPS | | 1 | 72 | 650 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | BASVALEE_2 | 322 | 11592 | 6 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
| 66 | NESTED LOOPS | | 1 | 72 | 650 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | BASVALEE_2 | 322 | 11592 | 6 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ LEADING ("B" "C") OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B")
"IDX$$_ADB70002" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B")
"IDX$$_ACF30001" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "BASVALEE_2"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "IDX$$_ACF30001"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1" SCALE_ROWS=711.488795 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B") "BASVALEE_2" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE
(NLJ_INDEX_FILTER "C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN
"C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 )
OPT_ESTIMATE (INDEX_SCAN "C" "IDX$$_BC440001" SCALE_ROWS=711.488795 ) OPT_ESTIMATE (NLJ_INDEX_FILTER
"C" ("B") "IDX$$_ADB70002" SCALE_ROWS=1000341615.000000 ) USE_NL ("C") INDEX ("C" "IDX$$_ADB70002")
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_2" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C")
"BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ACF30001"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCALE_ROWS=203644971.200000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_1" SCALE_ROWS=379255.137600 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_B0950002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB40003"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_FILTER "B" "BASVALEEP1" SCALE_ROWS=333.181328 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 )
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "IDX$$_ACF30001" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B"
"IDX$$_BC440001" SCALE_ROWS=333.181328 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_2"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_3" SCALE_ROWS=263636499.900000
) INDEX ("B" "BASVALEE_2") */ 0 FROM "EPMM"."BASVALEE" "C","EPMM"."BASVALEE" "B" WHERE
"B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."FIELD_CODE"=U'TP' AND
"C"."TECH_KEY1"="B"."TECH_KEY1" AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND
"C"."FIELD_CODE"=U'01') OR EXISTS (SELECT /*+ LEADING ("B" "C") OPT_ESTIMATE (NLJ_INDEX_SCAN "C"
("B") "IDX$$_ADB70002" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B")
"IDX$$_ACF30001" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "BASVALEE_2"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "IDX$$_ACF30001"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1" SCALE_ROWS=711.488795 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B") "BASVALEE_2" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE
(NLJ_INDEX_FILTER "C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN
"C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 )
OPT_ESTIMATE (INDEX_SCAN "C" "IDX$$_BC440001" SCALE_ROWS=711.488795 ) OPT_ESTIMATE (NLJ_INDEX_FILTER
"C" ("B") "IDX$$_ADB70002" SCALE_ROWS=1000341615.000000 ) USE_NL ("C") INDEX ("C" "IDX$$_ADB70002")
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_2" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C")
"BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002" SCA)
7 - access("ORGT"."CENTRO_SAP"(+)=SYS_OP_C2C("SLD"."SHIPTO_FACILITY_CODE"))
8 - access("ORGC"."CENTRO_SAP"(+)=SYS_OP_C2C("SLH"."FACILITY_CODE"))
11 - filter(TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')>=TO_DATE(' 2017-09-05 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')<=TO_DATE('
2018-09-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
12 - access("SLH"."HDR_GROUP_NBR"="SLD"."HDR_GROUP_NBR")
13 - filter("SLD"."PALLET_NBR"='01PLF00700477509' AND ((SUBSTR("ORDER_NBR",1,3)='ORD' OR
SUBSTR("ORDER_NBR",1,3)='OST' OR SUBSTR("ORDER_NBR",1,3)='SDD' OR SUBSTR("ORDER_NBR",1,3)='SDE' OR
SUBSTR("ORDER_NBR",1,3)='SDR' OR SUBSTR("ORDER_NBR",1,3)='TFC' OR SUBSTR("ORDER_NBR",1,3)='TRF') OR
"SLH"."FACILITY_CODE"=SUBSTR("SLD"."ORDER_NBR",1,4) AND
SUBSTR("ORDER_NBR",1,3)=SUBSTR("SLH"."FACILITY_CODE",1,3)))
15 - access("P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
16 - access("P"."FIELD_CODE"(+)=U'TP' AND "P"."ENTITY_NAME"(+)=U'ORGMSTEE')
17 - access("S"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
18 - access("S"."FIELD_CODE"(+)=U'01' AND "S"."ENTITY_NAME"(+)=U'ORGMSTEE')
19 - filter("O"."ORG_LVL_ID"=1)
21 - access("P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
22 - access("P"."FIELD_CODE"(+)=U'TP' AND "P"."ENTITY_NAME"(+)=U'ORGMSTEE')
23 - access("S"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
24 - access("S"."FIELD_CODE"(+)=U'01' AND "S"."ENTITY_NAME"(+)=U'ORGMSTEE')
25 - filter("O"."ORG_LVL_ID"=1)
26 - access("PRD"."PRD_LVL_NUMBER"=LTRIM("SLD"."ITEM_ALTERNATE_CODE",'0'))
28 - filter("HDR"."ERR_CODE"(+)=0 AND "SLD"."SHIPTO_FACILITY_CODE"="HDR"."FACILITY_CODE"(+))
29 - access("SLH"."LOAD_MANIFEST_NBR"="HDR"."SHIPMENT_NBR"(+))
31 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
32 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE'
AND "C"."TECH_KEY1"="B"."TECH_KEY1")
34 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
35 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE'
AND "C"."TECH_KEY1"="B"."TECH_KEY1")
36 - filter( EXISTS (SELECT /*+ LEADING ("B" "C") OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1"
SCALE_ROWS=711.488795 ) OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 ) OPT_ESTIMATE (INDEX_SCAN "C"
"IDX$$_BC440001" SCALE_ROWS=711.488795 ) USE_NL ("C") INDEX ("C" "IDX$$_ADB70002") OPT_ESTIMATE
(NLJ_INDEX_SCAN "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B"
"BASVALEE_2" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ACF30001"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCALE_ROWS=203644971.200000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_1" SCALE_ROWS=379255.137600 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_B0950002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB40003"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_FILTER "B" "BASVALEEP1" SCALE_ROWS=333.181328 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 )
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "IDX$$_ACF30001" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B"
"IDX$$_BC440001" SCALE_ROWS=333.181328 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_2"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_3" SCALE_ROWS=263636499.900000
) INDEX ("B" "BASVALEE_2") */ 0 FROM "EPMM"."BASVALEE" "C","EPMM"."BASVALEE" "B" WHERE
"B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."FIELD_CODE"=U'TP' AND
"C"."TECH_KEY1"="B"."TECH_KEY1" AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND
"C"."FIELD_CODE"=U'01') OR EXISTS (SELECT /*+ LEADING ("B" "C") OPT_ESTIMATE (INDEX_FILTER "C"
"BASVALEEP1" SCALE_ROWS=711.488795 ) OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 ) OPT_ESTIMATE
(INDEX_SCAN "C" "IDX$$_BC440001" SCALE_ROWS=711.488795 ) USE_NL ("C") INDEX ("C" "IDX$$_ADB70002")
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_2" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C")
"BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ACF30001"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCALE_ROWS=203644971.200000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_1" SCALE_ROWS=379255.137600 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_B0950002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB40003"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_FILTER "B" "BASVALEEP1" SCALE_ROWS=333.181328 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 )
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "IDX$$_ACF30001" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B"
"IDX$$_BC440001" SCALE_ROWS=333.181328 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_2"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_3" SCALE_ROWS=263636499.900000
) INDEX ("B" "BASVALEE_2") */ 0 FROM "EPMM"."BASVALEE" "C","EPMM"."BASVALEE" "B" WHERE
"B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."FIELD_CODE"=U'TP' AND
"C"."TECH_KEY1"="B"."TECH_KEY1" AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B2) AND
"C"."FIELD_CODE"=U'01'))
39 - access("SLH"."ERR_CODE"="WEI"."ERR_CODE"(+))
43 - filter(TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')>=TO_DATE(' 2017-09-05 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')<=TO_DATE('
2018-09-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
44 - filter("SLD"."PALLET_NBR"='01PLF00700477509' AND SUBSTR("ORDER_NBR",1,3)='RTV')
45 - access("SLH"."HDR_GROUP_NBR"="SLD"."HDR_GROUP_NBR")
49 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE' AND
"S"."VALUE"=SYS_OP_C2C("SLH"."FACILITY_CODE"))
50 - filter("O"."ORG_LVL_ID"=1)
51 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
52 - access("P"."ENTITY_NAME"(+)=U'ORGMSTEE' AND "P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD" AND
"P"."FIELD_CODE"(+)=U'TP')
56 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE' AND
"S"."VALUE"=SYS_OP_C2C("SLD"."SHIPTO_FACILITY_CODE"))
57 - filter("O"."ORG_LVL_ID"=1)
58 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
59 - access("P"."ENTITY_NAME"(+)=U'ORGMSTEE' AND "P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD" AND
"P"."FIELD_CODE"(+)=U'TP')
61 - access("PRD"."PRD_LVL_NUMBER"=LTRIM("SLD"."ITEM_ALTERNATE_CODE",'0'))
64 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
65 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE'
AND "C"."TECH_KEY1"="B"."TECH_KEY1")
67 - access("B"."FIELD_CODE"=U'TP' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T')
68 - access("C"."FIELD_CODE"=U'01' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE'
AND "C"."TECH_KEY1"="B"."TECH_KEY1")
2- Using SQL Profile
--------------------
Plan hash value: 2464513863
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 13677 | 622K (1)| 00:00:22 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 13 | 6422 | 575K (1)| 00:00:20 |
| 4 | VIEW | | 13 | 5902 | 575K (1)| 00:00:20 |
| 5 | NESTED LOOPS | | 13 | 5083 | 575K (1)| 00:00:20 |
| 6 | NESTED LOOPS | | 13 | 5083 | 575K (1)| 00:00:20 |
|* 7 | HASH JOIN OUTER | | 13 | 4056 | 575K (1)| 00:00:20 |
|* 8 | HASH JOIN OUTER | | 13 | 3094 | 575K (1)| 00:00:20 |
| 9 | NESTED LOOPS | | 13 | 2132 | 575K (1)| 00:00:20 |
| 10 | NESTED LOOPS | | 18 | 2132 | 575K (1)| 00:00:20 |
|* 11 | TABLE ACCESS FULL | WMS_SHP_LOAD_DTL | 18 | 1872 | 575K (1)| 00:00:20 |
|* 12 | INDEX UNIQUE SCAN | PK_WMS_SHP_LOAD_HDR | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_HDR | 1 | 60 | 2 (0)| 00:00:01 |
| 14 | VIEW | VI_ORGMSTEE | 8464 | 611K| 22 (0)| 00:00:01 |
|* 15 | HASH JOIN RIGHT OUTER | | 8464 | 950K| 22 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX$$_ADB70002 | 483 | 17388 | 6 (0)| 00:00:01 |
|* 17 | HASH JOIN RIGHT OUTER | | 469 | 37051 | 16 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | BASVALEE_2 | 340 | 9180 | 6 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 10 (0)| 00:00:01 |
| 20 | VIEW | VI_ORGMSTEE | 8464 | 611K| 22 (0)| 00:00:01 |
|* 21 | HASH JOIN RIGHT OUTER | | 8464 | 950K| 22 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX$$_ADB70002 | 483 | 17388 | 6 (0)| 00:00:01 |
|* 23 | HASH JOIN RIGHT OUTER | | 469 | 37051 | 16 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | BASVALEE_2 | 340 | 9180 | 6 (0)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 10 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 3 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | WMS_RCV_ASN_HDR | 1 | 40 | 3 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | WMS_RCV_ASN_HDR_IDX01 | 1 | | 2 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
|* 36 | FILTER | | | | | |
| 37 | NESTED LOOPS | | 1 | 384 | 47418 (1)| 00:00:02 |
| 38 | NESTED LOOPS | | 1 | 384 | 47418 (1)| 00:00:02 |
|* 39 | HASH JOIN OUTER | | 1 | 305 | 47415 (1)| 00:00:02 |
| 40 | NESTED LOOPS OUTER | | 1 | 301 | 47395 (1)| 00:00:02 |
| 41 | NESTED LOOPS | | 1 | 234 | 47385 (1)| 00:00:02 |
| 42 | NESTED LOOPS OUTER | | 1 | 171 | 47383 (1)| 00:00:02 |
|* 43 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_DTL | 1 | 104 | 47373 (1)| 00:00:02 |
|* 44 | INDEX RANGE SCAN | IDX$$_B7680001 | 611K| | 1830 (1)| 00:00:01 |
| 45 | VIEW PUSHED PREDICATE | VI_ORGMSTEE | 1 | 67 | 10 (0)| 00:00:01 |
|* 46 | HASH JOIN OUTER | | 18 | 2070 | 10 (0)| 00:00:01 |
| 47 | NESTED LOOPS | | 18 | 1584 | 4 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 18 | 1584 | 4 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | ORGMSTEEP1 | 1 | | 0 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID| ORGMSTEE | 18 | 936 | 1 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | BASVALEE_2 | 340 | 9180 | 6 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | WMS_SHP_LOAD_HDR | 1 | 63 | 2 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | PK_WMS_SHP_LOAD_HDR | 1 | | 1 (0)| 00:00:01 |
| 55 | VIEW PUSHED PREDICATE | VI_ORGMSTEE | 1 | 67 | 10 (0)| 00:00:01 |
|* 56 | HASH JOIN OUTER | | 18 | 2070 | 10 (0)| 00:00:01 |
| 57 | NESTED LOOPS | | 18 | 1584 | 4 (0)| 00:00:01 |
| 58 | NESTED LOOPS | | 18 | 1584 | 4 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | ORGMSTEEP1 | 1 | | 0 (0)| 00:00:01 |
|* 61 | TABLE ACCESS BY INDEX ROWID | ORGMSTEE | 18 | 936 | 1 (0)| 00:00:01 |
|* 62 | INDEX RANGE SCAN | BASVALEE_2 | 340 | 9180 | 6 (0)| 00:00:01 |
| 63 | TABLE ACCESS FULL | WMS_ERROR_INT | 41 | 164 | 19 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 2 (0)| 00:00:01 |
| 65 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 3 (0)| 00:00:01 |
| 66 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
| 69 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | BASVALEE_2 | 1 | 36 | 3 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | IDX$$_ADB70002 | 1 | 36 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "IDX$$_ADB70002"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B") "IDX$$_ACF30001"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "BASVALEE_2"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "IDX$$_ACF30001"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1" SCALE_ROWS=711.488795 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B") "BASVALEE_2" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE
(NLJ_INDEX_FILTER "C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN
"C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 )
OPT_ESTIMATE (INDEX_SCAN "C" "IDX$$_BC440001" SCALE_ROWS=711.488795 ) OPT_ESTIMATE (NLJ_INDEX_FILTER
"C" ("B") "IDX$$_ADB70002" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C")
"BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_2"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ACF30001"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCALE_ROWS=203644971.200000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_1" SCALE_ROWS=379255.137600 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_B0950002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB40003" SCALE_ROWS=263636499.900000
) OPT_ESTIMATE (INDEX_FILTER "B" "BASVALEEP1" SCALE_ROWS=333.181328 ) OPT_ESTIMATE (NLJ_INDEX_FILTER
"B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C")
"IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ACF30001"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_BC440001" SCALE_ROWS=333.181328 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(NLJ_INDEX_SCAN "B" ("C") "BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B"
"BASVALEE_3" SCALE_ROWS=263636499.900000 ) */ 0 FROM "EPMM"."BASVALEE" "C","EPMM"."BASVALEE" "B" WHERE
"C"."TECH_KEY1"="B"."TECH_KEY1" AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T' AND
"B"."FIELD_CODE"=U'TP' AND "C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."FIELD_CODE"=U'01') OR EXISTS (SELECT /*+ OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "IDX$$_ADB70002"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B") "IDX$$_ACF30001"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "BASVALEE_2"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "C" ("B") "IDX$$_ACF30001"
SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1" SCALE_ROWS=711.488795 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "C" ("B") "BASVALEE_2" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE
(NLJ_INDEX_FILTER "C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN
"C" ("B") "BASVALEE_3" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 )
OPT_ESTIMATE (INDEX_SCAN "C" "IDX$$_BC440001" SCALE_ROWS=711.488795 ) OPT_ESTIMATE (NLJ_INDEX_FILTER
"C" ("B") "IDX$$_ADB70002" SCALE_ROWS=1000341615.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C")
"BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_2"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ACF30001"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCA)
7 - access("ORGT"."CENTRO_SAP"(+)=SYS_OP_C2C("SLD"."SHIPTO_FACILITY_CODE"))
8 - access("ORGC"."CENTRO_SAP"(+)=SYS_OP_C2C("SLH"."FACILITY_CODE"))
11 - filter("SLD"."PALLET_NBR"='01PLF00700477509')
12 - access("SLH"."HDR_GROUP_NBR"="SLD"."HDR_GROUP_NBR")
13 - filter(TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')>=TO_DATE(' 2017-09-05 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')<=TO_DATE(' 2018-09-06
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ((SUBSTR("ORDER_NBR",1,3)='ORD' OR
SUBSTR("ORDER_NBR",1,3)='OST' OR SUBSTR("ORDER_NBR",1,3)='SDD' OR SUBSTR("ORDER_NBR",1,3)='SDE' OR
SUBSTR("ORDER_NBR",1,3)='SDR' OR SUBSTR("ORDER_NBR",1,3)='TFC' OR SUBSTR("ORDER_NBR",1,3)='TRF') OR
"SLH"."FACILITY_CODE"=SUBSTR("SLD"."ORDER_NBR",1,4) AND
SUBSTR("ORDER_NBR",1,3)=SUBSTR("SLH"."FACILITY_CODE",1,3)))
15 - access("S"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
16 - access("S"."FIELD_CODE"(+)=U'01' AND "S"."ENTITY_NAME"(+)=U'ORGMSTEE')
filter("S"."ENTITY_NAME"(+)=U'ORGMSTEE')
17 - access("P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
18 - access("P"."FIELD_CODE"(+)=U'TP' AND "P"."ENTITY_NAME"(+)=U'ORGMSTEE')
19 - filter("O"."ORG_LVL_ID"=1)
21 - access("S"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
22 - access("S"."FIELD_CODE"(+)=U'01' AND "S"."ENTITY_NAME"(+)=U'ORGMSTEE')
filter("S"."ENTITY_NAME"(+)=U'ORGMSTEE')
23 - access("P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
24 - access("P"."FIELD_CODE"(+)=U'TP' AND "P"."ENTITY_NAME"(+)=U'ORGMSTEE')
25 - filter("O"."ORG_LVL_ID"=1)
26 - access("PRD"."PRD_LVL_NUMBER"=LTRIM("SLD"."ITEM_ALTERNATE_CODE",'0'))
28 - filter("HDR"."ERR_CODE"(+)=0 AND "SLD"."SHIPTO_FACILITY_CODE"="HDR"."FACILITY_CODE"(+))
29 - access("SLH"."LOAD_MANIFEST_NBR"="HDR"."SHIPMENT_NBR"(+))
31 - access("C"."FIELD_CODE"=U'01' AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B1))
32 - access("B"."FIELD_CODE"=U'TP' AND "B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
34 - access("C"."FIELD_CODE"=U'01' AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B1))
35 - access("B"."FIELD_CODE"=U'TP' AND "B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
36 - filter( EXISTS (SELECT /*+ OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1" SCALE_ROWS=711.488795 )
OPT_ESTIMATE (TABLE "C" SCALE_ROWS=726.779035 ) OPT_ESTIMATE (INDEX_SCAN "C" "IDX$$_BC440001"
SCALE_ROWS=711.488795 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_2"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_2" SCALE_ROWS=263636499.900000
) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN
"B" "IDX$$_ADB70002" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C")
"IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCALE_ROWS=203644971.200000 )
OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_1" SCALE_ROWS=379255.137600 ) OPT_ESTIMATE (INDEX_SCAN "B"
"IDX$$_B0950002" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB40003"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_FILTER "B" "BASVALEEP1" SCALE_ROWS=333.181328 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(NLJ_INDEX_SCAN "B" ("C") "IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN
"B" "IDX$$_ACF30001" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_BC440001"
SCALE_ROWS=333.181328 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_2"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_3"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "BASVALEE_3" SCALE_ROWS=263636499.900000
) */ 0 FROM "EPMM"."BASVALEE" "C","EPMM"."BASVALEE" "B" WHERE "C"."TECH_KEY1"="B"."TECH_KEY1" AND
"B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T' AND "B"."FIELD_CODE"=U'TP' AND
"C"."VALUE"=SYS_OP_C2C(:B1) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."FIELD_CODE"=U'01') OR EXISTS
(SELECT /*+ OPT_ESTIMATE (INDEX_FILTER "C" "BASVALEEP1" SCALE_ROWS=711.488795 ) OPT_ESTIMATE (TABLE "C"
SCALE_ROWS=726.779035 ) OPT_ESTIMATE (INDEX_SCAN "C" "IDX$$_BC440001" SCALE_ROWS=711.488795 )
OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_2" SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C")
"BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C") "IDX$$_ADB70002"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB70002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "IDX$$_ACF30001"
SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (TABLE "B" SCALE_ROWS=203644971.200000 ) OPT_ESTIMATE
(INDEX_SCAN "B" "BASVALEE_1" SCALE_ROWS=379255.137600 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_B0950002"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ADB40003" SCALE_ROWS=263636499.900000
) OPT_ESTIMATE (INDEX_FILTER "B" "BASVALEEP1" SCALE_ROWS=333.181328 ) OPT_ESTIMATE (NLJ_INDEX_FILTER
"B" ("C") "IDX$$_ADB70002" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (NLJ_INDEX_SCAN "B" ("C")
"IDX$$_ACF30001" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_ACF30001"
SCALE_ROWS=263636499.900000 ) OPT_ESTIMATE (INDEX_SCAN "B" "IDX$$_BC440001" SCALE_ROWS=333.181328 )
OPT_ESTIMATE (NLJ_INDEX_FILTER "B" ("C") "BASVALEE_2" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE
(NLJ_INDEX_SCAN "B" ("C") "BASVALEE_3" SCALE_ROWS=322110000000.000000 ) OPT_ESTIMATE (INDEX_SCAN "B"
"BASVALEE_3" SCALE_ROWS=263636499.900000 ) */ 0 FROM "EPMM"."BASVALEE" "C","EPMM"."BASVALEE" "B" WHERE
"C"."TECH_KEY1"="B"."TECH_KEY1" AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND "B"."VALUE"=U'T' AND
"B"."FIELD_CODE"=U'TP' AND "C"."VALUE"=SYS_OP_C2C(:B2) AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."FIELD_CODE"=U'01'))
39 - access("SLH"."ERR_CODE"="WEI"."ERR_CODE"(+))
43 - filter("SLD"."PALLET_NBR"='01PLF00700477509')
44 - access(SUBSTR("ORDER_NBR",1,3)='RTV')
46 - access("P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
49 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE' AND
"S"."VALUE"=SYS_OP_C2C("SLD"."SHIPTO_FACILITY_CODE"))
50 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
51 - filter("O"."ORG_LVL_ID"=1)
52 - access("P"."FIELD_CODE"(+)=U'TP' AND "P"."ENTITY_NAME"(+)=U'ORGMSTEE')
53 - filter(TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')>=TO_DATE(' 2017-09-05 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(SUBSTR("SLH"."SHIP_DATE",1,8),'YYYY-MM-DD')<=TO_DATE(' 2018-09-06
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
54 - access("SLH"."HDR_GROUP_NBR"="SLD"."HDR_GROUP_NBR")
56 - access("P"."TECH_KEY1"(+)="O"."ORG_LVL_CHILD")
59 - access("S"."FIELD_CODE"=U'01' AND "S"."ENTITY_NAME"=U'ORGMSTEE' AND
"S"."VALUE"=SYS_OP_C2C("SLH"."FACILITY_CODE"))
60 - access("S"."TECH_KEY1"="O"."ORG_LVL_CHILD")
61 - filter("O"."ORG_LVL_ID"=1)
62 - access("P"."FIELD_CODE"(+)=U'TP' AND "P"."ENTITY_NAME"(+)=U'ORGMSTEE')
64 - access("PRD"."PRD_LVL_NUMBER"=LTRIM("SLD"."ITEM_ALTERNATE_CODE",'0'))
67 - access("C"."FIELD_CODE"=U'01' AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B1))
68 - access("B"."FIELD_CODE"=U'TP' AND "B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
70 - access("C"."FIELD_CODE"=U'01' AND "C"."ENTITY_NAME"=U'ORGMSTEE' AND "C"."VALUE"=SYS_OP_C2C(:B1))
71 - access("B"."FIELD_CODE"=U'TP' AND "B"."VALUE"=U'T' AND "B"."ENTITY_NAME"=U'ORGMSTEE' AND
"C"."TECH_KEY1"="B"."TECH_KEY1")
3- Using New Indices
--------------------
Plan hash value: 3022257570
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 13677 | 123 (1)| 00:00:01 | | | |
| 1 | UNION-ALL | | | | | | | | |
|* 2 | FILTER | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10008 | 13 | 6422 | 79 (0)| 00:00:01 | Q1,08 | P->S | QC (RAND) |
| 5 | BUFFER SORT | | 14 | 13677 | 123 (1)| 00:00:01 | Q1,08 | PCWP | |
| 6 | NESTED LOOPS OUTER | | 13 | 6422 | 79 (0)| 00:00:01 | Q1,08 | PCWP | |
| 7 | VIEW | | 13 | 5902 | 65 (0)| 00:00:01 | Q1,08 | PCWP | |
|* 8 | HASH JOIN OUTER | | 13 | 5083 | 65 (0)| 00:00:01 | Q1,08 | PCWP | |
| 9 | PX RECEIVE | | 13 | 4121 | 50 (2)| 00:00:01 | Q1,08 | PCWP | |
| 10 | PX SEND HASH | :TQ10006 | 13 | 4121 | 50 (2)| 00:00:01 | Q1,06 | P->P | HASH |
|* 11 | HASH JOIN OUTER BUFFERED | | 13 | 4121 | 50 (2)| 00:00:01 | Q1,06 | PCWP | |
| 12 | BUFFER SORT | | | | | | Q1,06 | PCWC | |
| 13 | PX RECEIVE | | 13 | 3159 | 34 (0)| 00:00:01 | Q1,06 | PCWP | |
| 14 | PX SEND HASH | :TQ10002 | 13 | 3159 | 34 (0)| 00:00:01 | | S->P | HASH |
| 15 | NESTED LOOPS | | 13 | 3159 | 34 (0)| 00:00:01 | | | |
| 16 | NESTED LOOPS | | 13 | 3159 | 34 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 13 | 2132 | 19 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID| WMS_SHP_LOAD_DTL | 18 | 1872 | 6 (0)| 00:00:01 | | | |
|* 19 | INDEX RANGE SCAN | IDX$$_132240001 | 18 | | 3 (0)| 00:00:01 | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID| WMS_SHP_LOAD_HDR | 1 | 60 | 1 (0)| 00:00:01 | | | |
|* 21 | INDEX UNIQUE SCAN | PK_WMS_SHP_LOAD_HDR | 1 | | 0 (0)| 00:00:01 | | | |
|* 22 | INDEX RANGE SCAN | IFHPRDMST_IX08 | 1 | | 1 (0)| 00:00:01 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | IFHPRDMST | 1 | 79 | 1 (0)| 00:00:01 | | | |
| 24 | PX RECEIVE | | 8464 | 611K| 16 (0)| 00:00:01 | Q1,06 | PCWP | |
| 25 | PX SEND HASH | :TQ10005 | 8464 | 611K| 16 (0)| 00:00:01 | Q1,05 | P->P | HASH |
| 26 | VIEW | VI_ORGMSTEE | 8464 | 611K| 16 (0)| 00:00:01 | Q1,05 | PCWP | |
|* 27 | HASH JOIN OUTER | | 8464 | 950K| 16 (0)| 00:00:01 | Q1,05 | PCWP | |
|* 28 | HASH JOIN OUTER | | 469 | 37051 | 10 (0)| 00:00:01 | Q1,05 | PCWP | |
| 29 | PX BLOCK ITERATOR | | 469 | 24388 | 4 (0)| 00:00:01 | Q1,05 | PCWC | |
|* 30 | TABLE ACCESS FULL | ORGMSTEE | 469 | 24388 | 4 (0)| 00:00:01 | Q1,05 | PCWP | |
| 31 | BUFFER SORT | | | | | | Q1,05 | PCWC |
SQL> select owner, table_name from dba_tables where table_name='IFH_CARGA_ATRIB_TMP';
select count(*) from EDSR.IFH_CARGA_ATRIB_TMP;
============== AZAR hints ===========
SQL> select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '5' minute
and session_id=&sid;
col sql_text for a80
select sql_text from v$sql where sql_id='&sqlid'
/
select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;
select sid, serial#,sql_id,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx
select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;
select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '5' minute
and session_id=&sid;
select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'EDSR';
select
s.instance_number,
s.end_interval_time,
rn.current_utilization
from
dba_hist_snapshot s
join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number = rn.instance_number)
where
rn.resource_name = 'sessions'
and s.end_interval_time > sysdate - 1
order by
s.end_interval_time
;
Comments
Post a Comment