Beiträge getaggt mit Performance Tuning

When is setting OPTIMIZER_INDEX_COST_ADJ appropriate?

Veröffentlicht von Uwe Hesse in TOI am Juli 22, 2009

The initialization parameter OPTIMIZER_INDEX_COST_ADJ has a certain popularity in the area of Oracle Database Performance Tuning. Especially, it can be used to make the Optimizer favor index access instead of full table scans. But that is not necessarily appropriate – in fact it can have very counterproductive effects on performance. Richard Foote has some very instructive postings about this, especially this one is impressive, in my view.

2 Kommentare

Reducing Buffer Busy Waits with Automatic Segment Space Management & Hash Partitioning

Veröffentlicht von Uwe Hesse in TOI am Juni 2, 2009

Last week, I was in Frankfurt (Germany) teaching about Performance Tuning & Partitioning in a Private Event for LHS Telecommunication. One demonstration that I developed for that event was about Reducing Buffer Busy Waits. One major reason why the wait event Buffer Busy Waits can occur is if many sessions are inserting at the same time in the same table. Large OLTP installations therefore will see it most likely. This can lead to a performance problem, especially if the site does not make use of Automatic Segment Space Management – a technique introduced in 9i and used by default since 10g. The old fashioned way to determine where to insert a new row in blocks before the high water mark uses Freelists. So if you are going with that „traditional“ technique with freelists (and PCTUSED), then your segment has one freelist by default, which leads to contention easy, because multiple simultaneous sessions that insert look at that one freelist and all pick the same Blocks to insert in. Here is an example:

SQL> create tablespace old datafile '/u01/app/oracle/oradata/orcl/old01.dbf'
     size 50m segment space management manual;

Tablespace created.

SQL> create table bbw_freelist (n number, name varchar2(50)) tablespace old;

Table created.

That table now uses (one) freelist and PCTUSED (40 by default) in order to determine what blocks may be used

for insert. The next steps are going to create 10 simultaneous sessions that insert 1 Million rows into the table.

SQL> create or replace procedure bufwait1 as
begin
for i in 1..100000 loop
insert into bbw_freelist values (i, 'BUFFER BUSY WAITS?');
end loop;
commit;
end;
/  2    3    4    5    6    7    8

Procedure created.

SQL> create or replace procedure do_bbw1 as
v_jobno number:=0;begin
FOR i in 1..10 LOOP
 dbms_job.submit(v_jobno,'bufwait1;', sysdate);
END LOOP;
commit;
end;
/  2    3    4    5    6    7    8

Procedure created.

SQL> exec do_bbw1

PL/SQL procedure successfully completed.


SQL> select count(*) from dba_jobs_running


COUNT(*)

----------

0

One easy way to discover Buffer Busy Waits is v$segment_statistics.


Of course, you may see them also in Statspack/AWR reports.


SQL> select sum(value) from v$segment_statistics
 where OBJECT_NAME='BBW_FREELIST' and statistic_name='buffer busy waits';

SUM(VALUE)
----------
 1623


Next we are doing exactly the same inserts with a table that uses Automatic Segment Space Management.


Simply speaking, ASSM achieves a better distribution of the new rows across multiple blocks if multiple


inserts occur at the same time. It is determined at the tablespace level:


SQL> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='USERS';

SEGMEN
------
AUTO

SQL> create table bbw_assm (n number, name varchar2(50)) tablespace users;

Table created.

create or replace procedure bufwait2 as
begin
for i in 1..100000 loop
insert into bbw_assm values (i, 'BUFFER BUSY WAITS?');
end loop;
commit;
end;
/

create or replace procedure do_bbw2 as
v_jobno number:=0;begin
FOR i in 1..10 LOOP   
 dbms_job.submit(v_jobno,'bufwait2;', sysdate);
END LOOP;
commit;
end;
/

SQL> exec do_bbw2

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_jobs_running;

 COUNT(*)
----------
 10

SQL> select count(*) from dba_jobs_running;

 COUNT(*)
----------
 0

SQL> select sum(value) from v$segment_statistics
 where OBJECT_NAME='BBW_ASSM' and statistic_name='buffer busy waits';

SUM(VALUE)
----------
 295


As you can see, ASSM reduced strongly the number of Buffer Busy Waits! But still there are some,


and still this may be a performance problem for very large OLTP sites. Right now, the table is


