Re: Urgent - Question on Auditing

"Patrick Sinke" <patrick.sinke_at_capgemini.nl> wrote in message news:989586896.693640_at_sleeper.capgemini.nl...
>
> Sybrand,
>
> Where is this temp tablespace used for anyway? We got a development
> environment here, and this TS is only 10 MB, and doesn't seem to be used.
> Just curiosity!
>

Sorts! If you do a select * from giant_table order by obscure_column, the sort takes place in your PGA. If your PGA runs out of space, what's Oracle supposed to do? Tell you you can't run that report, it's too hard?? Nope... it pages the filled PGA out to disk (somewhere), and continues sorting the next batch of records... pages ... sorts .... pages .... etc, and then merges all the stuff off disk to bring you the finished product.

Guess what happens when you create an Index? Er, that's right ... records get sorted.... PGA gets used .... temp tablespace gets pages to.

Same for group by's, unions, etc etc etc

If your temp tablespace is not being used, that's a good thing (usually), because it means that all the sorts are being comfortably housed in the PGA, and hence raring along at nanosecond speeds in the PGA.

However, it could be that your temp tablespace is simply *called* "temp", without being proper temporary tablespace (ie, one created with the 'temporary' keyword). In permanent tablespace, sorts get swapped to disk as usual (thus chewing up extents), and at the end of the sort, those extents are dropped -so if you happen to query the temp tablespace usage when all sorts have finished for the day, the thing will look empty. Proper temporary tablespace, by contrast, does not drop extents at the end of the sort -they just get marked for re-use by the next sort. So extents don't get dropped, so the tablespace looks busy (in fact, if it's not 100% full or nearly so, something is probably wrong).

So... is your temp tablespace empty because you are generous with memory, or because you didn't create it properly??

Regards
HJR
> Regards,
> Patrick Sinke,
> Cap Gemini Ernst & Young, sector Financial Services.
>
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> schreef in berichtnieuws
> tfnjchpf3cc86b_at_beta-news.demon.nl...
> > Moving it in the TEMP tablespace is unfortunately a *very bad* idea.
> >
> > Regards,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > "Dave Weeks" <dweeks_at_nospam.gnseurope.com> wrote in message
> > news:9dgcne$m5i$1_at_reader-00.news.insnet.cw.net...
> > > Hi there,
> > >
> > > Things about auditing I've done on our 8.1.5. (Solaris 2.7) setup:
> > >
> > > 1) Moved sys.aud$ out of the SYSTEM tablespace and into TEMP. This
 will
> > > prevent SYSTEM from being fragmented by audit records being
> > > inserted/deleted.
> > > 2) Or, set the auditing to write to the OS instead of the database
 (this
> > > superceedes #1)
> > >
> > > 3) Read

 http://w3.uqah.uquebec.ca/ORACLE/server.805/a58397/ch22.htm#1108
> > > (take an hour to read it all)
> > >
> > > Best regards,
> > > Dave Weeks.
> > >
> > >
> > > Balachandra Rao <bprao_at_worldnet.att.net> wrote in message
> > > news:BHBK6.15073$4f7.1182444_at_bgtnsc06-news.ops.worldnet.att.net...
> > > > Hi ,
> > > > We have oracle 8.1.6 running on NT. Our dba started the auditing on
> > > > (connections) but we found the system slowing down. He stopped
 auditing
 by
> > > > changing the parameter in init.ora file to false. Our query in
 sys.aud$
> > > > table shows no increase in records after changing the parameter. Is
 this
> > > > the only step to be taken to stop auditing.
> > > > If not what are the other steps required.
> > > >
> > > > Thanks in advance.
> > > > BPR
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Sat May 12 2001 - 02:07:00 CDT