MODEL Performance Tuning

Partitioning

In this topic we see whether the PARTITION BY clause affects session memory or not.

Tests

Without
PARTITION BY
100 Rows Updated:

select m_1 from t_100k
model
  dimension by (key_3, key_2)
  measures (m_1)
  rules ( m_1[any,1] = 'x' )
;

Test Complete
 
With
PARTITION BY
100 Rows Updated:

select m_1 from t_100k
model
  partition by (key_3)
  dimension by (key_2)
  measures     (m_1)
  rules ( m_1[1] = 'x' )
;

Test Complete
 
Without
PARTITION BY
10000 Rows Updated:

select m_1 from t_100k
model
  dimension by (key_3, key_2)
  measures     (m_1)
  rules ( m_1[any,for key_2 like '%' from 1 to 10 increment 1] = 'x' )
;

Test Complete
 
With
PARTITION BY
10000 Rows Updated:

select m_1 from t_100k
model
  partition by (key_3)
  dimension by (key_2)
  measures     (m_1)
  rules ( m_1[for key_2 like '%' from 1 to 100 increment 1] = 'x' )
;

Test Complete
 

Results


                           Without          With             Without            With
                           PARTITION BY     PARTITION BY     PARTITION BY       PARTITION BY
Metric                     100 Rows Updated 100 Rows Updated 10000 Rows Updated 10000 Rows Updated
-------------------------- ---------------- ---------------- ------------------ ------------------
session pga memory max (K) 30,592           39,168           30,272             39,168
session uga memory max (K) 29,780           37,993           29,468             37,993

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

Observations

In the above tests partitioning had no effect on session memory usage.




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-12261.html]SQL Snippets: MODEL Performance Tuning - Partitioning[/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-12261.html">SQL Snippets: MODEL Performance Tuning - Partitioning</a>

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

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