SQL>
SQL> alter session set "_optimizer_compute_index_stats"=false;
Session altered.
SQL>
Now, create the index on the EMP table:
SQL> create index emp_idx on emp(job);
Index created.
Let’s set optimizer_index_cost_adj to 10 and see what plans we get for two queries, one that should generate a full table scan and one that should use the index:
SQL>
SQL> set autotrace on
SQL>
SQL> alter session set optimizer_index_cost_adj=10;
Session altered.
SQL>
SQL> select empno, ename, job, sal
2 from emp
3 where job = 'CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
7369 SMITH CLERK 800
...
7934 MILLER CLERK 1300
1024 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1472992808
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1024 | 39936 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1024 | 39936 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_IDX | 1024 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"='CLERK')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
204 consistent gets
4 physical reads
0 redo size
31902 bytes sent via SQL*Net to client
1247 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024 rows processed
SQL>
SQL> select empno, ename, job, sal
2 from emp
3 where job = 'PRESIDENT';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7869 JACK PRESIDENT 5000
Execution Plan
----------------------------------------------------------
Plan hash value: 1472992808
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 78 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"='PRESIDENT')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
40 consistent gets
1 physical reads
0 redo size
812 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
Notice that both queries used the index. This is because the index cost was artificially lowered by the optimizer_index_cost_adj setting in force. This was a fairly small table (4098 rows) and that 25% of the total row count was returned by the first query. Let’s now adjust the parameter setting back to its default and generate statistics on the schema:
SQL> alter session set optimizer_index_cost_adj=100;
Session altered.
SQL>
SQL> exec dbms_stats.gather_schema_stats('BING')
PL/SQL procedure successfully completed.
SQL>
SQL> select empno, ename, job, sal
2 from emp
3 where job = 'CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
7369 SMITH CLERK 800
...
7934 MILLER CLERK 1300
1024 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1024 | 23552 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1024 | 23552 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='CLERK')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
31902 bytes sent via SQL*Net to client
1247 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024 rows processed
SQL>
SQL> select empno, ename, job, sal
2 from emp
3 where job = 'PRESIDENT';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7869 JACK PRESIDENT 5000
Execution Plan
----------------------------------------------------------
Plan hash value: 1472992808
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"='PRESIDENT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
812 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
Now we get the ‘correct’ execution plans as the query returning 25% of the total rows uses a full table scan to reduce I/O. This is because the index access cost has not been artificially modified to favor such scans. We still get an index scan for the second query that returns two rows, which is as it should be.
What was good in earlier releases of Oracle may no longer provide benefit since changes in the CBO and statistics gathering could make such settings detrimental to performance, as the above example illustrates. This example was also executed against an EMP table containing around 1.5 million rows with the same results; it isn’t difficult to realize that doubling the I/O arbitrarily isn’t a good idea. Sometimes the status quo shouldn’t be maintained.
It’s easy at times to pass through settings that once provided benefit in older releases of Oracle. Such settings should be examined and tested before passing them on to production unaltered as they may increase the work Oracle does to retrieve data. Testing is key; yes, that can prolong completing a migration to a newer release but it could prove invaluable in reducing I/O and improving performance, as illustrated here. And it may prevent the DBA from hunting down a performance problem that could have been avoided.
No comments:
Post a Comment