not partitioned, so we have only one „hot extent“ if the table is continually growing, all inserts


occur in the newest allocated extent – where they are distributed nicely via ASSM. If that same table


would be Hash Partitioned, then we could have multiple „hot extents“ further more reducing the contention:


SQL> create table bbw_hash (n number, name varchar2(50))tablespace users
 partition by hash (n) partitions 256;

Table created.

SQL> create or replace procedure bufwait3 as
begin
for i in 1..100000 loop
insert into bbw_hash values (i, 'BUFFER BUSY WAITS?');
end loop;
commit;
end;
/  2    3    4    5    6    7    8

Procedure created.

SQL> create or replace procedure do_bbw3 as
v_jobno number:=0;begin
FOR i in 1..10 LOOP
 dbms_job.submit(v_jobno,'bufwait3;', sysdate);
END LOOP;
commit;
end;
/  2    3    4    5    6    7    8

Procedure created.

SQL> exec do_bbw3

PL/SQL procedure successfully completed.

SQL> select sum(value) from v$segment_statistics
 where OBJECT_NAME='BBW_HASH' and statistic_name='buffer busy waits';

SUM(VALUE)
----------
 226


We observe a (in this case) moderate further decrease of the Buffer Busy Waits. That


is one major reason for hash partitioning: Fighting against contention. Another reason


is – as in other kinds of partitioning – the possibilty for Partiton Pruning, if the


partition key is part of the where-clause of our statement. Then the optimizer


implicitly knows what partitions to scan. Even in my (relatively tiny) two tables, that


effect is visible:


SQL> set timing on
SQL> select * from bbw_assm where n=4711;

 N NAME
---------- --------------------------------------------------
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?

10 rows selected.

Elapsed: 00:00:00.11

SQL> select * from bbw_hash where n=4711;

 N NAME
---------- --------------------------------------------------
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?
 4711 BUFFER BUSY WAITS?

10 rows selected.

Elapsed: 00:00:00.01

,

7 Kommentare

Restrict the usage of new execution plans with SQL Plan Baselines

Veröffentlicht von Uwe Hesse in TOI am Mai 19, 2009

We have several new features in the 11g version related to change management. One of them is SQL Plan Baselining. That means that you intend a very  conservative approach regarding the usage of new execution plans computed by the optimizer. In other words: You suspect that after a change, the newly computed execution plans may be bad and lead to a performance degradation.

Before the introduction of SQL Plan Baselines, you where able to fix the ‚good old execution plans‘ with hints or with stored outlines. Both have the disadvantage that they make it impossible for the optimizer to come up with even better execution plans than the ‚good old‘ ones. By using SQL Plan Baselines, you let the optimizer compute the new plans and you store them, with the possibility to later on test them and allow them for usage, if they have proved to be good. That  process is called evolvement.

I have developed a little scenario to demonstrate that. First we see a statement with the ‚good old‘ execution plan of ACCESS BY INDEX ROWID:

SQL> select channel_id,count(*) from sales group by channel_id;

CHANNEL_ID   COUNT(*)
---------- ----------
 1          1
 2    2064200
 4     947328
 3    4322623
 9      16592

SQL> create  bitmap index sales_bix on sales (channel_id) nologging;

Index created.

SQL> begin
       dbms_stats.gather_table_stats('ADAM','SALES',cascade=>true,
       method_opt=>'FOR ALL INDEXED COLUMNS SIZE 5');
     end;
     /

SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 1232.16

Execution Plan
----------------------------------------------------------
Plan hash value: 3388944652

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |     8 |  1107   (0)| 00:00:14 |
|   1 |  SORT AGGREGATE               |         |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | SALES   |  5774 | 46192 |  1107   (0)| 00:00:14 |
|   3 |    BITMAP CONVERSION TO ROWIDS|         |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | SALES_BIX       |       |            |          |
-----------------------------------------------------------------------------------------

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

 4 - access("CHANNEL_ID"=1)

Now I want to create a SQL Baseline for that statement with that execution plan.

One possibility to create it is the following:

SQL> show parameter baseline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL> select * from dba_sql_plan_baselines;

no rows selected

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

The following (repeating) statements in this session are getting recorded together with their execution plans:

SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 1232.16

SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 1232.16

SQL> alter session set optimizer_capture_sql_plan_baselines=false;

Session altered.

Following shows that there was one statement with one plan was recorded. This one plan

(the index access) is the only accepted one.

