Reducing Redo Julian Dyke Independent Consultant Web Version
Reducing Redo Julian Dyke Independent Consultant Web Version © 2005 Julian Dyke juliandyke. com
Agenda u u u 2 Introduction Tests u Indexes u Number of columns processed u SELECT FOR UPDATE u Number of rows processed u COMMIT u Batch size u Global temporary tables u External tables Conclusion © 2005 Julian Dyke juliandyke. com
Redo Records Header Redo Block 512 or 1024 bytes Redo Block Header 16 bytes Redo Block Body 496 bytes Body Redo Record 1 Header Redo Record 2 Body Spare Header Wastage Redo Record 3 Body Spare 3 STOP © 2005 Julian Dyke Wastage juliandyke. com
Change Vectors Header Body Change Vector 1 Header Body Change Vector 2 Header Body Redo Record 4 STOP © 2005 Julian Dyke Change Vector 3 Change Vectors juliandyke. com
Change Vector Header Body 5 STOP © 2005 Julian Dyke juliandyke. com
Example u Examples in this presentation taken from Formula 1 database u Contains full details of all races from 1961 to 2004 u Updated annually in November (end of season) u Currently u 20 cars per race u 19 races per season u `Approximately 360 new rows per season juliandyke. net 6 © 2005 Julian Dyke juliandyke. com
Schema CIRCUIT COUNTRY SEASON RACE GRANDPRIX DRIVER TEAM ENGINE CAR CLASSIFICATION 7 © 2005 Julian Dyke juliandyke. com
Cars 8 u Each season has up to 18 races (19 in 2005) u Each race has up to 39 entrants (13 races in 1989) u Each car has u driver, team and engine u laps completed (may be zero) u optional notes u Results are classified as follows © 2005 Julian Dyke C Classified DNF Did not finish DNS Did not start DNQ Did not qualify DIS Disqualified juliandyke. com
Points u u 9 Points basically awarded to driver and team as follows 1 st 2 nd 3 rd 4 th 5 th 6 th Pre 1991 9 6 4 3 2 1 1991 -2002 10 6 4 3 2 1 2003 onwards 10 8 6 5 4 3 7 th 8 th 2 1 But. . . not always straightforward u Half points awarded for incomplete races u Split races (two half point races aggregated) u Driver and / or team disqualifications e. g. Tyrrell in 1984 u Up to 1980 only best scores counted for each half of season e. g. u Best 5 results from first 7 races and best 5 results from last 7 races u 1982 -1990 only best 11 results counted for drivers u 1961 -1978 only first car to finish counted for each team © 2005 Julian Dyke juliandyke. com
Input file - car. csv u u u 10 Comma separated file 16181 rows Fields are: u season_key u race_key u position u driver_key u team_key u engine_key u laps_completed u classification_key u notes (optional) © 2005 Julian Dyke 2004 2004 2004 2004 2004 2004 17 17 18 18 18 18 18 17 18 19 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ZBAU DCOU RBAR MWEB JMON KRAI RBAR FALO RSCH TSAT MSCH FMAS GFIS JVIL DCOU JTRU RZON CKLI TGLO ZBAU GBRU MWEB NHEI JBUT MIN MCL FER JAG WIL MCL FER REN WIL BAR FER SAU REN MCL TOY JAG JOR MIN JAG JOR BAR FOR MER FOR BMW MER FER REN BMW HON FER FER REN MER TOY FOR FOR FOR HON 41 38 38 20 71 71 71 70 70 69 69 67 67 23 15 3 DNF DNF C C C C C DNF DNF Spin Accident Overheated Accident Clutch Engine juliandyke. com
Input file - points. csv u u u 11 Comma separated file 16181 rows Fields are: u season_key u race_key u position u driver_points u team_point © 2005 Julian Dyke 2004 2004 2004 2004 2004 2004 17 17 18 18 18 18 18 17 18 19 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 0 0 0 0 10 8 6 5 4 3 2 1 0 0 0 0 0 0 juliandyke. com
CAR table u CAR table and index definitions CREATE TABLE car ( season_key race_key position driver_key team_key engine_key laps_completed classification_key notes driver_points team_points ); NUMBER VARCHAR 2(4) VARCHAR 2(3) NUMBER VARCHAR 2(4) VARCHAR 2(100), NUMBER NOT NULL, NOT NULL, NOT NULL DEFAULT 0, DEFAULT 0 ALTER TABLE car ADD CONSTRAINT car_pk PRIMARY KEY (season_key, race_key, position); CREATE INDEX car_driver ON car (season_key, driver_points); 12 © 2005 Julian Dyke juliandyke. com
CAR u CAR table relational integrity definitions ALTER TABLE car ADD CONSTRAINT car_race FOREIGN KEY (season_key, race_key) REFERENCES race (season_key, race_key); ALTER TABLE car ADD CONSTRAINT car_driver FOREIGN KEY (driver_key) REFERENCES driver (driver_key); ALTER TABLE car ADD CONSTRAINT car_team FOREIGN KEY (team_key) REFERENCES team (team_key); ALTER TABLE car ADD CONSTRAINT car_engine FOREIGN KEY (engine_key) REFERENCES engine (engine_key); ALTER TABLE car ADD CONSTRAINT car_classification FOREIGN KEY (classification_key) REFERENCES classification (classification_key); 13 © 2005 Julian Dyke juliandyke. com
Baseline - Insert For each line in car. csv { read : season_key, : race_key, : position, : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes; INSERT INTO car (season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes) VALUES (: season_key, : race_key, : position, : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes) COMMIT; } 14 © 2005 Julian Dyke juliandyke. com
Baseline - Insert Redo Generation for each Insert Statement Header 15 Oracle 9. 2 and below 5. 2 INSERT Undo 5. 1 (11. 1) INSERT Redo 11. 2 INSERT Undo 5. 1 (10. 22) INSERT Redo 10. 2 COMMIT Commit © 2005 Julian Dyke 5. 4 Start Transaction Undo insert row in CAR table Insert row in CAR table Undo insert row into CAR_PK index Insert row into CAR_PK index Undo insert row into CAR_DRIVER index Insert row into CAR_DRIVER index End Transaction juliandyke. com
Insert Statement Redo Generation for each Insert Statement INSERT 16 Oracle 10. 1 and above Insert row in CAR table Redo 11. 2 Header 5. 2 INSERT Redo 10. 2 Insert row into CAR_PK index INSERT Redo 10. 2 Insert row into CAR_DRIVER index COMMIT Commit INSERT Undo 5. 1 (11. 1) INSERT Undo 5. 1 (10. 22) Undo insert row into CAR_PK index INSERT Undo 5. 1 (10. 22) Undo insert row into CAR_DRIVER index © 2005 Julian Dyke 5. 4 Start Transaction End Transaction Undo insert row in CAR table juliandyke. com
Baseline - Update For each line in points. csv { read : season_key, : race_key, : position, : driver_points, : team_points; SELECT driver_key, team_key, engine_key, laps_completed, classification_key, notes INTO : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes FROM car WHERE season_key = : season_key AND race_key = : race_key AND position = : position FOR UPDATE; UPDATE car SET driver_key = : driver_key, team_key = : team_key, engine_key = : engine_key, laps_completed = : laps_completed, classification_key = : classification_key, notes = : notes, driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; COMMIT; 17 } © 2005 Julian Dyke juliandyke. com
Baseline - Update Redo Generation for each Update Statement Header 18 Oracle 9. 2 and below 5. 2 SELECT FOR UPDATE Undo 5. 1 (11. 1) SELECT FOR UPDATE Redo 11. 4 UPDATE Undo 5. 1 (11. 1) UPDATE Redo 11. 5 UPDATE Undo 5. 1 (10. 22) UPDATE Redo 10. 4 UPDATE Undo 5. 1 (10. 22) UPDATE Redo 10. 2 COMMIT Commit © 2005 Julian Dyke 5. 4 Start Transaction Undo lock row in CAR table Lock row in CAR table Undo update row in CAR table Undo delete row from CAR_DRIVER index Delete row from CAR_DRIVER index Undo insert row into CAR_DRIVER index Insert row into CAR_DRIVER index End Transaction juliandyke. com
Baseline - Update Redo Generation for each Update Statement SELECT FOR UPDATE 19 Oracle 10. 1 and above Lock row in CAR table Redo 11. 4 Header 5. 2 UPDATE Redo 11. 5 Update row in CAR table UPDATE Redo 10. 4 Delete row from CAR_DRIVER index UPDATE Redo 10. 2 Insert row into CAR_DRIVER index COMMIT Commit SELECT FOR UPDATE Undo 5. 1 (11. 1) Undo lock row in CAR table UPDATE Undo 5. 1 (11. 1) Undo update row in CAR table UPDATE Undo 5. 1 (10. 22) Undo delete row from CAR_DRIVER index UPDATE Undo 5. 1 (10. 22) Undo insert row into CAR_DRIVER index © 2005 Julian Dyke 5. 4 Start Transaction End Transaction juliandyke. com
Baseline - Results u Redo Generation in Bytes Operation Baseline u 20 INSERT (car. csv) 20448852 UPDATE (points. csv) 14409676 Total 34858528 Note u Amount of redo generated by both INSERT and UPDATE can be variable due to u Undo segment management u Recursive DDL statements e. g. extent allocation u Block cleanouts © 2005 Julian Dyke juliandyke. com
Test 1 u Check for unused indexes u CAR_PK indexes columns u SEASON_KEY u RACE_KEY u POSITION u supports primary key therefore mandatory u CAR_DRIVER indexes columns u SEASON_KEY u DRIVER_POINTS u no longer required by current version of application DROP INDEX car_driver; 21 © 2005 Julian Dyke juliandyke. com
Test 1 - Insert Redo Generation for each Insert Statement Header 22 STOP 5. 2 INSERT Undo 5. 1 (11. 1) INSERT Redo 11. 2 INSERT Undo 5. 1 (10. 22) INSERT Redo 10. 2 COMMIT Commit © 2005 Julian Dyke 5. 4 Start Transaction Undo insert row in CAR table Insert row in CAR table Undo insert row into CAR_PK index Insert row into CAR_PK index Undo insert row into CAR_DRIVER index Insert row into CAR_DRIVER index End Transaction juliandyke. com
Test 1 - Update Redo Generation for each Update Statement Header 23 STOP 5. 2 SELECT FOR UPDATE Undo 5. 1 (11. 1) SELECT FOR UPDATE Redo 11. 4 UPDATE Undo 5. 1 (11. 1) UPDATE Redo 11. 5 UPDATE Undo 5. 1 (10. 22) UPDATE Redo 10. 4 UPDATE Undo 5. 1 (10. 22) UPDATE Redo 10. 2 COMMIT Commit © 2005 Julian Dyke 5. 4 Start Transaction Undo lock row in CAR table Lock row in CAR table Undo update row in CAR table Undo delete row from CAR_DRIVER index Delete row from CAR_DRIVER index Undo insert row into CAR_DRIVER index Insert row into CAR_DRIVER index End Transaction juliandyke. com
Test 1 - Results u Redo Generation in Bytes Operation u 24 INSERT (car. csv) UPDATE (points. csv) Total Baseline 20448852 14409676 34858528 Test 1 14687756 12467400 27155156 Conclusion u Eliminating redundant index reduced u insert redo generation by 5761096 bytes u update redo generation by 1942276 bytes © 2005 Julian Dyke juliandyke. com
Test 2 u In UPDATE statements u For tables undo and redo is generated for all columns in SET clause u u For indexes undo and redo are only generated for index keys that have changed Statements often update all columns to reduce parsing e. g. : UPDATE car SET driver_key = : driver_key, team_key = : team_key, engine_key = : engine_key, laps_completed = : laps_completed, classification_key = : classification_key, notes = : notes, driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; 25 © 2005 Julian Dyke juliandyke. com
Test 2 - Update Redo Generation for each Update Statement Header SELECT FOR UPDATE Undo 5. 1 (11. 1) SELECT FOR UPDATE Redo 11. 4 UPDATE Undo 5. 1 (11. 1) UPDATE Redo 11. 5 COMMIT Commit Slot = 23 Col 3 = JMON Col 4 = WIL Col 5 = BMW Col 6 = 71 Col 7 = C Col 8 = <Null> Col 9 = 0 Col 10= 0 26 STOP 5. 2 © 2005 Julian Dyke 5. 4 Start Transaction Undo lock row in CAR table Lock row in CAR table Undo update row in CAR table Update row in CAR table End Transaction Slot = 23 Col 3 = JMON Col 4 = WIL Col 5 = BMW Col 6 = 71 Col 7 = C Col 8 = <Null> Col 9 = 10 Col 10= 10 juliandyke. com
Test 2 u Only update columns which can have new values u DRIVER_POINTS u TEAM_POINTS For each line in points. csv { read : season_key, : race_key, : position, : driver_points, : team_points; SELECT. . . FOR UPDATE; UPDATE car SET driver_key = : driver_key, team_key = : team_key, engine_key = : engine_key, laps_completed = : laps_completed, classification_key = : classification_key, notes = : notes, driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; } 27 COMMIT; © 2005 Julian Dyke juliandyke. com
Test 2 - Results u Redo Generation in Bytes Operation u 28 INSERT (car. csv) UPDATE (points. csv) Total Baseline 20448852 14409676 34858528 Test 1 14687756 12467400 27155156 Test 2 14560052 11584760 26144812 Conclusion u Eliminating unnecessary columns from update statements reduced update redo generation by 882640 bytes u Would be significantly more if unchanged columns included long fields e. g. CHAR, or VARCHAR 2 © 2005 Julian Dyke juliandyke. com
Test 3 u Eliminate unnecessary SELECT FOR UPDATE statements For each line in points. csv { read : season_key, : race_key, : position, : driver_points, : team_points; SELECT driver_key, team_key, engine_key, laps_completed, classification_key, notes INTO : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes FROM car WHERE season_key = : season_key AND race_key = : race_key AND position = : position FOR UPDATE; UPDATE car SET driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; } 29 COMMIT; © 2005 Julian Dyke juliandyke. com
Test 3 - Update Redo Generation for each Update Statement Header 30 STOP 5. 2 SELECT FOR UPDATE Undo 5. 1 (11. 1) SELECT FOR UPDATE Redo 11. 4 UPDATE Undo 5. 1 (11. 1) UPDATE Redo 11. 5 COMMIT Commit © 2005 Julian Dyke 5. 4 Start Transaction Undo lock row in CAR table Lock row in CAR table Undo update row in CAR table Update row in CAR table End Transaction juliandyke. com
Test 3 - Results u Redo Generation in Bytes Operation u 31 INSERT (car. csv) UPDATE (points. csv) Total Baseline 20448852 14409676 34858528 Test 1 14687756 12467400 27155156 Test 2 14560052 11584760 26144812 Test 3 14554428 8475484 23029912 Conclusion u Eliminating SELECT FOR UPDATE statement reduced update redo generation by 3109276 bytes © 2005 Julian Dyke juliandyke. com
Test 4 u u Rows are inserted with default values of 0 for driver_points and team_points Points only scored by u first eight cars - 2003 onwards u first six cars - pre 2003 Team Driver u Points No Points 3514 30 No Points 324 12313 Only update rows with non-zero rows for driver_points and/or team_points Driver 324 32 STOP © 2005 Julian Dyke Team 3514 30 12313 juliandyke. com
Test 4 - Update Redo Generation for each Update Statement Header 5. 2 Undo 5. 1 (11. 1) Redo 11. 5 Commit 5. 4 UPDATE car SET driver_points = 1 team_points = 1 WHERE. . . UPDATE car SET driver_points = 0 team_points = 0 WHERE. . . UPDATE car SET driver_points = 9 team_points = 9 WHERE. . . 33 STOP © 2005 Julian Dyke col 9 = 0 col 10 = 0 col 9 = 1 col 10 = 1 col 9 = 0 col 10 = 0 col 9 = 0 col 10 = 0 col 9 = 9 col 10 = 9 juliandyke. com
Test 4 u Only update rows with non-zero rows for driver_points and/or team_points For each line in points. csv { read : season_key, : race_key, : position, : driver_points, : team_points; IF driver_points != 0 OR team_points != 0 THEN { UPDATE car SET driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; } 34 } COMMIT; © 2005 Julian Dyke juliandyke. com
Test 4 - Results u Redo Generation in Bytes Operation u 35 INSERT (car. csv) UPDATE (points. csv) Total Baseline 20448852 14409676 34858528 Test 1 14687756 12467400 27155156 Test 2 14560052 11584760 26144812 Test 3 14554428 8475484 23029912 Test 4 14683408 2070316 16753724 Conclusions u Eliminating unnecessary update statements reduced update redo generation by 6405168 bytes © 2005 Julian Dyke juliandyke. com
Test 5 u Eliminate unnecessary COMMIT statements For each line in car. csv { read : season_key, : race_key, : position, : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes; INSERT INTO car (season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes) VALUES (: season_key, : race_key, : position, : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes) COMMIT; } COMMIT; 36 © 2005 Julian Dyke juliandyke. com
Test 5 u Eliminate unnecessary COMMIT statements (continued) For each line in points. csv { read : season_key, : race_key, : position, : driver_points, : team_points; IF driver_points != 0 OR team_points != 0 THEN { UPDATE car SET driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; } } COMMIT; 37 © 2005 Julian Dyke juliandyke. com
Test 5 - Insert Redo Generation for each Insert Statement Header 38 STOP 5. 2 Start Transaction INSERT Undo 5. 1 (11. 1) INSERT Redo 11. 2 INSERT Undo 5. 1 (10. 22) INSERT Redo 10. 2 COMMIT Commit 5. 4 End Transaction Header 5. 2 Start Transaction INSERT Undo 5. 1 (11. 1) INSERT Redo 11. 2 INSERT Undo 5. 1 (10. 22) INSERT Redo 10. 2 COMMIT Commit © 2005 Julian Dyke 5. 4 Undo insert row in CAR table Insert row in CAR table Undo insert row into CAR_PK index Insert row into CAR_PK index End Transaction juliandyke. com
Test 5 - Results u Redo Generation in Bytes Operation u 39 INSERT (car. csv) UPDATE (points. csv) Total Baseline 20448852 14409676 34858528 Test 1 14687756 12467400 27155156 Test 2 14560052 11584760 26144812 Test 3 14554428 8475484 23029912 Test 4 14683408 2070316 16753724 Test 5 9516512 1028084 10544596 Conclusion u Eliminating COMMIT statements reduced u insert redo generation by 5166896 bytes u update redo generation by 1042232 bytes © 2005 Julian Dyke juliandyke. com
Test 6 u u Default batch size is 1 Test INSERT and UPDATE with different batch sizes Batch Size INSERT Redo 40 UPDATE Redo Total Redo 1 9517096 1028084 10545180 2 5654136 1028084 6682220 4 3927092 1028440 4955532 8 3011944 1028084 4040028 16 2588540 1028636 3617176 32 2375884 1028172 3404056 64 2254936 1028040 3282976 128 2195876 1028084 3223960 256 2179404 1028440 3207844 512 2163816 1028084 3191900 1024 2163084 1028084 3191168 2048 2160012 1028084 3188096 © 2005 Julian Dyke juliandyke. com
Test 6 - Results 41 © 2005 Julian Dyke juliandyke. com
Test 6 - Results u Redo Generation in Bytes Operation u 42 INSERT (car. csv) UPDATE (points. csv) Total Baseline 20448852 14409676 34858528 Test 1 14687756 12467400 27155156 Test 2 14560052 11584760 26144812 Test 3 14554428 8475484 23029912 Test 4 14683408 2070316 16753724 Test 5 9516512 1028084 10544596 Test 6 2195876 1028084 3223960 Conclusion u Batch Size of 128 u reduced insert redo generation by 7320636 bytes u update redo generation unaffected © 2005 Julian Dyke juliandyke. com
Test 7 u Create global temporary table CREATE GLOBAL TEMPORARY TABLE temporary_car ( season_key VARCHAR 2(4), race_key VARCHAR 2(2), position NUMBER, driver_key VARCHAR 2(4), team_key VARCHAR 2(3), engine_key VARCHAR 2(3), laps_completed NUMBER, classification_key VARCHAR 2(4), notes VARCHAR 2(100), driver_points NUMBER, team_points NUMBER ) ON COMMIT PRESERVE ROWS; 43 © 2005 Julian Dyke juliandyke. com
Test 7 u Insert rows into global temporary table For each line in car. csv { read : season_key, : race_key, : position, : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes; INSERT INTO temporary_car (season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes) VALUES (: season_key, : race_key, : position, : driver_key, : team_key, : engine_key, : laps_completed, : classification_key, : notes) COMMIT; } u 44 Generated 64140 bytes of redo © 2005 Julian Dyke juliandyke. com
Test 7 u Update points in global temporary table For each line in points. csv { read : season_key, : race_key, : position, : driver_points, : team_points; IF driver_points != 0 OR team_points != 0 THEN { UPDATE temporary car SET driver_points = : driver_points, team_points = : team_points WHERE season_key = : season_key AND race_key = : race_key AND position = : position; } } COMMIT; u 45 Generated 652884 bytes of redo © 2005 Julian Dyke juliandyke. com
Test 7 u Copy rows from global temporary table to permanent table INSERT INTO car ( season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes, driver_points, team_points ) SELECT season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes, driver_points, team_points FROM temporary_car; 46 u Generated 2166724 bytes of redo u APPEND hint had no effect © 2005 Julian Dyke juliandyke. com
Test 7 - Results u u 47 Redo Generation in Bytes Operation Description Total Baseline Update all rows 34858528 Test 1 Update affected rows 27155156 Test 2 Update affected columns 26144812 Test 3 Drop index 23029912 Test 4 SELECT FOR UPDATE 16753724 Test 5 COMMIT 10544596 Test 6 Increase Batch Size 3223960 Test 7 Global Temporary Table 2883748 Conclusion u Global Temporary Table reduced total redo generation by 340212 bytes © 2005 Julian Dyke juliandyke. com
Test 8 u Create external tables CREATE OR REPLACE DIRECTORY external_dir AS '/u 01/app/oracle/gp'; CREATE TABLE external_points ( season_key VARCHAR 2(4), race_key VARCHAR 2(2), position NUMBER, driver_points NUMBER, team_points NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY external_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ', ' ) LOCATION ('points. csv') ); 48 © 2005 Julian Dyke juliandyke. com
Test 8 CREATE TABLE external_car ( season_key VARCHAR 2(4), race_key VARCHAR 2(2), position NUMBER, driver_key VARCHAR 2(4), team_key VARCHAR 2(3), engine_key VARCHAR 2(3), laps_completed NUMBER, classification_key VARCHAR 2(4), notes VARCHAR 2(100) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY external_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ', ' MISSING FIELD VALUES ARE NULL ) LOCATION ('car. csv') ); 49 © 2005 Julian Dyke juliandyke. com
Test 8 u Insert directly into permanent table joining contents of both external tables INSERT INTO car ( season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes, driver_points, team_points ) SELECT c. season_key, c. race_key, c. position, c. driver_key, c. team_key, c. engine_key, c. laps_completed, c. classification_key, c. notes, p. driver_points, p. team_points FROM external_car c, external_points p WHERE c. season_key = p. season_key AND c. race_key = p. race_key AND c. position = p. position"; u 50 Generated 2166724 bytes of redo © 2005 Julian Dyke juliandyke. com
Test 8 - Results u u 51 Redo Generation in Bytes Operation Description Total Baseline Update all rows 34858528 Test 1 Update affected rows 27155156 Test 2 Update affected columns 26144812 Test 3 Drop index 23029912 Test 4 SELECT FOR UPDATE 16753724 Test 5 COMMIT 10544596 Test 6 Increase Batch Size 3223960 Test 7 Global Temporary Table 2883748 Test 8 External Table 2166724 Conclusion u External Tables reduced total redo generation by 717024 bytes © 2005 Julian Dyke juliandyke. com
Conclusion u We have seen that the following techniques can be used to reduce the amount of redo generated: u u u u 52 Eliminating redundant indexes Reducing the number of columns updated Eliminating redundant SELECT FOR UPDATE statements Reducing the number of rows processed Eliminating COMMIT statements Increasing the batch size Using Global Temporary Tables Using External Tables © 2005 Julian Dyke juliandyke. com
Thank you for your interest For more information and to provide feedback please contact me My e-mail address is: info@juliandyke. com My website address is: www. juliandyke. com 53 © 2005 Julian Dyke juliandyke. com
- Slides: 53