SQL Snippets ™: Tutorials for Oracle Developers

In this topic we see how reference models affect session memory. In the following tests the number and size of reference models varies from one query to the next.

Tests

0 Ref.
w/ 1 Meas.:

select m_1 from t_100k
model dimension by(key) measures(m_1) () ;

Test Complete
 

0 Ref.
w/ 3 Meas.:

select m_1, m_2, m_3 from t_100k
model dimension by(key) measures(m_1, m_1 as m_2, m_1 as m_3) () ;

Test Complete
 

1 Ref.
w/ 1 Meas.:

select x from dual where 1=2
model
  reference r on
    ( select key, m_1 from t_100k model )
    dimension by (key)
    measures     (m_1)
  main m
    dimension by (rownum as row_num)
    measures     ('x' as x)
    ()
;

Test Complete
 

1 Ref.
w/ 3 Meas.:

select 'x' as x from dual where 1=2
model
  reference r on
    ( select key, m_1, m_1 as m_2, m_1 as m_3 from t_100k model )
    dimension by (key)
    measures     (m_1, m_2, m_3 )
  main m
    dimension by (rownum as row_num)
    measures     ('x' as x)
    ()
;

Test Complete
 

2 Ref.
w/ 1 Meas.:

select 'x' as x from dual where 1=2
model
  reference r1 on
    ( select key, m_1 from t_100k model )
    dimension by (key)
    measures     (m_1)
  reference r2 on
    ( select key, m_1 from t_100k model )
    dimension by (key)
    measures     (m_1)
  main m
    dimension by (rownum as row_num)
    measures     ('x' as x)
    ()
;

Test Complete
 

2 Ref.
w/ 3 Meas.:

select 'x' as x from dual where 1=2
model
  reference r1 on
    ( select key, m_1, m_1 as m_2, m_1 as m_3 from t_100k model )
    dimension by (key)
    measures     (m_1, m_2, m_3)
  reference r2 on
    ( select key, m_1, m_1 as m_2, m_1 as m_3 from t_100k model )
    dimension by (key)
    measures     (m_1, m_2, m_3)
  main m
    dimension by (rownum as row_num)
    measures     ('x' as x)
    ()
;

Test Complete
 

Results

                           0 Ref.     0 Ref.     1 Ref.     1 Ref.     2 Ref.     2 Ref.
Metric                     w/ 1 Meas. w/ 3 Meas. w/ 1 Meas. w/ 3 Meas. w/ 1 Meas. w/ 3 Meas.
-------------------------- ---------- ---------- ---------- ---------- ---------- ----------
session pga memory max (K) 2,624      3,136      1,408      2,048      3,520      4,736
session uga memory max (K) 2,616      3,113      1,334      1,936      3,334      4,483

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

Observations

The reference model queries tested used less session memory than equivalent main models. Queries with two reference models used more session memory than those with one. Reference models with three measures used more session memory than those with one measure.