MODEL Performance Tuning: Minimize Datatype Lengths

Ensuring each mutable measure has the smallest possible declared length will help conserve session memory (see Measure Width).

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

Query 1:

select m_3 from t_1k
model
  dimension by ( key )
  measures     ( cast(null as VARCHAR2(4000)) as m_3 )
  rules        ( m_3[any] = 'x' )
;

Test Complete

Query 2:

select m_3 from t_1k
model
  dimension by ( key )
  measures     ( cast(null as VARCHAR2(1)) as m_3 )
  rules        ( m_3[any] = 'x' )
;

Test Complete

Results


Metric                     Query 1 Query 2
-------------------------- ------- -------
session pga memory max (K) 3,456   256
session uga memory max (K) 3,318   256

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

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-12266.html]SQL Snippets: MODEL Performance Tuning - Minimize Datatype Lengths[/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-12266.html">SQL Snippets: MODEL Performance Tuning - Minimize Datatype Lengths</a>

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

Link Text : SQL Snippets: MODEL Performance Tuning - Minimize Datatype Lengths
URL (href): http://www.sqlsnippets.com/en/topic-12266.html

SQL Snippets ™: Tutorials for Oracle Developers

MODEL Performance Tuning

Minimize Datatype Lengths

Results

Linking to SQL Snippets ™

Revision Notes