Help with optimizing sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Help with optimizing sql

  1. #1
    Join Date
    Aug 2001
    Posts
    64

    Question

    All, I am running solaris 2.6 with oracle815. The following sql is taking a long time to run; Can some one help optimize this. Thanks.

    $QUERY1 = "
    SELECT p.station_call, p.station, p.city_code, c.city_name, m.monitor, m.channel
    FROM monitor_station m, starcd_cities c, port_map p
    WHERE p.station = m.station_id and m.city_code = p.city_code \
    and c.city_code = m.city_code and c.city_code \
    NOT IN ('90') and station_call != 'WYNY'
    ORDER BY c.city_name";

    $sth_stations = $dbh->prepare("$QUERY1");
    $sth_stations->execute;


    $QUERY2= "select ((sysdate - d.activity_date) * 24) \
    from detections d, port_map p \
    where d.city_code=p.city_code \
    and d.city_code= :city and p.station_call= :station \
    and p.station=d.station_id \
    order by d.activity_date desc";
    $sth_detections = $dbh->prepare("$QUERY2") || die $DBI::errstr;"

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Check for any wait events occuring for the current sessions buy executing the following query

    select w.sid,substr(s.username,1,10) username,substr(w.event,1,35) event,
    w.wait_time,w.state,w.seconds_in_wait SEC_IN_WAIT
    from v$session s,v$session_wait w where s.username is not null and w.sid=s.sid and
    w.event not like '%SQL*Net%' order by w.wait_time desc;

    Execute the above query multiple times to see if the events are waiting for a long time by looking at the wait_time and seconds_in_wait columns.

    If sessions are waiting for the event for a long time..execute the below query to know for which resource the events are waiting...

    select sid,substr(event,1,35) event,substr(p1text,1,10),p1,
    substr(p2text,1,10),p2,substr(p3text,1,10),p3 from v$session_wait where
    event not like '%SQL%' and event not like '%rdbms%';


    The column p1 will tell u datafile no and p2 will tell u the block no.By knowing the datafile no,you can know where they are placed.It could be that both index and data files are placed in the same drive causing I/O issue.By knowing the block no.,you can get to know the segment name for which the sessions are waiting.

    if u have any problem please let me know.

    regards
    anandkl

    anandkl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width