MODEL Performance Tuning: Measures and Rules

In this topic we see how MEASURES and RULES affect session memory. In each of the following tests the number of measures and the number of RULES that update each measure varies from one test to the next.

Number of Measures Defined

1 Measure:

select key, m_1 from t_1k
model dimension by( key ) measures( M_1 )
() ;

Test Complete

2 Measures:

select key, m_1 from t_1k
model dimension by( key ) measures( M_1, M_1 AS M_2 )
() ;

Test Complete

3 Measures:

select key, m_1 from t_1k
model dimension by( key ) measures( M_1, M_1 AS M_2, M_1 AS M_3 )
() ;

Test Complete

Results


Metric                     1 Measure 2 Measures 3 Measures
-------------------------- --------- ---------- ----------
session pga memory max (K) 256       256        256
session uga memory max (K) 256       256        256

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

Observations

Session memory usage is not affected by the number of measures defined.

Number of Measures on the Right Side of a Rule

0 Measures:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( m_1[any] = 'X' ) ;

Test Complete

1 Measure:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( m_1[any] = M_2[CV()] ) ;

Test Complete

2 Measures:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( m_1[any] = least( M_2[CV()], M_3[CV()] ) ) ;

Test Complete

3 Measures:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( m_1[any] = least( M_2[CV()], M_3[CV()], M_4[CV()] ) ) ;

Test Complete

Results


Metric                     0 Measures 1 Measure 2 Measures 3 Measures
-------------------------- ---------- --------- ---------- ----------
session pga memory max (K) 3,456      3,456     3,456      3,456
session uga memory max (K) 3,318      3,318     3,318      3,318

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

Observations

Session memory usage is not affected by the number of measures referenced on the right hand side of a rule.

Number of Measures on the Left Side of a Rule

1 Measure
Updated:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( m_1[any] = 'X' ) ;

Test Complete

1 Measure
Update by
2 Rules:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( M_1[ANY] = 'X', M_1[ANY] = 'X' ) ;

Test Complete

2 Measures
Updated:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( M_1[ANY] = 'X', M_2[ANY] = 'Y' ) ;

Test Complete

3 Measures
Updated:

select key, m_1 from t_1k
model dimension by( key ) measures( m_1, m_1 as m_2, m_1 as m_3, m_1 as m_4 )
rules update ( M_1[ANY] = 'X', M_2[ANY] = 'Y', M_3[ANY] = 'Z' ) ;

Test Complete

Results


                                     1 Measure
                           1 Measure Update by 2 Measures 3 Measures
Metric                     Updated   2 Rules   Updated    Updated
-------------------------- --------- --------- ---------- ----------
session pga memory max (K) 3,456     3,456     7,552      11,648
session uga memory max (K) 3,382     3,382     7,359      11,335

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

Observations

Max. session memory was directly proportional to the number of measures that were changed by at least one rule. We will call these "mutable measures" going forwards. Measures that are not touched by any rules will be called "non-mutable measures".

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

SQL Snippets ™: Tutorials for Oracle Developers