Re: Checking for code using hints(from database)

Tanel,

Thanks. I did check it from v$sql and provided the list to client. As it is a proprietry application hence code tuning by client is not an option.

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tue, 16 Sep 2003 03:49:24 -0800

(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" <ORACLE-L_at_fatcity.com> 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 <rafiq9857_at_hotmail.com> 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: rafiq9857_at_hotmail.com
> >
> >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: ListGuru_at_fatcity.com (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: gkatteri_at_zuvio.com
>
> 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: ListGuru_at_fatcity.com (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: tanel.poder.003_at_mail.ee

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: ListGuru_at_fatcity.com (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).

_________________________________________________________________
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: rafiq9857_at_hotmail.com

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: ListGuru_at_fatcity.com (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).