MODEL Performance Tuning

Use Measures Instead of Rules

Operations implemented with MEASURES use less memory than those implemented with RULES.

For example, Query 2 below will use less memory than Query 1.

Query 1:

select m_1, m_3 from t_1k
model
  dimension by (key)
  measures     ( m_1, m_2, m_1 as m_3 )
  RULES SEQUENTIAL ORDER
  (
    M_3[ANY] = M_1[CV()] || M_2[CV()] ,
    m_1[any] = 'x'
  )
;

Test Complete
 
Query 2:

select m_1, m_3 from t_1k
model
  dimension by (key)
  MEASURES
  (
    m_1,
    M_1 || M_2 AS M_3
  )
  rules ( m_1[any] = 'x' )
;

Test Complete
 

Results


Metric                     Query 1 Query 2
-------------------------- ------- -------
session pga memory max (K) 7,488   3,392
session uga memory max (K) 7,295   3,318

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

Gotchas

Be careful not to alter the end result when you move logic from rules to measures. For example, if we started with something similar to Query 1

select m_1, m_2, m_3 from t_1k where key < 10
model
  dimension by (key)
  measures     ( m_1, m_2, m_1 as m_3 )
  RULES SEQUENTIAL ORDER
  (
    m_1[any] = 'x' ,
    M_3[ANY] = M_1[CV()] || M_2[CV()] -- appears as second rule this time
  )
;
 
M_1 M_2 M_3
--- --- ---
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
 

we would not get the same results by rewriting it like this.

select m_1, m_2, m_3 from t_1k where key < 10
model
  dimension by (key)
  MEASURES
  (
    m_1,
    m_2,
    m_1 || m_2 as m_3
  )
  rules ( m_1[any] = 'x' )
;
 
M_1 M_2 M_3
--- --- ---
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
 

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.




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-12264.html]SQL Snippets: MODEL Performance Tuning - Use Measures Instead of Rules[/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-12264.html">SQL Snippets: MODEL Performance Tuning - Use Measures Instead of Rules</a>

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

  • Link Text : SQL Snippets: MODEL Performance Tuning - Use Measures Instead of Rules
  • URL (href): http://www.sqlsnippets.com/en/topic-12264.html

Revision Notes

Date Category Note
2007-05-11 Revision Moved code here from topic-12225. Fixed bug in Query 1 (rules were in the wrong order).