Oracle Queries performace

[Naresh Nimmala]

Hi I would like to know how we can imrove oracle queries performance.


For example i have following sample queries which i need to optimize.


1) select count(0) totalCount from analyst_call_log rl,analyst_team at, (select associate_id from
associate)creted_by_detail where rl.added_by_assoc_id = creted_by_detail.associate_id and
at.associate_id = rl.added_by_assoc_id and at.team_id = rl.team_id and rl.confidential != 'Y' and
rl.analyst_call_log_id in(select ac.analyst_call_log_id from analyst_call_log_contact ac where
ac.client_id in (select client_id from client_hier_coverage where assoc_id= 16369/*?*/) ) and
rl.call_log_dt_utc >= add_months(TO_DATE('03-19-2010'/*?*/,'MM/DD/YY'), -6)


2) select CASE WHEN added_by_assoc_id = 16369/*?*/
THEN 'Y'
ELSE 'N'
END coveredByUser,
get_analyst_call_log_attendees(analyst_call_log_id) client_attendees, get_analyst_call_log_clients(analyst_call_log_id) client_submasters,
analyst_call_log_id callLogId ,'Analyst' callLogType,created_dt_utc,
to_char(call_log_dt_utc, 'YYYY-Mon-DD') call_log_dt_utc,
added_by_assoc_id,subject,created_by_name,type,team_name,country,obo_user,tickers_discussed,call_duration from
(select row_number() over (order by call_log_dt_utc ASC,rl.created_dt_utc desc) rn,rl.analyst_call_log_id,rl.created_dt_utc,rl.added_by_assoc_id,rl.subject,creted_by_detail.created_by_name,obo_user_detail.country,obo_user_detail.obo_user,rl.type
, rl.call_log_dt_utc,at.team_name, rl.tickers_discussed,

call_duration from analyst_call_log rl,analyst_team at,
(select NBK_ID,associate_short_name created_by_name from associate)creted_by_detail,
(select associate_id, associate_short_name obo_user,country from associate) obo_user_detail
where upper(rl.created_by) = upper(creted_by_detail.NBK_ID)
and rl.added_by_assoc_id = obo_user_detail.associate_id
and at.associate_id = rl.added_by_assoc_id


Anybody can help me how i can optimize database sql queries if there are any tutorials let me know for improve performance.

[Ireneusz Kordal]

See these informative links:
http://forums.oracle.com/forums/thread.jspa?messageID=1812597#1812597
http://forums.oracle.com/forums/thread.jspa?threadID=863295&tstart=0

[Jan Cumps]

Welcome to JavaRanch, Naresh.

Do these queries actually work?
Besides the performance issues you have, do they give you the correct results?

Regards, Jan

[Naresh Nimmala]

yes they are working but taking time to execute, i am new to sql queries.

[Jan Cumps]

I would go back to the drawing table.
Bring back each of these queries (one at a time) to their most simple working form: select only values from the most important table.
Then - and only when you have the first step right - link in the additional tables, one at the time. Be sure to understand what you are doing. Only proceed to the next table if you have a working, performant and understood query.

When I look at your queries, I think it would help if you ask an experienced sql person of your team to assist you for a few hours.
You need to get the basis right. These queries are fairly complex if you are new to sql.


Regards, Jan