MODEL Performance Tuning

Reference Models

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12237.html]SQL Snippets: MODEL Performance Tuning - Reference Models[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12237.html">SQL Snippets: MODEL Performance Tuning - Reference Models</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: MODEL Performance Tuning - Reference Models
  • URL (href): http://www.sqlsnippets.com/en/topic-12237.html

Revision Notes

Date Category Note
2007-05-11 Revision Fixed bug in the MEASURES clauses of test #6.