At the beginning of this section I mentioned that MODEL query performance can behave dramatically different than non-MODEL queries. In this topic we support that statement with tests that illustrate this difference and highlight the role session memory plays.
Non-MODEL Queries
Before we examine the performance characteristics of MODEL queries, let's create a baseline of non-MODEL query metrics.
Testing 1 Row select upper(m_1) from t_100k where key <= 1 ;
Testing 19,000 Rows select upper(m_1) from t_100k where key <= 19000 ;
Testing 38,000 Rows select upper(m_1) from t_100k where key <= 38000 ;
Testing 38,500 Rows select upper(m_1) from t_100k where key <= 38500 ;
Testing 39,000 Rows select upper(m_1) from t_100k where key <= 39000 ;
Testing 100,000 Rows select upper(m_1) from t_100k where key <= 100000 ;
Results
19,000 38,000 38,500 39,000 100,000
Metric 1 Row Rows Rows Rows Rows Rows
------------------------- ------- ------- ------- ------- ------- ---------
Elapsed Time (1/100 sec) 4 17 30 29 33 68
Latch Gets Total 573 10,258 20,118 20,229 20,480 36,773
session pga memory max 262,144 262,144 262,144 262,144 262,144 262,144
session uga memory max 261,964 261,964 261,964 261,964 261,964 261,964
redo size 2,688 2,640 2,744 2,684 2,684 2,684
bytes received via SQL*Ne 1,386 15,318 29,255 29,618 29,992 74,719
bytes sent via SQL*Net to 1,083 256,860 512,769 519,460 526,247 1,347,655
session logical reads 50 3,385 6,867 6,895 6,929 10,820
user calls 13 1,279 2,546 2,579 2,613 6,679
consistent gets from cach 11 3,346 6,826 6,856 6,890 10,781
consistent gets 11 3,346 6,826 6,856 6,890 10,781
SQL*Net roundtrips to/fro 9 1,275 2,542 2,575 2,609 6,675
buffer is not pinned coun 5 4,572 0 0 0 0
no work - consistent read 3 3,338 6,810 6,840 6,874 10,763
table fetch by rowid 2 19,001 0 0 0 0
buffer is pinned count 0 34,695 0 0 0 0
table scan blocks gotten 0 0 6,810 6,840 6,874 10,763
table scan rows gotten 0 0 155,752 156,442 157,224 246,667
See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
Observations
Note how the "session % memory max" values stay constant regardless of how many rows are processed. Note also that elapsed time increases somewhat linearly with the number of rows processed. These factors will behave differently in the next series of tests.
MODEL Queries
Now let's look at equivalent queries written using MODEL clause features.
Testing 1 Row select m_1 from t_100k where key <= 1 model dimension by( key ) measures( m_1 ) rules ( m_1[any] = upper(m_1[cv()]) ) ;
Testing 19,000 Rows select m_1 from t_100k where key <= 19000 model dimension by( key ) measures( m_1 ) rules ( m_1[any] = upper(m_1[cv()]) ) ;
Testing 38,000 Rows select m_1 from t_100k where key <= 38000 model dimension by( key ) measures( m_1 ) rules ( m_1[any] = upper(m_1[cv()]) ) ;
Testing 38,500 Rows select m_1 from t_100k where key <= 38500 model dimension by( key ) measures( m_1 ) rules ( m_1[any] = upper(m_1[cv()]) ) ;
Testing 39,000 Rows select m_1 from t_100k where key <= 39000 model dimension by( key ) measures( m_1 ) rules ( m_1[any] = upper(m_1[cv()]) ) ;
Results
19,000 38,000 38,500 39,000
Metric 1 Row Rows Rows Rows Rows
------------------------- ------- ---------- ---------- ---------- -----------
Elapsed Time (1/100 sec) 153 28 60 612 5,987
Latch Gets Total 2,830 4,955 14,968 21,816 26,676
session pga memory max 262,144 19,660,800 44,433,408 40,435,712 40,239,104
session uga memory max 261,964 19,054,172 44,368,180 39,242,128 38,970,796
bytes received via SQL*Ne 1,386 15,318 29,255 29,618 29,992
bytes sent via SQL*Net to 1,076 256,853 512,762 519,453 526,240
session logical reads 48 908 4,441 4,441 4,441
user calls 13 1,279 2,546 2,579 2,613
SQL*Net roundtrips to/fro 9 1,275 2,542 2,575 2,609
consistent gets from cach 9 869 4,402 4,402 4,402
consistent gets 9 869 4,402 4,402 4,402
CPU used when call starte 3 19 46 67 135
DB time 3 19 46 595 5,969
CPU used by this session 3 19 46 67 135
buffer is not pinned coun 2 828 0 0 0
buffer is pinned count 2 37,174 0 0 0
table fetch by rowid 2 19,001 0 0 0
no work - consistent read 1 861 4,386 4,386 4,386
physical read bytes 0 0 0 39,362,560 570,630,144
physical writes 0 0 0 4,805 5,456
physical writes direct 0 0 0 4,805 5,456
physical write IO request 0 0 0 155 176
physical write bytes 0 0 0 39,362,560 44,695,552
physical writes non check 0 0 0 4,805 5,456
physical reads direct tem 0 0 0 4,805 69,657
physical writes direct te 0 0 0 4,805 5,456
table scan rows gotten 0 0 100,000 100,000 100,000
table scan blocks gotten 0 0 4,386 4,386 4,386
physical read total multi 0 0 0 155 2,247
physical read total IO re 0 0 0 155 2,247
physical read IO requests 0 0 0 155 2,247
physical reads direct 0 0 0 4,805 69,657
physical reads 0 0 0 4,805 69,657
physical write total byte 0 0 0 39,362,560 44,695,552
physical write total mult 0 0 0 155 176
user I/O wait time 0 0 0 533 5,832
19,000 38,000 38,500 39,000
Metric 1 Row Rows Rows Rows Rows
------------------------- ------- ---------- ---------- ---------- -----------
physical read total bytes 0 0 0 39,362,560 570,630,144
physical write total IO r 0 0 0 155 176
See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
Observations
These results show that MODEL queries can consume far more session memory than equivalent non-MODEL queries. As well, after a certain maximum amount of available session memory was reached elapsed query times and I/O increased exponentially.
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.