Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing

Sep19
2011 7 Comments Written by Christian Antognini

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?

Posted in 11gR1, 11gR2, Query Optimizer

optimizer_secure_view_merging and VPD

Sep11
2011 10 Comments Written by Christian Antognini

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.

Posted in 10gR2, 11gR1, 11gR2, Query Optimizer, TOP

ITL Waits – Changes in Recent Releases (script)

Jun20
2011 3 Comments Written by Christian Antognini

A reader of this blog, Paresh, asked me how I was able to find out the logic behind ITL waits without having access to Oracle code. My reply was: I wrote a test case that reproduce ITL waits and a piece of code that monitors them.

Since other readers might be interested, here is the shell script I wrote. Notice that it takes four parameters as input: user name, password, SID, and how long it has to wait in the monitoring phase.

#!/bin/sh

user=$1
password=$2
sid=$3
wait=$4

#
# Setup test environment
#

sqlplus -s $user/$password@$sid < $i.$sid.sql
  if [[ $i = 6 ]]
  then
    # make sure that the other processes have locked one row
    echo 'execute dbms_lock.sleep(1)' >> $i.$sid.sql
  fi
  echo 'UPDATE t SET n = n WHERE n =' $i ';' >> $i.$sid.sql
  echo 'SET TERMOUT OFF' >> $i.$sid.sql
  if [[ $i < 6 ]]
  then
    echo 'execute dbms_lock.sleep(' $wait ')' >> $i.$sid.sql
  fi
  sqlplus -s $user/$password@$sid @$i.$sid.sql &
done

#
# Monitor ITL wait
#

sqlplus -s $user/$password@$sid < l_blocking_session_prev
         OR l_blocking_session_prev IS NULL
         OR i = c_iterations
      THEN
        dbms_output.put_line(to_char((i-1)*c_sleep,'000000')||
                             ' blocking_session='||nvl(l_blocking_session_prev,l_blocking_session_curr)||
                             ' sleep='||nvl(l_seconds_in_wait_prev,l_seconds_in_wait_curr));
      END IF;
      l_blocking_session_prev := l_blocking_session_curr;
      l_seconds_in_wait_prev := l_seconds_in_wait_curr;
      dbms_lock.sleep(c_sleep);
    END LOOP;
  END;
  /
END

#
# Cleanup
#

for i in 1 2 3 4 5 6
do
  rm $i.$sid.sql
done

sleep 5

sqlplus -s $user/$password@$sid <
The outputs I got are the following:

10.2.0.4

000000 blocking_session=136 sleep=0
000005 blocking_session=136 sleep=6
000010 blocking_session=140 sleep=3
000015 blocking_session=152 sleep=6
000020 blocking_session=159 sleep=6
029995 blocking_session=158 sleep=29979

10.2.0.5

000000 blocking_session=158 sleep=0
000001 blocking_session=158 sleep=0
000002 blocking_session=152 sleep=2
000003 blocking_session=141 sleep=0
000004 blocking_session=148 sleep=0
000005 blocking_session=140 sleep=3
000007 blocking_session=158 sleep=0
000009 blocking_session=152 sleep=3
000011 blocking_session=141 sleep=3
000013 blocking_session=148 sleep=0
000015 blocking_session=140 sleep=3
000019 blocking_session=158 sleep=3
000023 blocking_session=152 sleep=6
000027 blocking_session=141 sleep=3
000031 blocking_session=148 sleep=3
000035 blocking_session=140 sleep=6
000040 blocking_session=158 sleep=3
000045 blocking_session=152 sleep=6
000050 blocking_session=141 sleep=6
000054 blocking_session=148 sleep=3
000062 blocking_session=140 sleep=9
000067 blocking_session=158 sleep=6
000072 blocking_session=152 sleep=3
000077 blocking_session=141 sleep=6
000082 blocking_session=148 sleep=6
000098 blocking_session=140 sleep=15
000103 blocking_session=158 sleep=6
000108 blocking_session=152 sleep=3
000113 blocking_session=141 sleep=6
000118 blocking_session=148 sleep=6
000149 blocking_session=140 sleep=30
000154 blocking_session=158 sleep=6
000159 blocking_session=152 sleep=6
000164 blocking_session=141 sleep=3
000169 blocking_session=148 sleep=6
000232 blocking_session=140 sleep=63
000237 blocking_session=158 sleep=6
000242 blocking_session=152 sleep=6
000247 blocking_session=141 sleep=3
000252 blocking_session=148 sleep=6
000379 blocking_session=140 sleep=129
000383 blocking_session=158 sleep=3
000388 blocking_session=152 sleep=6
000393 blocking_session=141 sleep=6
000398 blocking_session=148 sleep=3
000651 blocking_session=140 sleep=258
000656 blocking_session=158 sleep=3
000661 blocking_session=152 sleep=6
000666 blocking_session=141 sleep=6
000671 blocking_session=148 sleep=3
001177 blocking_session=140 sleep=514
001182 blocking_session=158 sleep=6
001187 blocking_session=152 sleep=3
001192 blocking_session=141 sleep=6
001197 blocking_session=148 sleep=6
014218 blocking_session=140 sleep=13184
029995 blocking_session=140 sleep=28788

