SQL Snippets ™: Tutorials for Oracle Developers

In this topic we see how the number of dimensions affects session memory. In each of the following tests one measures is defined and no rules are used. The number of dimensions varies from one test to the next.

Tests

Non-MODEL
Query:

select  'x' as x from t_100k ;

Test Complete
 

1 Dimension:

select x from t_100k
model dimension by( key_1 ) measures( 'x' as x ) () ;

Test Complete
 

2 Dimensions:

select x from t_100k
model dimension by( key_1, key_2 ) measures( 'x' as x ) () ;

Test Complete
 

3 Dimensions:

select x from t_100k
model dimension by( key_1, key_2, key_3 ) measures( 'x' as x ) () ;

Test Complete
 

Results

                           Non-MODEL
Metric                     Query     1 Dimension 2 Dimensions 3 Dimensions
-------------------------- --------- ----------- ------------ ------------
session pga memory max (K) 256       12,160      23,936       33,024
session uga memory max (K) 248       11,877      23,266       32,028

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

Observations

Session memory increased as the number of dimensions increased, even though no rules were defined.

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 ave rage the results from multiple runs before making performance decisions.