Home » RDBMS Server » Performance Tuning » Regarding sort operation
Regarding sort operation [message #214872] Thu, 18 January 2007 05:36 Go to next message
Messages: 100
Registered: December 2005
Senior Member
Does execution of the ANALYZE command causes a sort operation?
Re: Regarding sort operation [message #214876 is a reply to message #214872] Thu, 18 January 2007 05:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10673
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yes. If the sort cannot be done in memory (defined by SORT_AREA_SIZE), the sort is done in disk (in temporary tablespace).
Re: Regarding sort operation [message #215159 is a reply to message #214872] Fri, 19 January 2007 08:51 Go to previous message
Messages: 41
Registered: January 2007
Try this one
16:52:42 SQL> connect crb/crb
16:52:48 SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:00.02
16:52:56 SQL> analyze table a compute statistics ;
Table analyzed.
Elapsed: 00:00:13.85
16:53:22 SQL> exit

and run tkprof on the trace file.
You will se several sort operations in the txt file.
As for instance :
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT ORDER BY (cr=7 pr=1 pw=0 time=23243 us)
      1   HASH JOIN OUTER (cr=7 pr=1 pw=0 time=23123 us)
      1    NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=238 us)
      1     TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=174 us)
      1      INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=74 us)(object id 3)
      0     TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=50 us)
      0      INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=43 us)(object id 711)
      0    VIEW  (cr=2 pr=1 pw=0 time=19871 us)
      0     SORT GROUP BY (cr=2 pr=1 pw=0 time=19862 us)
      0      TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=1 pw=0 time=19641 us)
      0       INDEX RANGE SCAN I_CDEF2 (cr=2 pr=1 pw=0 time=19634 us)(object id 51)

and lots more all run by SYS for dealing with your analyze command.

Best regards.

Previous Topic: The Temporary Tablespace??
Next Topic: Optimalization problem with static subquery
Goto Forum:

Current Time: Sat Jun 24 12:24:00 CDT 2017

Total time taken to generate the page: 0.05421 seconds