Re: Estimate temp space

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: 6 Apr 2007 06:54:09 -0700
Message-ID: <1175867649.781734.263690@d57g2000hsg.googlegroups.com>

On Apr 6, 3:04 pm, "EscVector" <J..._at_webthere.com> wrote:
> On Apr 6, 8:07 am, "Cristian Cudizio" <cristian.cudi..._at_yahoo.it>
> wrote:
>
>
>
> > On Apr 6, 1:07 am, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net>
> > wrote:
>
> > > On Thu, 05 Apr 2007 14:19:35 -0700, DA Morgan wrote:
> > > > Try this:
>
> > > > explain plan for select source from sys.source$ order by source; set
> > > > linesize 121
> > > > select * from table(dbms_xplan.display);
>
> > > > I think you'll change your mind.
>
> > > You are right, I stand corrected. Thanks.
>
> > > --http://www.mladen-gogala.com
>
> > I think that what mladen gogala says is correct regard usage of temp
> > tablespace.
> > If dabase uses Automati PGA management and set an high PGA_TARGET may
> > be that
> > Oracle uses more RAM and less DISK (TEMP tablespace)
> > Do you think this is right?
>
> > By>From Italy
>
> >http://cristiancudizio.wordpress.it
>
> SQL> explain plan for select source from sys.source$ order by source;
>
> Explained.
>
> SQL> set linesize 121
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------
> Plan hash value: 995087943
>
> --------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)| Time |
> --------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 555K| 31M| | 9792
> (2)| 00:01:58 |
> | 1 | SORT ORDER BY | | 555K| 31M| 72M| 9792
> (2)| 00:01:58 |
> | 2 | TABLE ACCESS FULL| SOURCE$ | 555K| 31M| | 1715
> (2)| 00:00:21 |
> --------------------------------------------------------------------------------------
>
> 9 rows selected.
>
> SQL> show parameter pga
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> pga_aggregate_target big integer 193M
> SQL> alter system set pga_aggregate_target=10M
> 2 ;
>
> System altered.
>
> SQL> explain plan for select source from sys.source$ order by source;
>
> Explained.
>
> SQL> set linesize 121
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------
> Plan hash value: 995087943
>
> --------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)| Time |
> --------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 555K| 31M| | 12604
> (2)| 00:02:32 |
> | 1 | SORT ORDER BY | | 555K| 31M| 72M| 12604
> (2)| 00:02:32 |
> | 2 | TABLE ACCESS FULL| SOURCE$ | 555K| 31M| | 1715
> (2)| 00:00:21 |
> --------------------------------------------------------------------------------------
>
> 9 rows selected.
>
> SQL> show parameter pga
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> pga_aggregate_target big integer 10M
> SQL>
>
> Since the temp space used does not change, the question is does the
> sort happen in memory or on disk?

fortunately value TempSpc does not chage, it is memory necessary to order source$ table by
column source, the algoritm it uses to estimate is the same, but to really view if it uses disk or ram
you have to execute it. I think that's an interesting accademic question, i've tried to execute the query on a 10.2.0.2 installation with PGA_AGGREGATE_TARGET=143M and with set autotrace on
and i've got

| 0 | SELECT STATEMENT | | 306K| 23M| | 7030 (2)| 00:0
1:25 |

| 1 | SORT ORDER BY | | 306K| 23M| 52M| 7030 (2)| 00:0
1:25 |

| 2 | TABLE ACCESS FULL| SOURCE$ | 306K| 23M| | 1188 (2)| 00:0
0:15 |

Statistiche

         59  recursive calls
          6  db block gets
       5365  consistent gets
       7470  physical reads
          0  redo size
   25213933  bytes sent via SQL*Net to client
     224510  bytes received via SQL*Net from client
      20377  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     305626  rows processed

SQL> show parameter pga_agg

NAME

TYPE                                         VALUE
--------------------------------------------
------------------------------
pga_aggregate_target
big integer                                  143M

It has sorted on disk, probably oracle decides based on various factors ,
SQL> select distinct(sid) from v$mystat;

SID

138

SQL> select
  2 sid,name,value
  3 from
  4 v$statname n,v$sesstat s
  5 where

  6     n.STATISTIC# = s.STATISTIC# and
  7     name like 'session%memory%'

8 and sid=138;

       SID NAME                                VALUE
---------- ------------------------------ ----------
       138 session uga memory                1072840
       138 session uga memory max           31382672
       138 session pga memory                4447828
       138 session pga memory max           35774036

My session has used max 35MBytes, when with 52 Mbytes it say's me that he can do the ordering

bye,
http://cristiancudizio.wordpress.com/ Received on Fri Apr 06 2007 - 08:54:09 CDT