Posts Tagged Performance Tuning

How to reduce Buffer Busy Waits with Hash Partitioned Indexes in #Oracle

fight_contention

Buffer Busy Waits can be a serious problem for large OLTP systems on both tables and indexes. If e.g. many inserts from multiple sessions occur simultaneously, they may have to compete about the same index leaf blocks like the picture below shows:

Index Leaf Block Contention

Index Leaf Block Contention

For the demo below, I’m using 100 jobs running at the same time to simulate 100 end user session that do inserts into table t with an ordinary index i that is not yet partitioned:

SQL> create table t (id number, sometext varchar2(50));

Table created.

SQL> create index i on t(id);

Index created.

SQL> create sequence id_seq;

Sequence created.

SQL> create or replace procedure manyinserts as
     begin
      for i in 1..10000 loop
       insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
      end loop;
      commit;
     end;
     /  

Procedure created.

SQL> create or replace procedure manysessions as
     v_jobno number:=0;
     begin
      for i in 1..100 loop
       dbms_job.submit(v_jobno,'manyinserts;', sysdate);
      end loop;
      commit;
     end;
     /

Procedure created.

SQL> exec manysessions

PL/SQL procedure successfully completed.

After a couple of minutes the jobs are done and the table is populated:

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value 
     from v$segment_statistics where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'I';

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
I			  167363

There have been Buffer Busy Waits on the table t as well of course, but let’s focus on the index here. Now the same load but with a Hash Partitioned index instead:

SQL> drop index i;

Index dropped.

SQL> truncate table t;

Table truncated.

SQL> create index i on t(id) global
     partition by hash(id) partitions 32;
 
Index created.

Notice that you have to say GLOBAL even though the table is not partitioned itself, so LOCAL is impossible. How about the effect?

SQL> exec manysessions

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value 
     from v$segment_statistics where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'I';


OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
I	   SYS_P249	     138
I	   SYS_P250	     122
I	   SYS_P251	     138
I	   SYS_P252	     120
I	   SYS_P253	     134
I	   SYS_P254	     116
I	   SYS_P255	     132
I	   SYS_P256	     129
I	   SYS_P257	     126
I	   SYS_P258	     140
I	   SYS_P259	     126
I	   SYS_P260	     129
I	   SYS_P261	     142
I	   SYS_P262	     142
I	   SYS_P263	     156
I	   SYS_P264	     155
I	   SYS_P265	     165
I	   SYS_P266	     121
I	   SYS_P267	     142
I	   SYS_P268	     148
I	   SYS_P269	     120
I	   SYS_P270	     112
I	   SYS_P271	     168
I	   SYS_P272	     130
I	   SYS_P273	     129
I	   SYS_P274	     137
I	   SYS_P275	     147
I	   SYS_P276	     131
I	   SYS_P277	     132
I	   SYS_P278	     136
I	   SYS_P279	     124
I	   SYS_P280	     138

32 rows selected.

Instead of having just one hot part, we now have as many ‘warm parts’ as there are partitions, like the picture below tries to show:

Reduced contention with hash partitioned index

Reduced contention with hash partitioned index

Precisely this was achieved by the solution:

SQL> select sum(value) from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'I'; 

SUM(VALUE)
----------
      4325

SQL> select 167363-4325 as waits_gone from dual;

WAITS_GONE
----------
    163038

Give me an Oracle Database and I don’t need a calculator🙂

,

Leave a comment

How to speed up Data Pump Import by suppressing redo generation in #Oracle 12c

fast

With your database in archive log mode, a Data Pump Import may be severely slowed down by the writing of much redo into online logs and the the generation of many archive logs. A 12c New Feature enables you to avoid that slow down by suppressing redo generation for the import only. You can keep the database in archive log mode the whole time. Let’s see that in action!

First without the new feature:

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:25:25 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:24:55 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select log_mode from v$database;

LOG_MODE
------------------------------------
ARCHIVELOG

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    15.37			 15		 10	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ impdp adam/adam tables=sales directory=DPDIR

Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:26:45 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** tables=sales directory=DPDIR 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."SALES"                              510.9 MB 10000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:29:46 2016 elapsed 0 00:03:00
[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:30:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:26:45 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    30.63			 15		 17	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

The import took 3 minutes and generated 7 archive logs. Now with the new feature:

SQL> drop table sales purge;

Table dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ impdp adam/adam tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y

Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:31:20 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."SALES"                              510.9 MB 10000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:32:15 2016 elapsed 0 00:00:54

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:32:25 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:31:20 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    30.63			 15		 17	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

SQL> select logging from user_tables where table_name='SALES';

LOGGING
---------
YES

About three times faster and no archive log generated! The table is still in logging mode after the import. Keep in mind to take a backup of the datafile that contains the table now, though! For the same reason you should take a backup after a NOLOGGING operation🙂

, ,

5 Comments

Index Competition in #Oracle 12c

win

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.

, ,

7 Comments

%d bloggers like this: