Update frequency of CurrentSpool in dbc.DiskSpace

Database
Enthusiast

Update frequency of CurrentSpool in dbc.DiskSpace

Hi,

How close to real time does the currentspool column update in dbc.diskspace?

The reason I ask is because I have run a query to identify queries skewing in spool and noticed I had a larger than expected value in CurrentSpool for the query I was running as User A. I then logged in using User B and although User A wasn't running anything, the same values appeared against User A in the results. So, it's clearly not updated in real time. How does this column get updated?

Using RDBMS version 13.10

Thanks

Rob

8 REPLIES
Junior Contributor

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Hi Rob,

It's not "real time" but "whenever necccessary". When you explain your query you'll notice:

explain sel * from dbc.DiskSpace;

*** Help information returned. 31 rows.
*** Total elapsed time was 1 second.

Explanation
---------------------------------------------------------------------
1) First, we lock DBC.DBSpace in view DiskSpace for access, and we
lock DBC.dbase in view DiskSpace for access.
2) Next, we flush the DISKSPACE cache.

This flush should ensure correct data.

There might have been a Volatile Table, it's materialized within the user's spool.

Or there's a Phantom Spool, indicating the internal housekeeping process failed somehow. You can test if you logoff all sessions of userA and there's still a CurrentSpool > 0. If it's not a significant percentage of the user's spool it's just annoying. It will be reset by the next dbc restart or when dbc submits a update spool space for userA; from the updatespace utility.

Dieter

Enthusiast

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Ah OK, thanks. I think we have a phantom spool issue. I've run the query again and we have a handful of users who are not logged on but have currentspool > 0.

Does phantom spool mean the space just appears to be in use but isn't actually taking up disk space, or does it mean the space is in use and unavailable for other queries?

Junior Contributor

Re: Update frequency of CurrentSpool in dbc.DiskSpace

The spool is released, i.e. not using space, but it's reducing the user's available spool because the size is not correct.

In fact there's another source for wrong CurrentSpace, Orphan spools, which have allocated space, but these are rare.

You can find any left-over spool using:

/*** Phantom Spool ***/
select
databasename
,sum(currentspool) as LeftOver
,'update spool space for ' || trim(databasename) || ';'
from dbc.diskspace
where databasename not in
(select username from dbc.sessioninfo)
group by 1
having LeftOver > 0

Dieter

Enthusiast

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Thanks for the query but running "update spool space for <user>;" threw up a syntax error:-

"...expected something like a name or Unicode delimited identifier or an 'UDFCALLNAME' keyword between the 'update' keyword and the 'spool' keyword."

I also tried variations with SpoolSpace and CurrentSpool in place of 'spool space' but they yielded similar errors.

Thanks

Rob

Junior Contributor

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Hi Rob,

the "update spool space ..." must be submitted from the updatespace utility not from a SQL client. You need access to the "Database Window" to do that.

Dieter

Fan

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Hi Dieter/TD Experts

Could you please help me to understand TPT FAST EXP utility functionality. We got into PROD issue when DB performance was severly impacted and it was very slow in response. We are using TPT Fast Exp to read data. We have used given SQL

SELECT TY_ID,FR_ID, Min(EFECT_STRT_DT), SRC_SYS_ST_FR_POOL_ID, MIN(REC_LD_TS) FROM TY_FR_DTL   WHERE  EFECT_END_DT = '9999-12-31'   GROUP BY TY_ID,FR_ID, SRC_SYS_ST_FR_POOL_ID HAVING COUNT (*) > 1

In Logs DBA observed that they got multiple entries of this query, They say it was runnin in Loop. I am very new to TPT usage and we are using it through informatica. In Informatica logs it just show following error message for every row

Message

: [ERROR] Plug-in failed to fetch data on row [1] with status [2646].

Message Code

: TPTRD_21215

Message

: [ERROR] Plug-in failed to fetch data on row [2] with status [2646].

Could you please help me to understand why TPT Fast Exp will run query in loop. I have doubt on DBA statment that it is running in loop but they are seeing multiple enteries of same query in logs.

Is this TPT functionality that it will fire select query for every record?

is there an possibility that DB was already slow and because select query couldn't fetch record it was issuing multiple times.

Please share your valuable comments that can help me to start Right Debugging.

Regards

Shail

Junior Contributor

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Could you please post unrelated questions as a new post?

2646 indicates running out of spool.

If this load was running previously without problems there might have been multiple parallel jobs submitted by the INFA user and Informatica seems to retry on that error.

Dieter

Fan

Re: Update frequency of CurrentSpool in dbc.DiskSpace

Thank you so much Dieter

i have open case with Informatica Support, let's see how it goes from here.

Have a good time

Regards

Shail