Posts Tagged Performance Tuning

Index Competition in #Oracle 12c

Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:

ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;

MAX(AMOUNT_SOLD)
----------------
            5000

ADAM@pdb1 > @lastplan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  3hrvrf1r6kn8s, child number 0
-------------------------------------
select max(amount_sold) from sales where channel_id=9

Plan hash value: 3593230073

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE                      |       |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | BSTAR |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   3 - access("CHANNEL_ID"=9)


20 rows selected.

There is a standard B*tree index on the column CHANNEL_ID that speeds up the SELECT above. I think a bitmap index would be better:

ADAM@pdb1 > create bitmap index bmap on sales(channel_id) invisible nologging;

Index created.

ADAM@pdb1 > alter index bstar invisible;

Index altered.

ADAM@pdb1 > alter index bmap visible;

Index altered.

ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;

MAX(AMOUNT_SOLD)
----------------
            5000

ADAM@pdb1 > @lastplan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
select max(amount_sold) from sales where channel_id=9

Plan hash value: 2178022915

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE                      |       |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES |     1 |     6 |     3   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |       |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | BMAP  |       |       |            |          |
----------------------------------------------------------------------------------------------

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

   4 - access("CHANNEL_ID"=9)


21 rows selected.

With this 12c New Feature (two indexes on the same column), I got a smooth transition to the new index type. But this left no choice to the optimizer. What about this?

ADAM@pdb1 > alter index bmap invisible;

Index altered.

ADAM@pdb1 > alter session set optimizer_use_invisible_indexes=true;

Now both indexes are invisible and the optimizer may choose any of them. Turns out that it likes the bitmap index better here. Instead of watching the execution plans, V$SEGMENT_STATISTICS can also be used to find out:

ADAM@pdb1 > select object_name,statistic_name,value
            from v$segment_statistics
            where object_name in ('BSTAR','BMAP')
            and statistic_name in ('physical reads','logical reads');

OBJECT STATISTIC_NAME                      VALUE
------ ------------------------------ ----------
BSTAR  logical reads                       22800
BSTAR  physical reads                       6212
BMAP   logical reads                        1696
BMAP   physical reads                          0

The numbers of BSTAR remain static while BMAP numbers increase. You may also monitor that with DBA_HIST_SEG_STAT across AWR snapshots. Now isn’t that cool?🙂
Couple of things to be aware of here:
Watch out for more than just physical/logical reads – bitmap indexes may cause a locking problem in an OLTP environment.
Don’t keep the two indexes invisible forever – after you saw which one performs better, drop the other one. Invisible indexes need to be maintained upon DML and therefore slow it down.

,

2 Comments

Create a SQL Profile to let the Optimizer ignore hints in #Oracle

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:04.92
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
--------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1767991108

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |   139K(100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES     |    17M|   131M|   139K  (1)| 00:00:06 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | SALES_BIX |       |       |            |          |
--------------------------------------------------------------------------------------------------

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

   4 - access("CHANNEL_ID"=3)

The index hint directs the optimizer here to use a bad plan that wouldn’t be used otherwise:

SQL> select max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.06
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  ahw4npmjpnu1k, child number 0
--------------------------------------
select max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

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

   2 - filter("CHANNEL_ID"=3)

Now the remedy:

begin
 dbms_sqltune.import_sql_profile(
 name => 'MYPROFILE1',
 category => 'DEFAULT',
 sql_text => 'select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3',
 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')
                                 );
end;
/

PL/SQL procedure successfully completed.

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.05

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
-------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

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

   2 - filter("CHANNEL_ID"=3)

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------
   - SQL profile MYPROFILE1 used for this statement

This works for that SQL statement only without having to modify the application. The SQL profile can be removed like this:

SQL> exec dbms_sqltune.drop_sql_profile('MYPROFILE1')
PL/SQL procedure successfully completed.

All the above is not new, but still I think it might be worthwhile to mention it here for your reference, should you encounter some nasty hints once🙂

,

3 Comments

Full Database Caching in #Oracle 12c

If your Oracle Database fits into the buffer cache, we will put all segments into it now upon access – no more direct reads:

SQL> connect / as sysdba
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1424

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1872

The requirements are met here, I don’t need to force anything:

SQL> select force_full_db_caching from v$database;

FORCE_FUL
---------
NO

SQL> select bytes/1024/1024 as mb from dba_segments 
     where owner='ADAM' and segment_name='SALES';

	MB
----------
       600

SQL> select count(*) from adam.sales;

  COUNT(*)
----------
  10000000

SQL> show parameter parallel_degree_policy

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
parallel_degree_policy		     string
MANUAL

The table got accessed once and is now loaded into the buffer cache. PARALLEL_DEGREE_POLICY is on the default MANUAL, remember that.

SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads					   42
physical reads						    0
physical reads direct					    0

SQL> select count(*) from sales;

  COUNT(*)
----------
  10000000

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads					75620
physical reads						    0
physical reads direct					    0

Only logical reads, because the table is in the buffer cache. Also for parallel queries:

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select * from v$pq_sesstat;

STATISTIC					   LAST_QUERY SESSION_TOTAL	CON_ID
-------------------------------------------------- ---------- ------------- ----------
Queries Parallelized					    1		  1	     0
DML Parallelized					    0		  0	     0
DDL Parallelized					    0		  0	     0
DFO Trees						    1		  1	     0
Server Threads						    4		  0	     0
Allocation Height					    4		  0	     0
Allocation Width					    1		  0	     0
Local Msgs Sent 					  156		156	     0
Distr Msgs Sent 					    0		  0	     0
Local Msgs Recv'd					  156		156	     0
Distr Msgs Recv'd					    0		  0	     0
DOP							    4		  0	     0
Slave Sets						    1		  0	     0

13 rows selected.

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads				       152410
physical reads						    0
physical reads direct					    0

SQL> select name,value from v$sysstat where name='physical reads direct';

NAME							VALUE
-------------------------------------------------- ----------
physical reads direct					   21

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select name,value from v$sysstat where name='physical reads direct';

NAME							VALUE
-------------------------------------------------- ----------
physical reads direct					   21

Looks like I did In-Memory Parallel Query although PARALLEL_DEGREE_POLICY is on MANUAL, doesn’t it? Just for comparison, I did the same with an 11g version:

SQL> select banner from v$version;

BANNER
-------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1090

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1200

SQL> select bytes/1024/1024 as mb from dba_segments where owner='ADAM' and segment_name='SALES';

	MB
----------
       528


SQL> select count(*) from adam.sales;

  COUNT(*)
----------
  10000000

SQL> show parameter parallel_degree_policy

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ---------
parallel_degree_policy		     string			       MANUAL

SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							       26
physical reads									0
physical reads direct								0

SQL> select count(*) from sales;

  COUNT(*)
----------
  10000000

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							    67465
physical reads								    67433
physical reads direct							    67433

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select * from v$pq_sesstat;

STATISTIC				   LAST_QUERY SESSION_TOTAL
------------------------------------------ ---------- -------------
Queries Parallelized				    1		  1
DML Parallelized				    0		  0
DDL Parallelized				    0		  0
DFO Trees					    1		  1
Server Threads					    4		  0
Allocation Height				    4		  0
Allocation Width				    1		  0
Local Msgs Sent 				  116		116
Distr Msgs Sent 				    0		  0
Local Msgs Recv'd				  116		116
Distr Msgs Recv'd				    0		  0

11 rows selected.

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							   135115
physical reads								   134866
physical reads direct							   134866

As you can see, I got direct reads in 11g for both the serial and the parallel query.
Do not confuse this feature with the In-Memory Option, it is much less sophisticated than that. But it doesn’t come with an extra charge at least🙂

, ,

8 Comments

Follow

Get every new post delivered to your Inbox.

Join 4,323 other followers

%d bloggers like this: