Re: How to improve performance using Oracle Spatial
- From: "Eriovaldo Andrietta" <ecandrietta@xxxxxxxxx>
- To: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
- Date: Sun, 4 Jan 2009 17:20:49 -0200
Stephane,
Many, many, many thanks.
I will continue working on this, changing the trace parameter from 12 to 8.
Best Regards
Eriovaldo
On Sun, Jan 4, 2009 at 3:16 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:
> Eriovaldo,
>
> For event 10046 more isn't always better - the level you set (12)
> asks for wait events plus bind variables - that means that each new
> parameter you pass is traced. I think that 8 (just the waits) would have
> been enough. Anyway, since your process is rather lengthy, I suggest you
> manage to find some Unix-like command-line utilities and use something
> such as grep to count, from the raw file, how many select/insert/update
> statements you have in your file. I suspect a lot, which usually means
> poor algorithms (SQL calls in loops). Also, try to page (with 'more' or
> such a command) through a fair bit of your file to see if statements are
> hard-coded (that is, variable are directly written in the statements) or
> not (that is, you find references to :varname in your statements). If
> your statements are hard-coded, the output of level 8 and level 12
> shouldn't be very different, and it's no use wasting your time another
> three hours ... In that case, set the cursor_sharing parameter to
> "force" (I hate that, but we are studying), then try to trace with level
> 8, you should get a more manageable trace file. But if you have many,
> many statements, it's the PL/SQL code that needs vigorous rewriting.
>
> Hope that helps
>
> SF
>
> Eriovaldo Andrietta wrote:
> > Hi friends,
> >
> > a.) I am using: Oracle Database 10g Enterprise Edition Release
> > 10.2.0.1.0 - Prod, running on Windows Vista (for study), in the
> > company it runs on Unix.
> >
> > b.) Yes, there are spatial indexes.
> > My goal is look at the trace result in order to know if the
> > index is selective
> >
> > c.) There are lot of procedures running. There are lot of queries
> > running inside of each process, using spatial and not spatial data.
> > It takes around 3 hours to finish (in my computer at home).
> >
> > d.) I generated the trace for the hole process and got a big trc file,
> > it is with 4.439.703kb.
> >
> > I used:
> > to start: exec dbms_system.set_ev (131,801,10046,12, '');
> > to stop: exec dbms_system.set_ev (131,801,10046,0, '');
> >
> > I am trying generate the .txt file using the following command:
> > tkprof
> > C:\Oracle\product\10.2.0\db_1\admin\oracle10\udump\oracle10_ora_4036.trc
> > c:\a_eri\trace4036.txt
> >
> > but the program is stopping while running and does not generate
> > the .txt file :(
> > I cannot see the trace result.
> >
> > Question 1 : How can I extract information from the trc file ? Is
> > there another way ?
> >
> > Question 2 : Can I select the information for extract ?
> >
> > My concerns:
> > If I cannot extract .txt from trc I will run the process step by
> > step and getting not big trc files to be possible evaluate its.
> >
> > thanks
> > Eriovaldo
> >
>
>
>
Other related posts:
