CODEWITHSUNDEEP
oracle-database
LoudNPossiblyWrong
LoudNPossiblyWrong

Reputation: 3893

How expensive is a query in terms of TEMP tablespace?

I have a few sprocs that execute some number of more complex queries and liberally use collections.

My DBA is complaining that they occasionally consume a S#$%ton of in-memory TEMP tablespace.

I can perform optimizations on the queries but i also wish to be as noninvasive as possible and to do this i need to see the effects my changes have on the TEMP tablespace.

QUESTION:

How can i see what cost my query has on the TEMP tablespace?

One thing to consider is i dont have DBA access.

Thanks in advance.

Upvotes: 2

Views: 3998

Answers (4)

David Aldridge
David Aldridge

Reputation: 52376

While your query is running you can query v$sql_workarea_active, or after it has run you can query v$sql_workarea.

These will show you the temp tablespace usage in terms of memory used, disk space used, and (most importantly) the number of passes (space usage is only part of the issue -- multipass sorts are very expensive), and correlate the usage to steps in the explain plan.

You can then consider whether modifying memory management would help you reduce temp tablespace usage both in terms of absolute space used and in the pass count.

Upvotes: 2

user123664
user123664

Reputation:

Before doing all kinds of interesting queries and tricks, estimate the data volume that should be sorted, after filtering. If this is larger than what fits in the sort area, the sort will move blocks from memory to temp and read them back later. Add a little overhead to the raw data size; use 30% overhead. This should give a reasonable estimation for the needed total sort size.

Use the same strategy for collections. There has to be room for the data somewhere, there is no magic/compression that makes your data volume smaller. If you have memory for 1000 rows max and try to use it with 1000.000 rows it won't fit. In that case talk to your dba and try to find a solution. It could be that you end up partitioning your workload.

Upvotes: 4

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

Depends what you mean by the cost your query has on temp.

If you can select from v$tempseg_usage, you can see how much space you are consuming in temp - on a DEV database there is no reason your DBA cannot give you access to that view.

As was mentioned by gpeche - autotrace will give you a good idea about how many IOs you are doing from temp - so that combined with the space usage will give you a good idea about what is going on.

Large collections are generally a bad idea - they consume a lot of memory in the PGA (which is very different from TEMP) which is shared by all the other sessions - this will be what your DBA is concerned about. How large is large depends on your system - low thousands of small records probably isn't too bad, but 100's of thousands or millions of records in a collection and I would be getting worried.

Upvotes: 4

gpeche
gpeche

Reputation: 22514

Without having DBA access, I would try with AUTOTRACE. It will not give you TEMP tablespace consumption, but you can get a lot of useful information for tuning your queries (logical reads, number of sorts to disk, recursive SQL, redo consumption, network roundtrips). Note that you need some privileges granted to use AUTOTRACE, but not full DBA rights.

Upvotes: 2

Related Questions