RE: Help on the query please

RE: Help on the query please

 

  

Thanks Benoit!

I think the problem is on
dbms_lob.substr(program_directive_version.purpose_text, 500, 1) as
purpose_text. If I don't select the clob then the query run fast.

Slow:

SELECT DISTINCT program_directive.pd_id,
dbms_lob.substr(program_directive_version.purpose_text, 500, 1) as
purpose_text FROM program_directive.....


Fast:

SELECT DISTINCT program_directive.pd_id FROM program_directive .......


Do you know if we can create index or whatever on the clob to speed up
the query?

-----Original Message-----
From: Benoit De Borggraef [mailto:Benoit.[Email address protected]
Sent: Friday, August 31, 2007 11:52 AM
To: Yang, Yanyan (JSC-OH230)[ARES]
Subject: RE: Help on the query please

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed
any attachments, and kept your email address secret **from this person,
and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal **Anything below this line is the original email text


Run explain plan :

SQL> set autotrace trace explain
SQL> set lines 20000
SQL> SELECT .... (your select statement)

>>> check for 'hash', 'full', 'cartesian', etc.
>>> if found (and not desired), consider using hints expected indexes,
nested loops, etc. Example :

SQL> SELECT /*+ ordered index(program_directive
program_directive_index1) use_nl(program_directive other_table)*/ ....

... and see if it improves the execution plan.

+ :-),

-- B.

Benoit De Borggraef
Amdocs - TELUS SSME

+1.604.432.2018 (desk)
+1.604.339.6514 (mobile)
+1.604.412.3695 (fax)

AMDOCS > CUSTOMER EXPERIENCE SYSTEMS INNOVATION


-----Original Message-----
From: Yang Yanyan (JSC-OH230)[ARES] [Email address protected]
Sent: Friday, August 31, 2007 9:34 AM
To: LazyDBA Discussion
Subject: Help on the query please


Hi DBAs,

We have a query below is running slow (about 15 min). If without
dbms_lob.substr on the LOB, it run about 10 seconds. All tables are very
small. Any suggestion to improve the speed? Thanks!

SELECT DISTINCT program_directive.pd_id,
dbms_lob.substr(program_directive_version.purpose_text, 500, 1) as
purpose_text FROM program_directive, program_directive_version,
version_xref, program_directive_type_code, app_user nasa_int, app_user
resp_focal, app_user initiator, app_user resp_board_sig, app_user
apprv_board_sig, app_user ip_sig_user, app_user cntr_sig_user,
organizations initiating_org, program_directive_status_code,
pd_ip_signature, pd_contractor_signature, pd_concurrence, pd_csb,
pd_csb_board, pd_evaluation_version, pd_evaluator, agency,
contractor_code, concurrence_status_code, code_value eval_status,
code_value eval_response, evaluator_code, version_xref eval_vxref WHERE
program_directive.pd_id = program_directive_version.pd_id AND
program_directive_version.pd_version_id = version_xref.version_id AND
trim(program_directive_version.product_type_cd) =
trim(version_xref.product_type_cd)
AND trim(program_directive.type_cd) =
trim(program_directive_type_code.code(+))
AND trim(program_directive_version.status_cd) =
trim(program_directive_status_code.code(+))
AND trim(program_directive_version.resp_focal_userid) =
trim(resp_focal.userid(+))
AND trim(program_directive_version.nasa_integrator_userid) =
trim(nasa_int.userid(+))
AND trim(program_directive_version.initiator_userid) =
trim(initiator.userid(+))
AND trim(program_directive_version.resp_board_sig_userid) =
trim(resp_board_sig.userid (+)) AND
trim(program_directive_version.apprv_board_sig_userid) =
trim(apprv_board_sig.userid (+)) AND trim(pd_ip_signature.sig_userid) =
trim(ip_sig_user.userid (+)) AND
trim(pd_contractor_signature.sig_userid) = trim(cntr_sig_user.userid
(+))
AND program_directive_version.initiating_org_id =
initiating_org.organization_id(+)
AND program_directive_version.pd_id = pd_ip_signature.pd_id (+) AND
program_directive_version.pd_version_id = pd_ip_signature.pd_version_id
(+) AND program_directive_version.pd_id = pd_contractor_signature.pd_id
(+) AND program_directive_version.pd_version_id =
pd_contractor_signature.pd_version_id
(+) AND program_directive_version.pd_id = pd_concurrence.pd_id (+) AND
program_directive_version.pd_version_id = pd_concurrence.pd_version_id
(+) AND program_directive_version.pd_id = pd_csb.pd_id (+) AND
program_directive_version.pd_version_id = pd_csb.pd_version_id (+) AND
pd_csb.pd_id = pd_evaluation_version.pd_id (+) AND pd_csb.pd_version_id
= pd_evaluation_version.pd_version_id (+) AND pd_csb.csb_id =
pd_evaluation_version.csb_id (+) AND pd_csb.pd_id = pd_csb_board.pd_id
(+) AND pd_csb.pd_version_id = pd_csb_board.pd_version_id (+) AND
pd_csb.csb_id = pd_csb_board.csb_id (+) AND pd_evaluation_version.pd_id
= pd_evaluator.pd_id (+) AND pd_evaluation_version.pd_version_id =
pd_evaluator.pd_version_id (+)

AND pd_evaluation_version.csb_id = pd_evaluator.csb_id (+) AND
pd_evaluation_version.evaluation_id = pd_evaluator.evaluation_id (+)

AND pd_evaluation_version.evaluation_version_id =
pd_evaluator.evaluation_version_id (+) AND
pd_evaluator.evaluator_code_id = evaluator_code.evaluator_code_id
(+)
AND pd_evaluation_version.evaluation_status_code_id =
eval_status.code_value_id (+) AND pd_evaluator.eval_response_code_id =
eval_response.code_value_id (+)

AND trim(pd_concurrence.concurrence_status_cd) =
trim(concurrence_status_code.code (+)) AND pd_ip_signature.agency_id =
agency.agency_id (+) AND pd_contractor_signature.contractor_id =
contractor_code.contractor_id (+) AND
pd_evaluation_version.evaluation_version_id = eval_vxref.version_id
(+)
AND trim(pd_evaluation_version.product_type_cd) =
trim(eval_vxref.product_type_cd (+)) /




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page