11.1.0.6

000000 blocking_session=146 sleep=0
000005 blocking_session=146 sleep=5
000010 blocking_session=129 sleep=5
000015 blocking_session=141 sleep=5
000020 blocking_session=126 sleep=5
029995 blocking_session=132 sleep=29978

11.1.0.7

000000 blocking_session=136 sleep=0
000005 blocking_session=136 sleep=5
000010 blocking_session=140 sleep=5
000015 blocking_session=132 sleep=5
000020 blocking_session=131 sleep=5
029995 blocking_session=134 sleep=29979

11.2.0.1

000000 blocking_session=131 sleep=0
000001 blocking_session=131 sleep=1
000002 blocking_session=133 sleep=1
000003 blocking_session=196 sleep=1
000004 blocking_session=67 sleep=1
000005 blocking_session=69 sleep=1
000007 blocking_session=131 sleep=2
000009 blocking_session=133 sleep=2
000011 blocking_session=196 sleep=2
000013 blocking_session=67 sleep=2
000015 blocking_session=69 sleep=2
000019 blocking_session=131 sleep=4
000023 blocking_session=133 sleep=4
000027 blocking_session=196 sleep=4
000031 blocking_session=67 sleep=4
000035 blocking_session=69 sleep=4
000040 blocking_session=131 sleep=5
000045 blocking_session=133 sleep=5
000050 blocking_session=196 sleep=5
000054 blocking_session=67 sleep=5
000062 blocking_session=69 sleep=8
000067 blocking_session=131 sleep=5
000072 blocking_session=133 sleep=5
000077 blocking_session=196 sleep=5
000082 blocking_session=67 sleep=5
000098 blocking_session=69 sleep=16
000103 blocking_session=131 sleep=5
000108 blocking_session=133 sleep=5
000113 blocking_session=196 sleep=5
000118 blocking_session=67 sleep=5
000149 blocking_session=69 sleep=32
000154 blocking_session=131 sleep=5
000159 blocking_session=133 sleep=5
000164 blocking_session=196 sleep=5
000169 blocking_session=67 sleep=5
000232 blocking_session=69 sleep=64
000237 blocking_session=131 sleep=5
000242 blocking_session=133 sleep=5
000247 blocking_session=196 sleep=5
000252 blocking_session=67 sleep=5
000379 blocking_session=69 sleep=128
000383 blocking_session=131 sleep=5
000388 blocking_session=133 sleep=5
000393 blocking_session=196 sleep=5
000398 blocking_session=67 sleep=5
000651 blocking_session=69 sleep=256
000656 blocking_session=131 sleep=5
000661 blocking_session=133 sleep=5
000666 blocking_session=196 sleep=5
000671 blocking_session=67 sleep=5
001177 blocking_session=69 sleep=512
001182 blocking_session=131 sleep=5
001187 blocking_session=133 sleep=5
001192 blocking_session=196 sleep=5
001196 blocking_session=67 sleep=5
029995 blocking_session=69 sleep=28787

Posted in 10gR1, 10gR2, 11gR1, 11gR2, 9iR2