Benchmarking Index Impact on OLTP Load Rates and

What’s going on? • Real-Time Data Warehousing • VLOLTP • Constraints – Very little downtime – Immediate impact on business processes – Growth is required by the business plan – Performance cannot harm business 2

Goals • Measure the impact of different types of indexes on load rates – Identify the costs that go along with index benefits – Improve testing procedures – Make better cost/benefit decisions on index creations 3

3 x 3 = 10 As a general rule, use this simple estimation guide for the cost of index maintenance: Each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERT into a table with three indexes, then it will be approximately 10 times slower. For DML, and particularly for INSERT-heavy applications, the index design should be seriously reviewed, which might require a compromise between the query and insert performance. - Oracle 9 i Performance Methods But sometimes, 1 x 3 = 10 4

Notes on the tests • Your Mileage Will Vary • Issues include: – Environment constraints – Data sort order – Database block sizes – Versions – Buy vs Build 5

Test configurations • Focused on isolated tables, isolated servers, isolated disks • 8 i & 9 i • Custom tables • Table-to-table inserts, repeated executions of multiple small and large transactions 6

Some indexes are more equal than others • Sort order impacts load rate dramatically – Results from internal block management – Space requirements impacted too • Test table structure: Table EMP, one single-column index on an Emp. Seq numeric column 7

Tracking Index Impact on Load Rate Initial rate (rows/s) Sustained rate (% of initial) 50% 0 0 Number of records inserted 8

Loading Sorted Order Initial rate (rows/s) Sustained rate 42% of initial 50% 0 0 Number of records inserted 9

Loading Unsorted Order Initial rate (rows/s) 50% 42% sustained 20% sustained 0 0 Number of records inserted 10

Conclusions • Unsorted inserts into indexes force more internal work to be done • Unsorted sustained rate is about one half of the sorted sustained rate • Initial rate is not an accurate indicator of performance • 1 x 3 = 5 for high enough values of 3 11

Managing Index Blocks Full index blocks Last block New record 12

99 -1 split of the last block Full index blocks 99% of old 1% of old Last block New record 13

Insert into any other block Full index blocks Middle block New record 14

50 -50 split of internal block Full index blocks 50% of old Middle block Last block New record 15

Result • Inserts into non-terminal index blocks cause more inefficient internal block splits • Costs load performance – Benefits query performance • Costs space usage • Cost will vary index by index – Sort by most expensive index 16

How to see this after the fact • Analyze indexes – Compare space usage – Compare clustering factor to leaf blocks 17

Seeing the space impact • Compare sorted insert vs unsorted insert • As of Oracle 8 i, you can use ORDER BY in views: create view EMP_SORTED as select * from EMP order by Ename; insert into EMP_WITH_ENAME_INDEX select * from EMP_SORTED; 18

Space and Performance Impact • • Test: 100, 000 rows, one index Sorted: 58% faster Sorted index blocks: 920 Unsorted index blocks: 1370 • Savings: 58% time cost, 49% space cost 19

Clustering factor • In load-sorted indexes, the clustering factor will be close to the number of leaf blocks • Select from USER_INDEXES • Sorted: 2, 731 for 920 blocks; ratio = 3 • Unsorted: 91, 654 for 1370 blocks; ratio = 90 • Serves as an indicator for index rebuilds 20

Mitigating the unsorted insert cost • Reduce the number of block splits – Use a larger database block size – Use a smaller PCTFREE for index blocks • Rebuild to reclaim space • Weigh these changes against their potential costs to query performance 21

No index vs one index No indexes Initial rate (rows/s) 50% 30% 9% sustained 0 0 Number of records inserted 22

Index cost during inserts • Initial cost is around 70% of performance • Performance then drops by another 70% after the number of entries in the index exceeds the limit for a 1 -level B*tree This is a step function, not a slow curve! 23

No index vs one index No indexes Initial rate (rows/s) Blevel=1 30% Blevel=2 9% sustained 0 0 Number of records inserted 24

B*tree levels • Index B*tree expands to hold new values • When it runs out of space for entries, it splits into a second level • 2 -level B*trees may add another 3 x to the index cost factor • Analyze, then check: select BLevel from USER_INDEXES where index_name = … ; 25

How to lower BLevel • Use a larger database block size – 9 i: block size varies at the tablespace level • Decrease PCTFREE • Partition the table, use local indexes with fewer records in each 26

What about performance testing? • Load rate testing should not begin until after the index has split – Initial load times are partly relevant – Predict 2 -Blevel load rates based on 1 -Blevel rates 27

How many entries per Blevel? • Varies widely, depending on database block size and index entry length • Blevel 1 to 2 split ranges observed – 2 K: 8, 000 to 12, 000 rows – 4 K: 17, 000 to 123, 000 rows – 8 K: 24, 000 to 307, 000 rows – 16 K: 563, 000 to 6, 243, 000 rows 28

The Good News • Using a higher block size lowers the performance cost of index loads • Only one 3 -level B*tree was found: – 16 K: 64, 300, 000 rows 29

Next steps • Evaluate relative costs: – One one-column index (1 -1) – One two-column index (1 -2) – Two one-column indexes (2 -1) – Three one-column indexes (3 -1) – relative to the initial load rate of 1 -1. 30

One-column vs Two-column indexes • Two columns • Comparable sort levels • Compare two one-column indexes to one-two column index on the same columns 31

One-one vs one-two 1 -1 Initial rate (rows/s) 1 -2 65% 50% 27% sustained 25% sustained 0 0 Number of records inserted 32

1 -1 vs 1 -2 vs 2 -1 1 -1 Initial rate (rows/s) 1 -2 65% 50% 2 -1 0 0 27% sustained 25% sustained 16% sustained Number of records inserted 33

Conclusions for load rates • For like columns: – Fewer longer indexes are preferable to more shorter indexes – Leading column drives the index load cost 34

Predictive load performance • If initial load into 1 index = 1000 records/s • Then: – When Blevel=2, load rate = 270 records/s • If a second index is added: – When both Blevels=2, load rate = 160 rec/sec • What about a third index? 35

1 -1, 1 -2, 2 -1, 3 -1 1 -1 Initial rate (rows/s) 1 -2 65% 50% 2 -1 27% sustained 25% sustained 16% sustained 13% sustained 25% 3 -1 0 0 Number of records inserted 36

So? • Going from 1 one-column index to 3 onecolumn indexes cuts the initial load rate by 75% and the sustained rate by 50% 37

As a percentage of unindexed rate 1 -1 Initial rate (rows/s) 1 -2 2 -1 9% sustained 8% sustained 6% sustained 5% sustained 3 -1 0 0 Number of records inserted 38

Other indexing options • • • Bitmap indexes Reverse key indexes Function-based indexes Unique indexes Index-organized tables 39

Bitmap index loading • Bitmap index maintenance is deferred until the end of each DML operation – One bitmap segment update per commit – Not a benefit to OLTP transactions 40

What about reverse keys for sequential inserts? • During load tests, reverse key indexes trail B*tree indexes by a factor of 2. 5: 1 to 3: 1 for sequentially loaded rows. 41

Function-based indexes? • Indexing UPPER(Name) costs approximately the same as indexing Name. 42

Unique indexes? • Uniqueness checks add little to the index load cost, generally < 10%. • For sequential unique data, initial load rates approach unindexed rates. – Still have a performance drop at Blevel=2 43

Index-organized tables? • Table. A: 6 columns, 1 one-column primary key • Table. B: 6 column index-organized table • Performance: Table. B load time was 34% slower than Table. A. 44

Conclusions • One one-column index, over time, can lower load performance by over 90%. • You must maintain the Blevel for the indexes on your major transaction tables. • The order of loaded data is critical to load performance. • The loading process is as slow as its weakest link. 45

Possible solutions • Partition the table - divide and conquer the index – Split partitions to maintain Blevels • Analyze the partitioned index following loads alter table EMP split partition P 2 at ‘JANE’; 46

More solutions • Use materialized views – Separate data loading and query processes – Heavily index materialized view tables, drop indexes on data source tables • Drop unused indexes – 9 i: alter index EMP_DEPT_NDX monitoring usage; then query V$OBJECT_USAGE. Used 47

Still more solutions • Favor fewer, longer indexes and use 9 i’s skipscan features for query tuning. – More efficient than index fast full scans • Increase the block size – 9 i: change online - covered in next section • Use external tables (9 i) 48

Larger block size implications • For larger, denser blocks: – Increase INITRANS – Increase MAXTRANS – Increase FREELISTS 49

Cost/Benefit Predictions If unindexed: 3, 000 rec/sec One index, Blevel 1: 900 rec/sec One index, Blevel 2: 300 rec/sec Two indexes, Blevel 2: 180 rec/sec Three indexes, Blevel 2: 150 rec/sec • Weigh against the query tuning benefits and administrative partitioning choices 50

Online Database Block Size Rebuilds • Increase the database block size in a live database while it is being accessed SYSTEM, RBS 4 K DATA TS 8 K INDEX TS 4 K INDEX TS 8 K 51

Using Oracle 9 i Features • Demonstrate online reorgs, dynamic memory management, and Oracle-managed files • Show to monitor the contents of the memory areas 52

Oracle 9 i Features • Features to exercise: – DB block size at the tablespace level – Separate buffer caches per block size – Dynamically changing buffer cache sizes – Oracle-managed files – Online object reorganizations • Not exercising: Quiesce 53

Quiesce • When the database is quiesced, no transactions by users other than SYS or SYSTEM are permitted • All actions appear to hang, including logins alter system quiesce restricted; 54

Is it Quiesced? select Active_State from V$INSTANCE; • values: NORMAL, QUIESCED, QUIESCING alter system unquiesce; 55

Why not use quiesce? • Requires that all actions be executed by SYS or SYSTEM – Impacts ownership of indexes – Impacts creation of foreign keys and links – Impacts availability of the database 56

SGA, 9 i style select Name, Value from V$PARAMETER where Name like '%cache_size'; • DB_BLOCK_BUFFERS is deprecated. 57

Sample output NAME ----------db_keep_cache_size db_recycle_cache_size db_2 k_cache_size db_4 k_cache_size db_8 k_cache_size db_16 k_cache_size db_32 k_cache_size db_cache_size VALUE -----0 0 0 0 20971520 58

SGA_MAX_SIZE • The SGA_MAX_SIZE parameter sets the maximum combined size for the SGA areas (including shared pool) • DB_n. K_CACHE_SIZE should be 0 for the database’s created block size • Cache sizes may be altered - increments are called granules 59

Granule sizes • If SGA_MAX_SIZE < 128 M, granule size = 4 MB. • Otherwise, granule size = 16 MB. • Other values are dynamically rounded up to the next highest granule size. • For this example, DB_BLOCK_SIZE = 4096, SGA_MAX_SIZE=105672596 60

Minimum sizes for an NT install • shared_pool_size = 60 m • db_cache_size = 20 m • java_pool_size = 8 m – If java_pool_size = 4 m, install will complete but all subsequent DDL operations will return shared pool errors (fixed in 9 i Release 2) – Changing java_pool_size or sga_max_size requires a shutdown/startup 61

Using OMF to simplify example • Oracle-managed files are automatically created and deleted when a tablespace is created or dropped. • OMFs can also include control files and redo log files. • Can create an entire database with: create database MYDB; 62

File locations • All datafiles: DB_CREATE_FILE_DEST='/u 01/oracle/C C 1' • 5 redo log and control file directories: DB_CREATE_ONLINE_LOG_DEST_1='/u 02/oracle/CC 1' DB_CREATE_ONLINE_LOG_DEST_2='/u 03/oracle/CC 1'. . . 63

Default sizes • • SYSTEM ts: 100 MB, autoextensible Redo logs: 100 MB each Undo tablespace: 10 MB autoextensible Default temp ts: 100 MB autoextensible • User tablespaces: 100 MB autoextensible 64

File names • File names will include a system-generated unique identifier: /u 01/oracle/CC 1/users_dest_erj 42201. dbf • OMF files can be renamed using standard methods (alter database rename file etc) following their creation. 65

Specifying block size for a tablespace • Override the DB_BLOCK_SIZE at the tablespace level: create tablespace NEW_TS blocksize 8 K; 66

Example, step 1 - create a cache • Increase SGA_MAX_SIZE if necessary – ideally keep at least 1 granule of free cache space available, or you will need to shutdown/startup • Create the cache for the new block size alter system set db_8 k_cache_size=4 M; 67

Example, Step 2 - create tablespace alter system set DB_CREATE_FILE_DEST='E: Oracleoradataomf'; • In USERS (4 K block size): SCOTT. EMP create tablespace USERS_DEST datafile size 5 M blocksize 8 k; alter user SCOTT quota 5 M on USERS_DEST; 68

Verify the file creation & block size • Check the directory – Rename the file if you wish select Block_Size from DBA_TABLESPACES where Tablespace_Name='USERS_DEST'; BLOCK_SIZE -----8192 69

Note on cache/tablespace • While an 8 K tablespace exists, the db_8 k_cache_size parameter cannot be set back to 0. 70

Checklist • Cache altered • Tablespace with higher block size created • OMF created • Next: Reorg the table – Restrictions apply. 71

Restrictions include You cannot perform online reorganizations for tables with no primary keys, for tables that have materialized views and materialized view logs defined on them, for materialized view container tables, for advanced queueing tables, SYS/SYSTEM tables, tables with LONGs, or for IOT overflow tables. • See the Oracle 9 i Admin Guide for full list! 72

Step 3 - Redefinition checklist 3. 1. Verify table can be rebuilt online 3. 2. Create interim table - indexes, grants, constraints, triggers 3. 3. Start the redefinition 3. 4. Optionally sync the source/destination tables or abort the process 3. 5. Finish the redefinition 73

3. 1 Can the table be rebuilt online? execute DBMS_REDEFINITION. CAN_REDEF_TABLE ('SCOTT', 'EMP'); • Errors will give the exceptions encountered. 74

Errors may confuse users * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at “SYS. DBMS_REDEFINITION” line 8 ORA-06512: at “SYS. DBMS_REDEFINITION” line 236 ORA-06512: at line 1 • What is the problem? 75

3. 2 Create the interim table create table EMP_DEST (EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR 2(10), JOB VARCHAR 2(9), MGR NUMBER(4), HIREDATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2)). . . 76

Partition during the move … partition by range (Dept. No) (partition PART 1 values less than ('30'), partition PART 2 values less than (MAXVALUE)) tablespace USERS_DEST storage (freelists 5); 77

What EMP_DEST is missing • • Grants Triggers Indexes Foreign keys – Create as disabled until process is completed • Can add all of these prior to starting the redefinition process 78

At the end of the process: • The grants, indexes, triggers, and constraints on the EMP_DEST table will replace those from the original table. • Can place indexes in their own separate 8 K block size tablespace 79

3. 3 Start the redefinition • Specify column mapping as the fourth parameter if the columns in the interim table are different from the source table. execute DBMS_REDEFINITION. START_REDEF_TABLE ('SCOTT', 'EMP_DEST'); 80

Quick check select COUNT(*) from EMP_DEST; COUNT(*) -----14 81

While the rebuild is occurring, • Transactions may be executed against the source table at any time. insert into EMP values (1111, 'FIRST', 'CEO', NULL, '01 -JAN-01', 1, 1, 10); commit; 82

3. 4 - OPTIONAL - Abort • To abort the redefinition process: execute DBMS_REDEFINITION. ABORT_REDEF_TABLE ('SCOTT', 'EMP_DEST'); • May want to truncate EMP_DEST • Can later restart from Step 3. 3 83

3. 4 OPTIONAL - Sync • Sync EMP and EMP_DEST to make the data changes in the final step complete faster – Offline instantiation of the committed data execute DBMS_REDEFINITION. SYNC_INTERIM_TABLE ('SCOTT', 'EMP_DEST'); 84

Post-SYNC select COUNT(*) from EMP_DEST; COUNT(*) -----15 85

3. 5 Finish the redefinition execute DBMS_REDEFINITION. FINISH_REDEF_TABLE ('SCOTT', 'EMP_DEST'); • Minimize the number of transactions between START and FINISH 86

