The issue:
Query:
select
* from t
where not exists (select
1 from c
where ban = t.ban
and subscriber_no = t.subscriber_no
and actv_bill_seq_no = t.bill_seq_no;
The execution plan will always be a FILTER.
By adding the UNNEST hint, we can actually force the optimizer to join the 2 tables, so we can hint for hash or nl joins.
select
* from t
where not exists (select /*+ unnest */
1 from c
where ban = t.ban
and subscriber_no = t.subscriber_no
and actv_bill_seq_no = t.bill_seq_no
Log Miner setup steps:
https://www.thegeekdiary.com/simple-steps-to-use-logminer-for-finding-high-redo-log-generation/
Just in case the link will move, the main steps are below:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/users/Florin/MYDB_1_714407_821540104.arc',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/users/Florin/MYDB_1_714408_821540104.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);>
PL/SQL procedure successfully completed.
SQL> l
1 select USERNAME,OS_USERNAME,MACHINE_NAME,SESSION_INFO from V$LOGMNR_CONTENTS
2 where TABLE_NAME='MY_TABLE'
3 and OPERATION='DELETE'
4* and rownum < 11
The article below is very good and useful, I was following the instructions and I was able to force the Production DB execution plan in ST DB, even that the problematic query never used the good plan in UAT.
https://docs.rackspace.com/blog/transfer-sql-plans-by-using-sql-plan-baseline/
Florin
So you have a query that seems to run forever..
v$session_wait shows "db file sequential read" , but since we have a multi join, the questions is on which object we are spending time.
Answer: P1, P2 and P3 reveal the object in case:
SQL>select wait_class,p1,p2,p3 from v$session where sid=425:
WAIT_CLASS P1 P2 P3
----------------------- ---------- ---------- ----------
db file sequential read 5 2046999 1
The goal is to replace the nested loop, that is taking long, by a hash join.
Original query:
SELECT /*+ PARALLEL(M,8) */
my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM
LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL
019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_
TXT,'R'
FROM my_tab M WHERE my_tab_BAN IN
(SELECT /*+ PARALLEL(HS,4) */
BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND
CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S') AND
my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;
Modified query:
SELECT /*+ PARALLEL(M,8) FULL(M) use_hash(M) */
my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM
LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL
019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_
TXT,'R'
FROM my_tab M WHERE my_tab_BAN IN
(SELECT /*+ PARALLEL(HS,4) */
BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND
CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S') AND
my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;
select round(avg(a.tps)) from (
WITH hist_snaps
AS (SELECT instance_number,
snap_id,
round(begin_interval_time,'MI') datetime,
( begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot where instance_number=&&1), hist_stats
AS (SELECT dbid,
instance_number,
snap_id,
stat_name,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
delta_value
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks') and instance_number=&&1)
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) TPS
FROM hist_snaps sn, hist_stats st
WHERE st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
and st.instance_number=&&1
GROUP BY datetime
ORDER BY 1 desc
) a
where rownum < 61
/
