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.