1 / 53

Reducing Redo

Reducing Redo. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Introduction Tests Indexes Number of columns processed SELECT FOR UPDATE Number of rows processed COMMIT Batch size Global temporary tables External tables Conclusion. Agenda. Header. Body.

odeda
Télécharger la présentation

Reducing Redo

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ReducingRedo Julian Dyke Independent Consultant Web Version juliandyke.com

  2. Introduction Tests Indexes Number of columns processed SELECT FOR UPDATE Number of rows processed COMMIT Batch size Global temporary tables External tables Conclusion Agenda

  3. Header Body Redo Record 1 Header Redo Record 2 Body Wastage Spare Redo Record 3 Header Body Wastage Spare STOP Redo Records Redo Block 512 or 1024 bytes Redo Block Header16 bytes Redo Block Body496 bytes

  4. Header Header ChangeVector 1 Body Header ChangeVector 2 Body Body Header ChangeVector 3 Body STOP Change Vectors ChangeVectors Redo Record

  5. Header STOP Change Vector Header Body

  6. Example • Examples in this presentation taken from Formula 1 database • Contains full details of all races from 1961 to 2004 • Updated annually in November (end of season) • Currently • 20 cars per race • 19 races per season • `Approximately 360 new rows per season juliandyke.net

  7. Schema CIRCUIT COUNTRY SEASON RACE DRIVER TEAM ENGINE GRANDPRIX CAR CLASSIFICATION

  8. Cars • Each season has up to 18 races (19 in 2005) • Each race has up to 39 entrants (13 races in 1989) • Each car has • driver, team and engine • laps completed (may be zero) • optional notes • Results are classified as follows

  9. Points • Points basically awarded to driver and team as follows • But . . . not always straightforward • Half points awarded for incomplete races • Split races (two half point races aggregated) • Driver and / or team disqualifications e.g. Tyrrell in 1984 • Up to 1980 only best scores counted for each half of season e.g. • Best 5 results from first 7 races and best 5 results from last 7 races • 1982-1990 only best 11 results counted for drivers • 1961-1978 only first car to finish counted for each team

  10. Input file - car.csv • Comma separated file • 16181 rows • Fields are: • season_key • race_key • position • driver_key • team_key • engine_key • laps_completed • classification_key • notes (optional)

  11. Input file - points.csv • Comma separated file • 16181 rows • Fields are: • season_key • race_key • position • driver_points • team_point

  12. CAR table • CAR table and index definitions CREATE TABLE car( season_key NUMBER NOT NULL, race_key NUMBER NOT NULL, position NUMBER NOT NULL, driver_key VARCHAR2(4) NOT NULL, team_key VARCHAR2(3) NOT NULL, engine_key VARCHAR2(3) NOT NULL, laps_completed NUMBER NOT NULL, classification_key VARCHAR2(4) NOT NULL, notes VARCHAR2(100), driver_points NUMBER NOT NULL DEFAULT 0, team_points NUMBER NOT NULL 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_key,driver_points);

  13. CAR • 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);

  14. 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;}

  15. Start Transaction Header 5.2 INSERT Undo insert row in CAR table Undo 5.1 (11.1) Insert row in CAR table INSERT Redo 11.2 INSERT Undo 5.1 (10.22) Undo insert row into CAR_PK index INSERT Redo 10.2 Insert row into CAR_PK index INSERT Undo 5.1 (10.22) Undo insert row into CAR_DRIVER index INSERT Insert row into CAR_DRIVER index Redo 10.2 COMMIT End Transaction Commit 5.4 Baseline - Insert Redo Generation for each Insert Statement Oracle 9.2 and below

  16. Start Transaction Header 5.2 INSERT Undo insert row in CAR table Undo 5.1 (11.1) Insert row in CAR table INSERT Redo 11.2 INSERT Undo 5.1 (10.22) Undo insert row into CAR_PK index INSERT Redo 10.2 Insert row into CAR_PK index INSERT Undo 5.1 (10.22) Undo insert row into CAR_DRIVER index INSERT Insert row into CAR_DRIVER index Redo 10.2 COMMIT End Transaction Commit 5.4 Insert Statement Redo Generation for eachInsert Statement Oracle 10.1 and above

  17. 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;}

  18. Start Transaction Header 5.2 SELECT FOR UPDATE Lock row in CAR table Redo 11.4 Update row in CAR table UPDATE Redo 11.5 UPDATE Undo 5.1 (10.22) Undo delete row from CAR_DRIVER index UPDATE Redo 10.4 Delete row from CAR_DRIVER index UPDATE Undo 5.1 (10.22) Undo insert row into CAR_DRIVER index UPDATE Insert row into CAR_DRIVER index Redo 10.2 COMMIT End Transaction Commit 5.4 Baseline - Update Redo Generation for eachUpdate Statement Oracle 9.2 and below SELECT FOR UPDATE Undo lock row in CAR table Undo 5.1 (11.1) UPDATE Undo update row in CAR table Undo 5.1 (11.1)

  19. Start Transaction Header 5.2 SELECT FOR UPDATE Lock row in CAR table UPDATE Redo 11.4 Update row in CAR table Redo 11.5 Undo 5.1 (10.22) Undo delete row from CAR_DRIVER index UPDATE Redo 10.4 Delete row from CAR_DRIVER index UPDATE UPDATE Undo 5.1 (10.22) Undo insert row into CAR_DRIVER index UPDATE Insert row into CAR_DRIVER index Redo 10.2 COMMIT End Transaction Commit 5.4 Baseline - Update Redo Generation for eachUpdate Statement Redo Generation Oracle 10.1 and above SELECT FOR UPDATE Undo lock row in CAR table Undo 5.1 (11.1) UPDATE Undo update row in CAR table Undo 5.1 (11.1)

  20. Baseline - Results • Redo Generation in Bytes • Note • Amount of redo generated by both INSERT and UPDATE can be variable due to • Undo segment management • Recursive DDL statements e.g. extent allocation • Block cleanouts

  21. Test 1 • Check for unused indexes • CAR_PK indexes columns • SEASON_KEY • RACE_KEY • POSITION • supports primary key therefore mandatory • CAR_DRIVER indexes columns • SEASON_KEY • DRIVER_KEY • DRIVER_POINTS • no longer required by current version of application DROP INDEX car_driver;

  22. Start Transaction Header 5.2 INSERT Undo insert row in CAR table Undo 5.1 (11.1) Insert row in CAR table INSERT Redo 11.2 INSERT Undo 5.1 (10.22) Undo insert row into CAR_PK index INSERT Redo 10.2 Insert row into CAR_PK index INSERT Undo insert row into CAR_DRIVER index Undo 5.1 (10.22) INSERT Insert row into CAR_DRIVER index Redo 10.2 COMMIT End Transaction Commit 5.4 STOP Test 1 - Insert Redo Generation for each Insert Statement

  23. Start Transaction Header 5.2 SELECT FOR UPDATE Lock row in CAR table UPDATE Redo 11.4 Update row in CAR table Redo 11.5 Undo 5.1 (10.22) Undo delete row from CAR_DRIVER index UPDATE Redo 10.4 Delete row from CAR_DRIVER index UPDATE UPDATE Undo 5.1 (10.22) Undo insert row into CAR_DRIVER index UPDATE Insert row into CAR_DRIVER index Redo 10.2 COMMIT End Transaction Commit 5.4 STOP Test 1 - Update Redo Generation for eachUpdate Statement Redo Generation SELECT FOR UPDATE Undo lock row in CAR table Undo 5.1 (11.1) UPDATE Undo update row in CAR table Undo 5.1 (11.1)

  24. Test 1 - Results • Redo Generation in Bytes • Conclusion • Eliminating redundant index reduced • insert redo generation by 5761096 bytes • update redo generation by 1942276 bytes

  25. Test 2 • In UPDATE statements • For tables undo and redo is generated for all columns in SET clause • 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_pointsWHERE season_key = :season_keyAND race_key = :race_keyAND position = :position;

  26. Start Transaction Header 5.2 SELECT FOR UPDATE Lock row in CAR table Redo 11.4 Redo Redo 11.5 11.5 Slot = 23Col 3 = JMONCol 4 = WILCol 5 = BMWCol 6 = 71Col 7 = C Col 8 = <Null>Col 9 = 10Col 10= 10 Slot = 23Col 3 = JMONCol 4 = WILCol 5 = BMWCol 6 = 71Col 7 = C Col 8 = <Null>Col 9 = 10Col 10= 10 Undo Undo 5.1 (11.1) 5.1 (11.1) Slot = 23Col 3 = JMONCol 4 = WILCol 5 = BMWCol 6 = 71Col 7 = C Col 8 = <Null>Col 9 = 0Col 10= 0 Slot = 23Col 3 = JMONCol 4 = WILCol 5 = BMWCol 6 = 71Col 7 = C Col 8 = <Null>Col 9 = 0Col 10= 0 COMMIT End Transaction Commit 5.4 STOP Test 2 - Update Redo Generation for eachUpdate Statement Redo Generation SELECT FOR UPDATE Undo lock row in CAR table Undo 5.1 (11.1) UPDATE Undo update row in CAR table Update row in CAR table UPDATE

  27. Test 2 • Only update columns which can have new values • DRIVER_POINTS • TEAM_POINTS For each line in points.csv{ read :season_key, :race_key, :position, :driver_points, :team_points; SELECT ... FOR UPDATE; UPDATE car SETdriver_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;} 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; COMMIT;}

  28. Test 2 - Results • Redo Generation in Bytes • Conclusion • Eliminating unnecessary columns from update statements reduced update redo generation by 882640 bytes • Would be significantly more if unchanged columns included long fields e.g. CHAR, or VARCHAR2

  29. Test 3 • 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; COMMIT;} 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; COMMIT;}

  30. Start Transaction Header 5.2 SELECT FOR UPDATE Lock row in CAR table Redo 11.4 UPDATE COMMIT End Transaction Commit 5.4 Update row in CAR table Redo 11.5 SELECT FOR UPDATE Undo lock row in CAR table Undo 5.1 (11.1) UPDATE Undo update row in CAR table Undo 5.1 (11.1) STOP Test 3 - Update Redo Generation Redo Generation for eachUpdate Statement

  31. Test 3 - Results • Redo Generation in Bytes • Conclusion • Eliminating SELECT FOR UPDATE statement reduced update redo generation by 3109276 bytes

  32. Driver Team 324 3514 30 12313 STOP Test 4 • Rows are inserted with default values of 0 for driver_points and team_points • Points only scored by • first eight cars - 2003 onwards • first six cars - pre 2003 • Only update rows with non-zero rows for driver_points and/or team_points

  33. col9 = 0 col10 = 0 col9 = 1 col10 = 1 col9 = 0 col10 = 0 Header Header Header Header Header 5.2 5.2 5.2 5.2 5.2 col9 = 0 col10 = 0 Undo Undo Undo Undo Undo 5.1 (11.1) 5.1 (11.1) 5.1 (11.1) 5.1 (11.1) 5.1 (11.1) Redo Redo Redo Redo Redo 11.5 11.5 11.5 11.5 11.5 Commit Commit Commit Commit Commit 5.4 5.4 5.4 5.4 5.4 col9 = 0 col10 = 0 col9 = 0 col10 = 0 col9 = 0 col10 = 0 col9 = 0 col10 = 0 UPDATE car SET driver_points = 9 team_points = 9WHERE ... col9 = 9 col10 = 9 col9 = 9 col10 = 9 STOP Test 4 - Update Redo Generation for eachUpdate Statement Redo Generation UPDATE car SET driver_points = 1 team_points = 1WHERE ... UPDATE car SET driver_points = 0 team_points = 0WHERE ... UPDATE car SET driver_points = 0 team_points = 0WHERE ... UPDATE car SET driver_points = 9 team_points = 9WHERE ...

  34. Test 4 • 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; 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;} 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;}}

  35. Test 4 - Results • Redo Generation in Bytes • Conclusions • Eliminating unnecessary update statements reduced update redo generation by 6405168 bytes

  36. Test 5 • 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;} 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;

  37. Test 5 • 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; }} 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; }} COMMIT;

  38. Start Transaction Start Transaction Header Header 5.2 5.2 INSERT Undo insert row in CAR table Undo 5.1 (11.1) Insert row in CAR table Insert row in CAR table INSERT Redo Redo 11.2 11.2 INSERT Undo 5.1 (10.22) Undo insert row into CAR_PK index INSERT INSERT Redo Redo 10.2 10.2 Insert row into CAR_PK index Insert row into CAR_PK index Undo insert row in CAR table INSERT Undo 5.1 (11.1) INSERT INSERT Undo insert row into CAR_PK index Undo 5.1 (10.22) COMMIT COMMIT End Transaction End Transaction Commit Commit 5.4 5.4 STOP Test 5 - Insert Redo Generation for eachInsert Statement Redo Generation

  39. Test 5 - Results • Redo Generation in Bytes • Conclusion • Eliminating COMMIT statements reduced • insert redo generation by 5166896 bytes • update redo generation by 1042232 bytes

  40. Test 6 • Default batch size is 1 • Test INSERT and UPDATE with different batch sizes

  41. Test 6 - Results

  42. Test 6 - Results • Redo Generation in Bytes • Conclusion • Batch Size of 128 • reduced insert redo generation by 7320636 bytes • update redo generation unaffected

  43. Test 7 • Create global temporary table CREATE GLOBAL TEMPORARY TABLE temporary_car ( season_key VARCHAR2(4), race_key VARCHAR2(2), position NUMBER, driver_key VARCHAR2(4), team_key VARCHAR2(3), engine_key VARCHAR2(3), laps_completed NUMBER, classification_key VARCHAR2(4), notes VARCHAR2(100), driver_points NUMBER, team_points NUMBER)ON COMMIT PRESERVE ROWS;

  44. Test 7 • 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;} • Generated 64140 bytes of redo

  45. Test 7 • 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; • Generated 652884 bytes of redo

  46. Test 7 • 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; • Generated 2166724 bytes of redo • APPEND hint had no effect

  47. Test 7 - Results • Redo Generation in Bytes • Conclusion • Global Temporary Table reduced total redo generation by 340212 bytes

  48. Test 8 • Create external tables CREATE OR REPLACE DIRECTORY external_dir AS '/u01/app/oracle/gp'; CREATE TABLE external_points ( season_key VARCHAR2(4), race_key VARCHAR2(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'));

  49. Test 8 CREATE TABLE external_car ( season_key VARCHAR2(4), race_key VARCHAR2(2), position NUMBER, driver_key VARCHAR2(4), team_key VARCHAR2(3), engine_key VARCHAR2(3), laps_completed NUMBER, classification_key VARCHAR2(4), notes VARCHAR2(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'));

  50. Test 8 • 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_keyAND c.position = p.position"; • Generated 2166724 bytes of redo

More Related