MODEL Performance Tuning

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;
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 ;
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


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.

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=]SQL Snippets: MODEL Clause - MODEL Performance Tuning[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="">SQL Snippets: MODEL Clause - MODEL Performance Tuning</a>

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

  • Link Text : SQL Snippets: MODEL Clause - MODEL Performance Tuning
  • URL (href):

Revision Notes

Date Category Note
2007-05-09 Updated Tutorial The section on MODEL Performance Tuning has been completely overhauled to provide a clearer and broader understanding of how various MODEL query types use memory.