Determine Queries Temporary Space Requirement

If you are ever in the situation where someone doesn’t want to add disk space to the temporary tablespace without having the specifics as to exactly how much space is required. There are two events that can be set that will provide this information.

The key is that you will actually need to run the query in question in order to get all the sort information required.

alter session set events ‘10032 trace name context forever’;
alter session set events ‘10033 trace name context forever’;

10032 will provide all the sort information to include the total amount of records input and output as well as the total amount of space required.:

10033 will provide the details of the sort run to include the block address and the number of blocks. At the end it will total the blocks used for all the sorts. This total number will be the amount of disk space required for this query with this data set.

Advertisements

Tagged:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: