• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle Queries performace

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes they are working but taking time to execute, i am new to sql queries.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
reply
    Bookmark Topic Watch Topic
  • New Topic