Recently I was presented a batch job with unacceptable running times. Especially the first stage of loading and normalizing data, just took to long time. When doing trace I found that about 70 percent of the time was spent doing inserts and updates towards to tables. For both of these, Oracle was reading way more blocks that there were rows inserted or updated. One of the tables had 19 indexes, and the other table had 10. Inserting into the table, Oracle have to insert into the index. In a heap table the row could be inserted wherever, Oracle just need a free block. When it comes to the index this is not so. The index is sorted, and Oracle needs to find the right block and place for the insert. An update including the columns in the indexes also becomes an update against the indexes. This update is really a delete and an insert, and very quickly becomes relative expensive. There was no selects during this stage, so if I could delete the indexes (and recreate them) this would really speed up the batch. But when a batch was run for one company, there were still online traffic against the rest of the data.
The tables seemed to be suited for list partitioning on the company id. There was about 70 companies in the tables. The application used a new schema for every monthly batch. Therefore there was no need for further sub partitions. I was not sure, but I believed if we could use this kind of partitioning we could be able to delete the local indexes on the partition for the company in the batch run. This would really speed up the batch without slowing down the online traffic. I just needed a proor of concept. Actually I first googled my idea, and quickly found some information on asktom.com that supported my idea. But I once lived in Missouri – the “show me” state – and really needed to test this my self.
SQL> create table input_data (id number, bid number, x varchar2(50), y varchar2(50)) 2 partition by list (bid) 3 ( partition inp_data_b1000 values (1000), 4 partition inp_data_b2000 values (2000), 5 partition inp_data_b3000 values (3000)); Table created. SQL> create unique index input_data_pk on input_data(id); Index created. SQL> alter table input_data modify id primary key; Table altered. SQL> create sequence input_data_seq start with 1 increment by 1; Sequence created. SQL> insert into input_data select input_data_seq.nextval, -- Insert 50000 rows spredd on partitons 2 case when mod(rownum,3)=0 then 1000 3 when mod(rownum,3)=1 then 2000 4 when mod(rownum,3)=2 then 3000 5 else 1000 end, 'xxx' || rownum, 'yyy' || rownum 6 from all_objects 7 where rownum<=50000; 30000 rows created.
Then I start to create the indexes, but I modify the local indexes on the partition for bid=2000 to be unusable.
SQL> create index input_data_idx01 on input_data(bid, id, x) local; Index created. SQL> alter index input_data_idx01 modify partition inp_data_b2000 unusable; Index altered.
Then I turn on autotrace and start to insert thousand of rows in the different partitions. Allready with two indexes, we see the effect of the indexes being unusable when inserting on bid=2000.
SQL> set autot on stat SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000; 1000 rows created. Statistics ---------------------------------------------------------- 140 recursive calls 348 db block gets 2030 consistent gets 7 physical reads 237468 redo size 850 bytes sent via SQL*Net to client 884 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 1000 rows processed SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000 1000 rows created. Statistics ---------------------------------------------------------- 82 recursive calls 272 db block gets 1960 consistent gets 0 physical reads 148984 redo size 849 bytes sent via SQL*Net to client 884 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1000 rows processed SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000; 1000 rows created. Statistics ---------------------------------------------------------- 53 recursive calls 316 db block gets 1954 consistent gets 0 physical reads 235272 redo size 850 bytes sent via SQL*Net to client 884 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1000 rows processed SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000; 1000 rows created. Statistics ---------------------------------------------------------- 71 recursive calls 442 db block gets 2023 consistent gets 0 physical reads 276480 redo size 850 bytes sent via SQL*Net to client 884 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1000 rows processed
Then i created even more indexes to see the additional effects:
SQL> create index input_data_idx02 on input_data(bid, x, y) local;
Index created.
SQL> alter index input_data_idx02 modify partition inp_data_b2000 unusable;
Index altered.
SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
145 recursive calls
658 db block gets
2038 consistent gets
79 physical reads
342232 redo size
851 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
82 recursive calls
264 db block gets
1958 consistent gets
0 physical reads
148552 redo size
851 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> create index input_data_idx03 on input_data(bid, id, x, y) local;
Index created.
SQL> alter index input_data_idx03 modify partition inp_data_b2000 unusable;
Index altered.
SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
210 recursive calls
1813 db block gets
2372 consistent gets
1 physical reads
1167848 redo size
851 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
82 recursive calls
275 db block gets
1960 consistent gets
0 physical reads
149116 redo size
851 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
66 recursive calls
921 db block gets
2007 consistent gets
0 physical reads
492880 redo size
851 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
71 recursive calls
992 db block gets
2034 consistent gets
0 physical reads
513848 redo size
851 bytes sent via SQL*Net to client
884 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
53 recursive calls
252 db block gets
1950 consistent gets
0 physical reads
147316 redo size
...
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistikk
----------------------------------------------------------
53 recursive calls
223 db block gets
1945 consistent gets
0 physical reads
128792 redo size
...
1000 rows processed
SQL> create index input_data_idx04 on input_data(bid, id, y) local;
Index created.
SQL> alter index input_data_idx04 modify partition inp_data_b2000 unusable;
Index altered.
SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
178 recursive calls
1194 db block gets
2187 consistent gets
1 physical reads
720404 redo size
...
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 1000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
62 recursive calls
1666 db block gets
2131 consistent gets
0 physical reads
1217688 redo size
...
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
82 recursive calls
233 db block gets
1957 consistent gets
0 physical reads
129392 redo size
...
1000 rows processed
SQL> insert into input_data select input_data_seq.nextval, 2000,'xxx'|| rownum, 'yyy'||rownum from all_objects where rownum<=1000;
1000 rows created.
Statistics
----------------------------------------------------------
53 recursive calls
250 db block gets
1950 consistent gets
0 physical reads
147268 redo size
...
1000 rows processed
SQL> set autot trace exp
SQL> select * from input_data where bid=1000 and x like 'a%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3569170837
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 2 | 48 | 4 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| INPUT_DATA | 2 | 48 | 4 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | INPUT_DATA_IDX02 | 2 | | 2 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BID"=1000 AND "X" LIKE 'a%')
filter("X" LIKE 'a%')
SQL> select * from input_data where bid=2000 and x like 'a%';
Execution Plan
----------------------------------------------------------
Plan hash value: 1553665518
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 32 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 2 | 48 | 32 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | INPUT_DATA | 2 | 48 | 32 (0)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X" LIKE 'a%')
SQL> select * from input_data where bid=1000 and id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1093277648
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 1 | 24 | 1 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | INPUT_DATA_IDX03 | 1 | 24 | 1 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BID"=1000 AND "ID"=100)
SQL> select * from input_data where bid=2000 and id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2656409764
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| INPUT_DATA | 1 | 24 | 2 (0)| 00:00:01 | 2 | 2 |
|* 2 | INDEX UNIQUE SCAN | INPUT_DATA_PK | 1 | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BID"=2000)
2 - access("ID"=100)
SQL> alter index input_data_idx01 rebuild partition inp_data_b2000;
Index altered.
SQL> alter index input_data_idx02 rebuild partition inp_data_b2000;
Index altered.
SQL> alter index input_data_idx03 rebuild partition inp_data_b2000;
Index altered.
SQL> alter index input_data_idx04 rebuild partition inp_data_b2000;
Index altered.
SQL> select * from input_data where bid=1000 and x like 'a%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3569170837
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 2 | 48 | 4 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| INPUT_DATA | 2 | 48 | 4 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | INPUT_DATA_IDX02 | 2 | | 2 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BID"=1000 AND "X" LIKE 'a%')
filter("X" LIKE 'a%')
SQL> select * from input_data where bid=2000 and x like 'a%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3569170837
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 2 | 48 | 4 (0)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| INPUT_DATA | 2 | 48 | 4 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | INPUT_DATA_IDX02 | 2 | | 2 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BID"=2000 AND "X" LIKE 'a%')
filter("X" LIKE 'a%')
Then everything is back to normal again. The “batch” is over, and all local indexes are made usable again.
This was a proof of concept. Now we need to test it against the the real batch, and the real data sizes.
When I get the results of these test I’ll publish this on my blog.
Post a Comment