Home » RDBMS Server » Performance Tuning » Results TRACE
Results TRACE [message #110268] Fri, 04 March 2005 14:52 Go to next message
Tads
Messages: 10
Registered: February 2005
Junior Member
Hi all,

I´m not understanding the result of trace.
Before I had 17 tables in FROM claus. Now I have 15.
this theoretically would reduce the consumption of some resources, but it increased. In both queries, the number
of the rows returned is 4905.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      328      8.40      20.68      54660     207969          6        4905
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      330      8.40      20.68      54660     207969          6        4905

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 151

Rows     Row Source Operation
-------  ---------------------------------------------------
   4905  SORT ORDER BY
   4905   NESTED LOOPS
   4905    NESTED LOOPS OUTER
   4905     NESTED LOOPS OUTER
   4905      NESTED LOOPS
   4905       NESTED LOOPS OUTER
   4905        NESTED LOOPS
   4905         NESTED LOOPS
   4905          NESTED LOOPS OUTER
   4905           NESTED LOOPS OUTER
   4905            NESTED LOOPS
  13085             NESTED LOOPS
  14721              HASH JOIN OUTER
  14721               NESTED LOOPS
      1                TABLE ACCESS BY INDEX ROWID OBJ#(17636)
      1                 INDEX UNIQUE SCAN OBJ#(17637) (object id 17637)
  14721                TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(18390) PARTITION: ROW LOCATION ROW LOCATION
2238406                 INDEX RANGE SCAN OBJ#(20087) (object id 20087)
      1               TABLE ACCESS FULL OBJ#(17366)
  13085              TABLE ACCESS BY INDEX ROWID OBJ#(17379)
  14721               INDEX UNIQUE SCAN OBJ#(17380) (object id 17380)
   4905             TABLE ACCESS BY INDEX ROWID OBJ#(17406)
  13085              INDEX UNIQUE SCAN OBJ#(17407) (object id 17407)
   4905            TABLE ACCESS BY INDEX ROWID OBJ#(17538)
   4905             INDEX RANGE SCAN OBJ#(17540) (object id 17540)
   4905           TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(18420) PARTITION: ROW LOCATION ROW LOCATION
   4905            INDEX RANGE SCAN OBJ#(18444) (object id 18444)
   4905          TABLE ACCESS BY INDEX ROWID OBJ#(17636)
   4905           INDEX UNIQUE SCAN OBJ#(17637) (object id 17637)
   4905         TABLE ACCESS BY INDEX ROWID OBJ#(17636)
   4905          INDEX UNIQUE SCAN OBJ#(17637) (object id 17637)
   4905        TABLE ACCESS BY INDEX ROWID OBJ#(17614)
   4905         INDEX RANGE SCAN OBJ#(17651) (object id 17651)
   4905       TABLE ACCESS BY INDEX ROWID OBJ#(17478)
   4905        INDEX UNIQUE SCAN OBJ#(17479) (object id 17479)
   4905      TABLE ACCESS BY INDEX ROWID OBJ#(17364)
   4905       INDEX UNIQUE SCAN OBJ#(17365) (object id 17365)
      0     TABLE ACCESS BY INDEX ROWID OBJ#(17375)
      0      INDEX UNIQUE SCAN OBJ#(17376) (object id 17376)
   4905    TABLE ACCESS BY INDEX ROWID OBJ#(17579)
   4905     INDEX UNIQUE SCAN OBJ#(17580) (object id 17580)



Somebody knows why?

thanks
Re: Results TRACE [message #110539 is a reply to message #110268] Tue, 08 March 2005 09:31 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Tads,

Do you see how your elapsed time is so much greater than your CPU time? That means you spent most of your time (59%) waiting for something. To find out what you were waiting for (network? disk?), you should dig into the raw trace file.

Hope this helps.
Previous Topic: Performance problems
Next Topic: Issue of low buffer hit ratio
Goto Forum:
  


Current Time: Thu Mar 28 03:32:17 CDT 2024