bind_peeking.pdfVIP

SAGE Computing Services Customised Oracle Training Workshops and Consulting Bind Peeking – The Endless Tuning Nightmare Penny Cookson Managing Director and Principal Consultant Working with Oracle products since 1987 Oracle Magazine Educator of the Year 2004 Oracle ACE .au penny@.au Characteristics I haven’t changed anything Its really slow this morning I did the same thing yesterday and it was fine Actually its OK now No its not Thank you so much you’ve fixed it (I haven’t done anything) Oracle Version 9 SELECT COUNT(l quantity) FROM Shared Pool SELECT COUNT(l.quantity) . bookings_skew l WHERE resource_code = :v1; FROM bookings_skew_large l WHERE resource_code = :v1; FULL SCAN How many rows do I expect? ‘BRLG’ ‘PC1’ Version 9 database N bi d kio n pee ng Oracle Version 9 SELECT COUNT(l quantity) FROM Shared Pool SELECT COUNT(l.quantity) . bookings_skew l WHERE resource_code = :v1; FROM bookings_skew l WHERE resource_code = :v1; FULL SCAN How many rows do I expect? ‘PC1’ ‘BRLG’ Version 9 database N bi d kio n pee ng What is Bind Peeking? SELECT COUNT(l quantity) FROM Shared Pool SELECT COUNT(l.quantity) . bookings_skew l WHERE resource_code = :v1; FROM bookings_skew l WHERE resource_code = :v1; FULL SCAN How many rows do I expect? ‘BRLG’ ‘PC1’ =Version 9 database Bi d kin pee ng What is Bind Peeking? SELECT COUNT(l quantity) FROM Shared Pool SELECT COUNT(l.quantity) . bookings_skew l WHERE resource_code = :v1; FROM bookings_skew l WHERE resource_code = :v1; INDEXED ACCESS How many rows do I expect? ‘PC1’ ‘ GBRL ’ =Version 9 database Bi d kin pee ng No Histogram *************************************** SINGLE TABLE ACCESS PATH Column (#3): RESOURCE_CODE(VARCHAR2) AvgLen: 5.00 NDV: 9 Nulls: 0 Density: 0.11111 Table: BOOKINGS SKEW Alias: L 5527238/9 = 614138 _ Card: Original: 5527238 Rounded: 614138 Computed: 614137.56 Non Adjusted: 614137.56 Access Path: TableScan Cost: 7701.80 Resp: 7701.80