transformations - how oracle rewrites your statements
TRANSCRIPT
![Page 1: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/1.jpg)
www.sagecomputing.com.au
Transformations – how
Oracle rewrites your
statements
Penny Cookson SAGE Computing Services
SAGE Computing Services
Customised Oracle Training Workshops and Consulting
![Page 2: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/2.jpg)
Agenda
How to identify transformations
Join Factorisation
Join Elimination
Other Transformations
Effect on Parse Time
Queries in the Select
![Page 3: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/3.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 10g:
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION
SET TRACEFILE_IDENTIFIER = ‘events_10053_1’;
ALTER SESSION
SET EVENTS ‘10053 trace name context forever’;
![Page 4: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/4.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 10g:
Parse the statement:-
SELECT * FROM organisations WHERE postcode = 6000
View the trace file
ALTER SESSION
SET EVENTS ‘10053 trace name context off’
![Page 5: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/5.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 11g:
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION
SET TRACEFILE_IDENTIFIER = ‘events_10053_2’;
ALTER SESSION
SET EVENTS
‘trace[rdbms.SQL_Optimizer.*][sql:40038fgx69y23]’;
![Page 6: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/6.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 11g:
Execute the statement:-
SELECT * FROM organisations WHERE postcode = 6000;
View the trace file
ALTER SESSION
SET EVENTS ‘trace[rdbms.SQL_Optimizer.*] off’;
![Page 7: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/7.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
More examples:
ALTER SESSION
SET EVENTS
‘trace[rdbms.SQL_Transform.*][sql:40038fgx69y23]’;
Trace file smaller
Transformations, session optimizer settings and bug fixes only
![Page 8: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/8.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 11g using DBMS_SQL_DIAG:
Automatically hard parse the statement
BEGIN
dbms_sqldiag.dump_trace (p_sql_id => '40038fgx69y23'
,p_child_number => 0
,p_component => 'Compiler'
,p_file_id => 'events_10053_4');
END;
/
View the trace file dbmsdiag.sql
![Page 9: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/9.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 11g using DBMS_SQL_DIAG: Is bind aware
DECLARE
v2 number;
cursor c1 (p_code in resources.code%type) is
SELECT /*+BIND_AWARE */ COUNT(l.quantity) FROM train1.bookings_large l WHERE
resource_code = p_code;
BEGIN
open c1 ('PC2');
fetch c1 into v2;
close c1;
open c1 ('BRLG');
fetch c1 into v2;
close c1;
END;
/
![Page 10: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/10.jpg)
Identifying Transformations –
Tracing the Optimizer’s decisions
Oracle 11g using DBMS_SQL_DIAG: Is bind aware
BEGIN
dbms_sqldiag.dump_trace (p_sql_id => '463fjw7fvwmq6'
,p_child_number => 0
,p_component => 'Compiler'
,p_file_id => 'events_10053_11');
END;
/
BEGIN
dbms_sqldiag.dump_trace (p_sql_id => '463fjw7fvwmq6'
,p_child_number => 1
,p_component => 'Compiler'
,p_file_id => 'events_10053_12');
END;
/
View the 11 trace file View the 12 trace file
ALTER SESSION
SET EVENTS ‘trace[rdbms.SQL_Optimizer.*] off’;
![Page 11: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/11.jpg)
Join Factorization
TABLE_A
TABLE_A
TABLE_B
TABLE_B
TABLE_C
TABLE_D
UNION ALL
![Page 12: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/12.jpg)
Join Factorization
TABLE_A
TABLE_B
TABLE_B
TABLE_C
TABLE_D
UNION ALL
![Page 13: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/13.jpg)
Join Factorization
alter session set optimizer_features_enable = '11.1.0.7'
SELECT e.description, b.booking_no, b.cost
FROM events_large e, bookings_large b, internal_organisations oi
WHERE e.event_no = b.event_no
AND e.org_id = oi.org_id
AND oi.state = 'WA'
UNION ALL
SELECT e.description, b.booking_no, b.cost
FROM events_large e, bookings_large b, external_organisations oe
WHERE e.event_no = b.event_no
AND e.org_id = oe.org_id
AND oe.state = 'WA'
![Page 14: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/14.jpg)
Version 11.1
![Page 15: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/15.jpg)
Join Factorization
alter session set optimizer_features_enable = '11.2.0.3'
SELECT e.description, b.booking_no, b.cost
FROM events_large e, bookings_large b, internal_organisations oi
WHERE e.event_no = b.event_no
AND e.org_id = oi.org_id
AND oi.state = 'WA'
UNION ALL
SELECT e.description, b.booking_no, b.cost
FROM events_large e, bookings_large b, external_organisations oe
WHERE e.event_no = b.event_no
AND e.org_id = oe.org_id
AND oe.state = 'WA'
![Page 16: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/16.jpg)
Join Factorization
![Page 17: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/17.jpg)
![Page 18: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/18.jpg)
Manual Join Factorization
alter session set optimizer_features_enable = '11.1.0.7'
SELECT evt.description, b.booking_no, b.cost
FROM bookings_large b,
(SELECT e.description, e.event_no
FROM events_large e, internal_organisations oi
WHERE e.org_id = oi.org_id
AND oi.state = 'WA'
UNION ALL
SELECT e.description, e.event_no
FROM events_large e, external_organisations oe
WHERE e.org_id = oe.org_id
AND oe.state = 'WA') evt
WHERE evt.event_no = b.event_no
![Page 19: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/19.jpg)
Manual Join Factorization
![Page 20: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/20.jpg)
How to Avoid Stuffing It Up
UNION (RATHER THAN UNION ALL)
DISTINCT
OUTER JOINS
![Page 21: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/21.jpg)
Join Factorization – This is OK
TABLE_A
TABLE_A
TABLE_B
TABLE_B
TABLE_C
TABLE_D
UNION ALL
(+)
(+)
![Page 22: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/22.jpg)
Join Factorization – This is not OK
TABLE_A
TABLE_A
TABLE_B
TABLE_B
TABLE_C
TABLE_D
UNION ALL
(+)
(+)
(+)
(+)
![Page 23: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/23.jpg)
Join Elimination
Oracle will get rid of joined tables if:
We give it indexes it can use instead
The PK and/or FK relationships imply a table is not required
![Page 24: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/24.jpg)
Its done this for a long time
SELECT b.booking_no, b.resource_code
FROM bookings_large b, events_large e
WHERE b.event_no = e.event_no
AND e.contact_name like 'JOSIE%'
![Page 25: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/25.jpg)
Why constraints are a good idea
SELECT e.description
FROM events e, organisations o
WHERE e.org_id = o.org_id
Constraints matter – they tell the optimiser stuff
![Page 26: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/26.jpg)
Without a foreign key constraint
SELECT e.description
FROM events e, organisations o
WHERE e.org_id = o.org_id
ALTER TABLE events DISABLE
CONSTRAINT org_fk
![Page 27: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/27.jpg)
Without a foreign key constraint
![Page 28: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/28.jpg)
Use RELY for Warehouses
SELECT e.description
FROM events e, organisations o
WHERE e.org_id = o.org_id
ALTER TABLE events MODIFY
CONSTRAINT org_fk RELY;
![Page 29: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/29.jpg)
Join Elimination - this is also OK
SELECT e.description, o.org_id
FROM events e, organisations o
WHERE e.org_id = o.org_id
![Page 30: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/30.jpg)
This won’t do join elimination
SELECT e.description
FROM event_state e, org_state o
WHERE e.org_id = o.org_id
AND e.state = o.state
![Page 31: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/31.jpg)
Multi column primary keys don’t do
Join Elimination
SELECT e.description
FROM event_state e, org_state o
WHERE e.org_id = o.org_id
AND e.state = o.state
SELECT *
FROM user_cons_columns
WHERE constraint_name = 'EVTST_ORG_FK'
![Page 32: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/32.jpg)
Multi column primary keys don’t do
Join Elimination
SELECT e.description
FROM event_state e, org_state o
WHERE e.org_id = o.org_id
SELECT *
FROM user_cons_columns
WHERE constraint_name = 'EVTST_ORG_FK'
Use artificial
sequence
numbers for
Primary Keys
![Page 33: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/33.jpg)
Join Elimination
So who would write that rubbish?
Useful with views where lots of columns are included but not
all selected from the view
Means views with a whole load of unnecessary stuff are not
so bad now
And we have View Merging transformations
![Page 34: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/34.jpg)
CREATE VIEW pretty_stuff_for_users
AS
SELECT o.org_id, o.name,
e.event_no, e.description event_description,
e.start_date, r.description resource_description,
b.booking_no, b.cost, b.quantity, b.resource_code
FROM organisations o, events e, bookings b, resources r
WHERE o.org_id = e.org_id
AND e.event_no = b.event_no
AND b.resource_code = r.code
![Page 35: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/35.jpg)
![Page 36: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/36.jpg)
SELECT booking_no, cost, quantity,
resource_code, resource_description
FROM pretty_stuff_for_users
![Page 37: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/37.jpg)
SELECT e.description
FROM events e, organisations o
WHERE e.org_id = o.org_id
![Page 38: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/38.jpg)
ALTER TABLE events modify (org_id not null)
SELECT booking_no, cost, quantity,
resource_code, resource_description
FROM pretty_stuff_for_users
![Page 39: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/39.jpg)
Join Factorization
![Page 40: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/40.jpg)
Join Factorization
![Page 41: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/41.jpg)
![Page 42: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/42.jpg)
If a column is not
null define it as
NOT NULL
![Page 43: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/43.jpg)
10G – JOIN before the GROUP BY
![Page 44: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/44.jpg)
![Page 45: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/45.jpg)
11G – GROUP BY before the JOIN
![Page 46: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/46.jpg)
![Page 47: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/47.jpg)
![Page 48: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/48.jpg)
![Page 49: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/49.jpg)
![Page 50: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/50.jpg)
10G – NOT IN (PROMISE_NO NULLABLE)
![Page 51: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/51.jpg)
10G – KILLED AFTER 1 hr
![Page 52: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/52.jpg)
11G – Rewrite as Null Aware Anti join
![Page 53: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/53.jpg)
![Page 54: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/54.jpg)
10G – Full Outer Join – default behaviour
![Page 55: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/55.jpg)
![Page 56: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/56.jpg)
From version 10.2.0.3
SELECT /*+ NATIVE_FULL_OUTER_JOIN */
count(e.comments) nume,
count (b.comments) numb
FROM events_large e
FULL OUTER JOIN bookings_large b
ON (e.event_no = b.event_no)
10G – Full Outer Join – hint
![Page 57: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/57.jpg)
11G Native Full Outer Join
![Page 58: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/58.jpg)
![Page 59: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/59.jpg)
SELECT b.booking_no, b.cost, b.quantity,
(SELECT description FROM resources r
WHERE r.code = b.resource_code)
FROM bookings_large b)
SELECT b.booking_no, b.cost, b.quantity,
r.description
FROM bookings_large b, resources r
WHERE b.resource_code = r.code
This is fine and will actually be
more efficient than this
(but may have a different result)
Queries in the SELECT
![Page 60: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/60.jpg)
SELECT b.booking_no, b.cost, b.quantity,
(SELECT description FROM resources r
WHERE r.code = b.resource_code),
(SELECT type_code FROM resources r
WHERE r.code = b.resource_code),
(SELECT daily_rate FROM resources r
WHERE r.code = b.resource_code)
FROM bookings_large b)
This is really stupid and will access the
RESOURCES table multiple times
Queries in the SELECT
![Page 61: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/61.jpg)
SELECT b.booking_no, b.cost, b.quantity,
booking_utl.get_stat1 (
(SELECT description FROM resources r
WHERE r.code = b.resource_code),
(SELECT type_code FROM resources r
WHERE r.code = b.resource_code),
(SELECT daily_rate FROM resources r
WHERE r.code = b.resource_code)),
booking_utl.get_stat2 (
(SELECT description FROM resources r
WHERE r.code = b.resource_code),
(SELECT type_code FROM resources r
WHERE r.code = b.resource_code),
(SELECT daily_rate FROM resources r
WHERE r.code = b.resource_code)),
FROM bookings_large b)
From a real example!!!
![Page 62: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/62.jpg)
Much More Query Transformation SELECT e.event_no, e.start_date, sum(cost) totcost
FROM events_large e, bookings_large b
WHERE e.event_no = b.event_no
GROUP BY e.event_no, e.start_date
![Page 63: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/63.jpg)
Oracle 10g – Run a whole load of random statements – all of
which require parsing
![Page 64: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/64.jpg)
Oracle 11g – Run a whole load of random statements – all of
which require parsing
So Tuning the Shared
Pool becomes more
important,
but overall the 11g
CBO is pretty smart
![Page 65: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/65.jpg)
Conclusion
Proper relational database design techniques are important
Views are OK
11g will do stuff 10g can’t – get rid of the hints
Developer are encouraged to do stupid stuff
Proper use of the shared pool is important
![Page 66: Transformations - how Oracle rewrites your statements](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d03cbcbb61eb0c788b468b/html5/thumbnails/66.jpg)
SAGE Computing Services
Customised Oracle Training Workshops and Consulting
Questions?
www.sagecomputing.com.au