Global Tables and COLLECT STATISTICS ON TEMPORARY

Hello .. The question is in regards to who gets to collect statistics on each instance of a GTT. We are trying to run some jobs in parallell, that uses lots of GTT's. If I run these jobs using the same user id, we could potentially run into spool issues. Hence, we are trying to run these jobs in parallell, using three different user id's, and we are running to issues in relation to not being able to collect stats on the instance of the GTT.

I used the following script to test. I have created a GTT using user id 1, was able to collect stats on index, columns, and also collect stats on the whole table, as well as .. COLLECT STATISTICS ON TEMPORARY. Then if I try to perform the same COLLECT STATISTICS ON TEMPORARY with user id 2, I am getting error 3523: The user does not have INDEX access to GT_STATS_TEST. It looks like there is some kind of access needed to collect stats on the temporary instance of the global table created by another user.

Re: Global Tables and COLLECT STATISTICS ON TEMPORARY

I have a feeling that you are using the wrong kind of tables for this process...

Global temporary tables are used when you want different sessions to use table with same name and structure but SHOULD NOT share the data instance. (ie it's like non-static member variables in object oriented programming, each object instance has it's own copy of data and can be manipulated independent of another object's copy).

You normally create a global temporary table definition once and use it repeatedly (ie you don't drop and recreate it every time)

Once the definition is stored in data dictionary, each session that tries to populate the table (say an insert statement) automatically gets a copy of the table for itself (it's said to be materialized)...

Another session (even using the same db user id) CANNOT access that instance.... though this session is free to materialize it's own copy of the GT table and work on it ...

You probably need to re-visit your design (and catch up a bit on how GT tables work).

Re: Global Tables and COLLECT STATISTICS ON TEMPORARY

The error message could be better, but the problem is that the second session has not yet materialized an instance of the global temporary table.

To me, it's actually more surprising that the creator of the table can materialize an instance via the "recollect" form of COLLECT STATS, and that both creator and other user can materialize an instance via COLUMN or INDEX form. I hadn't realized that was possible, since COLLECT STATS is currently considered DDL not DML.

You do know that instances of the GTT will be local to each session? Session2 can't access data from Session1's materialized instance. And the space for the GTT instances will be charged against the TEMPORARY space limit, not SPOOL.

Re: Global Tables and COLLECT STATISTICS ON TEMPORARY

Thanks Joe and Fred for your response .. The moment I read your both responses, I knew what was wrong in my test - exactly as what Fred said - . I added an insert into the table (thanks Joe) with the 2nd user and then the COLLECT STATISTICS ON TEMPORARY worked just fine.

We are not trying to drop and create the GTT every time. The intend is to create the GTT's just once, and be able to use it in three different instances in parallel. I know that the materialized instance of the GTT is not shared by another session even if it is the same user id.

I did some further research after posting my initial query. There is a way a 2nd user can perform the COLLECT STATISTICS ON TEMPORARY, without materializing an instance of the GTT. The creator can do a GRANT ALL ON gtt TO userid; and then the second user can do the COLLECT STATISTICS ON TEMPORARY, even without materializing an instance of the GTT.

Re: Global Tables and COLLECT STATISTICS ON TEMPORARY

Thanks Joe and Fred for your response .. The moment I read both your responses, I knew what was wrong in my test - exactly as what Fred said - the problem is that the second session has not yet materialized an instance of the global temporary table. I added an insert into the table (thanks Joe) with the 2nd user and then the COLLECT STATISTICS ON TEMPORARY worked just fine.

We are not trying to drop and create the GTT every time. The intend is to create the GTT's just once, and be able to use it in three different instances in parallel. I know that the materialized instance of the GTT is not shared by another session even if it is the same user id.

I did some further research after posting my initial query. There is a way a 2nd user can perform the COLLECT STATISTICS ON TEMPORARY, without materializing an instance of the GTT. The creator can do a GRANT ALL ON gtt TO userid; and then the second user can do the COLLECT STATISTICS ON TEMPORARY, even without materializing an instance of the GTT.