Spool space

Database
Enthusiast

Spool space

There are 3 users created under database which has PERM space 50 GB. Each user is allocated 5 GB PERM space & 20 GB Spool space.

Now, the PERM Space left in database- 35 GB.

I've created the another user for whom i allocated 20 GB PERM space.

Now, the PERM Space left in database- 15 GB ==> UNUSED space.

Even though  i allocated 20 GB spool space to each user, if i submit a sql query which takes more than 15 GB should be allocated since the unused space is only 15 GB ?? Am i missing out some thing here?

8 REPLIES

Re: Spool space

Spool memory = unused perm + spool assigned. The particular user will have this much spool for his queries.

Hope i make sense to your question.

Senior Apprentice

Re: Spool space

SPOOL is the currently not used perm space on system level, but not on user/database level.

The spool on a database level is only used for inheritance:

When there's no spool assigned for a new user he inherits the owner's spool size.

And when there's spool it may not exceed the owner's size.

Dieter

Enthusiast

Re: Spool space

Thanks sir. Small clarification needed.

Let us say if only 20 GB un used PERM space left at SYSTEM level. And there are 2 users in a system. If i run queries in both the users simultaneously and each is expected to take more than 15 GB spool space. In this scenario one query has to be aborted right? 

Senior Apprentice

Re: Spool space

The system will run out of spool and return a 2507 error: "Out of spool space on disk", this probably affects all active queries.

I don't know if only a single or multiple sessions will be aborted, you usually try to avoid that problem :-)

Dieter

Enthusiast

Re: Spool space

Hi, I want to understand what unused PERM space is being used as a SPOOL space in a query. Supposed I have this query:

"Select * from A.1 left outer join B.1 where...."

Am I using the unused PERM of database A and B?

Supporter

Re: Spool space

Any free cyclinder can be used as Spool space. It is not limited to the databases specified in the SQL.

Enthusiast

Re: Spool space

ulrich, please explain... why I have 5TB spool space in a user and it is accessing a table in a database with only 2TB unused space. The system has 576 amps. Based on the explain plan the query will be using at least 576G of spool.

 If you will divide 2TB/576 amps, you will get 3.4GB. Could this be the reason why? because it is consuming all the least unused space per amp?

Supporter

Re: Spool space

I am not sure that I understand your issue.

Yes, if you have 5TB asssigned to your user and you have 576 VPROCs then you have 3.4G per VPROC for spoolspace. So skew can be limiting your spoolspace! So if you want to use 5GB on one VPROC you get the out of spool error message even if total spool space is only 500GB at this point of time.

The 5TB are not limited to the free space in your DBs of your tables.

To be able to use the full 5 TB you need even distributed data AND 5TB of FREE cyclinders in the whole DB system.