Oracle Tips and Tricks — David Fitzjarrell

April 28, 2017

Adjusting Things

Filed under: General — dfitzjarrell @ 10:53

"The only thing you can do easily is be wrong, and that's hardly worth the effort." 
-- Norton Juster, The Phantom Tollbooth

Query tuning is both an art and a science and, because of this, usually occurs on a case-by-case basis. There may be occasions, though, where a series of queries, similar in structure and differing in predicate values, need to be tuned. Rather than go through each and every query, setting up a ‘standard’ SQL Profile (where force_match=FALSE) and enabling it, it may be easier to alter the setting for optimizer_index_cost_adj (presuming indexes are in use) so the index scans/index access paths are more ‘favorable’ than a table scan. Yet on the other hand it might be worth the effort to create the profile with force_match=TRUE, to cover all queries with the same SQL text outside of the literal values supplied. Let’s look at examples of why these might be good plans of attack (and a case where it wouldn’t be).

The optimizer, in its infinite wisdom and using current statistics, computes the cost of access for every table and associated index touched by the problem query. Sometimes the index cost is ever so slightly greater than that for a table scan and, as a result, the index path gets scrapped in favor of the table scan. In such cases nudging the optimizer in the ‘proper’ direction is as simple as changing the value on the optimizer_index_cost_adj parameter, which defaults to 100. The value you need should be chosen carefully, hopefully so that the queries you want affected will be affected and most others won’t. In our example let’s look at a very small portion of the level 2 10053 trace; the names were changed to protect the ‘innocent’:


 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: PLORGENFLOTZ_PK
    resc_io: 3.000000  resc_cpu: 342602
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 3.014879  Resp: 3.014879  Degree: 1
...
  Best:: AccessPath: TableScan
         Cost: 2.006465  Degree: 1  Resp: 2.006465  Card: 228.000000  Bytes: 0.000000

Notice the cost of the index access is just slightly higher than the cost of a full table scan so the optimizer passes up that option and chooses the table scan. This is where optimizer_index_cost_adj can change things. If, for example, we set optimizer_index_cost_adj to 50 the cost of the index access will go down:


 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (IndexOnly)
    Index: PLORGENFLOTZ_PK
    resc_io: 1.000000  resc_cpu: 63786
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 1.001385  Resp: 1.001385  Degree: 0
    SORT ressource         Sort statistics
      Sort width:        5989 Area size:     1048576 Max Area size:  1046896640
      Degree:               1
      Blocks to Sort: 1 Row size:     21 Total Rows:            243
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0.000000      Total CPU sort cost: 23112595
      Total Temp space used: 0
...
  Best:: AccessPath: IndexRange
  Index: PLORGENFLOTZ_PK
         Cost: 1.507553  Degree: 1  Resp: 1.507553  Card: 34.346487  Bytes: 0.000000

The calculated cost of using this index has been cut in half (which should be expected when setting optimizer_index_cost_adj to 50) so now the optimizer elects to take the index range scan as the best possible path. Notice that the optimizer_index_cost_adj isn’t applied until the actual cost has been calculated; the total cost is adjusted by the percentage provided in the optimizer_index_cost_adj setting as the final step. Looking at the final execution plan we see the following steps:


...
| 44  |                   TABLE ACCESS BY INDEX ROWID BATCHED         | PLORGENFLOTZ_TBL              |    34 |  1326 |     2 |  00:00:01 |      |      |     |        |       |
| 45  |                    INDEX RANGE SCAN                           | PLORGENFLOTZ_PK               |   243 |       |     1 |  00:00:01 |      |      |     |        |       |
...

which replaced this step in the plan where optimizer_index_cost_adj was unmodified:


...
| 111 |                        TABLE ACCESS FULL                           | PLORGENFLOTZ_TBL            |    32 |  1248 |     2 |  00:00:01 |      |      |           |       |
...

Other path steps were changed in addition to those listed here and the overall execution plan was shortened, as evidenced by the step numbers from the included plan excerpts.

