Striving for Optimal Performance

The STATISTICS_LEVEL parameter controls a bunch of features. In addition to the documentation, also the V$STATISTICS_LEVEL view provides a list of the ones it controls.

SQL> SELECT statistics_name, description, activation_level
  2  FROM v$statistics_level
  3  ORDER BY 3 DESC, 1;

STATISTICS_NAME                        DESCRIPTION                                                  ACTIVATION_LEVEL
-------------------------------------- ------------------------------------------------------------ ----------------
Active Session History                 Monitors active session activity using MMNL                  TYPICAL
Adaptive Thresholds Enabled            Controls if Adaptive Thresholds should be enabled            TYPICAL
Automated Maintenance Tasks            Controls if Automated Maintenance should be enabled          TYPICAL
Bind Data Capture                      Enables capture of bind values used by SQL statements        TYPICAL
Buffer Cache Advice                    Predicts the impact of different cache sizes on number of    TYPICAL
                                       physical reads
Global Cache Statistics                RAC Buffer Cache statistics                                  TYPICAL
Longops Statistics                     Enables Longops Statistics                                   TYPICAL
MTTR Advice                            Predicts the impact of different MTTR settings on number of  TYPICAL
                                       physical I/Os
Modification Monitoring                Enables modification monitoring                              TYPICAL
PGA Advice                             Predicts the impact of different values of pga_aggregate_tar TYPICAL
                                       get on the performance of memory intensive SQL operators
Plan Execution Sampling                Enables plan lines sampling                                  TYPICAL
SQL Monitoring                         Controls if SQL Monitoring should be enabled                 TYPICAL
Segment Level Statistics               Enables gathering of segment access statistics               TYPICAL
Shared Pool Advice                     Predicts the impact of different values of shared_pool_size  TYPICAL
                                       on elapsed parse time saved
Streams Pool Advice                    Predicts impact on Streams perfomance of different  Streams  TYPICAL
                                       pool sizes
Threshold-based Alerts                 Controls if Threshold-based Alerts should be enabled         TYPICAL
Time Model Events                      Enables Statics collection for time events                   TYPICAL
Timed Statistics                       Enables gathering of timed statistics                        TYPICAL
Ultrafast Latch Statistics             Maintains statistics for ultrafast latches in the fast path  TYPICAL
Undo Advisor, Alerts and Fast Ramp up  Transaction layer manageability features                     TYPICAL
V$IOSTAT_* statistics                  Controls if I/O stats in v$iostat_ should be enabled         TYPICAL
Plan Execution Statistics              Enables collection of plan execution statistics              ALL
Timed OS Statistics                    Enables gathering of timed operating system statistics       ALL

Something that I learned only recently is that STATISTICS_LEVEL also controls cardinality feedback and adaptive cursor sharing. This fact, according to me, is neither (clearly) documented nor pointed out by the information provided by V$STATISTICS_LEVEL. In any case, when STATISTICS_LEVEL is set to BASIC at the system level both features are disabled. Interestingly, an ALTER SESSION SET STATISTICS_LEVEL = TYPICAL it is not enough to enable them… For adaptive cursor sharing it is possible to use the BIND_AWARE hint, though.

Note that I never advise to set STATISTICS_LEVEL at the system level to a value that is different from the default (TYPICAL). Probably for this reason I didn’t notice its impact for such a long time…

In any case I find it a bit disappointing that this information is not clearly stated somewhere. Or I’m the only one that was not aware?

At page 189 of TOP I wrote the following piece of text:

In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could be suboptimal as a result. For this reason, if you are not using views for security purposes, it is better to set this initialization parameter to FALSE.

What I didn’t consider when I wrote it, it is the implication of predicate move-around related to Virtual Private Database (VPD). In fact, as described in the documentation, that parameter controls view merging as well as predicate move-around.

To point out what the impact is, let’s have a look to an example based on the description provided in TOP:

• Say you have a very simple table with one primary key and two more columns.

CREATE TABLE t (
  id NUMBER(10) PRIMARY KEY,
  class NUMBER(10),
  pad VARCHAR2(10)
);

• For security reasons, you define the following policy. Notice the filter that is applied with the function to partially show the content of the table. How this function is implemented and what it does exactly is not important.

CREATE OR REPLACE FUNCTION s (schema IN VARCHAR2, tab IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
  RETURN 'f(class) = 1';
END;
/

BEGIN
  dbms_rls.add_policy(object_schema   => 'U1',
                      object_name     => 'T',
                      policy_name     => 'T_SEC',
                      function_schema => 'U1',
                      policy_function => 'S');
END;
/

• Now let’s say that a user who has access to the table creates the following PL/SQL function. As you can see, it will just display the value of the input parameters through a call to the package dbms_output.

CREATE OR REPLACE FUNCTION spy (id IN NUMBER, pad IN VARCHAR2) RETURN NUMBER AS
BEGIN
  dbms_output.put_line('id=' || id || ' pad=' || pad);
  RETURN 1;
END;
/

• With the initialization parameter optimizer_secure_view_merging set to FALSE, you can run two test queries. Both return only the values that the user is allowed to see. In the second one, however, you are able to see data that you should not be able to access.

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5
  4  AND spy(id, pad) = 1;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=2 pad=XOZnqYRJwI
id=3 pad=nlGfGBTxNk
id=4 pad=AszBGEUGEL
id=5 pad=qTSRnFjRGb

• With the initialization parameter optimizer_secure_view_merging set to TRUE, the second query returns the following output. As you can see, the function and the query display the same data.

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5
  4  AND spy(id, pad) = 1;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=4 pad=AszBGEUGEL

The execution plans that are used in the two situations are the following. As you can see only the second one guarantee that the policy defined via VPD is applied before the predicate based on the SPY function. Interestingly enough the other predicate based on the ID column is applied before the one of the policy. Hence, the query optimizer can choose an access path that takes advantage of the primary key.

• optimizer_secure_view_merging = FALSE

---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T           |
|*  2 |   INDEX RANGE SCAN          | SYS_C009970 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("SPY"("ID","PAD")=1 AND "F"("CLASS")=1))
   2 - access("ID">=1 AND "ID"<=5)

• optimizer_secure_view_merging = TRUE

----------------------------------------------------
| Id  | Operation                    | Name        |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|*  1 |  VIEW                        | T           |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T           |
|*  3 |    INDEX RANGE SCAN          | SYS_C009971 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SPY"("ID","PAD")=1)
   2 - filter("F"("CLASS")=1)
   3 - access("ID">=1 AND "ID"<=5)

Based on these observations, the summary that is provided by TOP at page 189 should be amended as follows:

In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging or predicate move-around could lead to security issues. If this is the case, they will not be performed, and performance could be suboptimal as a result. For this reason, if you are not using views or VPD for security purposes, it is better to set this initialization parameter to FALSE.