Oracle Database Discussions

Hi all,

I am running into a consistent issue in our development and QA environments.

We are running Oracle 11.2.0.2 on Sun Solaris
We have assigned 8 Gigabytes of temp tablespace to our user executing the query.

When I run an dbms_xplan.display against the query it is informing me that it will require to 5 Meg blocks of temporary tablespace.

Please note that This user has a dedicated tempspace and that we generally only have 2 or less developers running on the instance at any one time and that they use a different login with its own tempspace.

When I execute the query I can monitor the sid and watch it use well over the 10 Megabytes estimated and can use all of the 8 Gigabytes of tempspace.


So my question is why do I have suh a large discrepancy between the estimated and the actual usage of tempspace.

Also please note that the dataset in question is in the approximate size of 50 megabytes of data including indexes?

As another note we had a recent issue in production where a report stop being able to run due to running out of tempspace. Ultimately the issue was resolved, by dropping and recreating indexes for one of the tables. Does this make sense? If so could this be part of the issue I am facing in development and if so how do I go about identifying the offending indexes?

Thanks
Bruce Kirk