Careful planning and testing needs to be done before settling on a value for optimizer_index_cost_adj as it will affect all index access calculations and could change acceptable plans using table scans to less-than-desirable plans forcing index access. The value of 50 used here was chosen after several runs using smaller and smaller settings until the desired plans were obtained. Being aggressive isn’t necessarily best when setting optimizer_index_cost_adj as extremely small settings, such as 20 or lower, may make some queries run very fast and make some others very slow (because index access isn’t always the best path to choose). Never make such changes on a production system without first investigating the effects in your test environment. The user community does not like unpleasant surprises.

Yet another way to tune a set of queries that differ only in literal values is by using a SQL Profile with force_match set to TRUE. This works by replacing the literal values with system-generated bind variables before the signature is generated; any similar query with different literal values will be associated with the same profile as the original query and thus will use the same execution plan. This presumes that ONLY literal values are in the source query statement; any additional bind variables present will generate a new signature that won’t match the signature associated with the profile and the known ‘good’ plan won’t be selected.

Let’s look at an example of that in action:


SQL>
SQL>--
SQL>-- Create table
SQL>--
SQL>create table plan_test(
  2  id      number,
  3  class   number,
  4  data    varchar2(45),
  5  cr_dt   date);

Table created.

SQL>
SQL>--
SQL>-- Load table
SQL>--
SQL>begin
  2  	     for i in 1..500000 loop
  3  		     insert into plan_test
  4  		     values(i, mod(i,337)+1, 'Value '||i, sysdate+mod(i,337));
  5  	     end loop;
  6
  7  	     for i in 500001..1000000 loop
  8  		     if mod(i,2)=0 then
  9  			     insert into plan_test
 10  			     values(3999, 3999, 'Value '||i, sysdate+mod(i,37));
 11  		     else
 12  			     insert into plan_test
 13  			     values(7734, 1234, 'Value '||i, sysdate+mod(i,37));
 14  		     end if;
 15  	     end loop;
 16
 17  	     commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL>--
SQL>-- Add an index
SQL>--
SQL>create index plan_test_idx on plan_test(class);

Index created.

SQL>
SQL>--
SQL>-- Compute stats and histograms
SQL>--
SQL>exec dbms_stats.gather_table_stats(user, 'PLAN_TEST', method_opt=>'for all columns size skewonly', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL>--
SQL>-- Run a query to get an index-access plan
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 1;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       337          1 Value 337                                     28-APR-17
       674          1 Value 674                                     28-APR-17
      1011          1 Value 1011                                    28-APR-17
...
    483932          1 Value 483932                                  28-APR-17
    487302          1 Value 487302                                  28-APR-17
    477529          1 Value 477529                                  28-APR-17
    480899          1 Value 480899                                  28-APR-17
    484269          1 Value 484269                                  28-APR-17
    487639          1 Value 487639                                  28-APR-17

1483 rows selected.

SQL>
SQL>--
SQL>-- Display the plan
SQL>--
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19vnyya8kzzsw, child number 0
-------------------------------------
select * from plan_test where class = 1

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]


32 rows selected.

SQL>
SQL>--
SQL>-- Run a query to get an full scan plan
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 3999;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
      3999       3999 Value 500682                                  02-JUN-17
      3999       3999 Value 500684                                  28-APR-17
      3999       3999 Value 500686                                  30-APR-17
...
      3999       3999 Value 997392                                  18-MAY-17
      3999       3999 Value 997394                                  20-MAY-17
      3999       3999 Value 997396                                  22-MAY-17

250000 rows selected.

SQL>
SQL>--
SQL>-- Display the plan
SQL>--
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g41z4n4rnvbqc, child number 0
-------------------------------------
select * from plan_test where class = 3999

Plan hash value: 534695957

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1304 (100)|          |
|*  1 |  TABLE ACCESS FULL| PLAN_TEST |   244K|  6916K|  1304   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1

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

   1 - filter("CLASS"=3999)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22],
       "PLAN_TEST"."DATA"[VARCHAR2,45], "PLAN_TEST"."CR_DT"[DATE,7]


29 rows selected.

SQL>

SQL>--
SQL>-- Create script to create profile
SQL>--
SQL>-- Profile uses force_match=TRUE
SQL>--
SQL>@coe_xfr_sql_profile 19vnyya8kzzsw 2494389488
SQL>--
SQL>-- Create the profile
SQL>--
SQL>@coe_xfr_sql_profile_19vnyya8kzzsw_2494389488
SQL>--
SQL>-- Test the profile
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 1;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       337          1 Value 337                                     28-APR-17
       674          1 Value 674                                     28-APR-17
      1011          1 Value 1011                                    28-APR-17
...
    483932          1 Value 483932                                  28-APR-17
    487302          1 Value 487302                                  28-APR-17
    477529          1 Value 477529                                  28-APR-17
    480899          1 Value 480899                                  28-APR-17
    484269          1 Value 484269                                  28-APR-17
    487639          1 Value 487639                                  28-APR-17

1483 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19vnyya8kzzsw, child number 0
-------------------------------------
select * from plan_test where class = 1

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 107;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       443        107 Value 443                                     12-AUG-17
       780        107 Value 780                                     12-AUG-17
      1117        107 Value 1117                                    12-AUG-17
...
    487071        107 Value 487071                                  12-AUG-17
    477298        107 Value 477298                                  12-AUG-17
    480668        107 Value 480668                                  12-AUG-17
    484038        107 Value 484038                                  12-AUG-17
    487408        107 Value 487408                                  12-AUG-17
    477635        107 Value 477635                                  12-AUG-17
    481005        107 Value 481005                                  12-AUG-17

1484 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  93zcxckzy9g3f, child number 0
-------------------------------------
select * from plan_test where class = 107

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=107)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 391;

no rows selected

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7n1ab3tyk1f33, child number 0
-------------------------------------
select * from plan_test where class = 391

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=391)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 1044;

no rows selected

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  52r213wp9sr9a, child number 0
-------------------------------------
select * from plan_test where class = 1044

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=1044)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>

All of the queries shown above return about the same number of rows, presuming they return rows, and all used the created profile, which is good. What isn’t so good is the next query, returning 250000 rows, also uses the profile:


SQL>
SQL>--
SQL>-- This one probably shouldn't use the profile but it does
SQL>--
SQL>-- Result of force_match=TRUE
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 3999;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
      3999       3999 Value 500682                                  02-JUN-17
      3999       3999 Value 500684                                  28-APR-17
      3999       3999 Value 997388                                  14-MAY-17
...
      3999       3999 Value 997392                                  18-MAY-17
      3999       3999 Value 997394                                  20-MAY-17
      3999       3999 Value 997396                                  22-MAY-17

250000 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g41z4n4rnvbqc, child number 0
-------------------------------------
select * from plan_test where class = 3999

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |   123K(100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |   244K|  6916K|   123K  (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |   244K|       |   510   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=3999)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>

Compare the cost of the index plan (123K) and the full table scan plan (1304) and you can see using the SQL Profile when returning a quarter of the table data is not the preferred path to take. Fixing the majority of the queries can ‘fix’ queries that don’t need fixing, and that’s the major issue with tuning with a broad brush.

It’s usually best to tune queries on an individual basis, but sometimes applications generate a set of queries that need attention. Judicious setting of optimizer_index_cost_adj could be the answer to such a tuning task, as could creating a SQL Profile with force_match=TRUE. Remember that making such changes at the database level can affect more than you had bargained for so test, test, test to verify minimal impact outside of the set of queries you are targeting. With respect to setting force_match=TRUE for a SQL Profile you may inadvertently set an inefficient plan using an index when a table scan would be preferable, as shown in the provided example.

Sometimes the “easy button” can be too easy to press.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: