Just stick it into the query analyzer available on the Oracle performance
pack cdrom. It'll tell you what execution plan it's using and recommend some
indexes to add/subtract, but evaluate what it tells you to do very
carefully. i.e. test on a dev system first (assuming the data is the same,
otherwise you can't compare) The query analyzer is of enormous help to me,
and every dba should be awre of it and know how to use it in my opinion.
It's like having a tuning expert on hand at all times.

Tony.

----- Original Message -----
From: "Pradhan " <oracledba-ezmlmshield-x72217927.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, August 04, 2004 8:36 AM
Subject: Query Help


> Hi Folks,
>
> Thie query is taking nearly 3-4 mins now - any idea on optimizing it...
>
> select
t.empcodea,(to_char(sum(to_number(substr(translate(time_diff,':','.'),1,leng
th(translate(time_diff,':','.'))-3)))+
>
(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_dif
f,':','.'))-1,2)))-
>
Mod(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_
diff,':','.'))-1,2))),60))/60)||
>
'.'||to_char(Mod(sum(to_number(substr(translate(time_diff,':','.'),length(tr
anslate(time_diff,':','.'))-1,2))),60))),
> round(m.avgctc/
>
> (select
to_char(sum(to_number(substr(translate(time_diff,':','.'),1,length(translate
(time_diff,':','.'))-3)))+
>
(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_dif
f,':','.'))-1,2)))-
>
Mod(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_
diff,':','.'))-1,2))),60))/60)||
>
'.'||to_char(Mod(sum(to_number(substr(translate(time_diff,':','.'),length(tr
anslate(time_diff,':','.'))-1,2))),60))
> from timesheet t2
> where TO_CHAR(T2.CURR_DAY,'YYYYMM')='200407' and
t2.empcodea=t.empcodea),2)AVGSAL
>
> from timesheet t,ctcmaster m,cost c
> where TO_CHAR(T.CURR_DAY,'YYYYMM')='200407' and t.empcodea in (select
distinct t.empcodea from timesheet t where t.client_function='SUN')
> and t.client_function='SUN'and c.empcodea=t.empcodea and
m.desigcode=(select c.cost from cost c where c.empcodea=t.empcodea)
> group by t.empcodea, m.avgctc,'AVGSAL'
>
> Pradhan
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
>


Oracle LazyDBA home page