SQL
Tuning
The first thing you need to know is the data. The
volume of data and the distribution of data will affect how you tune individual
queries. You also need to have a “shopping cart" full of tuning methods to
try. Multiple approaches must be made to cover all types of queries. A single
method of tuning or a single tuning product is not enough. You also need to
know where the system is slow. Many DBAs and developers spend endless hours
finding problem queries instead of asking the users of the system. Users will
almost always be happy to volunteer this information. You also need to network
with other developers that work on a similar system. Sharing information at
user groups is a great way to network.
General
Points
• Do not
always assume Oracle is at fault. If you have used an inefficient algorithm,
then your code will always run slowly.
• Use
EXPLAIN PLAN and TKPROF to verify that PL/SQL is causing the performance
problem and not the SQL that it executes.
• Avoid
selects against the database when the same functionality can be achieved in
PL/SQL.
·
Anonymous PL/SQL should be moved into a stored
object when possible and this object then pinned in the shared pool using
dbms_shared_pool.keep(), if the object is frequently referenced.
1) For If
statement(s) use the NOT IN operator, and rewrite them with NOT EXISTS.
2) Compose
Predicates Using AND and =.
3) Use
equijoins (a join with a join condition containing an equality operator).
4) Avoid a
full-table scan if it is more efficient to get the required rows through an
index.
5) Avoid
using an index that fetches 10,000 rows from the driving table if you could
instead use another index that fetches 100 rows and choose selective indexes.
6) Choose
the join order so you will join fewer rows to tables later in the join order.
7) Set up
the driving table to contain the filter condition that eliminates the highest
percentage of the table.
8) Use
untransformed column values.
9) Do not
use SQL functions in predicate clauses or WHERE clauses. Use function-based
indexes where possible.
11) When
using IN and NOT IN with a sub query try to rewrite it with WHERE (NOT) EXISTS
as an alternative.
12) Minimize
the use of DISTINCT.
14) Do not
use inequality operators such as ‘<>’, ‘=!’.
15) Do not
use ‘IS NULL’ and ‘IS NOT NULL’.
16) Use INDEX
BY clause while using collections.
17) Avoid
Rollback Segment Problems with huge Updates / Deletes. If you have to UPDATE or
DELETE a huge number of rows, you may encounter problems with Rollback
Segments. One solution is to COMMIT after sets of n Rows.
18) Use SQL
standards within an application. Rules like the following are easy to implement
and will allow more sharing within Oracle's memory.
Using a
single case for all SQL verbs Beginning all SQL verbs on a new line
Right or
left aligning verbs within the initial SQL verb Separating all words with a
single space
19) Use a
standard approach to table aliases. If two identical SQL statements vary
because an identical table has two different aliases, then the SQL is different
and will not be shared.
20) Use table
aliases and prefix all column names by their aliases when more than one table
is involved in a query. This reduces parse time AND prevents future syntax
errors if someone adds a column to one of the tables with the same name as a
column in another table. (ORA-00918: COLUMN AMBIGUOUSLY DEFINED)
21) Beware of
WHERE clauses which do not use indexes at all. Even if there is an index over a
column that is referenced by a WHERE clause included in this section, Oracle
will ignore the index. All of these WHERE clauses can be re-written to use an
index, while returning the same values. In other words, don't perform
operations on database objects referenced in the WHERE clause.
22) Don't
forget to tune views. Views are SELECT statements and can be tuned in just the
same way as any other type of SELECT statement can be. All tuning applicable to
any SQL statement are equally applicable to views.
23) Avoid
including a HAVING clause in SELECT statements. The HAVING clause filters
selected rows only after all rows have been fetched. Using a WHERE clause helps
reduce overheads in sorting, summing, etc. HAVING clauses should only be used
when columns with summary operations applied to them are restricted by the
clause .
24) Minimize
the number of table lookups (subquery blocks) in queries, particularly if your
statements include subquery SELECTs or multicolumn UPDATEs.
25) Consider
the alternatives EXISTS, IN and table joins when doing multiple table joins.
None of these are consistently faster; it depends on your data. If there is a
poor performer here, it's likely the IN clause.
26) Avoid
joins that require the DISTINCT qualifier on the SELECT list in queries which
are used to determine information at the owner end of a one-to-many
relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying
the table join and then sort and filter out duplicate values. EXISTS is a
faster alternative, because the Oracle optimizer realizes when the subquery has
been satisfied once, there is no need to proceed further and the next matching
row can be fetched.
27) Consider
whether a UNION ALL will suffice in place of a UNION. The UNION clause forces
all rows returned by each portion of the UNION to be sorted and merged and
duplicates to be filtered before the first row is returned. A UNION ALL simply
returns all rows including duplicates and does not have to perform any sort,
merge or filter. If your tables are mutually exclusive (include no duplicate
records), or you don't care if duplicates are returned, the UNION ALL is much
more efficient.
28) Consider
using DECODE to avoid having to scan the same rows repetitively or join the
same table repetitively. Note, DECODE is not necessarily faster as it depends
on your data and the complexity of the resulting query. Also, using DECODE
requires you to change your code when new values are allowed in the field.
29) Oracle
automatically performs simple column type conversions (or casting) when it
compares columns of different types. Depending on the type of conversion,
indexes may not be used. Make sure you declare your program variables as the
same type as your Oracle columns, if the type is supported in the programming
language you are using.
30) If you
specify 2 tables in the FROM clause of a SELECT statement, the parser will
process the tables from right to left, so the table name you specify last will
be processed first. In this case you have to choose one table as driving table.
Always choose the table with less number of records as the driving table.
31) If three
tables are being joined, select the intersection tables as the driving table.
The intersection table is the table that has many tables dependent on it.
32) Never
compare NULL to anything else. All expressions return NULL if one of the
operands is NULL. This is applicable for all operators except Concatenation
operator (||).
33) Use
DECODE when you want to scan same rows repetitively or join the same table
repetitively.
34) Always
use table alias and prefix all column names with the aliases when you are using
more than one table.
35) Use NOT
EXISTS in place of NOT IN.
36) Use Joins
in place of EXISTS.
37) Use
EXISTS in place of DISTINCT.
38) The
optimizer fully evaluates expressions whenever possible and translates certain
syntactic constructs into equivalent constructs. This is done either because
Oracle can more quickly evaluate the resulting expression than the original
expression or because the original expression is merely a syntactic equivalent
of the resulting expression.
39) Never use
NOT on an indexed column. Whenever Oracle encounters a NOT on an index column,
it will perform full-table scan.
40) Never use
a function / calculation on an indexed column. If there is any function is used
on an index column, optimizer will not use index. Use some other alternative.
41) Use AFTER
row triggers instead of BEFORE row triggers if at all possible.
42) Use a
WHEN clause on the trigger if at all possible so that the trigger is only fired
when absolutely necessary.
43) Are there
8 or more tables in the join? If so then the CBO may not be even considering
the join order that you want to use. Consider using the
Parameter:OPTIMIZER_MAX_PERMUTATIONS parameter to change the maximum number of
permutations considered.
44) Add or
remove indexes (BOTH CBO/RBO). Additional indexes may provide a better access
path for slow queries. Equally an unselective index may be causing an
inefficient access path. The CBO should be able to determine the relative
usefulness of different indexes if the statistics provided are adequate. If
indexes are causing a problem but cannot be removed then they can be disabled
using hints or other methods.
45) If the
problem relates to CPU bound applications then CPU information for each session
can be examined to determine the culprits. The v$sesstat view can be queried to
find high cpu using sessions and then SQL can be listed as before.
47) Late row
elimination. Queries are more likely to be performant if the bulk of the rows
can be eliminated early in the plan. If this does happen then unnecessary
comparisons may be made on rows that are simply eliminated later. This tends to
increase CPU usage with no performance benefits. If these rows can be
eliminated early in the access path using a selective predicate then this may
significantly enhance the query performance.
48) Missing
indexes/use of 'wrong' indexes .If indexes are missing on key columns then
queries will have to use Full Table Scans to retrieve data. Usually indexes for
performance should be added to support selective predicates included in
queries.
49) Wrong
plan or join order selected. If the wrong plan has been selected then you may
want to force the correct one. If the problem relates to an incorrect join
order, then it ofter helps to draw out the tables linking them together to show
how they join e.g.
A-B-C-D
E-F
This can help with visualisation of the join order
and identifications of missing joins. When tuning a plan, try different join
orders examining number of rows returned to get an idea of how good they may
be.
50) Insufficiently high sample rate for CBO (Cost
Based Optimization). If the CBO does not have the correct statistical
information then it cannot be expected to produce accurate results. Usually a
sample size of 5% will be sufficient, however in some cases it may be necessary
to have more accurate statistics at it’s' disposal
51) Use
Explain plan to find problematic queries.
52) Use the
top command to find the worst user on the system at a given point of time.
53) The sar
and iostsat commands can be used to find potential disk I/O problem areas.
These commands are further enhanced by utilizing the capabilities of shell
scripting.
54) Use the
vmstat command to find blocked processes (users waiting for CPU time) and also
for paging and swapping problems. The vmstat command is a great way to see many
of the sar options in one screen.
55) Retrieving
information from memory is over 10,000 times (depending on the memory you have)
faster than retrieving it from disk, so make sure that the SGA is large enough.
Ensure that the INIT.ORA parameter SORT_AREA_SIZE is large enough to
accommodate sorts in memory; otherwise, the sort will take place on disk.
Caching an often used but relatively small table into memory will ensure that
the data is not pushed out of memory by other data.
56) Concatenated
(composite) indexes that can be used to satisfy a query without accessing the
table. This eliminates the need to go to the table to retrieve the data,
reducing I/O.
57) Separate
key Oracle data files to ensure that disk contention is not a bottleneck. By
separating tables and indexes of often-joined tables, you can ensure that even
the worst of table joins do not result in disk contention. Solve disk
contention problems by moving data files to disks that are not as heavily
accessed.
58) To
minimize disk I/O on a single large table, break the table into multiple
partitions that reside on different physical disks. Partitions can dramatically
increase performance.
59) Add
larger and drop the smaller redo log files to increase the speed of large
INSERT, UPDATE, and DELETE statements.
60) De-normalise
frequently joined tables. Normalisation is an analysis technique, not a design
technique.
61) Make sure
indexes are being used where appropriate. Functions on an indexed column
(except for Oracle8i and above function-based indexes) will prevent the use of
an index.
e.g. select <my_data> from <my_table>
where max(indexed_col) = <some_val> will require a full table scan of the
table being queried.
62) If you
have an OLTP system, beware of having too many indexes. Whenever records are
inserted or deleted the corresponding index entries have to be created or
removed, generating twice as much work for the database.
63) Use
bit-map indexes for columns that store boolean/flag values.
64) The parse
phase for statements can be decreased by efficient use of aliasing. This helps
the speed of parsing the statements in two ways:
• If an
alias is not present, the engine must resolve which tables own the specified
columns.
• A short
alias is parsed more quickly than a long table name or alias. If possible,
reduce the alias to a single letter.
65) List of
factors that will prevent an index from being used:
• The
optimizer decides it would be more efficient not to use the index. As a rough
rule of thumb, on evenly distributed data an index will be used if it restricts
the number rows returned to 5% or less of the total number of rows. In the case
of randomly distributed data, an index will be used if it restricts the number
of rows returned to 25% or less of the total number of rows.
• You
perform a function on the indexed column i.e. WHERE Upper(name) = 'JONES'
• You
perform mathematical operations on the indexed column i.e. WHERE salary + 1 =
10001
• You
concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
• You do
not include the first column of a concatenated index in the WHERE clause of
your statement. For the index to be used in a partial match, the first column
(leading-edge) must be used.
• It is
debatable whether the order of columns referencing the index is important.
There are sometimes differences in the performance of the statement depending
on column order, especially when using Rule Based Optimization (RBO).
• The use
of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely
choose to use an index on column referenced using an OR statement. It will even
ignore optimizer hints in this situation. The only way of guaranteeing the use
of indexes in these situations is to use the /*+ RULE */ hint.
66) The
structure of the FROM and WHERE clauses of DML statements can be tailored to
improve the performance of the statement. The rules vary depending on whether
the database engine is using the Rule or Cost based optimizer. The situation is
further complicated by the fact that the engine may perform a Merge Join or a
Nested Loop join to retrieve the data. Despite this, there are a few rules you
can use to improve the performance of your SQL.
67) Queries
will execute much faster if the data they reference is already cached. For
small frequently used tables performance may be improved by caching tables.
Normally, when full table scans occur, the cached data is placed on the Least
Recently Used (LRU) end of the buffer cache. This means that it is the first
data to be paged out when more buffer space is required. If the table is cached
(ALTER TABLE employees CACHE;) the data is placed on the Most Recently Used
(MRU) end of the buffer, and so is less likely to be paged out before it is
re-queried. Caching tables may alter the CBO's path through the data and should
not be used without careful consideration
68) The
EXISTS function searches for the presence of a single row meeting the stated
criteria as opposed to the IN statement which looks for all occurrences. Use
Exists to avoid processing overhead.
PRODUCT- 1000 rows
ITEMS - 1000 rows
(A)
SELECT p.product_id
FROM products p
WHERE p.item_no IN (SELECT i.item_no
FROM items i);
(B)
SELECT p.product_id
FROM products p
WHERE EXISTS (SELECT '1'
FROM items i
WHERE i.item_no = p.item_no)
For query
A, all rows in ITEMS will be read for every row in PRODUCTS. The effect will be
1,000,000 rows read from items. In the case of query B, a maximum of 1 row from
ITEMS will be read for each row of PRODUCTS, thus reducing the processing
overhead of the statement.
69) If a query uses inequalities (item_no > 100)
the optimizer must estimate the number of rows returned before it can decide
the best way to retrieve the data. This estimation is prone to errors. If you
are aware of the data and it's distribution you can use optimizer hints to
encourage or discourage full table scans to improve performance.
If an index is being used for a range scan on the
column in question, the performance can be improved by substituting >= for
>. In this case, item_no > 100 becomes item_no >= 101. In the first
case, a full scan of the index will occur. In the second case, Oracle jumps
straight to the first index entry with an item_no of 101 and range scans from
this point. For large indexes this may significantly reduce the number of
blocks read.
70) Identify
the specific statement(s) that are causing a problem. The simplest way to do
this usually involves running the individual statements using SQLPlus and
timing them (SET TIMING ON)
71) Use
EXPLAIN to look at the execution plan of the statement. Look for any full table
accesses that look dubious. Remember, a full table scan of a small table is
often more efficient than access by rowid.
72) Check to
see if there are any indexes that may help performance. A quick way to do this
is to run the statement using the Rule Based Optimizer (RBO) (SELECT /*+ RULE
*/ ). Under the RBO, if an index is present it will be used. The resultant
execution plan may give you some ideas as to what indexes to play around with.
You can then remove the RULE hint and replace it by the specific index hints
you require. This way, the CBO will still be used for table accesses where
hints aren't present. Remember, if data volumes change over time, the hint that
helped may become a hindrance! For this reason, hints should be avoided if
possible, especially the /*+ RULE */ hint.
73) Try
adding new indexes to the system to reduce excessive full table scans.
Typically, foreign key columns should be indexed as these are regularly used in
join conditions. On occasion it may be necessary to add composite
(concatenated) indexes that will only aid individual queries. Remember,
excessive indexing can reduce INSERT, UPDATE and DELETE performance.
74) Order of
the tables in Joins: If you specify 2 or more tables in the FROM clause of a
SELECT statement, then Oracle parser will process the tables from right to
left, so the table name you specify last will be processed first. In this case
you have to choose one table as driving table.Always choose the table with less
number of records as the driving table.
75) Name the
Columns in a Query: There are three good reasons why it is better to name the
columns in a query rather than to use "select *
from ...".
1. Network
traffic is reduced. This can have a significant impact on performance if the
table has a large number of columns, or the table has a long or long raw column
(both of which can be up to 2 GB in length). These types of columns will take a
long time to transfer over the network and so they should not be fetched from
the database unless they are specifically required.
2. The code
is easier to understand.
3. It could
save the need for changes in the future. If any columns is added to or removed
from the base table/view, then “select * “statement can produce wrong results
set and statement may fall
76) Avoid
High Cardinality Operations.
77) Avoid
High cost Operations.
78)
Use Bind Variables: It is also better to use bind
variables in queries. That way the query becomes generic and therefore re-usable.
For example, instead of writing a query like –
SELECT ename, sal
FROM emp
WHERE deptno = 20;
Change it to –
SELECT ename, sal
FROM emp
WHERE deptno = :deptno;
The first query can be re-used for deptno number 20
only, whereas the second query can be reused for any other deptno also.
79) Avoid
REVERSE KEY index together with a FOREIGN Key.
80)
Use of expressions and indexes: The optimizer fully
evaluates expressions whenever possible and translates certain syntactic
constructs into equivalent constructs. This is done either because Oracle can
more quickly evaluate the resulting expression than the original expression or
because the original expression is merely a syntactic equivalent of the
resulting expression.
81)
Use of NOT operator on indexed columns: Never use
NOT operator on an indexed column. Whenever Oracle encounters a NOT on an index
column, it will perform full-table scan.
For
Example:
SELECT *
FROM emp
WHERE NOT deptno = 0;
Instead use the following:
SELECT *
FROM emp
WHERE deptno > 0;
82) Function or Calculation on indexed columns:
Never use a function or calculation on an indexed column. If there is any
function is used on an index column, optimizer will not use index.
For Example:
Do not use until need exactly match string:
SELECT *
FROM emp
WHERE SUBSTR (ename, 1, 3) = 'MIL';
Use following instead:
SELECT *
FROM emp
WHERE ename LIKE 'MIL%';
83) Do not use the following as || is the
concatenate function. Like other functions and it disables index.
SELECT *
FROM emp
WHERE ename || job = 'MILLERCLERK';
Use the following instead
SELECT *
FROM emp
WHERE ename = 'MILLER' AND job = 'CLERK';.
84) Avoid Transformed Columns in the WHERE Clause:
Use untransformed column values.
For example, use:
WHERE a.order_no = b.order_no
Rather than
WHERE TO_NUMBER (SUBSTR(a.order_no,
INSTR(b.order_no, '.') - 1))
= TO_NUMBER
(SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
85) Combine
Multiples Scans with CASE Statements: Often, it is necessary to calculate
different aggregates on various sets of tables. Usually, this is done with
multiple scans on the table, but it is easy to calculate all the aggregates
with one single scan. Eliminating n-1 scans can greatly improve performance.
Combining
multiple scans into one scan can be done by moving the WHERE condition of each
scan into a CASE statement, which filters the data for the aggregation. For each
aggregation, there could be another column that retrieves the data.
The
following example has count of all employees who earn less then 2000, between
2000 and 4000, and more than 4000 each month. This can be done with three
separate queries.
SELECT COUNT (*)
FROM emp
WHERE sal < 2000;
SELECT COUNT (*)
FROM emp
WHERE sal BETWEEN 2000 AND 4000;
SELECT COUNT (*)
FROM emp
WHERE sal>4000;
However,
it is more efficient to run the entire query in a single statement. Each number
is calculated as one column. The count uses a filter with the CASE statement to
count only the rows where the condition is valid. For example:
SELECT COUNT (
CASE WHEN sal < 2000
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN sal BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN sal > 4000
THEN 1 ELSE null END) count3
FROM emp;
86)
Find problem queries “hurting” memory. A single
index or a single query can bring an entire system to a near standstill. By
using v$sqlarea, you can find the problem queries on your system. Below, the
example shows how to find the problem queries. I am searching for queries where
the disk reads are greater than 10,000. If your system is much larger, you may
need to set this to a higher number.
87)
Using Key Hints for Optimization: The Oracle
optimizer is not perfect; however, there are HINTS that can be used to change
how the optimizer behaves. Eventually, you will find a query that requires
specific tuning attention. When the query is found, you must take advantage of
the “hints” that Oracle offers for tuning individual queries. The syntax for the
main hints are listed below. Keep in mind, the syntax must be correct or the
hint will be ignored, and no error message will be issued. Also, remember that
hints only apply to the statement they are in. Nested statements are treated as
totally different statements, requiring their own hints. I will cover the most
effective hints (many more are available) for query tuning.
88) If you
compare the wrong data types, your index may be suppressed internally. This is
because Oracle will re-write the query so that the comparison is correct. This
problem is at times difficult to track down.
89) You
must be careful when writing queries or you may unintentional suppress (turn
off) an index that you may have intended on using. Any function that modifies
the column name in a WHERE clause will suppress the corresponding index. In
Oracle8I, there are function-based indexes that allow indexes to be built on
functions like UPPER, SUBSTR and DECODE. Many common functions that are used to
suppress a standard index are listed below
• NOT / IS
NULL / != or <>
• Comparing
a number field to a character field
• Any
modification to the Indexed Column Name
• (TO_CHAR,
TO_DATE, +0, || '', SUBSTR, DECODE...)
90)
Developers using the UPPER function can suppress an
index on a column for a given query. In Oracle8i, there is now a way to combat
this problem. Function-based indexes allow you to create an index based on a
function or expression. The value of the function or expression is specified by
the person creating the index and is stored in the index. Function-based
indexes can involve multiple columns, arithmetic expressions or may be a PL/SQL
function or C callout.
91)
Using nested subqueries instead of joining tables
in a single query can lead to dramatic performance gains (at times over 1000%).
Only certain queries will meet the criteria for making this modification. When
you find the right one, this trick will take performance improvement to an
exponentially better height. The conditions for changing a query to a nested
subquery occur when:
• Tables
are being joined to return the rows from ONLY one table.
• Conditions
from each table will lead to a reasonable percentage of the rows to be
retrieved (more then 10%)
92)
A better way for measuring the performance of
queries is to use the AUTOTRACE command.
93)
When multiple indexes on a single table are used
within a query, use the most restrictive index. While Oracle’s cost-based
optimizer will generally force the use of the most restrictive index,
variations can occur based on the version of oracle used and structure of the
query. Forcing the use of the most restrictive index will guarantee the best
performance.
94) Any
modification to the column side of the query results in the suppression of the
index.
95)
In a three-table join, the driving table is the
intersection table or the table that has join condition to each of the other
two tables in the join. Try to use the most limiting table as the driving table
(or intersection table) so that your result set from the join of the first two
tables is small when you join it to the third table. Also, ensure that all join
conditions on all tables are indexed.
96)
To ensure that you are reading your EXPLAIN PLAN
correctly, run a query for which you are sure of the driving table (with nested
subqueries)
97)
Identify tables for caching. You can frequently
find small, frequently accessed tables for placement in the KEEP pool or for
use with the CACHE option.
98)
Identifying tables for row sequencing. You can
locate large tables that have frequent index-range scans in order to resequence
the rows, to reduce I/O.
99)
Dropping unused indexes. You can reclaim space
occupied by unused indexes. Studies have found that an Oracle database that
never uses more than a quarter of all indexes available or doesn’t use them in
the way for which they were intended.
100)
Avoid literal values in SQL. Any SQL statement that
contains embedded literal values is highly unlikely to be reused (e.g. select *
from sales where name=’JONES’;). These non-reusable statements can fill the
library cache with non-reusable SQL statements. The solution is to encourage
all developers to use host variables in all SQL.
101) Any of
the following conditions will cause Oracle to invoke a full-table scan:
• When no
indexes exist for the table.
• When a
query does not contain a where clause
• When an
indexed column references in invalidated by placing it inside a BIF
• When a
query use like operator and it begins with a ‘%’
• With the
cost-based optimizer, when a table contains a small number of rows.
• When the
optimizer_mode = all_rows in the initialization file.
Queries
with NULL conditions. Oracle cannot use an index to select NULL column values
because the NULL’s are not stored in the index.
• Queries
with a NOT Equals Condition. The not equals condition or a not in condition
will always use a full-table scan unless the common values are skewed and
column histogram indicate that the index scan is faster.
Niemiec’s 7 Rules of Tuning:
Rule 1:
The level of tuning achieved can be directly attributable to the number of
straight
hours
that you can work and how much junk food is available.
Rule 2:
The level of tuning achieved is tremendously increased if user input is
solicited and those users are NOT of the type that try to be politically
correct (i.e. You need users that are not afraid to say that this report runs
horribly!).
Rule 3:
The level of tuning achieved can be directly attributable to the security
access to the system that the tuning professional has.
Rule 4:
The level of tuning achieved is severely hampered by the level of theoretical
knowledge required by the tuning professional.
Rule 5:
The level of tuning achieved is severely hampered by the amount of time that a
manager is present.
Rule 6:
The level of tuning achieved by the number of keyboards, terminals, monitors
and PC’s that are within the reach of the tuning professional.
Rule 7:
The usual attributes of a good tuning professional (outside of actual performance)
can usually be spotted by the person who; calculates the shortest line at
McDonalds; calculates the most efficient method for getting each task done yet
still leaves at 1am; has coupons for every pizza place that stays open 24 hours
at their desk; tends to use twice as much coffee grounds when making the coffee
or uses caffeine enhanced water when making the coffee; asks if you would like
to go to lunch when it is time for dinner; answers email with a single or half
sentence (never a paragraph); has an occasional triple digit weekly hours
reported; has no time to be political; and when they have one hour left to go
with a problem, you can guarantee that you better multiply by at least four.
Details
21)
Do Not Use
|
Use
|
||
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
||
amount
|
FROM transaction
|
||
FROM transaction
|
WHERE account_name LIKE
'CAPITAL%';
|
||
WHERE
SUBSTR(account_name,1,7) =
|
|||
'CAPITAL';
|
|||
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
||
amount
|
FROM transaction
|
||
FROM transaction
|
WHERE account_name LIKE
NVL (
|
||
WHERE account_name = NVL (
|
:acc_name, '%');
|
||
:acc_name, account_name);
|
|||
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
||
amount
|
FROM transaction
|
||
FROM transaction
|
WHERE trans_date BETWEEN
TRUNC
|
|
WHERE TRUNC (trans_date) =
TRUNC
|
(SYSDATE) AND TRUNC
(SYSDATE) +
|
|
(SYSDATE);
|
.99999;
|
|
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
|
amount
|
||
FROM transaction
|
||
FROM transaction
|
||
WHERE account_name =
'AMEX'
|
||
WHERE account_name ||
account_type
|
||
AND account_type = 'A';
|
||
= 'AMEXA';
|
||
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
|
amount
|
||
FROM transaction
|
||
FROM transaction
|
||
WHERE amount < 2000;
|
||
WHERE amount + 3000 <
5000;
|
||
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
|
amount
|
||
FROM transaction
|
||
FROM transaction
|
||
WHERE amount > 0;
|
||
WHERE amount != 0;
|
||
SELECT account_name,
trans_date,
|
SELECT account_name,
trans_date, amount
|
|
amount
|
||
FROM transaction
|
||
FROM transaction
|
||
WHERE amount > 0;
|
||
WHERE amount NOT = 0;
|
||
23)
Do Not Use
|
Use
|
SELECT region, AVG
(loc_size)
|
SELECT region, AVG
(loc_size)
|
FROM location
|
FROM location
|
GROUP BY region
|
WHERE region != 'SYDNEY'
|
HAVING region != 'SYDNEY'
|
AND region != 'PERTH';
|
AND region != 'PERTH';
|
GROUP BY region;
|
24)
SELECT emp_name
|
||
FROM emp
|
||
Separate
|
WHERE emp_cat = (SELECT
MAX (category)
|
|
FROM emp_categories)
|
||
Subqueries
|
||
AND emp_range = (SELECT
MAX (sal_range)
|
||
FROM emp_categories)
|
||
AND emp_dept = 0020;
|
||
SELECT emp_name
|
||
FROM emp
|
||
Combined
|
WHERE (emp_cat, sal_range)
|
|
Subqueries
|
= (SELECT MAX (category),
MAX (sal_range)
|
|
FROM emp_categories)
|
||
AND emp_dept = 0020;
|
25)
(Note,
this query returns the employee names from each department in department
category 'A'.)
SELECT emp_name
FROM emp E
WHERE EXISTS ( SELECT 'X'
FROM dept
WHERE dept_no = E.dept_no
AND dept_cat = 'A');
SELECT emp_name
FROM emp E
WHERE dept_no IN ( SELECT
dept_no
FROM dept
WHERE dept_no = E.dept_no
AND dept_cat = 'A');
SELECT emp_name
FROM dept D, emp E
WHERE E.dept_no = D.dept_no
AND D.dept_cat = 'A';
26)
(Note: This query returns all department numbers and names which have at least
one employee.)
Do Not Use
|
Use
|
SELECT dept_no, dept_name
|
|
SELECT DISTINCT dept_no,
|
FROM dept D
|
dept_name
|
WHERE EXISTS (
|
FROM dept D,
|
SELECT 'X'
|
emp E
|
FROM emp E
|
WHERE D.dept_no =
E.dept_no;
|
WHERE E.dept_no =
|
D.dept_no);
|
27)
Do Not Use
|
Use
|
SELECT acct_num,
balance_amt
|
SELECT acct_num,
balance_amt
|
FROM debit_transactions
|
FROM debit_transactions
|
WHERE tran_date =
'31-DEC-95'
|
WHERE tran_date =
'31-DEC-95'
|
UNION
|
UNION ALL
|
SELECT acct_num,
balance_amt
|
SELECT acct_num,
balance_amt
|
FROM credit_transactions
|
FROM credit_transactions
|
WHERE tran_date =
'31-DEC-95';
|
WHERE tran_date =
'31-DEC-95';
|
28)
SELECT
COUNT(*) FROM emp WHERE status = 'Y'
AND emp_name LIKE 'SMITH%';
----------
SELECT
COUNT(*) FROM emp WHERE status = 'N'
AND emp_name LIKE 'SMITH%';
SELECT COUNT(DECODE(status,
'Y', 'X', NULL)) Y_count,
COUNT(DECODE(status, 'N',
'X', NULL)) N_count
WHERE emp_name LIKE
'SMITH%';
29)
Datatype
of field
|
Your
Query
|
After
Implicit
|
Index
|
|||||||||
in
where clause
|
Conversion
|
Used?
|
||||||||||
SELECT
...
|
SELECT
...
|
|||||||||||
emp_no
|
FROM
emp
|
|||||||||||
FROM
emp
|
||||||||||||
indexed
|
WHERE
|
YES
|
||||||||||
WHERE
emp_no =
|
||||||||||||
numeric
|
emp_no
=
|
|||||||||||
TO_NUMBER('123');
|
||||||||||||
'123';
|
||||||||||||
SELECT
...
|
SELECT
...
|
|||||||||||
emp_type
|
FROM
emp
|
|||||||||||
FROM
emp
|
NO!
|
|||||||||||
indexed
|
WHERE
|
|||||||||||
WHERE
TO_NUMBER
|
||||||||||||
varchar2
|
emp_type
=
|
|||||||||||
(emp_type)
= 123;
|
||||||||||||
123;
|
||||||||||||
33)
Example:
SELECT COUNT(*) , SUM(SAL)
FROM
|
EMP
|
WHERE
DEPTNO = 10
|
|
AND
|
ENAME LIKE 'MILLER' ;
|
SELECT
COUNT(*) , SUM(SAL)
|
|
FROM
|
EMP
|
WHERE
DEPTNO = 20
|
|
AND
|
ENAME LIKE 'MILLER' ;
|
The same result can be
achieved using a single query as follows:
SELECT
COUNT(DECODE(DEPTNO,20,'X')) DEPT20_COUNT,
COUNT(DECODE(DEPTNO,10,'X'))
DEPT10_COUNT,
SUM(DECODE(DEPTNO,20,SAL))
DEPT20_SAL,
SUM(DECODE(DEPTNO,10,SAL))
DEPT10_SAL
FROM EMP
WHERE ENAME LIKE 'MILLER' ;
35)
In
sub-query statement such as the following NOT IN clause causes an internal
Sort/Merge.
SELECT *
|
||
FROM
|
emp e
|
|
WHERE
|
e.deptno NOT IN ( SELECT
d.deptno
|
|
FROM
|
dept d
|
|
WHERE d.dname like %S% ) ;
|
||
To improve performance,
use the following code.
|
||
SELECT *
|
||
FROM
|
emp e
|
|
WHERE
|
NOT EXISTS ( SELECT
d.deptno
|
|
FROM dept d
WHERE d.deptno = e.deptno
AND d.dname LIKE '%S%' ) ;
36)
In general, join tables rather than specifying
sub-queries.
SELECT
*
|
||
FROM
|
emp e
|
|
WHERE
|
EXISTS ( SELECT d.deptno
|
|
FROM
|
dept d
|
|
WHERE e.deptno = d.deptno
|
||
AND
|
d.dname = 'RESEARCH') ;
|
|
To improve performance, use the following:
SELECT *
FROM
|
emp e, dept d
|
WHERE
e.deptno = d.deptno
|
|
AND
|
d.dname = 'RESEARCH' ;
|
37)
Use EXISTS in place of DISTINCT if you want the
result set to contain distinct values while joining tables.
SELECT DISTINCT d.deptno ,
d.dname ,
FROM dept d ,
emp e
WHERE d.deptno = e.deptno ;
The following SQL statement
is a better alternative.
SELECT d.deptno ,
d.dname
FROM dept d
WHERE EXISTS ( SELECT
e.deptno
FROM emp e
WHERE d.deptno = e.deptno )
;
38)
Any
computation of constants is performed only once when the statement is optimized
rather than each time the statement is executed. Consider these conditions that
test for monthly salaries greater than 2000:
sal > 24000/12
sal > 2000
sal*12 > 24000
If a SQL statement contains the first condition,
the optimizer simplifies it into the second condition.
Note that the optimizer does not simplify
expressions across comparison operators. The optimizer does
not simplify the third expression into the second. For this reason, application
developers should write conditions that compare columns with constants whenever
possible, rather than conditions with expressions involving columns.
The Optimizer does not use index for the following
statement.
SELECT *
FROM emp
WHERE sal*12 > 24000 ;
Instead use the following statement.
SELECT *
FROM emp
WHERE sal > 24000/12 ;
39)
SELECT *
FROM emp
WHERE NOT deptno = 0;
Instead use the following.
Note : Optimizer uses the index only when
optimizer_goal=FIRST_ROWS, otherwise it doesn't use it in the below query
either:
SELECT *
FROM emp
WHERE deptno > 0;
40)
Examples:
/** Do Not use **/
SELECT *
FROM emp
WHERE SUBSTR(ENAME,1,3) =
'MIL' ;
/** Suggested Alternative
**/
Note: Optimizer uses the index only when
optimizer_goal is set to FIRST_ROWS.
SELECT *
FROM emp
WHERE ENAME LIKE 'MIL%' ;
/** Do Not use **/
SELECT *
FROM emp
WHERE sal != 0 ;
Note: Index can tell you what is there in a table
but not what is not in a table.
Note: Optimizer uses the
index only when optimizer_goal = FIRST_ROWS.
/** Suggested Alternative
**/
SELECT *
FROM emp
WHERE sal > 0 ;
/** Do Not use **/
SELECT *
FROM emp
WHERE ename || job =
'MILLERCLERK' ;
Note: || is the concatenate function. Like other
functions it disables index.
/** Suggested Alternative
**/
Note : Optimizer uses the
index only when optimizer_goal=FIRST_ROWS.
SELECT *
FROM emp
WHERE ename = 'MILLER'
AND job = 'CLERK' ;
45)
1. Verify the reference number for the 'CPU used by
this session'
statistic:
|
|
SELECT name ,statistic#
|
|
FROM
|
v$statname
|
WHERE
|
name LIKE '%CPU%session';
|
NAME
|
STATISTIC#
|
-----------------------------------
----------
CPU
used by this session
|
12
|
2. Then determine which session is using most of
the cpu:
SELECT * FROM v$sesstat
WHERE statistic# = 12;
SID
STATISTIC#
|
VALUE
|
|||||
---------- ----------
----------
|
||||||
1
|
12
|
0
|
||||
2
|
12
|
0
|
||||
3
|
12
|
0
|
||||
4
|
12
|
0
|
||||
5
|
12
|
0
|
||||
6
|
12
|
0
|
||||
7
|
12
|
0
|
||||
8
|
12
|
0
|
||||
9
|
12
|
0
|
||||
10
|
12
|
0
|
||||
11
|
12
|
0
|
||||
12
|
0
|
|||||
16
|
12
|
1930
|
||||
3. Lookup details for this session:
SELECT address ,SUBSTR(sql_text,1,20) Text,
buffer_gets, executions, buffer_gets/executions AVG
FROM v$sqlarea a, v$session
s
WHERE sid = 16
AND s.sql_address = a.address
AND executions > 0
ORDER BY 5;
4. Use
v$sqltext to extract the whole SQL text.
5. Explain
the queries and examine their access paths. Autotrace is a useful tool for
examining access paths.
46)
The first
question to ask is 'Does the data REALLY need to be sorted?' If sorting does
need to be done then try to allocate enough memory to prevent the sorts from
spilling to disk an causing i/o problems.
Sorting is a very expensive operation:
- High CPU
usage
- Potentially
large disk usage
Try to
make the query sort the data as late in the access path as possible. The idea
behind this is to make sure that the smallest number of rows possible are
sorted.
Remember that:
- Indexes
may be used to provided presorted data.
- Sort
merge joins inherently need to do a sort.
- Some
sorts don't actually need a sort to be performed. In this case the explain plan
should show NOSORT for this operation.
In summary:
- Increase
sort area size to promote in memory sorts.
- Modify
the query to process less rows -> Less to sort
- Use an
index to retrieve the rows in order and avoid the sort.
- use
sort_direct_writes to avoid flooding the buffer cache with sort blocks.
51)
EXPLAIN
PLAN Usage
When an SQL statement is passed to the server the
Cost Based Optimizer (CBO) uses database statistics to create an execution plan
which it uses to navigate through the data. Once you've highlighted a problem
query the first thing you should do is EXPLAIN the statement to check the
execution plan that the CBO has created. This will often reveal that the query
is not using the relevant indexes, or indexes to support the query are missing.
Interpretation of the execution plan is beyond the scope of this article.
•
Plan Table
•
AUTOTRACE - The Easy Option?
•
EXPLAIN PLAN
•
Statement ID
Plan
Table
The explain plan process stores data in the
PLAN_TABLE. This table can be located in the current schema or a shared schema
and is created using in SQL*Plus as follows:
SQL>
@%ORACLE_HOME%\rdbms\admin\utlxplan.sql
SQL>
GRANT select, insert, update, delete ON plan_table TO public;
SQL>
CREATE PUBLIC SYNONYM plan_table FOR .plan_table;
AUTOTRACE
- The Easy Option?
Switching on the AUTOTRACE parameter in SQL*Plus
causes an explain to be performed on every query.
SQL>
SET AUTOTRACE ON SQL> SELECT * FROM dual;
D
-
X
1 row
selected.
Execution
Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1
0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive
calls 4 db block gets 1 consistent gets
0 physical
reads
0 redo size
363 bytes sent via SQL*Net
to client
429 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from
client
0 sorts
(memory)
0 sorts
(disk)
1 rows processed
SQL>
This is a relatively easy way to get the execution
plan but there is an issue. In order to get the execution plan the statement
must be run to completion. If the query is particularly inefficient and/or
returns many rows, this may take a considerable time.
EXPLAIN
PLAN
The EXPLAIN PLAN method doesn't require the query
to be run, greatly reducing the time it takes to get an execution plan for
long-running queries compared to AUTOTRACE. First the query must be explained:
SQL> EXPLAIN PLAN FOR 2 SELECT * FROM dual;
Explained.
SQL>
Then the
execution plan displayed:
SQL>
@%ORACLE_HOME%\rdbms\admin\utlxpls.sql
Plan
Table
-------------------------------------------------------------------------
| Operation |
Name | Rows | Bytes| Cost | Pstart|
Pstop |
-------------------------------------------------------------------------
|
SELECT STATEMENT |
|
|
|
|
|
|
|
|
|
|
|
|
|
| TABLE
ACCESS FULL |DUAL
|
|
|
|
|
|
|
|
|
|
|
|
|
-------------------------------------------------------------------------
5 rows
selected.
SQL>
For
parallel queries use the utlxplp.sql script instead of utlxpls.sql.
Statement
ID
If multiple people are accessing the same plan
table, or you would like to keep a history of the execution plans you should
use the STATEMENT_ID. This associates a user specified ID with each plan which
can be used when retrieving the data.
SQL> EXPLAIN PLAN STATEMENT_ID = 'TIM' FOR 2
SELECT * FROM dual;
Explained.
SQL>
By default the Oracle scripts to not accept a
statement_id parameter. You can easily modify the scripts or you can use the
script listed under DBA Scripts on this site.
66)
Oracle
processes result sets a table at a time. It starts by retrieving all the data
for the first (driving) table. Once this data is retrieved it is used to limit
the number of rows processed for subsequent (driven) tables. In the case of
multiple table joins, the driving table limits the rows processed for the first
driven table. Once processed, this combined set of data is the driving set for
the second driven table etc. Roughly translated into English, this means that
it is best to process tables that will retrieve a small number of rows first.
The optimizer will do this to the best of it's ability regardless of the
structure of the DML, but the following factors may help.
Both the
Rule and Cost based optimizers select a driving table for each DML statement.
If a decision cannot be made, the order of processing is from the end of the
FROM clause to the start. Therefore, you should always place your driving table
at the end of the FROM clause.
Subsequent
driven tables should be placed in order so that those retrieving the most rows
are nearer to the start of the FROM clause. Confusingly, the WHERE clause
should be writen in the opposite order, with the driving tables conditions
first and the final driven table last. ie.
FROM d, c, b, a
WHERE a.join_column = 12345
AND a.join_column = b.join_column
AND b.join_column = c.join_column
AND c.join_column = d.join_column;
If we now
want to limit the rows brought back from the "D" table we may write
the following:
FROM d, c, b, a
WHERE a.join_column = 12345
AND a.join_column = b.join_column
AND b.join_column = c.join_column
AND c.join_column = d.join_column
AND d.name = 'JONES';
Depending on the number of rows and the presence of
indexes, Oracle my now pick "D" as the driving table. Since
"D" now has two limiting factors (join_column and name), it may be a
better candidate as a driving table so the statement may be better written as:
FROM c, b, a, d
WHERE d.name = 'JONES'
AND d.join_column = 12345
AND d.join_column = a.join_column
AND a.join_column = b.join_column
AND b.join_column = c.join_column
This
grouping of limiting factors will guide the optimizer
|
more
efficiently making table "D"
|
return relatively
few rows, and
so make it a
|
more efficient
driving table.
|
Remember, the order of the items in both the FROM
and WHERE clause will not force the optimizer to pick a specific table as a
driving table, but it may influence it's decision. The grouping of limiting
conditions onto a single table will reduce the number of rows returned from
that table, and will therefore make it a stronger candidate for becoming the
driving table.
76)
High
cardinality operations
Cardinality is the expected number of rows returned
from an operation. Therefore high cardinality operations are those that process
or return large numbers of rows. Large numbers of rows can cause problems when
optimizing and executing queries. Depending on the access methods used, the
performance of a particular access path is highly dependant on the number of
rows involved in the query.
Typically, when faced with high cardinality, the
goal is to reduce the number of rows processed by each step in the query as
much as possible. The most benefit should be gained by reducing the number of
rows as early in the query as possible. Otherwise operations may be performed
on rows that are eliminated later, thus wasting resources. Specifically, look
for the following:
• Operations
that drive other operations (i.e. outer tables in Nested Loop joins)
The
efficiency of a nested loop join relies on 2 factors: the number of rows in the
driving row source and the size of the inner row source. Nested loop joins work
by reading the outer row source and then executing the inner row source once
for every row in the outer. For a nested loop joins to operate efficiently, the
number of rows in the outer row source should be a small as possible. If the
number of times the inner row source is probed is excessive then this can have
a major affect on performance. If a remote (in a distributed query) or other expensive
row source is used as the inner then any changes in the number of rows in the
outer row source can have a drastic affect on the overall query performance.
• Operations
that have functions applied to them and Sorting/Aggregate/Merging Operations
All these
operations involve use of cpu to manipulate a row source or multiple row
sources. The more rows there are, the more cpu is likely to be consumed.
Unfortunately, in current releases, CBO does not really take cpu costs into
account because it is felt that cpu cost is likely to be insignificant compared
to io costs and so plans are be chosen on the basis that they minimise io cost
instead. This policy will usually provide a decent access plan but sometimes,
with large numbers of rows and complex computations, cpu usage can become a
significant factor in query performance and the io based plan may not turn out
to be the best available. To avoid excessive cpu activity, cpu intensive
operations should be performed when as many rows have been removed as possible
(i.e. as late as possible in the query execution or when the number of rows
involved is as close as possible to the final number of rows).
• Index
Range scans
Indexes
are especially good at finding and returning small numbers of rows quickly.
Index range scans involve single block io and key comparisons are cpu intensive
operations. If large numbers of rows are being processed using indexes then it
is likely that there is a more efficient access method available.
Guidelines
to reduce the impact of high cardinality operations:
• Include
more selective predicates
• Apply the
selective predicates as early as possible to reduce the number of rows operated
by the subsequent steps
• Apply
operations whose performance is row dependant as late as possible in the
queries execution when the result set is complete.
• Potentially,
find alternative, less expensive, methods of achieving the same goal. For
example, if there are performance issues associated with sorting a large row
source, then consider driving off a pre-sorted index to avoid the sort at
execution time.
77)
High cost
operations
This section covers operations that consume
significant amounts of operating system io. Both the RBO and CBO can be
effected by high cost operations, but it is easier to identify potentially
expensive operations from CBO explain plan output.
If high cost operations are present the solutions
are similar to the high cardinality options: try to minimise the quantity of
data that is under manipulation at each step of the query and attempt to
eliminate as much data as possible early in the execution of the query.
Additionally there are numerous system changes that can be implemented to
memory etc that can reduce the io requirements of queries. These are beyond the
scope of this document and are discussed at length in other articles..
79)
Reverse
key index on foreign key column allows deletion of parent key !
The FOREIGN KEY integrity constraint is provided to
define and ensure the integrity of a parent-child relationship between two
tables. It requires that each value in a column, or a set of columns match a
value in a related (parent) table's UNIQUE or PRIMARY KEY. FOREIGN KEY
integrity constraints also define referential integrity actions such as ON
DELETE CASCADE which specifies that upon the deletion of the row in the parent
table, all corresponding rows in the referential (child) tables will be deleted
as well. If the ON DELETE CASCADE option is not specified for the FOREIGN KEY
constraint, the deletion of the row from the parent table is prevented with an
error message signalling the presence of corresponding rows in a child table.
If, however, a REVERSE KEY index is created on the
column(s) defined in the FOREIGN KEY integrity constraint, the deletion of the
row in the parent table is allowed to proceed without error, thereby leaving
orphaned rows in the corresponding child table(s). If the FOREIGN KEY was
created with the ON DELETE CASCADE option, this directive is ignored and the
corresponding rows in the child table are not deleted.
Likelihood
of Occurence
If you have defined a REVERSE KEY index on
column(s) designated as a FOREIGN KEY constraint and have deleted rows from the
parent table, then it is likely that you will have orphaned rows in the child
table.
There are no error messages generated unless you
disable and attempt to re-enable the FOREIGN KEY constraint. If orphaned rows
exist in the child table, you will receive an ORA-02291 error message listing
the name of the FOREIGN KEY constraint. "ORA-02291:integrity constraint
(SCOT7.FK-DEPTNO),violated-parent key not found"
80)
Any
computation of constants is performed only once when the statement is optimized
rather than each time the statement is executed. Consider these conditions that
test for salaries greater than $2000.
sal >
24000/12 sal > 2000 sal*12 > 24000
If a SQL
statement contains the first condition, the optimizer simplifies it into the
second condition.
Please note that optimizer does not simplify
expressions across comparison operators. The optimizer does not simplify the
third expression into the second. For this reason, we should write conditions
that compare columns with constants whenever possible, rather than conditions
with expressions involving columns.
The Optimizer does not use index for the following
statement:
SELECT *
FROM emp
WHERE sal*12 > 24000 ;
Instead of this use the
following statement:
SELECT *
FROM emp
WHERE sal > 24000/12 ;
86)
(Finding the largest amount of physical reads by
query):
select
|
disk_reads, sql_text
|
||
from
|
v$sqlarea
|
||
where
|
disk_reads > 10000
|
||
order
|
by disk_reads desc;
|
||
DISK_READS
|
SQL_TEXT
|
||
------------------
|
-----------------------------------------------------------------
|
||
12987
|
select
order#,columns,types from orders
|
||
where
substr(orderid,1,2)=:1
|
|||
11131
|
select custid, city from
customer
|
||
where city = ‘CHICAGO’
|
|||
(Finding
the largest amount of logical reads by query):
|
|||
select
|
buffer_gets, sql_text
|
||
from
|
v$sqlarea
|
||
where
|
buffer_gets > 200000
|
||
order
by
|
buffer_gets desc;
|
||
BUFFER_GETS
SQL_TEXT
|
|||
------------------
|
-----------------------------------------------------------------
|
||
300219
|
select order#,cust_no,
from orders
|
||
where division = ‘1’
|
|||
You may need to join to the v$sqltext view:
You may
have to join in the v$sqltext table to get the full text since v$sqlarea only
shows a portion of the SQL_TEXT.
Break on User_Name On Disk_Reads on Buffer_Gets on
Rows_Processed
select
itemno, custno from items where nvl(orderno,0) = 131313 or nvl(orderno,0) =
777777
Few Examplaes:
1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;
2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as
For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;
Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';
3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';
4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items
5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
For Example: Write the query as
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;
6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;
7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';
Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier.
Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;
8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';
Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';
9) To store large binary objects, first place them in the file system and add the file path in the database.
10) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb