Re: Estimate temp space

From: EscVector <Junk_at_webthere.com>
Date: 6 Apr 2007 06:04:31 -0700
Message-ID: <1175864671.418994.281700@y66g2000hsf.googlegroups.com>

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? Received on Fri Apr 06 2007 - 08:04:31 CDT