RE: Third party application - how to begin performance tuning efforts?

Thanks for your suggestions. You have provided me with some excellent ideas.

Saira

-----Original Message-----
Mladen Gogala
Sent: September 26, 2003 1:05 AM
To: Multiple recipients of list ORACLE-L efforts?

Yes, that's a great idea for forcing plans. Thanks for thinking of that. Essentially (this part is for the original poster, Saira Somani), you create
a small copy of your production database, by using exp rows=no, if necessary
and then repeat the query in that small database, where you can tweak the
parameters, the structure and the quantity of data. That means that you can
force full table scans, hash joins and alike, then, when you are reasonably
satisfied with the execution plan, create an outline and trensplant it to
your production database.
Stephane, it's a great idea. I'll write it to my book of spells.

On 2003.09.25 23:14, Stephane Faroult wrote:
> Outlines ?
>
> Mladen Gogala wrote:
> >
> > Saira, you can turn on tracing, you can analyze tables, create
histograms
> > and create indexes.
> > The first thing to do would be to take 10046 trace, level 12 and
analyze
> it
> > with tkprof. Then,
> > you should find the few most expensive SQL statements and see
whether
> > something quick and easy
> > could be done, like index creation, for instance. If not, and the
> > performance isn't satisfactory,
> > then determine where is the problem and contact the vendor. No big
science
> > there and nothing more
> > you can do.
> >
> > --
> > Mladen Gogala
> > Oracle DBA
> >
> > > -----Original Message-----
> > > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> > > Behalf Of Saira Somani-Mendelin
> > > Sent: Thursday, September 25, 2003 4:35 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Third party application - how to begin performance
> > > tuning efforts?
> > >
> > >
> > > List,
> > >
> > > I begin with an apology for repeating something that has
> > > probably been asked before in different words.
> > >
> > > We use an integrated ERP/WMS/Query application provided by a
> > > vendor but we do not have the ability to change any code.
> > >
> > > How do I know that my Oracle database is running optimally
> > > (if there is such a thing)?
> > >
> > > Obviously I cannot rewrite queries in the application code
> > > (which is 4GL code BTW). So what other aspects of the
> > > database can I change/tune? I can definitely see some costly
> > > SQL statements when I feel curious and want to check what's
> > > happening on the database. But isn't cost all relative?
> > >
> > > Are there any recommendations for articles, white papers,
> > > books on how to tune the database for a third party application?
> > >
> > > Also, I will be attending the DBA/Developer Day in Toronto on
> > > Monday October 6. I am looking forward to the sessions by Tim
> > > Gorman, Tim Quinlan and Michael Abbey. Anyone else attending
> > > this conference?
> > >
> > > Thanks in advance,
> > > Saira
> > >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.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).
>

--
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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: Saira Somani-Mendelin
  INET: saira_somani_at_yahoo.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).