MODEL Performance Tuning

Measure Width

In this topic we explore the effect of measure width on session memory. In each of the following tests the number of rows in the array is fixed at 10,000 and the number of mutable measures is fixed at one. The width and datatype of the mutable measure varies from one run to the next.

Tests

Starting Test #1

create table t( key number, m_1 char(1) ) ;

select key, m_1 from t
model dimension by( key ) measures( m_1 )
rules upsert ( m_1[for key from 1 to 10000 increment 1] = 'x' )
 
Starting Test #2

create table t( key number, m_1 char(10) ) ;

select key, m_1 from t
model dimension by( key ) measures( m_1 )
rules upsert ( m_1[for key from 1 to 10000 increment 1] = 'x' )
 
Starting Test #3

create table t( key number, m_1 char(100) ) ;

select key, m_1 from t
model dimension by( key ) measures( m_1 )
rules upsert ( m_1[for key from 1 to 10000 increment 1] = 'x' )
 
Starting Test #4

create table t( key number, m_1 char(1000) ) ;

select key, m_1 from t
model dimension by( key ) measures( m_1 )
rules upsert ( m_1[for key from 1 to 10000 increment 1] = 'x' )
 
Starting Test #5

create table t( key number, m_1 varchar2(1000) ) ;

select key, m_1 from t
model dimension by( key ) measures( m_1 )
rules upsert ( m_1[for key from 1 to 10000 increment 1] = 'x' )
 

Results



METRIC_NAME                CHAR(1) CHAR(10) CHAR(100) CHAR(1000) VARCHAR2(1000)
-------------------------- ------- -------- --------- ---------- --------------
session pga memory max (K)     256      256       448      9,856          9,856
session uga memory max (K)     256      256       426      9,290          9,290
 

Observations

Not surpisingly we see that the width of a mutable measure directly affects the amount of session memory used. As well, we again see that memory allocation for VARCHAR2 measures behaves the same as with CHAR measures.

Gotchas

Large Number of Rows

Be careful when using MODEL queries with a large number of rows. The following results were obtained by running tests similar to those above on a one million row table. In each run the width of the VARCHAR2 measure column varied from 11 to 12 to 13 characters.

METRIC_NAME                        VARCHAR2(11)     VARCHAR2(12)     VARCHAR2(13)
------------------------------ ---------------- ---------------- ----------------
Elapsed Time (1/100 sec)                  1,081            1,109        3,419,722
physical writes direct tempora                0                0        6,884,666
recursive calls                               3                3               81
recursive cpu usage                           2                2                3
CPU used when call started                  651              637           19,866
CPU used by this session                    651              637           19,866
DB time                                     668              658        3,419,277
user I/O wait time                            0                0        3,400,644
session uga memory max               46,985,684       48,003,780       33,880,316
session pga memory max               47,906,816       48,955,392       34,930,688
enqueue requests                              9                9               87
enqueue releases                              6                6               84
physical read total IO request                0                0        1,036,108
physical read total multi bloc                0                0        1,036,108
physical read total bytes                     0                0  263,121,698,816
physical write total IO reques                0                0          222,086
physical write total multi blo                0                0          222,086
physical write total bytes                    0                0   56,399,183,872
physical reads                                0                0       32,119,348
physical reads direct                         0                0       32,119,348
physical read IO requests                     0                0        1,036,108
physical read bytes                           0                0  263,121,698,816
physical writes                               0                0        6,884,666
physical writes direct                        0                0        6,884,666
physical write IO requests                    0                0          222,086
physical write bytes                          0                0   56,399,183,872
physical writes non checkpoint                0                0        6,884,666
physical reads direct temporar                0                0       32,119,348
workarea executions - optimal                 8                8                6
workarea executions - onepass                 0                0                2
 

As we can see, in the third run using a datatype length of 13 was indeed an unlucky thing to do.

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 average 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-12110.html]SQL Snippets: MODEL Performance Tuning - Measure Width[/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-12110.html">SQL Snippets: MODEL Performance Tuning - Measure Width</a>

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

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

Revision Notes

Date Category Note
2007-05-09 Revision Renamed topic. Removed empty measure columns which did not have significant impact on the results. Added tests for VARCHAR2 and CLOB columns.