Rewriting SQL for faster performance
Because SQL is
a declarative language, you can write the same query in many
forms, each getting the same result but with vastly different
execution plans and performance. Re-writing SQL for
easier readability (and maintenance) plus faster performance is an
important tuning tool.
-
Rewrite SQL to remove
subqueries - Subqueries can be very problematic
from a performance perspective.
-
Rewriting the SQL in PL/SQL
- For certain queries
rewriting SQL in PL/SQL can result in more than a 20x
performance improvement.
-
Rewrite SQL to simplify query
- Decomposing a query into multiple queries using
the WITH clause (or global temporary tables) greatly aids
performance.
In this
example, we select all books that do not have any sales.
Note that this is a non-correlated sub-query, but it could be
re-written in several ways.
select
book_key
from
book
where
book_key NOT IN (select book_key from sales);
There are
serious problems with subqueries that may return NULL values. It
is a good idea to discourage the use of the NOT IN clause (which
invokes a sub-query) and to prefer NOT EXISTS (which invokes a
correlated sub-query), since the query returns no rows if any
rows returned by the sub-query contain null values.
select
book_key
from
book
where
NOT EXISTS (select book_key from sales);
Subqueries can
often be re-written to use a standard outer join, resulting in
faster performance. As we may know, an outer join uses the
plus sign (+) operator to tell the database to return all
non-matching rows with NULL values. Hence we combine the
outer join with a NULL test in the WHERE clause to reproduce the
result set without using a sub-query.
select
b.book_key
from
book b,
sales s
where
b.book_key = s.book_key(+)
and
s.book_key IS NULL;
This execution
plan will also be faster by eliminating the sub-query.
Rewriting SQL for better readability and
faster response time speed
Here is an actual example of a
poorly-written SQL query. It's hard to read, and the
execution plan is horrible:
SELECT ART.DEMO_MEMBER DEMO_MEMBER,
(SELECT PARAMETER_VALUE
FROM PPM_CIA_PREMIUM_VAL
CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND CIA_NAME IN
('Baseline')
AND PRMA_MKT_MEMBER
= ?
AND
INVOICE_TYPE_NAME IN ('Weekly')
AND GROUP_TYPE_CODE
= 'C'
) WEEKLY_VALS,
(SELECT PARAMETER_VALUE
FROM PPM_CIA_PREMIUM_VAL
CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND CIA_NAME IN
('Baseline')
AND PRMA_MKT_MEMBER
= ?
AND
INVOICE_TYPE_NAME IN ('Monthly')
AND GROUP_TYPE_CODE
= 'C'
) MONTHLY_VALS,
(SELECT PARAMETER_VALUE
FROM PPM_CIA_PREMIUM_VAL
CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND CIA_NAME IN
('Baseline')
AND PRMA_MKT_MEMBER
= ?
AND
INVOICE_TYPE_NAME IN ('90Day')
AND GROUP_TYPE_CODE
= 'C'
) NINETYDAY_VALS,
(SELECT PARAMETER_VALUE
FROM PPM_CIA_PREMIUM_VAL
CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND CIA_NAME IN
('Baseline')
AND PRMA_MKT_MEMBER
= ?
AND
INVOICE_TYPE_NAME IN ('Annual')
AND GROUP_TYPE_CODE
= 'C'
) ANNUAL_VALS,
'C' AS GROUP_TYPE_CODE
FROM
(SELECT DISTINCT PRD_DEMO_CHRSTC_MEMBER
DEMO_MEMBER
FROM
PPM_CIA_PREMIUM_VAL
WHERE
CIA_NAME IN ('Baseline')
AND PRMA_MKT_MEMBER = ?
AND INVOICE_TYPE_NAME IN ('Weekly' ,
'Monthly',
'Annual' ,
'90Day')
AND GROUP_TYPE_CODE = 'C'
GROUP BY
PRD_DEMO_CHRSTC_MEMBER
) ART
UNION ALL
SELECT ART.DEMO_MEMBER DEMO_MEMBER,
(SELECT PARAMETER_VALUE
FROM
PPM_CIA_PREMIUM_VAL CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND
CIA_NAME IN ('Baseline')
AND
PRMA_MKT_MEMBER = ?
AND
INVOICE_TYPE_NAME IN ('Weekly')
AND
GROUP_TYPE_CODE = 'T'
) WEEKLY_VALS,
(SELECT PARAMETER_VALUE
FROM
PPM_CIA_PREMIUM_VAL CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND
CIA_NAME IN ('Baseline')
AND
PRMA_MKT_MEMBER = ?
AND
INVOICE_TYPE_NAME IN ('Monthly')
AND
GROUP_TYPE_CODE = 'T'
) MONTHLY_VALS,
(SELECT PARAMETER_VALUE
FROM
PPM_CIA_PREMIUM_VAL CIA_VAL
WHERE
CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
AND
CIA_NAME IN ('Baseline')
AND
PRMA_MKT_MEMBER = ?
AND
INVOICE_TYPE_NAME IN ('90Day')
AND
GROUP_TYPE_CODE = 'T'
) NINETYDAY_VALS,
(SELECT PARAMETER_VALUE
FROM
PPM_CIA_PREMIUM_VAL CIA_VAL
WHERE CIA_VAL.PRD_DEMO_CHRSTC_MEMBER
= ART.DEMO_MEMBER
AND
CIA_NAME IN ('Baseline')
AND
PRMA_MKT_MEMBER = ?
AND
INVOICE_TYPE_NAME IN ('Annual')
AND
GROUP_TYPE_CODE = 'T'
)
ANNUAL_VALS,
'T' AS GROUP_TYPE_CODE
FROM
(SELECT DISTINCT
PRD_DEMO_CHRSTC_MEMBER DEMO_MEMBER
FROM
PPM_CIA_PREMIUM_VAL
WHERE
CIA_NAME IN ('Baseline')
AND PRMA_MKT_MEMBER = ?
AND INVOICE_TYPE_NAME
IN ('Weekly' ,
'Monthly',
'Annual' ,
'90Day')
AND GROUP_TYPE_CODE = 'T'
GROUP BY
PRD_DEMO_CHRSTC_MEMBER
) ART
ORDER BY GROUP_TYPE_CODE
Look like Greek? That's not a good SQL practice, to
write convoluted SQL! Let's look at the same query,
rewritten for clarity and faster performance.
The above
convoluted query can be completely re-written. This SQL
runs faster and more efficiently.
Rewritten query:
The above convoluted
query can be completely re-written.
This SQL runs faster and more efficiently.
SELECT ART.PRD_DEMO_CHRSTC_MEMBER DEMO_MEMBER,
SUM(DECODE(ART.INVOICE_TYPE_NAME,'Weekly',ART.parameter_value))
WEEKLY_VALS,
SUM(DECODE(ART.INVOICE_TYPE_NAME,'Monthly',ART.parameter_value))
MONTHLY_VALS,
SUM(DECODE(ART.INVOICE_TYPE_NAME,'90Day',ART.parameter_value))
NINETYDAY_VALS,
SUM(DECODE(ART.INVOICE_TYPE_NAME,'Annual',ART.parameter_value))
ANNUAL_VALS,
ART.GROUP_TYPE_CODE
FROM PPM_CIA_PREMIUM_VAL ART
WHERE
CIA_NAME IN ('Baseline')
AND PRMA_MKT_MEMBER = ?
AND INVOICE_TYPE_NAME IN ('Weekly' ,
'Monthly',
'Annual' ,
'90Day')
AND GROUP_TYPE_CODE IN ('C','T')
GROUP BY ART.PRD_DEMO_CHRSTC_MEMBER,ART.GROUP_TYPE_CODE
ORDER BY ART.GROUP_TYPE_CODE, ART.PRD_DEMO_CHRSTC_MEMBER;
As we see, SQL is very flexible and an equivalent query can
be written in many ways, all giving the same result, but with
radically different readability and execution response time.
A script to display the memory used by any specific session
would look something like this:
display_session.ram.sql
select
to_char(ssn.sid, '9999') || ' - '
|| nvl(ssn.username,
nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) ssession,
to_char(prc.spid,
'999999999') pid_thread,
to_char((se1.value / 1024) /
1024, '999g999g990d00') current_size_mb,
to_char((se2.value / 1024) /
1024, '999g999g990d00') maximum_size_mb
from
v$statname stat1,
v$statname stat2,
v$session ssn,
v$sesstat se1,
v$sesstat se2,
v$bgprocess bgp,
v$process prc,
v$instance ins
where
(See
CODE DEPOT for full working
script)
stat1.name = 'session pga memory'
and
stat2.name = 'session pga memory
max'
and
se1.sid = ssn.sid
and
se2.sid = ssn.sid
and
se2.statistic# = stat2.statistic#
and
se1.statistic# = stat1.statistic#
and
ssn.paddr = bgp.paddr(+)
and
ssn.paddr = prc.addr(+);
Click for more details on
displaying RAM memory used by an Oracle session.
and include the URL for the page.