4. Verify the redefinition select Table_Name, Tablespace_Name, High_Value from DBA_TAB_PARTITIONS where Owner = 'SCOTT'; TABLE_NAME ------EMP TABLESPACE_NAME -------USERS_DEST HIGH_VALUE -----MAXVALUE '30' 87

The new EMP • EMP is now in USERS_DEST – 8 k block size • EMP blocks will now use the db_8 k_cache_size cache • EMP is now partitioned 88

Dealing with the reorganized table • • Check that foreign keys are enabled Check grants Create non-primary key indexes Rebuild materialized views 89

5. Dealing with the interim table • EMP_DEST is still out there! select COUNT(*) from EMP_DEST; COUNT(*) -----15 90

Interim table, post-redefinition • EMP_DEST is no longer partitioned! – As SCOTT: select Distinct Table_Name from USER_TAB_PARTITIONS; TABLE_NAME -----EMP 91

After verification: • Truncate or drop EMP_DEST – It switched names with the old EMP table – Located in the old EMP table’s tablespace 92

Now what? • Expand to full tablespace-level reorganizations – START_REDEF_TABLE on multiple tables in parallel – Group application tables by tablespace – Allocate space for source and interim tables to be online concurrently 93

6. Additional options - partitions • Move partitioned tables via existing ALTER TABLE options, no DBMS_REDEFINITION calls necessary 94

For indexes • As of Oracle 8 i, you can also use ALTER INDEX REBUILD ONLINE – No support for bitmap indexes – Known index corruption bugs for this process in 8 i. – Until 9 i, no support for functional indexes, reverse key indexes, compressed key indexes, etc. 95

7. Monitoring usage of the caches • Identify object usage by block size cache 96

What’s in the buffer cache? • In single-block size databases or the default cache, join X$BH to DBA_OBJECTS select Object_Name, Object_Type , COUNT(*) Num_Buff from X$BH A, SYS. DBA_OBJECTS B where A. Obj = B. Object_ID and Owner not in ('SYS', 'SYSTEM') group by Object_Name, Object_Type; 97

What’s in multi block size caches? • X$BH contains the buffer cache data for all block sizes. The BLSIZ column shows the block size (4096, 8192, etc). • For non-default block sizes, the Obj values in X$BH do not match Object_Ids in DBA_OBJECTS. 98

X$BH select distinct Obj from X$BH where Bl. Siz=8192; OBJ -----0 31541 select MAX(Object_ID) from DBA_OBJECTS; MAX(OBJECT_ID) -------31519 99

EMP • EMP now has 3 object_Ids: – 31516 table – 31517 and 31518 table partitions 100

Use extent location instead • Slower than joining X$BH to DBA_OBJECTS select Segment_Name, Segment_Type, COUNT(*) Num_Buff from X$BH a, SYS. DBA_EXTENTS B where a. Dbarfil = b. File_id and a. Dbablk >= b. Block_id and a. Dbablk <= (b. Block_id + b. Blocks) and Owner not in ('SYS', 'SYSTEM') and a. Blsiz=8192 group by Segment_Name, Segment_Type; 101

What’s in the 8 K cache SEGMENT_NAM -----EMP SYS_C 002451 SEGMENT_TYPE NUM_BUFF ---------TABLE PARTITION 7 INDEX 1 102

To see all, add Blsiz and Owner select Blsiz, Owner, Segment_Name, Segment_Type, COUNT(*) Num_Buff from X$BH a, SYS. DBA_EXTENTS B where a. Dbarfil = b. File_id and a. Dbablk >= b. Block_id and a. Dbablk <= (b. Block_id + b. Blocks) and Owner not in ('SYS', 'SYSTEM') group by Blsiz, Owner, Segment_Name, Segment_Type; • May wish to also exclude owners WKSYS, SH, RMAN, etc 103

Looking back • DB_n. K_BLOCK_SIZE - set up new caches and tablespaces online • Used Oracle-Managed Files • Reorganized tables into higher block sizes online • Partitioned non-partitioned tables online • Verified cache usage 104

Questions? loneyk@tusc. com http: //www. tusc. com 1 -800 -755 -TUSC 105