I extracted those table names from different views and functions. Execution time takes 1day+ to finish. My goal is to reduce the time from 1day to 30minutes or less. My boss suggested me to fine tuned all the queries inside the views under views. Any tips or suggestions on how to reduce the time?
Query
SELECT to_char(con.originalstartdate, 'MM/DD/YY') TXNDATEIN,
to_char(con.originalstartdate, 'HH24:MI:SS') TIMEIN,
to_char(CON.LASTACTIVITYDATE, 'MM/DD/YY') TXNDATEOUT,
to_char(con.lastactivitydate, 'HH24:MI:SS') TIMEOUT,
pack.mes_packagename, prodfam.productfamilyname,
con.containername RELATEDCONTNAME, shd.qty QTYIN,
con.qty QTYOUT, qtyhist.qty, lossrea.lossreasonname,
resdef.resourcename WS, emp.fullname EMPLOYEENAME,
oprtn.operationname, hml.txndate,
( SELECT GET_DIFFUSION_CONTAINER_APP(con.containername)
FROM DUAL) AS DIFFUSION
FROM container con, package pack, productfamily prodfam,
lossreason lossrea, resourcedef resdef, employee emp,
operation oprtn, qtyhistory qtyhist, splithistorydetails shd,
historymainline hml
where con.lastactivitydate >= to_date('06/23/2014 6:00:00', 'MM/DD/YYYY HH:MI:SS')
and con.lastactivitydate < to_date('06/24/2014 6:00:00', 'MM/DD/YYYY HH:MI:SS')
and hml.txndate >= to_date('06/23/2014 6:00:00', 'MM/DD/YYYY HH:MI:SS')
and to_char(hml.txndate, 'MM/DD/YYYY') <= to_char(con.lastactivitydate, 'MM/DD/YYYY')
ORDER BY RELATEDCONTNAME;
ORIGINAL QUERY
SET ECHO OFF
SET COLSEP ,
SET PAGES 0
SET LINESIZE 1000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET UNDERLINE OFF
SET VER OFF
column NCURRENT NEW_VALUE VFILE NOPRINT
column NYEST NEW_VALUE VFILE2
column NNOW NEW_VALUE VFILE3
column NCURRENTYEAR NEW_VALUE VFILE4
column WEEKCODE NEW_VALUE VFILE5
select to_char(sysdate -2 , 'RRRR-MM-DD')||' 06:00:00' NYEST
--select '2013-05-18 12:00:00' NYEST
from dual;
select to_char(sysdate-1, 'RRRR-MM-DD')||' 06:00:00' NNOW
--select '2013-05-19 12:00:00' NNOW
from dual;
select to_char(sysdate, 'RRRR') NCURRENTYEAR
from dual;
select 'FName_'||to_char(sysdate-1, 'MONDDRRRR')||'.csv' NCURRENT
from dual;
spool [Filepath];
select
TXNDATEIN||','||TIMEIN||','||TXNDATEOUT||','||TIMEOUT||','|| MES_PACKAGENAME||','||MINICOMPANYNAME||','||PRODUCTFAMILYNAME||','||RELATEDCONTNAME||','||
QTYIN||','||QTYOUT||','||QTY||','||LOSSREASONNAME||','||WS||','||REPLACE(EMPLOYEENAME,',',' ')||','||OPERATIONNAME||','||
(SELECT GET_DIFFUSION_CONTAINER_APP(RELATEDCONTNAME) FROM DUAL) AS DIFFUSION
from
vw_power_overallyield_batchfe
where
txndate >= to_date('&&VFILE2', 'yyyy-mm-dd HH24:MI:SS')
and txndate < to_date('&&VFILE3', 'yyyy-mm-dd HH24:MI:SS')
and scraptxndate >= to_date('&&VFILE2', 'yyyy-mm-dd HH24:MI:SS')
and to_char(scraptxndate, 'MM/DD/YY') <= TXNDATEOUT
ORDER BY RELATEDCONTNAME;
spool off;
exit
EXPLAIN PLAN
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18E| 15E| | 18E (0)|
| 1 | FAST DUAL | | 1 | | | 2 (0)|
| 2 | SORT ORDER BY | | 18E| 15E| 15E| 18E (0)|
| 3 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|
| 4 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|
| 5 | MERGE JOIN CARTESIAN | | 18E| 15E| | 204P (1)|
| 6 | MERGE JOIN CARTESIAN | | 54T| 5457T| | 521G (1)|
| 7 | MERGE JOIN CARTESIAN | | 84G| 7259G| | 942M (1)|
| 8 | MERGE JOIN CARTESIAN | | 17M| 1258M| | 210K (1)|
| 9 | MERGE JOIN CARTESIAN | | 8713 | 536K| | 87 (3)|
| 10 | MERGE JOIN CARTESIAN | | 16 | 816 | | 16 (13)|
| 11 | MERGE JOIN | | 1 | 43 | | 13 (16)|
| 12 | SORT JOIN | | 3 | 24 | | 5 (20)|
|* 13 | INDEX RANGE SCAN | HISTORYMLBYTXNDATEANDRES | 3 | 24 | | 4 (0)|
|* 14 | SORT JOIN | | 4 | 140 | | 8 (13)|
| 15 | TABLE ACCESS BY INDEX ROWID| CONTAINER | 4 | 140 | | 7 (0)|
|* 16 | INDEX RANGE SCAN | CONTAINERBYLADATE | 5 | | | 3 (0)|
| 17 | BUFFER SORT | | 25 | 200 | | 9 (23)|
| 18 | TABLE ACCESS FULL | PACKAGE | 25 | 200 | | 3 (0)|
| 19 | BUFFER SORT | | 552 | 6624 | | 84 (3)|
| 20 | TABLE ACCESS FULL | RESOURCEDEF | 552 | 6624 | | 4 (0)|
| 21 | BUFFER SORT | | 2020 | 24240 | | 210K (1)|
| 22 | TABLE ACCESS FULL | PRODUCTFAMILY | 2020 | 24240 | | 24 (0)|
| 23 | BUFFER SORT | | 4814 | 81838 | | 942M (1)|
| 24 | TABLE ACCESS FULL | EMPLOYEE | 4814 | 81838 | | 54 (2)|
| 25 | BUFFER SORT | | 638 | 12122 | | 521G (1)|
| 26 | TABLE ACCESS FULL | OPERATION | 638 | 12122 | | 6 (0)|
| 27 | BUFFER SORT | | 411K| 2009K| | 204P (1)|
| 28 | TABLE ACCESS FULL | SPLITHISTORYDETAILS | 411K| 2009K| | 3785 (1)|
| 29 | BUFFER SORT | | 1171K| 5720K| | 18E (0)|
| 30 | TABLE ACCESS FULL | QTYHISTORY | 1171K| 5720K| | 4690 (1)|
| 31 | BUFFER SORT | | 1382 | 13820 | | 18E (0)|
| 32 | TABLE ACCESS FULL | LOSSREASON | 1382 | 13820 | | 4 (0)|
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
13 - access("HML"."TXNDATE">=TO_DATE(' 2014-06-23 06:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"HML"."TXNDATE" IS NOT NULL)
14 - access(TO_CHAR(INTERNAL_FUNCTION("HML"."TXNDATE"),'MM/DD/YYYY')<=TO_CHAR(INTERNAL_FUNCTION("CON".
"LASTACTIVITYDATE"),'MM/DD/YYYY'))
filter(TO_CHAR(INTERNAL_FUNCTION("HML"."TXNDATE"),'MM/DD/YYYY')<=TO_CHAR(INTERNAL_FUNCTION("CON".
"LASTACTIVITYDATE"),'MM/DD/YYYY'))
16 - access("CON"."LASTACTIVITYDATE">=TO_DATE(' 2014-06-23 06:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"CON"."LASTACTIVITYDATE"<TO_DATE(' 2014-06-24 06:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
enter code here
The answer has been given in the comments already: You are cross-joining your tables.
First you are taking all record from table Container in a certain date range. Let's say that's 200 records. Then you take all records from table package. Let's say this table contains 300 records. You combine all records, which gives you 300 x 200 = 60000 records. Then you take the next table and multiply again, ...
What one would expect is that the tables are related somehow and you would hence use these relations. Let's say that each package resides in a container. Therefore each package record would have a container ID. You don't want to join all packages to all containers, but each package to the one container it belongs to:
FROM container con
JOIN package pack ON pack.container_id = con.id
You could do this also as
FROM container con, package pack
WHERE pack.container_id = con.id
but you shouldn't. This error-prone implicit join syntax was replaced more than twenty years ago by the explicit joins mentioned above. If you had used
FROM container con
JOIN package pack
without the ON clause, then the DBMS would have noticed your mistake and told you to join the records properly rather then executig the nonsensical query.
EDIT: As to the original query you posted in your edit, make sure that all tables in vw_power_overallyield_batchfe view are properly joined and you have indexes on the linking columns. Then there is little you can do. It may help to have indexes on the date columns, as the DBMS may decide to use a range index scan then. You can also try to execute the query parallelized (with a PARRALLEL HINT).
The line to_char(scraptxndate, 'MM/DD/YY') <= TXNDATEOUT looks suspicious, btw. '12/31/14' would be greater than '01/01/15' for instance. That doesn't seem to make sense. Don't you rather want to compare dates, i.e. scraptxndate <= to_date(TXNDATEOUT,'MM/DD/YY')?