Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, John.

Asked: November 16, 2002 - 11:39 am UTC

Answered by: Tom Kyte - Last updated: March 03, 2006 - 11:19 am UTC

Category: SQL*Plus - Version: 8.0.6

Viewed 1000+ times

You Asked

Hi, Tom

I've got a problem creating a temporary table from a large table with 1.5 million records.
The temp table should have about 40000 records

Create table temp as
(select loc, team, year,
sum(a) sales1,
sum(b) sales2,
....
sum(z) sales12,
sum(a) - sum(aa) - sum(aaa) margin1,
.....
sum(z) - sum(zz) - sum(zzz) margin12
from master
group by loc, team, year)

The problem looks like a workspace problem as it doesn't even finish the query in 4 hours, and that's all the spare time I have overnight to do housekeping before the applications restart.
Is the problem the 24 (Sum)s or am I missing something

Thanks

John

and we said...

could be indicative of being ultra conservative on your sort_area_size. Here is an example on a modest Solaris box

two CPU:

Oct 24 15:37:33 aria-dev unix: cpu1: SUNW,UltraSPARC-II (upaid 1 impl 0x11 ver 0xa0 clock 400 MHz)
Oct 24 15:37:33 aria-dev unix: cpu3: SUNW,UltraSPARC-II (upaid 3 impl 0x11 ver 0xa0 clock 400 MHz)

and 2gig of ram (quite a few years old, definitely not state of the art, has about 6 instances of Oracle running on it -- nothing fancy with the disks -- just "plain old disks"

So, I created a table T:

drop table t;

create table t ( loc varchar2(10), team varchar2(10), year int,
a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int,
n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int );


begin
for l_loc in 1..200
loop
for l_team in 1..40
loop
for l_year in 1998..2002
loop
insert into t values ( 'loc' || l_loc, 'team' || l_team, l_year,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26);
end loop;
end loop;
end loop;
end;
/

insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;

select count(*) from t;

1,280,000 records...


and then:

ops$tkyte@ORA806.WORLD> set timing on
ops$tkyte@ORA806.WORLD> alter session set sort_area_size = 104857600
2 /

Session altered.

Elapsed: 00:00:00.00
ops$tkyte@ORA806.WORLD> create table t2
2 as
3 select loc, team, year,
4 sum(a) sales1, sum(b) sales2, sum(c) sales3,
5 sum(d) sales4, sum(e) sales5, sum(f) sales6,
6 sum(g) sales7, sum(h) sales8, sum(i) sales9,
7 sum(j) sales10, sum(k) sales11, sum(l) sales12,
8 sum(m) sales13,
9 sum(q)-sum(r)-sum(q) margin1,
10 sum(a)-sum(f)-sum(w) margin2,
11 sum(k)-sum(d)-sum(e) margin3,
12 sum(c)-sum(s)-sum(r) margin4,
13 sum(e)-sum(z)-sum(t) margin5,
14 sum(j)-sum(y)-sum(y) margin6,
15 sum(l)-sum(x)-sum(u) margin7,
16 sum(o)-sum(w)-sum(i) margin8,
17 sum(n)-sum(v)-sum(o) margin9,
18 sum(j)-sum(u)-sum(p) margin10,
19 sum(g)-sum(t)-sum(g) margin11,
20 sum(n)-sum(g)-sum(h) margin12,
21 sum(w)-sum(f)-sum(j) margin13
22 from t
23 group by loc, team, year
24 /

Table created.

Elapsed: 00:02:28.50
ops$tkyte@ORA806.WORLD>

took about 2 1/2 minutes. Normally, the sort area size is 102,400 bytes (100k). For this test, using a temp tablespace with 1m extents (that never actually got used!) and a 100m sort area size.

Now, trying the same thing with a 10m sort area size, it is very different, I let it run for 1/2 hour -- it took up about 1.4 gig of temp and I stopped it (got bored, proved my point)...

So suggestion: increase sort_area_size in your batch session to some large number to improve the performance of your very very large sort. The smaller the sort area size -- the smaller the swap pieces - the more temp you need -- the longer it will take (lots of extra IO and all).


People tend to way undersize this and never change at the session level for batch jobs. It can make a TREMENDOUSLY huge difference though!






and you rated our response

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Illustrative; shows up with materialized views, too

November 16, 2002 - 2:22 pm UTC

Reviewer: Daryl

Thanks for the nice example. I've seen this with refreshing materialized views as well. With a default sort_area_size, it takes *forever*. Upping it to 100m cut it to under 30 seconds.

How about 9i

December 06, 2002 - 3:47 am UTC

Reviewer: A reader from India

Hi Tom

If I am using 9i with workarea size policy = AUTO. Then would Oracle take care of dynamically sizing the sort_area_size according to the need of the session?

Thanks


Tom Kyte

Followup  

December 06, 2002 - 7:23 am UTC

yes, according to the need of the session AND the currently available resources.

Other operations

March 16, 2004 - 4:52 am UTC

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

Hi Tom,

You have proved the effect of "sort area size" for SELECTs.
But what about the effect "sort area size" on inserts and updates ?

From where "sort area size" is allocated ? From SGA or PGA or from the OS ?
And when is the "sort area size" used for query released ?
If I have set "sort area retained size" 0, the memory used for a query is released immediately after the query ?

Thanks in advance.


Tom Kyte

Followup  

March 16, 2004 - 7:54 am UTC

inserts can have selects

insert into t select ......

updates can have a query portion as well.

same with deletes

and merge


anything that "sorts".


The sort_area_size is allocated from the UGA. The UGA is in the PGA in dedicated server mode. The UGA is in the SGA in shared server mode (specifically the large_pool if one is allocated, else the shared_pool)


The sort area size is immediately shrunk down to sort area retained when the sort operation is completed. If sort area retained is not set (and would show as "0") then sort area retained = sort area size.

If you have my book "Expert one on one Oracle" -- i cover memory allocations and how/when they work in Oracle in some detail in the architecture chapter.

work_area_size_police=AUTO

March 03, 2006 - 9:31 am UTC

Reviewer: A reader

Is it true that sort_area_size is of no use in 9i
when we set work_area_size_policy=AUTO. Meaning just
set it to auto and forget about the sort_area_size for
ever.

Tom Kyte

Followup  

March 03, 2006 - 11:19 am UTC

if you are using shared server in 9i OR workarea policy is manual, *_size parameters are used

if you are using dedicated server in 9i and workarea policy is auto, then *_size parameters are not used to size workareas.


in 10g if workarea policy is manual, *_size parameters are used
else if workarea policy is auto, *_size parameters are not used

All

December 07, 2006 - 12:39 am UTC

Reviewer: Tom from Minneapolis

Awesome place!!! I heard the AskTom for a long time, got 2 books from Tom Kyte, one free and signed. This is the first time, I wish I have been to this place years... years earlier!


Yes..A true engineer's oracle library

February 08, 2007 - 10:11 pm UTC

Reviewer: A reader from NJ USA

I fully agree that this site is most comprehensive, concise and most practical. I too think if had known AskTom site atleast 3 years back, I would have gained 15-20% of Tom's knowledge. The more I read on this site, the more I understand that the less I know in oracle though I have some tuning experience to my credit. I am thinking about making this site as popular as google to my cirle of colleagues and friends. Thanks Tom for the great work.