The performance characteristics of SQL MODEL queries can be quite different from the non-MODEL queries many developers are accustomed to. In some cases MODEL query performance can even be so poor that it renders the query unusable. One of the keys to writing efficient and scalable MODEL queries seems to be keeping session memory use to a minimum. In this section we will see how various query component affect session memory usage and learn ways to reduce it without changing the end result.
Unfortunately, the inner workings of MODEL queries are not documented. We can, however, gain some practical insight into the process by testing various query configurations and then examining the memory usage of each test. Memory usage will be determined by examining the "session pga memory max" and "session uga memory max" performance statistics from the data dictionary's V$MYSTAT view.
Test Data
The queries in this section use the following test data.
desc t_1k Name Null? Type ---------------------------------------------- -------- ------------------------------- KEY NOT NULL NUMBER(38) KEY2 VARCHAR2(10) M_1 VARCHAR2(4000) M_2 VARCHAR2(4000)
select count(*) from t_1k;
COUNT(*)
----------
1000
select * from t_1k sample(1) ;
KEY KEY2 M_1 M_2
---------- ---------- --- ---
274 274 a b
436 436 a b
558 558 a b
586 586 a b
599 599 a b
667 667 a b
696 696 a b
762 762 a b
793 793 a b
805 805 a b
862 862 a b
desc t_100k Name Null? Type ---------------------------------------------- -------- ------------------------------- KEY NOT NULL NUMBER KEY_1 CHAR(100) KEY_2 CHAR(100) KEY_3 CHAR(100) M_1 VARCHAR2(1000)
select count(*) from t_100k ;
COUNT(*)
----------
100000
select * from t_100k sample(0.01) ;
KEY KEY_1 KEY_2 KEY_3 M_1
---------- ----- ----- ----- ---
1975 1975 975 1000 a
4155 4155 155 4000 a
19424 19424 424 19000 a
26248 26248 248 26000 a
29602 29602 602 29000 a
43886 43886 886 43000 a
69810 69810 810 69000 a
95798 95798 798 95000 a
97164 97164 164 97000 a
Caveats
The content in this section was written for and tested against Oracle 10g. The findings may not apply to future Oracle versions.
Large test table datatypes were deliberately chosen to emphasize the differences between various queries in the tests to follow. Readers with queries that use smaller datatypes than these may see smaller memory figures than those shown here. Readers with queries that use more columns or more rows than these may see larger memory figures.