First of all, you must have a bufferpool that matches the page size of the tablespace. By that token if the temporary tablespace is the only one with the 32K page size then it will have the bufferpool for its exclusive use.
If you have other tablespaces with the 32K page size, only monitoring the system performance will tell you if there may be a benefit from a separate temp space bufferpool.
You can use select * from sysibmadm.mon_bp_utilization
to look at the bufferpool hit ratios and select * from table (mon_get_bufferpool(NULL,NULL))
to check page cleaner activity (POOL_NO_VICTIM_BUFFER
and POOL_DRTY_PG_STEAL_CLNS
should ideally be 0). If you see that a drop in the bufferpool hit ratio or high dirty page contention coincides with the temporary space use (a spike in POOL_TEMP_DATA_L_READS
in select * from table (mon_get_tablespace(NULL,NULL))
), creating a separate bufferpool of an appropriate size for the temporary tablespace in question might be useful.
What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.
According to the Book
Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.
It says in paragraph 2 on Page 117:
Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.
Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.
I hope this answers your question.
UPDATE 2014-01-31 11:26 EST
In your last comment, you said
If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?
Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;
Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.
Best Answer
You find all available metrics in
V$SQL_MONITOR
- disk rate, requests and consistent gets per execution per session - here you also found if parallel execution is degradedV$SQL_PLAN_MONITOR
- disk and memory data per session, execution and plan lineV$ACTIVE_SESSION_HISTORY
- 1 s sampled data per session, execution and plan lineV$PROCESS
- current counters for process memory usage (join to session usingPADDR = ADDR
)If you are familiar with execution monitor in Enterprise manager or other Oracle monitoring tools, they use exactly same views. You just need to join the data from them and model into an output you want.
There one more view useful for finding out parallel skew -
V$PQ_TQSTAT
.