Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
performance issue with the Oracle SQL query
Dears
Good evening
I'm new to start using Oracle SQL , I have a sql query which is taking more time to execute in the production .
Each table in the query contains 1.2 million records and DBA suggested to use the 'Oracle - hints' . i dont have proper knowledge on this topic and facing problems to implement this hints to imrpovise the performance . In the product the Informatica jobs are failed and stuck with this query performance issue .
i request from this forum for an urgent help for me to resolve this issue by using 'hints' . kindly help me .
SELECT
CASE.ID,
CASE.DTYPE,
CASE.VERSION,
CASE.EXTERNAL_REF,
CASE.CREATION_TS,
RQ.TYPE
FROM
PAS_CASE CASE,
AS_REQUEST RQ,
PAS_CONTEXT CN
where rq.case_id = case.id
AND rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and CN.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
AND CAST(CN.CREATION_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND
CAST(CN.CREATION_TS AS DATE) < TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
------ 2nd query
SELECT
RA.ID,
RA.VERSION,
RA.REQUEST_ID,
RA.NAME,
RA.VALUE,
RA.LOB_ID,
RA.DTYPE,
RA.CREATION_TS,
TASK.MAIN_REQ_TYPE
from PAS_REQUESTATTRIBUTE RA
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on RA.REQUEST_ID = task.ID
and RA.ID between $$LOW_ID1 AND $$HIGH_ID1
UNION
SELECT
RA.ID,
RA.VERSION,
RA.REQUEST_ID,
RA.NAME,
RA.VALUE,
RA.LOB_ID,
RA.DTYPE,
RA.CREATION_TS,
MAIN_REQ.TYPE
from PAS_REQUESTATTRIBUTE RA
join
(
select rq.id,rq.type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on RA.REQUEST_ID = main_req.ID
--- 3rd query
SELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT ,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is not null
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
UNION
SELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is nullSELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT ,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is not null
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
UNION
SELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is null
--- 4th query
SELECT
RI.ID,
RI.DTYPE,
RI.VERSION,
RI.RESULTBLOCK_ID,
RI.NAME,
RI.VALUE,
RI.UNIT,
RI.COLOR,
RI.LOB_ID,
RI.CREATION_TS,
RI.SEQUENCE,
RI.DETAILLEVEL,
RES_BLK.MAIN_REQ_TYPE
FROM
PAS_RESULTITEM RI
join
(
select
rb.ID, rb.TYPE as rb_type , task.TYPE as task_type, task. main_req_type from pas_resultblock rb
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between 20141800000000000 AND 20141999999999999
) task
on rb.task_id = task.ID
and rb.ID between 20141800000000000 AND 20141999999999999
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
) res_blk
on ri.resultblock_id = res_blk.ID
where RI.NAME IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and RI.ID between 20141800000000000 AND 20141999999999999
and RI.NAME is not null
UNION
select
RI.ID,
RI.DTYPE,
RI.VERSION,
RI.RESULTBLOCK_ID,
RI.NAME,
RI.VALUE,
RI.UNIT,
RI.COLOR,
RI.LOB_ID,
RI.CREATION_TS,
RI.SEQUENCE,
RI.DETAILLEVEL ,
RES_BLK.MAIN_REQ_TYPE
from pas_resultitem ri
join
(
select
rb.ID, rb.TYPE as rb_type , task.TYPE as task_type, task. main_req_type from pas_resultblock rb
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between 20141800000000000 AND 20141999999999999
) task
on rb.task_id = task.ID
and rb.ID between 20141800000000000 AND 20141999999999999
) res_blk
on ri.resultblock_id = res_blk.ID
where RI.ID between 20141800000000000 AND 20141999999999999
and RI.NAME is null
-- 5HT QUERY
SELECT
TSK.ID,
TSK.VERSION,
TSK.DTYPE,
TSK.CASE_ID,
TSK.TYPE,
TSK.CORRELATION_ID,
TSK.INITIATOR,
TSK.EXECUTOR,
TSK.CATEGORY,
TSK.PARENT_CONTEXT_ID,
TSK.CREATION_TS,
MAIN_REQ.MAIN_REQ_TYPE
FROM
PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
UNION
select
MREQ.ID,
MREQ.VERSION,
MREQ.DTYPE,
MREQ.CASE_ID,
MREQ.TYPE,
MREQ.CORRELATION_ID,
MREQ.INITIATOR,
MREQ.EXECUTOR,
MREQ.CATEGORY,
MREQ.PARENT_CONTEXT_ID,
MREQ.CREATION_TS,
MREQ.TYPE
from PAS_REQUEST mreq
, PAS_CONTEXT cn
where mreq.id = cn.request_id
and mreq.DTYPE = 'MAINREQUEST'
and mreq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and mreq.ID between 20141800000000000 AND 20141999999999999
Best Answer
-
Hints might not be necessary (necessity proportional to cardinalities)
select pc.id,pc.dtype,pc.version,pc.external_ref,pc.creation_ts,rq.type
from pas_case pc,
as_request rq,
pas_context cn
where rq.case_id = pc.id
and rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.creation_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.creation_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
------ 2nd query
with
main_request as
(select /*+ materialize */
cn.id as context_id,rq.type as main_req_type
from pas_request rq
join
pas_context cn
on rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.chc.polling',
'bgc.tbf.repair.vap','bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution',
'bgc.cbm','bgc.dar.e2etest.preparation'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and rq.id between 20141800000000000 and 20141999999999999
)
select ra.id,ra.version,ra.request_id,ra.name,ra.value,ra.lob_id,ra.dtype,ra.creation_ts,task.main_req_type
from pas_requestattribute ra
join
(select tsk.id,tsk.type,main_req.main_req_type
from pas_request tsk
join
main_request main_req
on tsk.parent_context_id = main_req.context_id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between $$low_id1 and $$high_id1
) task
on ra.request_id = task.id
and ra.id between $$low_id1 and $$high_id1
union
select ra.id,ra.version,ra.request_id,ra.name,ra.value,ra.lob_id,ra.dtype,ra.creation_ts,main_req.type
from pas_requestattribute ra
join
main_request main_req
on ra.request_id = main_req.context_id
------ 3rd query
select rb.id,rb.dtype,rb.version,rb.type,rb.creation_ts,rb.task_id,rb.color,rb.global_result,task.main_req_type
from pas_resultblock rb
join
(select tsk.id,tsk.type,main_req.main_req_type
from pas_request tsk
join
(select cn.id as context_id,rq.type as main_req_type
from pas_request rq
join
pas_context cn
on rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution',
'bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and rq.id between 20141800000000000 and 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between $$low_id1 and $$high_id1
) task
on rb.task_id = task.id
and rb.id between $$low_id1 and $$high_id1
where type is null
or (type is not null
and type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
)
------ 4th query
select ri.id,ri.dtype,ri.version,ri.resultblock_id,ri.name,ri.value,ri.unit,ri.color,ri.lob_id,
ri.creation_ts,ri.sequence,ri.detaillevel,res_blk.main_req_type
from pas_resultitem ri
join
(select rb.id,rb.type as rb_type,task.type as task_type,task.main_req_type
from pas_resultblock rb
join
(select tsk.id,tsk.type,main_req. main_req_type
from pas_request tsk
join
(select cn.id as context_id,rq.type as main_req_type
from pas_request rq
join
pas_context cn
on rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution',
'bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and rq.id between 20141800000000000 and 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between 20141800000000000 and 20141999999999999
) task
on rb.task_id = task.id
and rb.id between 20141800000000000 and 20141999999999999
and rb.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
) res_blk
on ri.resultblock_id = res_blk.id
and ri.id between 20141800000000000 and 20141999999999999
where ri.name is null
or (ri.name is not null
and ri.name in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
)
------ 5th query
with
main_request as
(select /*+ materialize */
mreq.id,mreq.version,mreq.dtype,mreq.case_id,mreq.type,mreq.correlation_id,mreq.initiator,mreq.executor,
mreq.category,mreq.parent_context_id,mreq.creation_ts,mreq.type
from pas_request mreq
join
pas_context cn
on mreq.id = cn.request_id
and mreq.dtype = 'MAINREQUEST'
and mreq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and mreq.id between 20141800000000000 and 20141999999999999
)
select tsk.id,tsk.version,tsk.dtype,tsk.case_id,tsk.type,tsk.correlation_id,tsk.initiator,tsk.executor,
tsk.category,tsk.parent_context_id,tsk.creation_ts,main_req.main_req_type
from pas_request tsk
join
main_request main_req
on tsk.parent_context_id = main_req.id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between $$low_id1 and $$high_id1
union
select id,version,dtype,case_id,type,correlation_id,initiator,executor,
category,parent_context_id,creation_ts,type
from main_request
Regards
Etbin
Answers
-
Oracle hint should be the last option to use.What is the data type of the column where you are using CAST eg, CN.CREATION_TS? Is there any index on this column? Use of function over this column will ignore index if any on this column.
Information you provided is not sufficient.
Regards
Biju
-
> i request from this forum for an urgent help
Keep in mind this is a forum of volunteers. All posts have the same priority: none.
-
Hello Sunny,
Could you please attach the explain plan of these queries it available.
Regards,
Ritesh
-
Hi Biju
Thanks for your reply .
CREATION_TS : is TIMESTAMP(6) data type
END_TS: is TIMESTAMP(6) data type
There is an index defined o nthe creation_ts column
-
If i was the DB or the QS i would prefer fixing the more "basic" issues with your queries rather than suggesting the usage of hints.
1. Handle dates as dates and give the optimizer the possibility to do the same: CAST(CN.CREATION_TS AS DATE)
2. Dont mix ansi-join syntax with oracle join syntax
-
Each table in the query contains 1.2 million records and DBA suggested to use the 'Oracle - hints'
1. A DBA should NOT be recommending ANY hints unless that DBA knows that the hint is required to fix whatever (currently unknown) problem you are having.
2. Hints are generally only used to help determine what the problem is. There are exceptions but hints are NOT normally used to actually 'fix' the problem.
-
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')It is weird to think about the business meaning of excluding 1 sec for the time range. Is not it simple to do something like trunc(end_ts) = :inputdate and create a function index to access the one day data? The function index is probably good enough for the first query and also helpful to the others (add other columns in if needed).
-
Thank you for your reply
Where as the field is having date time in the mentioned format .
the field CREATION_TS = 06-MAY-14 12.30.07.205000 PM
will it be fine still if we use the TO_TIMESTAMP . would you please suggest
-
What is the data type of the column CREATION_TS?
If it is timestamp than the "format" you see is just a "display" format.
If it is varchar2 your on the wrong road.
-
Hints might not be necessary (necessity proportional to cardinalities)
select pc.id,pc.dtype,pc.version,pc.external_ref,pc.creation_ts,rq.type
from pas_case pc,
as_request rq,
pas_context cn
where rq.case_id = pc.id
and rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.creation_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.creation_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
------ 2nd query
with
main_request as
(select /*+ materialize */
cn.id as context_id,rq.type as main_req_type
from pas_request rq
join
pas_context cn
on rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.chc.polling',
'bgc.tbf.repair.vap','bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution',
'bgc.cbm','bgc.dar.e2etest.preparation'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and rq.id between 20141800000000000 and 20141999999999999
)
select ra.id,ra.version,ra.request_id,ra.name,ra.value,ra.lob_id,ra.dtype,ra.creation_ts,task.main_req_type
from pas_requestattribute ra
join
(select tsk.id,tsk.type,main_req.main_req_type
from pas_request tsk
join
main_request main_req
on tsk.parent_context_id = main_req.context_id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between $$low_id1 and $$high_id1
) task
on ra.request_id = task.id
and ra.id between $$low_id1 and $$high_id1
union
select ra.id,ra.version,ra.request_id,ra.name,ra.value,ra.lob_id,ra.dtype,ra.creation_ts,main_req.type
from pas_requestattribute ra
join
main_request main_req
on ra.request_id = main_req.context_id
------ 3rd query
select rb.id,rb.dtype,rb.version,rb.type,rb.creation_ts,rb.task_id,rb.color,rb.global_result,task.main_req_type
from pas_resultblock rb
join
(select tsk.id,tsk.type,main_req.main_req_type
from pas_request tsk
join
(select cn.id as context_id,rq.type as main_req_type
from pas_request rq
join
pas_context cn
on rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution',
'bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and rq.id between 20141800000000000 and 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between $$low_id1 and $$high_id1
) task
on rb.task_id = task.id
and rb.id between $$low_id1 and $$high_id1
where type is null
or (type is not null
and type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
)
------ 4th query
select ri.id,ri.dtype,ri.version,ri.resultblock_id,ri.name,ri.value,ri.unit,ri.color,ri.lob_id,
ri.creation_ts,ri.sequence,ri.detaillevel,res_blk.main_req_type
from pas_resultitem ri
join
(select rb.id,rb.type as rb_type,task.type as task_type,task.main_req_type
from pas_resultblock rb
join
(select tsk.id,tsk.type,main_req. main_req_type
from pas_request tsk
join
(select cn.id as context_id,rq.type as main_req_type
from pas_request rq
join
pas_context cn
on rq.id = cn.request_id
and rq.dtype = 'MAINREQUEST'
and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution',
'bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and rq.id between 20141800000000000 and 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between 20141800000000000 and 20141999999999999
) task
on rb.task_id = task.id
and rb.id between 20141800000000000 and 20141999999999999
and rb.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
) res_blk
on ri.resultblock_id = res_blk.id
and ri.id between 20141800000000000 and 20141999999999999
where ri.name is null
or (ri.name is not null
and ri.name in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
)
------ 5th query
with
main_request as
(select /*+ materialize */
mreq.id,mreq.version,mreq.dtype,mreq.case_id,mreq.type,mreq.correlation_id,mreq.initiator,mreq.executor,
mreq.category,mreq.parent_context_id,mreq.creation_ts,mreq.type
from pas_request mreq
join
pas_context cn
on mreq.id = cn.request_id
and mreq.dtype = 'MAINREQUEST'
and mreq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap',
'bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution','bgc.cbm',
'bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')
and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')
and cn.id between 20141800000000000 and 20141999999999999
and mreq.id between 20141800000000000 and 20141999999999999
)
select tsk.id,tsk.version,tsk.dtype,tsk.case_id,tsk.type,tsk.correlation_id,tsk.initiator,tsk.executor,
tsk.category,tsk.parent_context_id,tsk.creation_ts,main_req.main_req_type
from pas_request tsk
join
main_request main_req
on tsk.parent_context_id = main_req.id
and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.id between $$low_id1 and $$high_id1
union
select id,version,dtype,case_id,type,correlation_id,initiator,executor,
category,parent_context_id,creation_ts,type
from main_request
Regards
Etbin