(reposting)
Hi!
I think you should focus on why you do have bad performance/CBO decisions
when all tables have been analyzed. Finding most expensive operations
(v$sql, 10046 trace, tkprof) -> comparing their execution plans with
different statistics (tkprof, explain plan, v$sql_plan in 9i) -> finding the
reasons behing bad CBO decisions (10053 trace).
But if you really want to search for hints, then you should search for
'%/*+%' in v$sql during normal database usage time (not v$sqlarea, because
it's access is more CPU resource hungry). DBA_SOURCE provides you
information about stored pl/sql only and you can't search wrapped code.
Tanel.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Sunday, September 14, 2003 11:09 AM
> I think you need to scan user_source;
> select distinct name from user_source where type in ('FUNCTION' ,
'PROCEDURE' , 'PACKAGE BODY') and text like '%/*+%';
> Did not test the query though.
>
> HTH
> GovindanK
>
> On Sat, 13 Sep 2003 19:59 , M Rafiq <[EMAIL PROTECTED]> sent:
>
> >Env OPEN VMS ALPHA 7.3
> >Database 8.1.7.4 Optimizer = Choose
> >Application written for Rule but use of lot of Hints in code.
> >Reason of using cost based optimizer to create/use function based
indexes.
> >Selected tables are analyzed as all tablles(except sys) resulted in bad
> >performance.
> >
> >This is the environment of a short time project I am working with. I
would
> >like to track all those tables which are being used in
> >application/customized code using HINTS and atleast analyze those tables
> >which are not in their selective analyze list which is also not being
> >analyzed regularly.
> >
> >What is the best place to check such codes v$sqlarea/V$sql/V$sqltext or
any
> >other place in database.
> >I will appreciate a sql code to fetch all such codes from database.
> >
> >If I am thinking in wrong direction, I shall appreciate your
> >assistance.Right now I don't have option to check application codes from
> >system.
> >
> >Regards
> >Rafiq
> >
> >_________________________________________________________________
> >Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage.
> >http://join.msn.com/\?PAGE=features/es
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: M Rafiq
> > INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
>
>
>
> ---- Message sent via Zuvio Mail
> Get your own FREE email account with SPAM and Antivirus protection!
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Govindan K
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tanel Poder
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
