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;"
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,
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.
Click Here to Expand Forum to Full Width