Re: How to improve performance using Oracle Spatial

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: ecandrietta@xxxxxxxxx
  • Date: Sun, 04 Jan 2009 18:16:38 +0100


    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


Eriovaldo Andrietta wrote:
> Hi friends,
> a.) I am using: Oracle Database 10g Enterprise Edition Release
> - 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: