Re: Why Not Allow Temp Tables?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 23 Sep 2003 10:21:44 +0100
Message-ID: <3f7010a8$0$245$ed9e5944@reading.news.pipex.net>

"(Pete Cresswell)" <x_at_y.z> wrote in message news:8hpsmvshfq5s9vtckp44n4bofl7o1cg3iu_at_4ax.com...
> RE/
> >The reasons are as follows:
> >
> >1. Kills performance and scalability
> >2. The privileges to create objects do not belong in a production schema
> >except during brief periods when it is being built or modified
> >under the direct supervision of the DBA.
> >3. Completely unncessary so no reason to do it.
>
> I'm not trying to be difficult - I'm taking everything that's been said to
> heart...but I'd also like to be able to argue the point with somebody who
tells
> me they want to use Temp tables.
>
> #1 makes sense - but it would be good to have a little insight into why.

Here is an app that does this. (db names etc snipped). 2 of the top 10 SQL statements doing IO are conventional application sql, the rest is DDL related recursive SQL.

SQL ordered by Gets for DB

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ------

1,698,015 11,396 149.0 6.2 288.36 295.07 285856375
Module: AGRSERV.EXE
SELECT DISTINCT priority_no, last_update, report_id, repo

rt_type, report_name, orderno, func_arg  FROM acrrepord  WHERE s
tatus IN (:"SYS_B_0", :"SYS_B_1")    AND poll_status = :"SYS_B_2
"    AND server_queue = :"SYS_B_3"    AND invoke_time <= to_date

(:"SYS_B_4", :"SYS_B_5") ORDER BY :"SYS_B_6" desc, :"SYS_B_7" a

412,195 40,990 10.1 1.5 41.31 41.18 392454249
delete from con$ where owner#=:1 and name=:2

383,755 40,990 9.4 1.4 54.95 55.31 3687727603
insert into col$(obj#,name,intcol#,segcol#,type#,length,precisio n#,scale,null$,offset,fixedstorage,segcollength,deflength,defaul t$,col#,property,charsetid,charsetform,spare1,spare2,spare3)valu es(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8, -127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,

380,803 40,990 9.3 1.4 62.78 63.17 1065777498
insert into cdef$(obj#,con#,type#,intcols,condlength,condition,r obj#,rcon#,match#,refact,enabled,cols,defer,mtime,spare1)values(

:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,
0,null,:7),decode(:8,0,null,:8),decode(:9,0,null,:9),decode(:10,
0,null,:10), decode(:11,0,null,:11),:12, decode(:13,0,null,:13),

        369,713       40,990            9.0    1.4    68.81     69.63

2237645050
update con$ set con#=:3 where owner#=:1 and name=:2

340,731 2,338 145.7 1.2 56.00 56.63 3839014650
delete from col$ where obj#=:1

339,711 2,338 145.3 1.2 66.34 66.84 1797318687
delete from cdef$ where obj#=:1

301,886 40,990 7.4 1.1 43.70 44.26 1433805645
insert into ccol$(con#,obj#,intcol#,pos#,col#) values(:1,:2,:3,d ecode(:4,0,null,:4),:5)

286,266 3 95,422.0 1.0 51.61 169.52 4247509018
Module: Agresso32.exe

SELECT :"SYS_B_0" AS tab,t.voucher_type,t.voucher_no,t.sequence_
no,t.voucher_date,t.period,t.account,t.dim_1,t.dim_2,t.dim_4,t.t
ax_code,t.description,t.amount,t.value_1,t.apar_type,t.att_1_id

FROM agltransact t WHERE :"SYS_B_1"=:"SYS_B_2" AND t.account = :"SYS_B_3" AND t.period >= :"SYS_B_4" AND t.dim_2 = :"SYS_B_5" A

252,809 1,169 216.3 0.9 48.80 49.02 2907501333
delete from ccol$ where obj#=:1

> #2 sounds like argument by assertion - but that's probably because I don't
know
> diddley.... Something about objects being created/destroyed impacting
> performance would be my first guess.

drop table sales_orders would 'impact performance' :( Of course you might not be able to do that but you might well be able to drop index on an index that was supporting a FK constraint and cause unnecessary locks.

>
> #3, I'm guessing, is because of the availablity of global temp tables.
True?

Pretty much yes. If you are on 7.3 you probably would do it this way.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Tue Sep 23 2003 - 04:21:44 CDT