SQL> select value 2 from v$parameter 3 where name='_optim_peek_user_binds'; VALUE -------------------- FALSEI immediately expect this:
SQL> select sum(case when max_cnt > 2 then 1 else 0 end) histograms, 2 sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms 3 from ( 4 select table_name, max(cnt) max_cnt 5 from ( 6 select table_name, column_name, count(*) cnt 7 from dba_tab_histograms 8 group by table_name, column_name 9 ) group by table_name 10 ); HISTOGRAMS NO_HISTOGRAMS ---------- ------------- 1169 2494The above is an example from a real-world OLTP system running with bind peeking disabled. It is no surprise to me. An exception, you say? Here's another one . . .
SQL> select value 2 from v$parameter 3 where name='_optim_peek_user_binds'; VALUE -------------------- FALSE SQL> SQL> select sum(case when max_cnt > 2 then 1 else 0 end) histograms, 2 sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms 3 from ( 4 select table_name, max(cnt) max_cnt 5 from ( 6 select table_name, column_name, count(*) cnt 7 from dba_tab_histograms 8 group by table_name, column_name 9 ) group by table_name 10 ); HISTOGRAMS NO_HISTOGRAMS ---------- ------------- 304 521Here comes my déja vu. If you tell me that you disabled bind peeking, my immediate response will be "do you have a lot of unnecessary histograms?" On an OLTP system, there is no way that you need histograms on a third of your tables (and I can hardly think of any DSS system where this amount of histograms can be justified).
SQL> create table t as
2 select case when level <= 9900 then 0 else level-9900 end n,
3 rpad('*', 100, '*') v
4 from dual
5 connect by level <= 10000;
Table created
SQL> create index i_t_n on t (n);
Index created
SQL> exec dbms_stats.gather_table_stats(user, 't', method_opt => 'for columns n size 254', cascade => true);
PL/SQL procedure successfully completed
In other words, we have a table T with 10000 rows, where 9900 rows have N=0 and 100 rows have N from 1 to 100. The histogram tells Oracle about this data distribution:
SQL> select column_name, endpoint_number, endpoint_value 2 from user_tab_histograms 3 where table_name='T'; COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ---------- --------------- -------------- N 9900 0 N 9901 1 N 9902 2 N 9903 3 N 9904 4 N 9905 5 ...skipped for clarity... N 9997 97 N 9998 98 N 9999 99 N 10000 100 101 rows selectedThe above is known as a skewed data distribution, and a histogram can help Oracle to choose the right plan, depending on the value:
SQL> set autot traceonly explain
SQL> select * from t where n=0;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 531K| 42 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 9900 | 531K| 42 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=0)
SQL> select * from t where n=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2912310446
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 55 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_N | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=1)
That is, Oracle is able to choose an index range scan when N=1 (returns only one row) and do an FTS when N=0 (returns 9900 rows) -- perfect execution plans given the conditions. What does this have to do with bind peeking you ask? Well, imagine that you've submitted the following query:
select * from t where n=:n;We used a bind variable in place of a literal. On a hard parse, Oracle will peek at the value you've used for
:n, and will optimize the query as if you've submitted the same query with this literal instead. The problem is that, in 10G, bind variable peeking happens only on a hard parse, which means that all following executions will use the same plan, regardless of the bind variable value. This is easy enough to demonstrate:
SQL> variable n number; SQL> exec :n:=0; PL/SQL procedure successfully completed. SQL> set autot traceonly stat SQL> select * from t where n=:n; 9900 rows selected. Statistics ---------------------------------------------------------- 982 recursive calls 0 db block gets 951 consistent gets 0 physical reads 0 redo size 106664 bytes sent via SQL*Net to client 7599 bytes received via SQL*Net from client 661 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 9900 rows processed SQL> exec :n:=1; PL/SQL procedure successfully completed. SQL> select * from t where n=:n; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 155 consistent gets 0 physical reads 0 redo size 476 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedYou can see that both executions used the same cursor with a full table scan:
SQL> select sql_id, executions, child_number
2 from v$sql
3 where sql_text = 'select * from t where n=:n';
SQL_ID EXECUTIONS CHILD_NUMBER
------------- ---------- ------------
g2n32un6t1c55 2 0
SQL> select * from table(dbms_xplan.display_cursor('g2n32un6t1c55'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID g2n32un6t1c55, child number 0
-------------------------------------
select * from t where n=:n
Plan hash value: 1601196873
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 42 (100)| |
|* 1 | TABLE ACCESS FULL| T | 9900 | 531K| 42 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=:N)
18 rows selected.
Both queries would use an index range scan if I had started my example with exec :n:=1 instead of exec :n:=0. Now, if 99% of your queries are not interested in querying the above table with :N:=0, then you want the plan with an index range scan because it will provide optimal performance most of the time, while resulting in suboptimal performance for only 1% of executions. With the histogram in place, one day you will be unlucky enough to have Oracle hard parse the query with a bind variable value of 0, which will force everyone else to use an FTS (as was demonstrated above), which in turn will result an abysmal performances for 99% of executions (until the next hard parse when you might get lucky again). And if you have a system where a third of the tables have histograms on them then -- I think you probably get the idea now.
SQL> exec dbms_stats.gather_table_stats(user, 't', method_opt => 'for columns n size 1', cascade => true); PL/SQL procedure successfully completedOracle will no longer have distribution information, and you'll get the same plan regardless of the value:
SQL> select * from t where n=0;
Execution Plan
----------------------------------------------------------
Plan hash value: 2912310446
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 5445 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 99 | 5445 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_N | 99 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=0)
SQL> select * from t where n=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2912310446
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 5445 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 99 | 5445 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_N | 99 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=1)
No more surprises! This also means that you'll get the same plan regardless of the bind variable value during a hard parse. However, the real question is . . .
SQL> select dbms_stats.get_param('method_opt') method_opt from dual;
METHOD_OPT
--------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
The SIZE ... part controls histograms collection. You can get the definition of AUTO in the Oracle documentation:
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.Well, as it says -- Oracle will decide on which columns to collect histograms. Not you. As a result, every day when this jobs runs, you might be presented with something new to make your life more interesting.
SIZE AUTO is doing such a bad job?AUTO tends to be a "lowest common denominator". For example, if you have an OLTP system, chances are you don't need histograms at all (apart from on a couple of tables, maybe). But how can DBMS_STATS know which type of a system do you run? OLTP, DWH, or mixed? Or maybe you're doing OLTP five days per week and DWH on a weekend? DBMS_STATS tries to use some heuristics to come up with an answer, but that's why two days of DWH on a weekend can completely screw up your OLTP activity for the rest of the week. DBMS_STATS just doesn't have enough information. We humans do.
REPEAT.
REPEAT: Collects histograms only on the columns that already have histograms.SQL> exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed
FOR ALL COLUMNS SIZE 1 clause), and manually add them when you decide that you really need one. The number of times I have had to go back to add histogram after a complete wipe out is surprisingly low -- much lower than number of surprises histograms were causing on these systems, and those systems never had any bind peeking surprises due to the excessive amount of unnecessary histograms.
select count(*) from table was running slow, and added that, "maybe we should collect histograms on that table." How on earth will a histogram help you to run this query faster? Many histograms on your system might be a result of a complete misunderstanding how histograms work, what they do, and, more important, what they do not.
METHOD_OPT => 'FOR COLUMNS X SIZE 1' and NO_INVALIDATE => FALSE. This will cause all dependent cursors to be invalidated immediately after stats have been gathered. Sometimes, however, you don't have enough time to understand what caused a problem (or you simply don't have time to regather the stats) and, if probability theory is on your side (chances for a good peeking are much higher), all you have to do to invalidate dependent cursors is to create comment on a table:
SQL> select n from t where n=:n; N ---------- 1 SQL> select executions, invalidations 2 from v$sql 3 where sql_text = 'select n from t where n=:n'; EXECUTIONS INVALIDATIONS ---------- ------------- 2 0 SQL> comment on table t is ''; Comment created. SQL> select n from t where n=:n; N ---------- 1 SQL> select executions, invalidations 2 from v$sql 3 where sql_text = 'select n from t where n=:n'; EXECUTIONS INVALIDATIONS ---------- ------------- 1 1The above will invalidate cursors that depend only on a specific table, thus significantly decreasing the risk of side effects.
AUTO to REPEAT and you are done. You'll have to create all required histograms manually but that's our intentional goal: to do histogram creation in a controllable and predictable manner. For existing systems that are plagued by gazillions of histograms, you'll have to figure out what to do. Wiping out histograms for entire database will do for queries with bind variables pretty much the same as turning off bind peeking. I still think, however, that disabling bind peeking in this situation is the wrong choice, since (a) you still run the risk of getting unpredictable results from queries with literals; and (b) you will be doing tons more work during statistics collection, since histogram computation is expensive.
Making sure new histograms appear in a controllable and predictable manner will be your first step in building a predictable environment.
Ready to optimize your Oracle Database for the future?