Temp tablespace usage in SQL Optimizer for Oracle 9.0.0

SQL Optimizer SQL Optimizer for Oracle
1

Hello,

Is there a way i can see/ compare the temp tablespace usage in the different plan alternatives that the SQL Optimizer generates? I can see other parameters like Cost, Record Count, Time etc.

Thanks!

2

Hi,

There is no direct statistics showing the usage of temp tablespace in the Alternatives grid. However, you may be interested in the “Sorts (Disk)” statistic which show the number of sort operations that require disk (which implies the use of temp tablespace).

Another statistic you can check is on the actual execution plan (note that this is not listed in the Alternatives grid but on the plan only). It is the TEMP_SPACE column in a plan. I will suggest you check the Actual Plan after test run. This TEMP_SPACE column shows the temporary space used in bytes by the plan operation.

If you don’t see the TEMP_SPACE information in your plan, right-click the plan to select “Plan Options…” and make sure the column is selected to be visible. Another possibility is to swite to the DBMS_XPLAN format if you are using the latest version 9.0.

Hope you find this information useful.

Thanks,

Alex

3

Thanks a ton, Alex!! I’ll try your suggestions and get back.

4

Hi Alex,

When I try to - right-click the plan to select “Plan Options…”, the Plan Options is hidden in the right click menu.

Any idea, why is that?

5

Hi - the Plan Options selection is only active if you are using the Tree Plan or Plain English plan View Plan options. If you are using DBMS_XPLAN, the Temp Space column will only show up if the plan actually used temp space.

Hope that helps!

6

Thank you Kevin!