SQL Snippets ™: Tutorials for Oracle Developers

In this topic we see how session memory is allocated when more than one measure column is updated. In each of the following tests the number of mutable measures is fixed at two. The number and distribution of updated cells varies from one run to the next.

Tests

Starting Test #1

select key, m_1, m_2 from t_1k
model dimension by( key ) measures( m_1, m_2 )
rules update
(
m_1[key between 1 and 250] = 'x' ,
m_2[-1] = 'y'
) ;
 

Starting Test #2

select key, m_1, m_2 from t_1k
model dimension by( key ) measures( m_1, m_2 )
rules update
(
m_1[key between 1 and 250] = 'x' ,
m_2[key between 1 and 250] = 'y'
) ;
 

Starting Test #3

select key, m_1, m_2 from t_1k
model dimension by( key ) measures( m_1, m_2 )
rules update
(
m_1[key between 1 and 500] = 'x' ,
m_2[-1] = 'y'
) ;
 

Starting Test #4

select key, m_1, m_2 from t_1k
model dimension by( key ) measures( m_1, m_2 )
rules update
(
m_1[key between 1 and 500] = 'x' ,
m_2[key between 1 and 500] = 'y'
) ;
 

Starting Test #5

select key, m_1, m_2 from t_1k
model dimension by( key ) measures( m_1, m_2 )
rules update
(
m_1[key between 1 and 1000] = 'x' ,
m_2[-1] = 'y'
) ;
 

Starting Test #6

select key, m_1, m_2 from t_1k
model dimension by( key ) measures( m_1, m_2 )
rules update
(
m_1[key between 1 and 1000] = 'x' ,
m_2[key between 1 and 1000] = 'y'
) ;
 

Results

The following table shows elapsed time and max. memory usage for each run.

                            250 Rows   250 Rows  500 Rows   500 Rows 1000 Rows  1000 Rows
METRIC_NAME                1 Measure 2 Measures 1 Measure 2 Measures 1 Measure 2 Measures
-------------------------- --------- ---------- --------- ---------- --------- ----------
session pga memory max (K)     1,536      1,536     3,584      3,584     7,680      7,680
session uga memory max (K)     1,393      1,393     3,446      3,382     7,359      7,359

See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
 

Observations

The results suggest session memory is allocated for entire rows of mutable measures, regardless of whether any single measure in the row is updated or not.

Warning: Results on your own systems with your own data will differ from these results. Results will even differ from one set of test runs to the next on the same machine. Run your own tests and average the results from multiple runs before making performance decisions.