SQL> column sql_text format a29
SQL> select SQL_TEXT,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;

SQL_TEXT                      SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
----------------------------- ------------------------------ ------------------------------ --- --- ---
select sum(amount_sold) from  SYS_SQL_ec28978ecd0909c3       SYS_SQL_PLAN_cd0909c333c3099a  YES YES NO
sales where channel_id=1

Now we change the conditions, so that a Full Table Scan becomes attractive to the optimizer:

SQL> alter table sales nologging;
SQL> insert /*+ append */ into sales select PROD_ID,CUST_ID,TIME_ID,1,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD from sales;

7350744 rows created.

SQL> commit;

Commit complete.

SQL> delete from sales where channel_id=3;

4322623 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table sales enable row movement;

Table altered.

SQL> alter table sales shrink space cascade;

Table altered.

SQL> begin dbms_stats.gather_table_stats('ADAM','SALES',cascade=>true,
       method_opt=>'FOR ALL INDEXED COLUMNS SIZE 5');
     end;
     /

PL/SQL procedure successfully completed.

The majority of rows now has the channel_id 1.  The optimizer would like to do FTS now, but the baseline restricts it to the usage of the ‚good old‘ index access:

SQL> set autotrace on explain

SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 785647882

Execution Plan
----------------------------------------------------------
Plan hash value: 3388944652

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |     8 | 59594   (1)| 00:11:56 |
|   1 |  SORT AGGREGATE               |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | SALES     |  7340K|    56M| 59594   (1)| 00:11:56 |
|   3 |    BITMAP CONVERSION TO ROWIDS|           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | SALES_BIX |       |       |            |          |
-------------------------------------------------------------------------------------------

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

 4 - access("CHANNEL_ID"=1)

Note
-----
 - SQL plan baseline "SYS_SQL_PLAN_cd0909c333c3099a" used for this statement

The new plan, however, got recorded, but not accepted:

SQL> select SQL_TEXT,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;

SQL_TEXT                      SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
----------------------------- ------------------------------ ------------------------------ --- --- ---
select sum(amount_sold) from  SYS_SQL_ec28978ecd0909c3       SYS_SQL_PLAN_cd0909c333c3099a  YES YES NO
sales where channel_id=1

select sum(amount_sold) from  SYS_SQL_ec28978ecd0909c3       SYS_SQL_PLAN_cd0909c335032dee  YES  NO NO
sales where channel_id=1

An evolvement has to prove a strong improvement compared to the old

plan in order to get the new plan accepted for usage by the optimizer:

set serverout on
DECLARE
 report clob;
BEGIN
 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
 sql_handle => 'SYS_SQL_ec28978ecd0909c3',
 plan_name=>'SYS_SQL_PLAN_cd0909c335032dee'
 );
 dbms_output.put_line(report);
END;
/

-------------------------------------------------------------------------------
 Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
 SQL_HANDLE = SYS_SQL_ec28978ecd0909c3
 PLAN_NAME  = SYS_SQL_PLAN_cd0909c335032dee
 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
 VERIFY     = YES
 COMMIT     = YES

Plan: SYS_SQL_PLAN_cd0909c335032dee
-----------------------------------
 Plan was verified: Time used 14.7 seconds.
 Failed performance criterion: Compound improvement ratio <= 1.01.

 Baseline Plan      Test Plan     Improv. Ratio
 -------------      ---------     -------------
 Execution Status:        COMPLETE       COMPLETE
 Rows Processed:                 1              1
 Elapsed Time(ms):           10883           3750               2.9
 CPUTime(ms):                 9697           2959              3.28
 Buffer Gets:                49997          50091                 1
 Disk Reads:                 46911          47217               .99
 Direct Writes:                  0              0
 Fetches:                    46911           1089             43.08
 Executions:                     1              1

-------------------------------------------------------------------------------
 Report Summary
-------------------------------------------------------------------------------
Number of SQL
plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

PL/SQL procedure successfully completed.

Although an improvement is visible, it is not big enough to lead to acceptance of the

new plan. This SQL Plan Baseline approach is really conservative.

But we can force the acceptance:

set serverout on
DECLARE
 report clob;
BEGIN
 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
 sql_handle => 'SYS_SQL_ec28978ecd0909c3',
 plan_name=>'SYS_SQL_PLAN_cd0909c335032dee',
 verify=>'NO'
 );
 dbms_output.put_line(report);
