In my server, we are getting spool space issue, If we delete few inactive old USERS, Will it resolve the spool space issue?
1. As per my Understanding, Database and users also provided with PERM Spaces, if we delete some inactive(old) users, Parent database will get those users perm spaces, So previous spool space issue may come down. Is't correct?
2. Users not necessary to be provided with Perm space always?
Thanks in advance.. :)
When you assign spool space to a user it defines the maximum spool limit that a user can use, but allocated only at run time according to the requirement. So deleting users does not mean that space will be increased. on the other hand deleting users with perm space will obviously increase the available spool space.
There are number of factors that can cause spool space issue, a poor sql code, lot of product join, data skewness all can cause spool space issue.
You can check all these alongwith the spool limits specified.
I think deleting old users is not a permanent solution for this issue, as mentioned earlier you should identify the problematic queries and you should start tuning those queries, moreover as mentioned earlier for problematic queries you need to check that whether stats are properly collected or not, furthermore you can view for product joins, data skewness, poor index selection and poor sql writing. Reading explain plan and then performing the required actions will be helpful in this regard.
I sincerely hope that you are not managing spool space on a user by user basis. This makes the DBA's job much more complex than it needs to be. You should be developing a profile for each group of users, based upon the work they typically perform and managing the spool space at the profile level.
If you have created profiles and are encountering spool space issues, there are many potential reasons as Kurram points out above. You should search these forums for discussions on "Impact CPU" and familiarize yourself with this metric to aid in your query tuning and the identification of "bad" queries. There are several investigative queries out there that will also help you identify which queries that need to be tuned.
Understanding and evaluating the explain plan of the identified queries will help resolve your spool space issues, unless of course your box is saturated, you've assigned a very low spool space value to your profiles, or have not balanced your users/profiles/account strings with enough spool.
All that being said, you've taken the right first step in not simply granting more spool space and you have identified that there are some other issues at play.
Thanks Khurram, Hammad, Blaine...
Its really informative reply. Thank you.
In this Project DBA where planning to delete these unused users by stating that to avoide spool issue. So I got above doubt. Now its cleared. For Run time spool space issue I will tune the code as per above tuning methods and try.
I have a query which inserts the data into one table. Before I insert, I would like to find out how much space it would occupy in the database once the data gets inserted into the the table.