Performance Considerations for Temporary Data in Oracle

Performance considerations for temporary data in Oracle

Temporary tables are effectively the same as in-memory tables thanks to caching and asynchronous I/O, and the temporary table solution does not require any overhead for converting between SQL and PL/SQL.

Confirming the results

Comparing the two versions with RunStats, the temporary table version looks much worse. All that junk for the temporary table version in Run1, and only a little extra memory for the PL/SQL version in Run2. At first it seems like PL/SQL should be the clear winner.

Type  Name                              Run1 (temp) Run2 (PLSQL)         Diff
----- -------------------------------- ------------ ------------ ------------
...
STAT  physical read bytes                    81,920            0      -81,920
STAT  physical read total bytes              81,920            0      -81,920
LATCH cache buffers chains                  104,663          462     -104,201
STAT  session uga memory                    445,488      681,016      235,528
STAT  KTFB alloc space (block)            2,097,152            0   -2,097,152
STAT  undo change vector size             2,350,188            0   -2,350,188
STAT  redo size                           2,804,516            0   -2,804,516
STAT  temp space allocated (bytes)       12,582,912            0  -12,582,912
STAT  table scan rows gotten             15,499,845            0  -15,499,845
STAT  session pga memory                    196,608   19,857,408   19,660,800
STAT  logical read bytes from cache     299,958,272            0 -299,958,272

But at the end of the day only the wall clock time matters. Both the loading and the querying steps run much faster with temporary tables.

The PL/SQL version can be improved by replacing the BULK COLLECT with cast(collect(test_o(MOD(a, 10), '' || MOD(a, 12))) as test_t) INTO t. But it's still significantly slower than the temporary table version.

Optimized Reads

Reading from the small temporary table only uses the buffer cache, which is in memory. Run only the query part many times, and watch how the consistent gets from cache (memory) increase while the physical reads cache (disk) stay the same.

select name, value
from v$sysstat
where name in ('db block gets from cache', 'consistent gets from cache', 
'physical reads cache');

Optimized Writes

Ideally there would be no physical I/O, especially since the temporary table is ON COMMIT DELETE ROWS. And it sounds like the next version of Oracle may introduce such a mechanism. But it doesn't matter much in this case, the disk I/O does not seem to slow things down.

Run the load step multiple times, and then run select * from v$active_session_history order by sample_time desc;. Most of the I/O is BACKGROUND, which means nothing is waiting on it. I assume the temporary table internal logic is just a copy of regular DML mechanisms. In general, new table data may need to be written to disk, if it's committed. Oracle may start working on it, for example by moving data from the log buffer to disk, but there is no rush until there is an actual COMMIT.

Where does the PL/SQL time go?

I have no clue. Are there multiple context switches, or a single conversion between the SQL and PL/SQL engines? As far as I know none of the available metrics show the time spent on switching between SQL and PL/SQL.

We may never know exactly why PL/SQL code is slower. I don't worry about it too much. The general answer is, the vast majority of database work has to be done in SQL anyway. It would make a lot of sense if Oracle spent more time optimizing the core of their database, SQL, than the add-on language, PL/SQL.

Additional notes

For performance testing it can be helpful to remove the connect by logic into a separate step. That SQL is a great trick for loading data, but it can be very slow and resource intensive. It's more realistic to load a sample table once with that trick, and then insert from that table.

I tried using the new Oracle 12c feature, temporary undo, and the new 18c feature, private temporary tables. Neither one improved performance over regular temporary tables.

I wouldn't bet on it, but I can see a way that the results would completely change as the data gets larger. The log buffer and the buffer cache can only get so large. And eventually that background I/O could add up and overwhelm some processes, turning the BACKGROUND wait into a FOREGROUND wait. On the other hand, there's only so much PGA memory for the PL/SQL solution, and then things crash.

Finally, this partially confirms my skepticism of "in-memory databases". Caching is nothing new, databases have been doing it for decades.

Oracle + compression + temp space usage

Basic table compression does not reduce temporary tablespace usage. It's difficult to prove something does not exist, here are my reasons:

1. Such a feature is not mentioned in the manuals or advertised as a feature.
2. I can't think of an easy way to implement it. Basic table compression is not deterministic. The same values can be represented in many different ways, therefore the data must be uncompressed before it can be meaningfully joined with other data.
3. A simple test case does not show any affect.

This example show a highly compressed table being grouped. Adding or removing the compress option does not change the amount of temporary space.

--Create a table and add data.
drop table table1;
create table table1(
    c1 char(100), c2 char(100), c3 char(100), c4 char(100), c5 char(100),
    c6 char(100), c7 char(100), c8 char(100), c9 char(100), c10 char(100)
) compress; -- Remove "compress" and re-run to compare.  Results will not change.
insert /*+ append */ into table1
select level,level,level,level,level,level,level,level,level,level
from dual connect by level <= 100000;
commit;

--There is about 95MB of data.
select 100000*100*10/1024/1024 mb from dual;

--Segment size is 13MB.
select bytes/1024/1024 from dba_segments where segment_name = 'TABLE1';

--Create a new table using a large GROUP BY.
drop table table2;
create table table2 as
select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10
from table1
group by c1,c2,c3,c4,c5,c6,c7,c8,c9,c10;

--In a separate session, measure the temporary tablespace usage.
--The value tops out at 89MB.
select bytes/1024/1024 mb from dba_segments where segment_name = 'TABLE1';

Oracle11 global temp table lifespan

Correct.

A global temporary table exists forever, just like any other database object. It is created when you install your application (not at runtime) just like any other database object.

If you specify ON COMMIT PRESERVE ROWS, the data persists for the lifetime of the session (and is only visible to the session that inserted it). If you specify ON COMMIT DELETE ROWS, the data persists only for the lifetime of the transaction (and is only visible to the transaction that inserted it).

Implications of too big TEMP tablespace?

A large temporary tablespace will not cause any performance issues. I've tuned SQL on databases with more than a terabyte of temporary tablespace and never seen any issues caused by that space.

How to turn off stats on Global Temporary Table and impact

This disables statistics gathering on a table

begin
    dbms_stats.delete_table_stats('TABLE_OWNER', 'TABLE_NAME');
    dbms_stats.lock_table_stats('TABLE_OWNER', 'TABLE_NAME');
end;
/

However a better idea is to collect statistics on the table when it is filled with representative data set:

begin
    dbms_stats.gather_table_stats('TABLE_OWNER', 'TABLE_NAME');
    dbms_stats.lock_table_stats('TABLE_OWNER', 'TABLE_NAME');
end;
/

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1043993

What is more efficient? (SP PL/SQL)

Option #2 is definitely more efficient, although it's hard to tell if it will be noticeable in your case.

Every COMMIT requires a small amount of physical I/O; Oracle must ensure all the data is written to disk, the system change number (SCN) is written to disk, and there are probably other consistency checks I'm not aware of. In practice, it takes a huge number of COMMITs from multiple users to significantly slow down a database. When that happens you may see unusual wait events involving REDO, control files, etc.

Before a COMMIT is issued, Oracle can make the changes in memory or asynchronously. This may allow the performance to be equivalent to an in-memory database.

---

An even better option is to avoid the issue entirely by using a single MERGE statement, as Sylvain Leroux suggested. If the processing must be done in PL/SQL, at least replace the OPEN/FETCH cursor syntax with a simpler cursor FOR-loop. A cursor FOR-loop will automatically bulk collect data, significantly improving read performance.

---

Related Topics