DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2001


    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");

    $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
    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.



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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.