END;
/
-------------------------------------------------------------------------------
 Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
 SQL_HANDLE = SYS_SQL_ec28978ecd0909c3
 PLAN_NAME  = SYS_SQL_PLAN_cd0909c335032dee
 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
 VERIFY     = NO
 COMMIT     = YES

Plan: SYS_SQL_PLAN_cd0909c335032dee
-----------------------------------
 Plan was changed to an accepted plan.

-------------------------------------------------------------------------------
 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 0.
Number of SQL plan baselines evolved: 1.

PL/SQL procedure successfully completed.

Now the optimizer is free to choose this accepted plan – or the other accepted ones.

SQL> select SQL_TEXT,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;

SQL_TEXT                      SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
----------------------------- ------------------------------ ------------------------------ --- --- ---
select sum(amount_sold) from  SYS_SQL_ec28978ecd0909c3       SYS_SQL_PLAN_cd0909c333c3099a  YES YES NO
sales where channel_id=1

select sum(amount_sold) from  SYS_SQL_ec28978ecd0909c3       SYS_SQL_PLAN_cd0909c335032dee  YES YES NO
sales where channel_id=1

SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 785647882

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     8 | 13795   (2)| 00:02:46 |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |  7340K|    56M| 13795   (2)| 00:02:46 |
----------------------------------------------------------------------------

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

 2 - filter("CHANNEL_ID"=1)

Note
-----
 - SQL plan baseline "SYS_SQL_PLAN_cd0909c335032dee" used for this statement

We can even fix one execution plan – for example for runtime comparison.

First we demand index access:

declare
 numplans pls_integer;
begin
 numplans:=
 DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SYS_SQL_ec28978ecd0909c3',
 plan_name=>'SYS_SQL_PLAN_cd0909c333c3099a',
 attribute_name=>'FIXED',
 attribute_value=>'YES');
 dbms_output.put_line(numplans);
end;
/

SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 785647882

Execution Plan
----------------------------------------------------------
Plan hash value: 3388944652

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |     8 | 59594   (1)| 00:11:56 |
|   1 |  SORT AGGREGATE               |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | SALES     |  7340K|    56M| 59594   (1)| 00:11:56 |
|   3 |    BITMAP CONVERSION TO ROWIDS|           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | SALES_BIX |       |       |            |          |
-------------------------------------------------------------------------------------------

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

 4 - access("CHANNEL_ID"=1)

Note
-----
 - SQL plan baseline "SYS_SQL_PLAN_cd0909c333c3099a" used for this statement

SQL> set autotrace off
SQL> set timing on
SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 785647882

Elapsed: 00:00:07.08

Now we demand Full Table Scan:

SQL> declare
 2     numplans pls_integer;
begin
 numplans:=
 DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SYS_SQL_ec28978ecd0909c3',
 plan_name=>'SYS_SQL_PLAN_cd0909c333c3099a',
 attribute_name=>'FIXED',
 attribute_value=>'NO');
 dbms_output.put_line(numplans);
end;
/  3    4    5    6    7    8    9   10   11   12
1

PL/SQL procedure successfully completed.

declare
 numplans pls_integer;
begin
 numplans:=
 DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SYS_SQL_ec28978ecd0909c3',
 plan_name=>'SYS_SQL_PLAN_cd0909c335032dee',
 attribute_name=>'FIXED',
 attribute_value=>'YES');
 dbms_output.put_line(numplans);
end;
/

SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 785647882

Elapsed: 00:00:02.04

As you see, the new execution plan is really faster in this case – it was the right decision to allow its usage.But first, we saw a proof of it during the evolvement. So we did not trust the optimizer blindly. We had a strong control over the change process!

Addendum: Please see this excellent series of Articles regarding SQL Plan Baselines from the Optimizer Development Group:

http://optimizermagic.blogspot.com/2009/01/plan-regressions-got-you-down-sql-plan.html

http://optimizermagic.blogspot.com/2009/01/sql-plan-management-part-2-of-4-spm.html

http://optimizermagic.blogspot.com/2009/01/sql-plan-management-part-3-of-4.html

http://optimizermagic.blogspot.com/2009/02/sql-plan-management-part-4-of-4-user.html

Second Addendum: Migrate Stored Outlines to SQL Plan Baselines – Why & How

http://blogs.oracle.com/optimizer/entry/how_do_i_migrate_stored

,

3 Kommentare