Many thanks to my editor, Jonathan Gennick. His feedback and suggestions have added significant improvements and clarity to this book. A hearty thanks to my team of technical reviewers: Sanjay Mishra, Stephen Andert, and Tim Gorman.Thanks also to my Mark Gurry & Associates consultants for their technical feedback. Special thanks to my wife Juliana for tolerating me during yet another book writing exercise.

This book does not cover every type of environment, nor does it cover all performance tuning scenarios that you will encounter as an Oracle DBA or developer.

I can’t stress enough the importance of regular hands-on testing in preparation for being able to implement your performance tuning recommendations.

It’s always exciting to get a new release of Oracle. This section briefly lists the new Oracle9i features that will assist us in getting SQL performance to improve even further than before. The new features are as follows:

The rule-based optimizer (RBO) uses a predefined set of precedence rules to figure out which path it will use to access the database. The RDBMS kernel defaults to the rule-based optimizer under a number of conditions, including:

The rule-based optimizer is driven primarily by 20 condition rankings, or “golden rules.” These rules instruct the optimizer how to determine the execution path for a statement, when to choose one index over another, and when to perform a full table scan. These rules, shown in Table 1-1, are fixed, predetermined, and, in contrast with the cost-based optimizer, not influenced by outside sources (table volumes, index distributions, etc.).

While knowing the rules is helpful, they alone do not tell you enough about how to tune for the rule-based optimizer. To overcome this deficiency, the following sections provide some information that the rules don’t tell you.

SELECT col1, ...
  FROM emp
 WHERE emp_name = 'GURRY'
   AND emp_no   = 127
   AND dept_no  = 12

Index1 (dept_no)
Index2 (emp_no, emp_name)

SELECT col1, ...
  FROM emp
 WHERE emp_name = 'GURRY'
   AND emp_no   = 127
   AND dept_no  = 12

Index1 (emp_name)
Index2 (emp_no, dept_no, cost_center)

SELECT col1, ...
  FROM emp
 WHERE emp_name = 'GURRY'
   AND emp_no   = 127
   AND dept_no  = 12
   AND emp_category = 'CLERK'

Index1 (emp_name, emp_category)  Created 4pm Feb 11th 2002
Index2 (emp_no, dept_no) Created 5pm Feb 11th 2002

SELECT col1, ...
  FROM emp
 WHERE emp_name  LIKE 'GUR%'
   AND emp_no       = 127
   AND dept_no      = 12
   AND emp_category = 'CLERK'
   AND emp_class    = 'C1'

Index1 (emp_category, emp_class, emp_name)  
Index2 (emp_no, dept_no)

SELECT col1, ...
  FROM emp
 WHERE emp_name     = 'GURRY'
   AND emp_no       = 127
   AND emp_class    = 'C1'

Index1 (emp_name, emp_class, emp_category)  
Index2 (emp_no, dept_no)

SELECT col1, ...
  FROM emp
 WHERE emp_name     = 'GURRY'

Index1 (emp_name, emp_class, emp_category) 
Index2 (emp_class, emp_name, emp_category)

SELECT col1, ...
  FROM emp
 WHERE LTRIM(emp_name) = 'GURRY'

The cost-based optimizer is a more sophisticated facility than the rule-based optimizer. To determine the best execution path for a statement, it uses database information such as table size, number of rows, key spread, and so forth, rather than rigid rules.

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. If a table has not been analyzed, the cost-based optimizer can use only rule-based logic to select the best access path. It is possible to run a schema with a combination of cost-based and rule-based behavior by having some tables analyzed and others not analyzed.

A SQL statement will default to the cost-based optimizer if any one of the tables involved in the statement has been analyzed. The cost-based optimizer then makes an educated guess as to the best access path for the other tables based on information in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

ANALYZE TABLE EMP DELETE STATISTICS;

EXPLAIN PLAN FOR
  SELECT count(*)
    FROM winners, horses
   WHERE winners.owner=horses.owner
     AND winners.horse_name LIKE 'Mr %'
    
COLUMN "SQL" FORMAT a56
SELECT lpad(' ',2*level)||operation||''  
        ||options ||' '||object_name||
       decode(OBJECT_TYPE, '', '', 
           '('||object_type||')') "SQL",
       cost "Cost", cardinality "Num Rows"
  FROM   plan_table
CONNECT BY prior id = parent_id
  START WITH id = 0;

SQL                            Cost   Num Rows
-----------------------------------------------
SELECT STATEMENT                 44          1
    SORT AGGREGATE                                                       
      HASH JOIN                  44     100469
        INDEX RANGE SCAN MG1(NON-UNIQUE)                      
                                  2       1471
        INDEX FAST FULL SCAN OWNER_PK(UNIQUE)                    
                                  4       6830

Let’s clear up some common misconceptions regarding the optimizers:

If you are currently using the rule-based optimizer, I strongly recommend that you transfer to the cost-based optimizer. Here is a list of the reasons why:

If the table driving a join is not optimal, there can be a significant increase in the amount of time required to execute a query. Earlier, in Section 1.2.1.6, I discussed what decides the driving table. Consider the following example, which illustrates the potential difference in runtimes:

SELECT COUNT(*)
  FROM acct a, trans b
WHERE b.cost_center = 'MASS'
  AND a.acct_name = 'MGA'
  AND a.acct_name = b.acct_name;

In this example, if ACCT_NAME represents a unique key index and COST_CENTER represents a single column non-unique index, the unique key index would make the ACCT table the driving table.

If both COST_CENTER and ACCT_NAME were single column, non-unique indexes, the rule-based optimizer would select the TRANS table as the driving table, because it is listed last in the FROM clause. Having the TRANS table as the driving table would likely mean a longer response time for a query, because there is usually only one ACCT row for a selected account name but there are likely to be many transactions for a given cost center.

With the rule-based optimizer, if the index rankings are identical for both tables, Oracle simply executes the statement in the order in which the tables are parsed. Because the parser processes table names from right to left, the table name that is specified last (e.g., DEPT in the example above) is actually the first table processed (the driving table).

SELECT COUNT(*)
  FROM acct a, trans b
WHERE  b.cost_center = 'MASS'
  AND  a.acct_name = 'MGA'
  AND  a.acct_name = b.acct_name;

Response = 19.722 seconds

The response time following the re-ordering of the tables in the FROM clause is as follows:

SELECT COUNT(*)
  FROM trans b, acct a
WHERE  b.cost_center= 'MASS'
  AND  a.acct_name = 'MGA'
  AND  a.acct_name = b.acct_name;

Response = 1.904 seconds

It is important that the table that is listed last in the FROM clause is going to return the fewest number of rows. There is also potential to adjust your indexing to force the driving table. For example, you may be able to make the COST_CENTER index a concatenated index, joined with another column that is frequently used in SQL enquires with the column. This will avoid it ranking so highly when joins take place.

WHERE clauses often provide the rule-based optimizer with a number of indexes that it could utilize. The rule-based optimizer is totally unaware of how many rows each index will be required to scan and the potential impact on the response time. A poor index selection will provide a response time much greater than it would be if a more effective index was selected.

The rule-based optimizer has simple rules for selecting which index to use. These rules and scenarios are described earlier in the various “What the RBO rules don’t tell you” sections.

Let’s consider an example.

An ERP package has been developed in a generic fashion to allow a site to use columns for reporting purposes in any way its users please. There is a column called BUSINESS_UNIT that has a single-column index on it. Most sites have hundreds of business units. Other sites have only one business unit.

Our JOURNAL table has an index on (BUSINESS_UNIT), and another on (BUSINESS_UNIT, ACCOUNT, JOURNAL_DATE). The WHERE clause of a query is as follows:

WHERE business_unit  ='A203'
  AND account           = 801919
  AND journal_date between 
      '01-DEC-2001'     and '31-DEC-2001'

The single-column index will be used in preference to the three-column index, despite the fact that the three-column index returns the result in a fraction of the time of the single-column index. This is because all columns in the single-column index are used in the query. In such a situation, the only options open to us are to drop the index or use the cost-based optimizer. If you’re not using packaged software, you may also be able to use hints.

The way you specify conditions in the WHERE clause(s) of your SELECT statements has a major impact on the performance of your SQL, because the order in which you specify conditions impacts the indexes the optimizer choose to use.

If two index rankings are equal -- for example, two single-column indexes both have their columns in the WHERE clause -- Oracle will merge the indexes. The merge (AND-EQUAL) order has the potential to have a significant impact on runtime. If the index that drives the query returns more rows than the other index, query performance will be suboptimal. The effect is very similar to that from the ordering of tables in the FROM clause. Consider the following example:

SELECT COUNT(*) 
  FROM trans
 WHERE  cost_center = 'MASS'
   AND  bmark_id    = 9;

Response Time = 4.255 seconds 

The index that has the column that is listed first in the WHERE CLAUSE will drive the query. In this statement, the indexed entries for COST_CENTER = `MASS’ will return significantly more rows than those for BMARK_ID=9, which will return at most only one or two rows.

The following query reverses the order of the conditions in the WHERE clause, resulting in a much faster execution time.

SELECT COUNT(*) 
   FROM trans
WHERE  bmark_id    = 9
  AND  cost_center = 'MASS';

Response Time = 1.044 seconds

For the rule-based optimizer, you should order the conditions that are going to return the fewest number of rows higher in your WHERE clause.

A less common problem with index selection, which we have observed at sites using the rule-based optimizer, is illustrated by the following query and indexes:

SELECT fod_flag, account_no...
  FROM account_master
 WHERE (account_code like 'I%')
  ORDER BY account_no;

Index_1 UNIQUE (ACCOUNT_NO)
Index_2        (ACCOUNT_CODE)

With the indexes shown, the runtime of this query was 20 minutes. The query was used for a report, which was run by many brokers each day.

In this example, the optimizer is trying to avoid a sort, and has opted for the index that contains the column in the ORDER BY clause rather than for the index that has the column in the WHERE clause.

The site that experienced this particular problem was a large stock brokerage. The SQL was run frequently to produce account financial summaries.

This problem was repaired by creating a concatenated index on both columns:

# Added Index (ACCOUNT_CODE, ACCOUNT_NO)

We decided to drop index_2 (ACCOUNT CODE), which was no longer required because the ACCOUNT_CODE was the leading column of the new index. The ACCOUNT_NO column was added to the new index to take advantage of the index storing the data in ascending order. Having the ACCOUNT_NO column in the index avoided the need to sort, adding the index in a runtime of under 10 seconds.

Imagine that we are consulting at a site with a table TRANS that has a column called STATUS. The column has two possible values: `O’ for Open Transactions that have not been posted, and `C’ for closed transactions that have already been posted and that require no further action. There are over one million rows that have a status of `C', but only 100 rows that have a status of `O’ at any point in time.

The site has the following SQL statement that runs many hundreds of times daily. The response time is dismal, and we have been called in to “make it go faster.”

SELECT acct_no, customer, product, trans_date, amt
  FROM trans
 WHERE status='O';

Response time = 16.308 seconds

In this example, taken from a real-life client of mine, the cost-based optimizer decides that Oracle should perform a full table scan. This is because the cost-based optimizer is aware of how many distinct values there are for the status column, but is unaware of how many rows exist for each of those values. Consequently, the optimizer assumes a 50/50 spread of data for each of the two values, `O’ and `C’. Given this assumption, Oracle has decided to perform a full table scan to retrieve open transactions.

If we inform Oracle of the data skewness by specifying the option FOR ALL INDEXED COLUMNS when we run the ANALYZE command or when we invoke the DBMS_STATS package, Oracle will be made aware of the skewness of the data; that is, the number of rows that exist for each value for each indexed column. In our scenario, the STATUS column is indexed. The following command is used to analyze the table:

ANALYZE TABLE TRANS COMPUTE STATISTICS 
        FOR ALL INDEXED COLUMNS

After analyzing the table and computing statistics for all indexed columns, the cost-based optimizer is aware that there are only 100 or so rows with a status of `O', and it will accordingly use the index on that column. Use of the index on the STATUS column results in the following, much faster, query response:

               Response Time: 0.259 seconds

Typically the cost-based optimizer will perform a full table scan if the value selected for a column has over 12% of the rows in the table, and will use the index if the value specified has less than 12% of the rows. The cost-based optimizer selections are not quite as firm as this, but as a rule of thumb this is the typical behavior that the cost-based optimizer will follow.

Prior to Oracle9i, if a statement has been written to use bind variables, problems can still occur with respect to skewness even if you use FOR ALL INDEXED COLUMNS. Consider the following example:

local_status := 'O';

SELECT acct_no, customer, product, trans_date, amt
  FROM trans
WHERE status= local_status;

# Response time = 16.608 

Notice that the response time is similar to that experienced when the FOR ALL INDEXED columns option was not used. The problem here is that the cost-based optimizer isn’t aware of the value of the bind variable when it generates an execution plan. As a general rule, to overcome the skewness problem, you should do the following:

If you are still experiencing performance problems in which the cost-based optimizer will not use an index due to bind variables being used, and you can’t change the source code, you can try deleting the statistics off the index using a command such as the following:

ANALYZE INDEX
TRANS_STATUS_NDX 
DELETE STATISTICS

Deleting the index statistics works because it forces rule-based optimizer behavior, which will always use the existing indexes (as opposed to doing full table scans).

I have been invited to many, many sites that have performance problems at which I quickly determined that the tables and indexes were not analyzed at a time when they contained typical volumes of data. The cost-based optimizer requires accurate information, including accurate data volumes, to have any chance of creating efficient execution plans.

The times when the statistics are most likely to be forgotten or out of date are when a table is rebuilt or moved, an index is added, or a new environment is created. For example, a DBA might forget to regenerate statistics after migrating a database schema to a production environment. Other problems typically occur when the DBA does not have a solid knowledge of the database that he/she is dealing with and analyzes a table when it has zero rows, instead of when it has hundreds of thousands of rows shortly afterwards.

SELECT table_name, num_rows,  
        last_analyzed 
  FROM user_tables;

SELECT table_name 
  FROM all_tab_columns
 WHERE column_name = 'LAST_ANALYZED'

As mentioned in the previous section, when tables are being joined, and one table in the join is analyzed and the other tables are not, the cost-based optimizer performs at its worst.

When you analyze your tables and indexes using the DBMS_STATS.GATHER_SCHEMA_STATS procedure and the GATHER_TABLE_STATS procedures, be careful to include the CASCADE>=TRUE option. By default, the DBMS_STATS package will gather statistics for tables only. Having statistics on the tables, and not on their indexes, can also cause the cost-based optimizer to make poor execution plan decisions.

One example of this problem that I experienced recently was at a site that had a TRANS table not analyzed, and an ACCT table analyzed. The DBA had rebuilt the TRANS table to purge data, and had simply forgotten to do the analyze afterwards. The following example shows the performance of a query joining the two tables:

SELECT a.account_name, sum(b.amount)
   FROM trans b, acct a
 WHERE b.trans_date > sysdate - 7
      AND a.acct_id   = b.acct_ID
      AND a.acct_status = 'A'
 GROUP BY account_name;

SORT GROUP BY
     NESTED LOOPS
          TABLE ACCESS BY ROWID ACCT
              INDEX UNIQUE SCAN ACCT_PK
          TABLE ACCESS FULL TRANS


Response Time = 410 seconds

After the TRANS table was analyzed using the following command, the response time for the query was reduced by a large margin:

ANALYZE TABLE trans ESTIMATE STATISTICS  
     SAMPLE 3 PERCENT
     FOR ALL INDEXED COLUMNS

The new execution plan, and response time, were as follows:

SORT GROUP BY
     NESTED LOOPS
          TABLE ACCESS BY ROWID ACCT
              INDEX UNIQUE SCAN ACCT_PK
          TABLE ACCESS BY ROWID TRANS
              INDEX RANGE SCAN TRANS_NDX1


Response Time = 3.1 seconds

One other site that I was asked to tune had been instructed by the vendor of their personnel package to ANALYZE only their indexes and not their tables. The software provider had developed the software for Microsoft’s SQL Server database, and had ported it to Oracle. The results of just analyzing the indexes caused widespread devastation. For example:

SELECT count(*)
   FROM trans
WHERE acct_id = 9
     AND cost_center = 'VIC';

TRANS_IDX2 is on ACCT_ID
TRANS_NDX3 is on COST_CENTER


Response Time 77.3 Seconds

Ironically, the software developers were blaming Oracle, claiming that it had inferior performance to SQL Server. After analyzing the tables and indexes, the response time of this SQL statement was dramatically improved to 0.415 seconds. The response times for many, many other SQL statements were also dramatically improved.

The moral of this story could be to let Oracle tuning experts tune Oracle and have SQL Server experts stick to SQL Server. However, with ever more mobile and cross-database trained IT professionals, I would suggest that we all take more care in reading the manuals when we tackle the tuning of a new database.

The cost-based optimizer will sometimes choose an inferior index, despite it appearing obvious that another index should be used. Consider the following Peoplesoft WHERE clause:

               where business_unit     = :5 
                 and ledger            = :6 
                 and fiscal_year       = :7 
                 and accounting_period = :8 
                 and affiliate         = :9 
                 and statistics_code   = :10 
                 and project_id        = :11 
                 and account           = :12 
                 and currency_cd       = :13 
                 and deptid            = :14 
                 and product           = :15

The Peoplesoft system from which I took this example had an index that had every single column in the WHERE clause contained within it. It would seem that Oracle would definitely use that index when executing the query. Instead, the cost-based optimizer decided to use an index on (BUSINESS_UNIT, LEDGER, FISCAL_YEAR, ACCOUNT). We extracted the SQL statement and compared its runtime to that when using a hint to force the use of the larger index. The runtime when using the index containing all the columns was over four times faster than the runtime obtained when using the index chosen by the cost-based optimizer.

After further investigation, we observed that the index should have been created as a UNIQUE index, but had mistakenly been created as a non-UNIQUE index as part of a data purge and table rebuild. When the index was rebuilt as a unique index, the index was used. The users were obviously happy with their four-fold performance improvement.

However, more headaches were to come. The same index was the ideal candidate for the following statement, which was one of the statements run frequently during end-of-month and end-of-year processing:

               where business_unit     = :5 
                 and ledger            = :6 
                 and fiscal_year       = :7 
                 and accounting_period between 1 and 12 
  and affiliate         = :9 
                 and statistics_code   = :10 
                 and project_id        = :11 
                 and account           = :12 
                 and currency_cd       = :13 
                 and deptid            = :14 
                 and product           = :15

Despite the index being correctly created as UNIQUE, the cost-based optimizer was once again ignoring it. The only difference between this statement and the last is that this statement is after a range of accounting periods for the financial year rather than just one accounting period.

This WHERE clause used the same incorrect index as previously mentioned, with the columns (BUSINESS_UNIT, LEDGER, FISCAL_YEAR, and ACCOUNT). Once again, we timed the statement using the index selected by the cost-based optimizer against the index that contained all of the columns, and found the larger index to be at least three times faster.

The problem was overcome by repositioning the ACCOUNTING_PERIOD column (originally third in the index) to be last in the index. The new index order was as follows:

business_unit
ledger            
fiscal_year       
affiliate
statistics_code
project_id 
account
currency_cd
deptid
Product
accounting_period

Another way to force the cost-based optimizer to use an index is to use one of the hints that allows you to specify an index. This is fine, but often sites are using third-party packages that can’t be modified, and consequently hints can’t be utilized. However, there may be the potential to create a view that contains a hint, with users then accessing the view. A view is useful if the SQL that is performing badly is from a report or online inquiry that is able to read from views.

As a last resort, I have discovered that sometimes, to force the use of an index, you can delete the statistics on the index. Occasionally you can also ANALYZE ESTIMATE with just the basic 1,064 rows being analyzed. Often, the execution plan will change to just the way you want it, but this type of practice is approaching black magic. It is critical that if you adopt such a black magic approach, you clearly document what you have done to improve performance. Yet another method to try is lowering the OPTIMIZER_INDEX_COST_ADJ parameter to between 10 and 50.

In summary, why does the cost-based optimizer make such poor decisions? First of all, I must point out that poor decision-making is the exception rather than the rule. The examples in this section indicate that columns are looked at individually rather than as a group. If they were looked at as a group, the cost-based optimizer would have realized in the first example that each row looked at was unique without the DBA having to rebuild the index as unique. The second example illustrates that if several of the columns in an index have a low number of distinct values, and the SQL is requesting most of those values, the cost-based optimizer will often bypass the index. This happens despite the fact that collectively, the columns are very specific and will return very few rows.

In fairness to the optimizer, queries using indexes with fewer columns will often perform substantially faster than those using an index with many columns.

Early versions of the cost-based optimizer often adopted a divide and conquer approach when more than five tables were joined. Consider the example shown in Figure 1-1. The query is selecting all related data for a company whose account ID (in the ACCT_ID column) is equal to 777818. The company has several branches, and the request is just for the branches in Washington State (WA). The A table is the ACCT table, the F table is ACCT_ADDRESS, and the G table is the ADDRESS table.

Figure 1-1. A join of seven tables

The query expects to return just a handful of rows from the various tables, and the response time should be no longer than one second. Ideally, Oracle receives the ACCT_ADDRESS rows for the relevant account, and then joins to the ADDRESS table to determine if the addresses are in Washington.

However, because so many tables are being joined, the cost-based optimizer will often process F and G independently of the other tables and then merge the data at the end. The result of joining F and G first is that all address that are in the state of Washington must be selected. That process could take several minutes, causing the overall runtime to be far beyond what it would have been if Oracle had driven all table accesses from the A table.

Assuming you have an ACCT_ID index on the ACCT_ADDRESS (F) table, you can often overcome this problem by placing a hint to tell the cost-based optimizer to use that index. This will speed the performance significantly.

Interestingly, the rule-based optimizer often makes a bigger mess of the execution plan when many tables are joined than does the cost-based optimizer. The rule-based optimizer often will not use the ACCT table as the driving table. To assist the rule-based optimizer, place the A table last in the FROM clause.

If you are using a third-party package, your best option may be to create a view with a hint, if that is allowable and possible with the package you are using.

Many sites utilize a pre-production database to test SQL performance prior to moving index and code changes through to production. Ideally the pre-production database will have production volumes of data, and will have the tables analyzed in exactly the same way as the production database. The pre-production database will often be a copy of the actual production datafiles.

When DBAs test changes in pre-production, they may work fine, but have problems with a different execution plan being used in production. How can this be? The reason for a different execution plan in production is often that there are different parameter settings in the production INIT.ORA file than in the pre-production INIT.ORA file.

I was at one site that ran the following update command and got a four-minute response, despite the fact that the statement’s WHERE clause condition referenced the table’s primary key. Oddly, if we selected from the ACCT table rather than updating it, using the same WHERE clause, the index was used.

UPDATE acct SET proc_flag = 'Y'
  WHERE pkey=100;

# Response Time took 4 minutes and
# wouldn't use the primary key

We tried re-analyzing the table every which way, and eventually removed the statistics. The statement performed well when the statistics were removed and the rule-based optimizer was used.

After much investigation, we decided to check the INIT.ORA parameters. We discovered that the COMPATIBLE parameter was set to 8.0.0 despite the database version being Oracle 8.1.7. We decided to set COMPATIBLE to 8.1.7, and, to our delight, the UPDATE statement correctly used the index and ran in about 0.1 seconds.

COMPATIBLE is not the only parameter that needs to be set the same in pre-production as in production to ensure that the cost-based optimizer makes consistent decisions. Other parameters include the following:

Remember that if any of these parameters are different in your pre-production database than in your production database, it is possible that the execution plans for your SQL statements will be different. Make the parameters identical to ensure consistent behavior.

Some SELECT statement WHERE clauses do not use indexes at all. Most such problems are caused by having a function on an indexed column. Oracle8i and later allow function-based indexes, which may provide an alternative method of using an effective index.

In the examples in this section, for each clause that cannot use an index, I have suggested an alternative approach that will allow you to get better performance out of your SQL statements.

In the following example, the SUBSTR function disables the index when it is used over an indexed column.

Do not use:

SELECT account_name, trans_date, amount  
FROM   transaction  
WHERE  SUBSTR(account_name,1,7) = 'CAPITAL';

Use:

SELECT account_name, trans_date, amount  
FROM   transaction
WHERE  account_name LIKE 'CAPITAL%';

In the following example, the!= (not equal) function cannot use an index. Remember that indexes can tell you what is in a table but not what is not in a table. All references to NOT, !=, and <> disable index usage.

Do not use:

SELECT account_name,trans_date,amount  
FROM   transaction
WHERE  amount != 0;

Use:

SELECT account_name,trans_date,amount  
FROM   transaction
WHERE  amount > 0 ;

In the following example, the TRUNC function disables the index.

Do not use:

SELECT account_name, trans_date, amount  
FROM   transaction
WHERE  TRUNC(trans_date) = TRUNC(SYSDATE);

Use:

SELECT account_name, trans_date, amount  
FROM   transaction
WHERE  trans_date BETWEEN TRUNC(SYSDATE) 
          AND TRUNC(SYSDATE) + .99999;

In the following example, || is the concatenate function; it strings two character columns together. Like other functions, it disables indexes.

Do not use:

SELECT account_name, trans_date, amount  
FROM   transaction
WHERE  account_name || account_type = 'AMEXA';

Use:

SELECT account_name, trans_date, amount
FROM   transaction
WHERE  account_name ='AMEX'   
               AND    account_type = 'A' ;

In the following example, the addition operator is also a function and disables the index. All the other arithmetic operators (-, *, and /) have the same effect.

Do not use:

SELECT account_name, trans_date, amount  
FROM   transaction
WHERE  amount + 3000 < 5000;

Use:

SELECT account_name, trans_date, amount  
FROM   transaction
WHERE  amount < 2000;

In the following example, indexes will not be used when a column or columns appears on both sides of an operator. The result will be a full table scan.

Do not use:

SELECT account_name, trans_date, amount
FROM  transaction
WHERE account_name = NVL(:acc_name, account_name);

Use:

SELECT account_name, trans_date, amount
FROM  transaction
WHERE account_name LIKE NVL(:acc_name, '%'),

As mentioned previously, function indexes can be used if the function in the WHERE clause represents the same function and column on which the function index was created. For function indexes to work, you must have the INIT.ORA parameter QUERY_REWRITE_ENABLED set to TRUE. You must also be using the cost-based analyzer. The statement in the following example uses a function index:

CREATE INDEX results_fn_ndx1 ON results(UPPER(owner))

SELECT count(*) 
  FROM results 
 WHERE UPPER(owner) = 'MR M A GURRY';

Execution Plan
-----------------------------------------
   0        SELECT STATEMENT Optimizer=CHOOSE (Cost=1 
                   Card=1 Bytes=32)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 
           'RESULTS_FN_NDX1' (NON-UNIQUE) 
            (Cost=1 Card=1 Bytes=32)

Another common problem that causes indexes not to be used is when the column indexed is one datatype and the value in the WHERE clause is another data type. Oracle automatically performs a simple column type conversion, or casting, when it compares two columns of different types. Assume that EMP_TYPE is an indexed VARCHAR2 column:

SELECT . . .
FROM   emp
WHERE  emp_type = 123

Execution Plan
-----------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
  TABLE ACCESS (FULL) OF 'EMP'

Because EMP_TYPE is a VARCHAR2 value, and the constant 123 is a numeric constant, Oracle will cast the VARCHAR2 value to a NUMBER value. This statement will actually be processed as:

SELECT . . .
FROM   emp
WHERE  TO_NUMBER(emp_type) = 123

The EXPLAIN PLAN utility cannot detect or identify casting problems; it simply assumes that all module bind variables are of the correct type. Programs that are not performing up to expectation may have a casting problem. The EXPLAIN PLAN output will report that the SQL statement is correctly using the index, but that won’t necessarily be true.

While it is important to use indexes to reduce response time, the use of indexes can often actually lengthen response times considerably. The problem occurs when more than 10% of a table’s rows are accessed using an index.

I am astounded at how many tuners, albeit inexperienced, believe that if a SQL statement uses an index, it must be tuned. You should always ask, “Is it the best available index?”, or “Could an additional index be added to improve the responsiveness?”, or “Would a full table scan produce the result faster?”

Another important aspect of indexes is that depending on their type and composition, an index can affect the execution plan of a SQL statement. This must be considered when adding or modifying indexes.

Indexing versus full table scans

Figure 1-2 depicts why an index may cause more physical reads than performing a FULL TABLE SCAN on an index. The table on the left side in Figure 1-2 shows the index entries with the corresponding physical addresses on disk. The lines with the arrows depict physical reads from disk. Notice that each row accessed has a separate read.

Figure 1-2. Physical reads caused by an index

A full table scan is typically able to read over 100 rows of table information per block. Added to this, the DB_FILE_MULTIBLOCK_READ_COUNT parameter allows Oracle to read many blocks with one physical disk read. You may be reading 800 rows with each physical read from disk. In comparison, an index will potentially perform one physical read for each row returned from the table.

If an index lookup is retrieving more than 10% of the rows in a table, the full table scan is likely to be a lot faster than index lookups followed by the additional physical reads to the table to retrieve the required data.

The exception to this rule is if the entire query can be satisfied by the index without the need to go to the table. In this case, an index lookup can be extremely effective. And if the SQL statement has an ORDER BY clause, and the index is ordered in the same order as the columns in the ORDER BY clause, a sort can be avoided, which can further improve performance.

SELECT SUM(val)
    FROM        account_tran
    WHERE        account_id     = 100101
    AND    fin_yr               = '1996'

WHERE A.SURNAME = 'GURRY'
       AND A.ACCT_NO = T.ACCT_NO
       AND T.TRAN_DATE > '01-JUL-97'
       AND T.TRAN_TYPE = 'SALARY'

Index ACCT by (SURNAME)
Index TRANS by (ACCT_NO, TRAN_DATE, TRAN_TYPE)

Early in this reference, I mentioned that Oracle will only join single-column indexes. That is, unless you use the INDEX_JOIN hint. Single-column index merges are bad news in all relational databases, not just Oracle. They cause each index entry to be read for the designated value on both indexes. Consider the following example, which is based on a schema used by a well-known stock brokerage:

INDEX1(APPROVE_FLAG) column and 
INDEX2(ADVISOR_CODE)

SELECT COUNT(*) 
  FROM account_master
     WHERE approve_flag='Y'
       AND  adviser_code='IAM';

3.509 secs

The number of ACCOUNT_MASTER rows that have APPROVE_FLAG = `Y’ is around one million. Oracle reads all of the Y’s, and then reads the much lower number of ADIVISOR_CODE = `IAM’ rows, then crunches the results together. By the way, there is a much lower number of N’s.

The good news is that Oracle has an easy way around this problem: simply create an index that contains both columns. In the example shown next, you would usually drop the single column index on ADVISOR_CODE after the new index is created:

CREATE INDEX mg1 
ON account_master (adviser_code, 
                    approve_flag)

SELECT COUNT(*) 
  FROM account_master
     WHERE approve_flag='Y'
       AND  adviser_code='IAM';

0.041 secs

Note the improvement in execution time, from 3.509 seconds down to only 0.041 seconds.

If you leave all of the Oracle INIT.ORA parameters intact, there is a definite bias towards using nested loops for table joins. Nested loops are great for online transaction processing systems, but can be disastrous for reporting and batch processing systems. The rule-based optimizer will always use a nested loop unless prompted to use other methods by hints, or by other means such as dropping all indexes off the tables.

Online screens should definitely use nested loops, because data will be returned immediately. Typically a screen will buffer 20 rows and stop retrieving until the user requests the next set of data. If effective indexes are in place, a typical response time for getting a set of data will be a second or so.

As a rule of thumb, if a query returns less than 10% of the rows from the tables involved, you should be using nested loops. Use hash joins or sort merges if 10% or more of the rows are being returned.

To perform a hash join, a hash table is created in the memory of the smallest table, and then the other table is scanned. The rows from the second table are compared to the hash.

A hash join will usually run faster than a merge join (involving a sort, then a merge) if memory is adequate to hold the entire table that is being hashed. The entire result set must be determined before a single row is returned to the user. Therefore, hash joins are usually used for reporting and batch processing.

Many DBAs and developers blindly believe that a hash join is faster than a merge join. This is not always the case. For starters, a hash join can only be used for joins based on equality (=), and not for joins based on ranges (<, <=, >, >=). If I have a WHERE clause on the join columns such as WHERE o.owner <= w.owner, a hash join will not work.

Merge joins will work effectively for joins based on equality as well as for those based on ranges. In addition, merge joins will often run faster when all of the columns in the WHERE clause are pre-sorted by being in an index. In this case, the rows are simply plucked from the tables using the ROWID in the index.

With a merge join, all tables are sorted, unless all of the columns in the WHERE clause are contained within an index. This sort can be expensive, and it explains why a hash join will often run faster than a merge join. As with a hash join, the entire result set must be determined before a single row is returned to the user. Therefore, merge and hash joins are usually used for reporting and batch processing.

It is great to identify that we are using a nested loop when we shouldn’t be, but what can we do about it? The answer is thatyou can consider using hints such as USE_NL, USE_HASH, or USE_MERGE. Hints are fine, but what if you are running packaged software and can’t use hints? Perhaps you can create a view for reporting purposes, and place hints in the view. If you are running the rule-based optimizer, you can change to the cost-based optimizer. The cost-based optimizer has the intelligence to work out which join method is the best to use in a given situation.

Oracle also has several INIT.ORA parameters that affect the selection of nested loops, hash joins, and sort merges. The se parameters include SORT_AREA_SIZE, HASH_AREA_SIZE, HASH_JOIN_ENABLED, OPTIMIZER_MODE, DB_FILE_MULTIBLOCK_READ_COUNT, OPTIMIZER_MODE_ENABLE, OPTIMIZER_INDEX_CACHING, and OPTIMIZER_INDEX_COST_ADJ. All these parameters are described earlier, in the list in Section 1.4.6.

As an example of the impact of some of these parameters, I found that if the SORT_AREA_SIZE is left at its default and the HASH_AREA_SIZE is left at its default (which is twice the SORT_AREA_SIZE) the settings are 64K and 128K, respectively. I have tuned Oracle databases where the cost-based optimizer will simply not use hash joins unless the HASH_AREA_SIZE is over one megabyte.

Make sure that you are totally aware of the meaning of all the INIT.ORA parameters listed and their impact on the optimizer decision making.

You are probably wondering which is faster, NOT IN or NOT EXISTS. Should you choose IN, EXISTS, or a table join? The fact is that each can be faster than the other under certain circumstances. Even the dreaded NOT IN can be made to run fast with an appropriate hint inserted. This section lists examples from real-life sites that may assist you in determining which construct is best for a given situation.

                  SELECT .....  FROM     emp  e
                  WHERE       EXISTS
                      (SELECT 'x'
                     FROM dept d
                     WHERE d.dept_no = e.dept_no
                     AND d.dept_cat = 'FIN'),

real: 47578

0   SELECT STATEMENT Optimizer=CHOOSE
1   0   SORT (AGGREGATE)
2   1      FILTER
3   2 TABLE ACCESS (FULL) OF 'EMP'
4   2      AND-EQUAL
5   4   INDEX (RANGE SCAN) OF 'DEPT_NDX1'
         (NON-UNIQUE)
6   4   INDEX (RANGE SCAN) OF 
          'DEPT_NDX2' (NON-UNIQUE)

                  SELECT  .... FROM  emp e, dept d
                  WHERE   e.dept_no = d.dept_no
                  AND     d.dept_cat = 'FIN';

    real: 2153
 

0   SELECT STATEMENT Optimizer=CHOOSE
1   0   SORT (AGGREGATE)
2   1  NESTED LOOPS
3   2   TABLE ACCESS (BY ROWID) OF 'DEPT'
4   3   INDEX (RANGE SCAN) OF 'DEPT_NDX2' 
         (NON-UNIQUE)
5   2   INDEX (RANGE SCAN) OF 'EMP_NDX1' 
         (NON-UNIQUE)

SELECT h.horse_name
  FROM horses h
 WHERE horse_name like 'C%'
   AND exists
   (SELECT 'x'
      FROM WINNERS  w
     WHERE w.position = 1
       AND w.location = 'MOONEE VALLEY'
       AND h.horse_name  = w.horse_name)
Execution Plan
----------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   1    1  INDEX (RANGE SCAN) OF 
   2    'HORSES_PK' (UNIQUE)
   3    1 TABLE ACCESS (BY INDEX     
   4       ROWID) OF 'WINNERS'
   5    3 INDEX (RANGE SCAN) OF 
   6      'WINNERS_NDX1' (NON-UNIQUE)

                  SELECT h.horse_name
                    FROM horses h
                   WHERE horse_name like 'C%'
                     AND horse_name IN
                     (SELECT horse_name
                        FROM WINNERS  w
                       WHERE w.position = 1
                         AND w.location = 'MOONEE VALLEY')

Execution Plan
-----------------------------------------
 0      SELECT STATEMENT Optimizer=CHOOSE
 1    0   NESTED LOOPS
 2    1     VIEW OF 'VW_NSO_1'
 3    2       SORT (UNIQUE)
 4    3       TABLE ACCESS (BY INDEX 
                 ROWID) OF 'WINNERS'
 5    4          INDEX (RANGE SCAN) OF 
                  'WINNERS_NDX4' (NON-UNIQUE)
 6    1     INDEX (UNIQUE SCAN) OF 
                     'HORSES_PK' (UNIQUE)

DELETE FROM 
  FROM ps_pf_ledger_f00
 WHERE EXISTS 
(SELECT 'x'
FROM ps_pf_led_pst2_t1 b
WHERE b.business_unit  = ps_pf_ledger_f00.business_unit 
AND b.fiscal_year      = ps_pf_ledger_f00.fiscal_year 
AND b.accounting_period= ps_pf_ledger_f00.accounting_period 
AND b.pf_scenario_id = ps_pf_ledger_f00.pf_scenario_id 
AND b.source         = ps_pf_ledger_f00.source 
AND b.account        = ps_pf_ledger_f00.account 
AND b.deptid         = ps_pf_ledger_f00.deptid 
AND b.cust_id        = ps_pf_ledger_f00.cust_id 
AND b.product_id     = ps_pf_ledger_f00.product_id 
AND b.channel_id     = ps_pf_ledger_f00.channel_id 
AND b.obj_id         = ps_pf_ledger_f00.obj_id 
AND b.currency_cd    = ps_pf_ledger_f00.currency_cd);

Elapsed: 00:08:160.51

DELETE FROM ps_pf_ledger_f00
WHERE( business_unit,fiscal_year,accounting_period,
       pf_scenario_id ,account,deptid ,cust_id ,
       product_id,channel_id,obj_id,currency_cd)
IN
(SELECT business_unit,fiscal_year,accounting_period,
        pf_scenario_id ,account,deptid ,cust_id ,
        product_id,channel_id,obj_id,currency_cd
  FROM ps_pf_led_pst2_t1 );

Elapsed: 00:00:00.30 

                  UPDATE PS_JRNL_LN 
                  SET JRNL_LINE_STATUS = 'D' 
                  WHERE BUSINESS_UNIT = 'A023' 
                  AND PROCESS_INSTANCE=0001070341 
                  AND JOURNAL_DATE 
                   IN ( TO_DATE('2001-08-01','YYYY-MM-DD'),
                        TO_DATE('2001-08-14','YYYY-MM-DD')) 
                  AND LEDGER IN ( 'ACTUALS') 
                  AND JRNL_LINE_STATUS = '0' 
                  AND EXISTS 
                  (SELECT /*+ HASH_SJ */ 'X' 
                  FROM  PS_COMBO_DATA_TBL 
                  WHERE SETID='AMP' 
                    AND PROCESS_GROUP='SERVICE01' 
                    AND COMBINATION IN ('SERVICE01',  
                          'SERVICE02', 'STAT_SERV1')  
                    AND VALID_CODE='V' 
                    AND PS_JRNL_LN.JOURNAL_DATE BETWEEN  
                             EFFDT_FROM AND EFFDT_TO 
                    AND PS_JRNL_LN.ACCOUNT = ACCOUNT 
                    AND PS_JRNL_LN.DEPTID = DEPTID)

UPDATE STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=80)
  UPDATE OF PS_JRNL_LN
    HASH JOIN (SEMI) (Cost=9 Card=1 
                       Bytes=80)
      TABLE ACCESS (BY INDEX ROWID) OF 
       PS_JRNL_LN (Cost=4 Card=1 
                  Bytes=33)
        INDEX (RANGE SCAN) OF PSDJRNL_LN 
           (NON-UNIQUE) (Cost=3 Card=1)
      INLIST ITERATOR
        TABLE ACCESS (BY INDEX ROWID) OF 
         PS_COMBO_DATA_TBL (Cost=4 
            Card=12 Bytes=564)
          INDEX (RANGE SCAN) OF 
           PSACOMBO_DATA_TBL (NON-UNIQUE) 
             (Cost=3 Card=12)

Despite a multitude of improvements in the way that Oracle handles sorts, including bypassing the buffer cache, having tablespaces especially set up as type temporary, and using memory more effectively, operations that include sorts can be expensive and should be avoided where practical.

The operations that require a sort include the following:

There are many things a DBA can do to improve sorting, such as making sure that the sorting tablespace is a TEMPORARY tablespace, having a large SORT_AREA_SIZE to allow more sorts to occur in memory, ensuring that the default INITIAL and NEXT extents on the TEMP tablespace are a multiple of the SORT_AREA_SIZE parameter, and ensuring that all users are correctly assigned to the TEMP tablespace for their sorting.

There are also things that you can do in your SQL to avoid sorts, discussed in the following sections.

SELECT acct_num, balance_amt
FROM   debit_transactions
WHERE  tran_date = '31-DEC-95'
UNION
SELECT acct_num, balance_amt
FROM   credit_transactions
WHERE  tran_date = '31-DEC-95'

SELECT acct_num, balance_amt
FROM   debit_transactions
WHERE  tran_date = '31-DEC-95'
UNION ALL
SELECT acct_num, balance_amt
FROM   credit_transactions
WHERE  tran_date = '31-DEC-95'

SELECT acc_name, acc_surname
FROM account acct
ORDER BY acc_name

SELECT /*+ INDEX_ASC(acct acc_ndx1) */ acc_name,
    acc_surname
FROM account acct
ORDER BY acc_name

SELECT acc_name, acc_surname
    FROM account
WHERE acc_name > chr(1)

I’ve visited sites that have a standard in place saying that no table can have more than six indexes. This will often cause almost all SQL statements to run beautifully, but a handful of statements to run badly, and indexes can’t be added because there are already six on the table.

Sometimes indexes may be redundant, such as an INDEX_1 on (A, B), INDEX_2 on (A, B, C), and INDEX_3 on (A, B, C, D). In such cases, DBAs often suggest dropping the first two indexes because they are redundant; i.e., they have the same leading columns, in the same order, as INDEX_3. Dropping redundant indexes, however, may cause problems with the selection of a new driving table on a join using the rule-based optimizer (see the “What the RBO rules don’t tell you” sections earlier in this book). There is far less risk associated with dropping redundant indexes when the cost-based optimizer is being utilized.

Having lots of indexes on a table will usually have only a small impact on OLTP systems, because only a few rows are processed in a single transaction, and the impact of updating many indexes is only milliseconds.

Having lots of indexes can be extremely harmful for batch update processing, with its typically high number of inserts, updates, and deletes. Table 1-5 demonstrates this.

Some sites overcome the problem of having many indexes on a table by dropping all indexes prior to batch updates, and re-creating them after the batch run is complete. Oracle has added lots of functionality to help speed index rebuilds. For example, you can rebuild indexes with the NOLOGGING or UNRECOVERABLE options, and you can rebuild indexes in parallel. Despite these enhancements, tables may get to a size at which the index rebuild process takes longer than running a batch update with the indexes intact.

My recommendation is to avoid rules stating that a site will not have any more than a certain number of indexes.

Oracle9i adds some great new functionality that allows you to identify indexes that are not being used. The command is ALTER INDEX MONITORING USAGE. Take advantage of it to identify and remove unused indexes.

The bottom line is that all SQL statements must run acceptably. There is ALWAYS a way of achieving this. If it requires having 10 indexes on a table, then you should put 10 indexes on the table.

When we visit sites to tune SQL, we always seem to identify a few unusual problems that are easily remedied. The OR EXISTS subquery is just such a headache. We have found statements similar to the following at several sites:

select    .....
 from   ps_jrnl_header a  
where   jrnl_hdr_status ='E'  
or exists 
 (select   'x'     
    from  ps_jrnl_header
   where business_unit_iu=a.business_unit_iu        
     and journal_id= a.journal_id
     and journal_date=a.journal_date        
     and unpost_seq=  a.unpost_seq
     and jrnl_hdr_status='E') 


Runtime: 4 Minute 16 seconds

Notice the long runtime for the statement. Wouldn’t it be nice to make the statement go faster? Luckily, the fix is simple. If you have access to change the code (i.e., you are not running a third-party package), use a UNION statement to implement the OR in the WHERE clause as two separate queries:

select            ...........
from               ps_jrnl_header a
where             jrnl_hdr_status ='E'
UNION
select             .....
from                    ps_jrnl_header    a, ps_jrnl_header b
where              b.business_unit_iu  
           =a.business_unit_iu  
    and           b.journal_id=a.journal_id
    and           b.journal_date=a.journal_date        
    and           b.unpost_seq=a.unpost_seq
    and           a.jrnl_hdr_status='E'
    and           b.jrnl_hdr_status != 'E';

Runtime:  0.02 seconds

The reason for the performance improvement is that the UNION allows the optimizer to perform two simple operations to return the rows, whereas the more complex OR construct has confused the optimizer into using a less optimal execution plan.

Oracle is similar to many other databases in that there are performance issues with deletes. Oracle has a high water mark, which represents the highest number of rows ever inserted into the table. This high-water mark can have an impact on performance. Consider the following example, which takes 5.378 seconds to read a table with 151,070 rows:

SELECT COUNT(*) FROM YYYY;
151070

real: 5378

It just happens that all 150,000 rows have the STATE column set to `VIC', and that the table has an index on the STATE column. If I use a WHERE clause to force the count to use the index on the STATE, it takes 16.884 seconds:

    SELECT COUNT(*) FROM YYYY  WHERE  STATE='VIC';
---------
    151070

real: 16884

Notice that the index scan took about three times longer than the full table scan. By the way, this SELECT statement was done using the rule-based optimizer. The cost-based optimizer would have performed a full table scan.

Now let’s delete all of the rows, so that the result is an empty table:

DELETE FROM YYYY;

real: 55277

Now that we have an empty table, let’s count all the rows again:

    SELECT COUNT(*) FROM YYYY;
---------
        0

 real: 5117

Notice that it takes the same amount of time to count zero rows as it took to count from the table when it was completely populated. This is because, when performing a full table scan, Oracle reads as far as the table’s high-water mark, and the high-water mark has not changed.

Let’s count the rows again using the index:

SELECT COUNT(*) FROM YYYY  WHERE STATE='VIC';
0
 real: 16029

Just as before, it takes the same amount of time to count zero rows as it took to count the original 150,000. This is because the index entries are logically deleted, but still exist physically.

The table has never had any rows with a state equal to `NSW’ (New South Wales), so let’s try counting the `NSW’ rows:

SELECT COUNT(*) FROM YYYY WHERE STATE='NSW';
 real: 16940

The count still takes the same amount time as before, when counting the `VIC’ rows. This is because scanning the index requires Oracle to scan past the logically deleted Victorian (`VIC') entries.

To avoid the types of performance problems I’ve just demonstrated, my recommendation is to rebuild a table, and its indexes, whenever the table has undergone many deletes. If index columns are frequently updated, you should also re-build the indexes, because an update forces a logical delete in the index followed by an insert of the new, updated entry. Some sites go as far as rebuilding indexes nightly when they have a lot of logical delete activity.

To detect which tables have many deletes and updates, you can run the following SELECT statement:

SELECT sql_text, executions
   FROM v$sqlarea
 WHERE UPPER(sql_text) LIKE 'DELETE%'
                        OR
      UPPER(sql_text) LIKE  'UPDATE%';

The output from this statement will contain SQL statements on tables that have many deletes and updates. You should consider regular rebuilds of indexes on these tables.

Another common problem I see is heavy usage of views of views, which can totally confuse both optimizers as well as the person trying to work out how to tune the resulting monstrosity. Keep in mind that using hints in views of views will often not give consistent and good performance. Using hints on the outer view is preferable to using hints on the inner view.

Joining more than five tables will almost always confuse both optimizers, and produce a poor execution plan. See Section 1.4.5 under Section 1.4. If you are lucky enough to be able to change the SQL to use hints, you can overcome the problem.

Joining more than five tables frequently in an application usually points to not enough performance consideration at the design stage, when the logical data model was translated into a physical data model.

The SQL statements in this section demonstrate how to identify SQL statements that have an expected response time of more than 10 seconds. The assumption has been made that 300 disk I/Os can be performed per second, and that 4,000 buffer gets can be performed per second. These times are typical of a medium- to high-end machine.

Use the following SQL*Plus commands to identify statements using, on average, more than 3,000 disk reads (10 seconds’ worth) per execution:

column "Response" format 999,999,999.99;
column nl newline;

ttitle 'SQL With Disk Reads > 10 Seconds'

SELECT sql_text nl, 'Executions='|| 
            executions  nl,
  'Expected Response Time in Seconds= ', 
   disk_reads / decode(executions, 0, 1, 
                    executions) / 300   
              "Response"  
  FROM v$sql
WHERE  disk_reads / decode(executions,0,1, executions) 
                   / 300 > 10
  AND executions > 0
ORDER BY hash_value, child_number;

Similarly, the following SQL*Plus commands identify statements that result in more than 40,000 buffer gets:

column "Response" format 999,999,999.99
ttitle 'SQL Buffer Scan > 10 Seconds'   

SELECT sql_text nl, 'Executions='|| 
    executions  nl,
   'Expected Response Time in Seconds= ', 
   buffer_gets / 
     decode(executions, 0, 1, executions) 
     / 4000 "Response"
  FROM v$sql
 WHERE  buffer_gets / 
      decode(executions, 0,1, executions) 
                   /   4000 > 10
 AND executions > 0
ORDER BY hash_value, child_number;

Once you’ve identified poorly performing SQL statements, you can work to tune them.

Oracle8i and later has a great feature that stores information on long-running queries currently active in the V$SESSION_LONGOPS view.

The following example shows the results of a query against V$SESSION_LONGOPS:

SELECT username, sql_text, sofar, totalwork, units

  FROM v$sql, v$session_longops
 
  WHERE sql_address=address
                  AND sql_hash_value=hash_value
                ORDER BY address, hash_value, child_number

HROA
select count(*) from winners w1, winners_backup w2 where w1.owner=w2.owner||''
      1061       7098 Blocks

In this example, the HROA user is running a SELECT COUNT that is about 15% complete. In other words, it has processed 1,061 blocks out of 7,098 (1061/7098 = 15%). The statement has been written to not use an index on the OWNER column of the WINNERS_BACKUP table, because it has a concatenation against that column. Perhaps the DBA should phone the HROA user and question the statement, maybe even canceling it if it is going to run for much longer.

Programmers often need a way to count and/or add up variable conditions for a group of rows. The DECODE statement provides a very efficient way of doing this. Because DECODE is rather complex, few programmers take the time to learn to use this statement to full advantage. The following statement uses DECODE to count the number of first, second, and third placings a racehorse has run:

SELECT horse_name, to_char(sum(decode(position,1,1,0))) 
      , to_char(sum(decode(position,2,1,0))) 
      , to_char(sum(decode(position,3,1,0))) 
  FROM winners
GROUP BY horse_name

In the sum(decode(position,2,1,0)) construct, we are saying that if the horse’s finishing position is 2 (second), add one to the count of seconds. The results of this statement appear as follows:

The alternative statement without the decode involves scanning the table three times, rather than once, as in the previous statement.

SELECT horse_name 
      , count(w1.position)
      , count(w2.position) 
      , count(w3.position) 
  FROM winners w1, winners w2, winners w3
 WHERE w1.horse_name = w2.horse_name
   AND w2.horse_name = w3.horse_name
   AND w1.position   = 1
   AND w2.position   = 2
   AND w3.position   = 3
GROUP BY horse_name

The values of bind variables do not need to be the same for two statements to be considered identical. If the bind variable references are the same, the parsed forms of the statements are considered to be the same. Consequently, the statements share the same parsed form in the shared pool area in memory.

Following are two sharable SQL statements:

SELECT * FROM emp WHERE emp_no = :B1;  Bind value: 123
SELECT * FROM emp WHERE emp_no = :B1;  Bind value: 987

These statements are shareable because bind variables have been used, making the statements themselves identical. The actual bind values do not result in one statement being different from the other.

Following are two non-sharable SQL statements. These statements are not sharable because bind variables have not been used, and the resulting hardcoded values make the statements different from each other.

SELECT * FROM emp WHERE emp_no = 123;
SELECT * FROM emp WHERE emp_no = 987;

In general, encourage your programmers to use bind variables in favor of hardcoding variable values. This will allow their statements to be stored once in memory rather than once for each distinct combination of variable values. Coding bind variables may mean extra effort, and should be attempted only when you are sure that a particular SQL statement will be used repetitively, with the only difference being bind variable values.

There is one situation in which bind variables are not such a great choice. If you have column data in a table having a disproportionate number of rows with certain values, and a very small number of rows with other values, you should be using histograms. Bind variables cannot use histogram information.

Imagine that you have a three-million row ACCOUNT table that has a SUSPENSE account with one million rows. All other accounts in the table have an average of a few hundred rows each. It is most efficient to do a full table scan when a user specifies WHERE ACCOUNT='SUSPENSE’ in a WHERE clause. On the other hand, it is far more efficient to use an index on the ACCOUNT column if any other account type is selected.

The cost-based optimizer can use histograms to intelligently utilize either the full table scan or the index scan where appropriate, based on the value specified in the WHERE clause. Using bind variables will prevent the optimizer from doing this, because the optimizer is unaware of the value that will be in the bind variable at the time it decides on the execution plan.

Oracle8i introduced a parameter named CURSOR_SHARING. This parameter allows you to hardcode your WHERE values with literals, numbers, or dates and still have them be shared, and stored only once in memory. This applies if you have the CURSOR_SHARING parameter set to FORCE or SIMILAR. Using this parameter, and hardcoding values in your WHERE clauses, will assist the cost-based optimizer in making substantially better decisions.

Beginning with Oracle9i, the optimizer will consider bind variable values when choosing execution plans. This avoids having to hardcode values and rely on CURSOR_SHARING = FORCE or SIMILAR to obtain the advantages of bind variables.

The optimizer cannot distinguish a bad hint from a programmer comment. An incorrectly structured or misspelled hint will be ignored rather than reported as an error.

The following list covers some of the more common mistakes people make when programming with hints. The list does not include the obvious, such as misspelling the hint or object name.

Oracle discourages the usage of hints in views because views can be used in different contexts from one usage to the next. I agree that views can be used in different contexts, but I must state that hints in views are one of the most useful tuning mechanisms available. I have found hints in views particularly useful for end-user reporting. Some reporting products, such as Peoplesoft nVision, cannot run anywhere near their optimum without hints being applied.

The following is a list of all hints available in Oracle9i. Many of the hints are also available in earlier releases of Oracle. The purpose of this list is not to exhaustively describe the syntax of each hint, but to show the way each hint is most commonly used.

DBMS_STATS offers two powerful ways of speeding up the analyze process. First, you can analyze tables (not indexes) in parallel. Second, you can analyze only tables and their associated indexes that have had more than 10% of their rows modified through INSERT, UPDATE, or DELETE operations.

To analyze a schema’s tables in parallel, use a command such as the following:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>
'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

This command estimates statistics for the schema HROA. The DEGREE value specifies the degree of parallelism to use. CASCADE=>TRUE causes the indexes for each table to be analyzed as well. DBMS_STATS has a GATHER STALE option that will only analyze tables that have had more than 10% of their rows changed. To use it, you first need to turn on monitoring for your selected tables. For example:

ALTER TABLE WINNERS MONITORING;

You can observe information about the number of table changes for a given table by selecting from the USER_TAB_MODIFICATIONS view. You can see if monitoring is turned on for a particular table by selecting the MONITORING column from USER_TABLES.

With monitoring enabled, you can run the GATHER_SCHEMA_STATS package using the GATHER STALE option:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>
'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE, 
OPTIONS=>'GATHER STALE'),

Because GATHER_STALE is specified, tables will only be analyzed if they have had 10% or more of their rows changed since the previous analyze.

DBMS_STATS gives you the ability to copy statistics from one schema to another, or from one database to another, using the following procedure:

Step 1. Create a table to store the statistics, if you have not already done so:

EXECUTE SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>
'HROA', STATTAB=>'HROA_STAT_TABLE'),

Step 2. Populate the table with the statistics from the schema that you are copying from:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=>
'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=>
'HROA_21SEP_2001'),

Step 3. If you are copying statistics to a different database, such as from production to development, export and import that statistics table as required:

exp hroa/secret@prod file=stats tables=hroa_stat_table

imp hroa/secret@dev file=stats tables=hroa_stat_table

Step 4. Populate the statistics in the target schema’s dictionary. In the following example, statistics are being loaded for the schema HROA_TEST from the table named HROA_STAT_TABLE:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>
'HROA_TEST', STATTAB=>'HROA_STAT_TABLE', STATID=>
'HROA_21SEP_2001', STATOWN=> 'HROA'),

Often you will want to determine if the cost-based optimizer will use the same execution plan in production as it is using in the current development and test databases. You can achieve this by using DBMS_STATS.SET_TABLE_STATS to modify the statistics for a table in your development or for a test database to match those in your production database. The optimizer uses the number of rows, number of blocks, and number of distinct values for a column to determine whether an index or a full table scan should be used.

The following example assumes that your production WINNERS table is going to have 1,000,000 rows in 6,000 blocks:

EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>
'HROA_DEV', TABNAME=>'WINNERS', NUMROWS=> 1000000, NUMBLKS=> 6000);

Regardless of how many rows you really have in your test database, the cost-based optimizer will now behave as if there were 1,000,000.

The optimizer also uses the number of distinct values for each column to decide on index usage. If the number of distinct values is less than 10% of the number of rows in the table, the optimizer will usually decide to perform a full table scan in preference to using an index on the table column. Change the percentage of distinct values for a column as follows:

EXECUTE SYS.DBMS_STATS.SET_COLUMN_STATS (OWNNAME=>
'F70PSOFT', TABNAME=>'PS_LED_AUTH_TBL', 
COLNAME=>'OPRID', DISTCNT=>971);

Usually, re-analyzing a schema and specifying a high percentage of rows for the sample size will improve performance. Unfortunately, the occasional hiccup will occur when you re-analyze tables. Sometimes the new statistics produce much worse execution plans than before. You can avoid the risk of a major screw up by using the DBMS_STATS package to save a copy of your current statistics just in case you need to restore them later. This requires the following steps:

Step 1. Export your schema statistics to your statistics table. If you don’t already have a statistics table, you can create it using the DBMS_STATS.CREATE_STATS_TABLE procedure. The export is performed as follows:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=> 
'HROA', STATTAB=> 'HROA_STAT_TABLE', STATID=> 
'PRE_21SEP_2001'),

Step 2. Gather your new statistics:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>
'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

Step 3. If there are problems with unsuitable execution paths being selected as a result of the new statistics, revert back to the previous statistics by loading the previous statistics from the statistics table:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>
'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=> 
'PRE_21SEP_2001'),

An outline is nothing more than a stored execution plan that Oracle uses rather than computing a new plan based on current table statistics. Before you can use outlines, you must record some. You can record outlines for a single statement, for all statements issued by a single session, or for all statements issued to an instance.

CREATE OR REPLACE OUTLINE aug0901
FOR CATEGORY harness_racing
ON select * 
     from winners
    where owner > 'G%';

   ALTER SESSION 
   SET CREATE_STORED_OUTLINES
       =GENERAL_LEDGER;

ALTER SESSION 
   SET CREATE_STORED_OUTLINES=TRUE;

ALTER SESSION 
   SET CREATE_STORED_OUTLINES=FALSE;

ALTER SYSTEM 
   SET CREATE_STORED_OUTLINES
       =GENERAL_LEDGER NOOVERRIDE;

ALTER SYSTEM 
   SET CREATE_STORED_OUTLINES=FALSE;

You now have your outlines stored away. In order for Oracle to use them, you must enable them.

ALTER SESSION 
   SET USE_STORED_OUTLINES
       =GENERAL_LEDGER;

ALTER SESSION 
   SET USE_STORED_OUTLINES= TRUE;

ALTER SESSION 
   SET USE_STORED_OUTLINES= FALSE;

ALTER SYSTEM 
   SET USE_STORED_OUTLINES
   =GENERAL_LEDGER NOOVERRIDE;

ALTER SYSTEM 
   SET USE_STORED_OUTLINES= FALSE;

You can view outlines to verify that they have been recording correctly and to see what execution plan has been recorded. You can also transfer outlines from one schema to another.

                  SELECT    uo.name, sql_text , hint
                    FROM user_outlines uo, 
                         user_outline_hints uoh
                   WHERE    uo.name = uoh.name
                   ORDER BY join_pos

SYS_OUTLINE_010805222913599
select owner ||','|| first ||','|| second ||',' ||third ||','
 ||points
 from winners_current_month
where points > 4
and owner is not null
order by points desc
NO_FACT(WINNERS_CURRENT_MONTH)

EXP OUTLN/OUTLN FILE=OUTLN_21SEP2001.DMP TABLES=OL$, OL$HINTS BUFFER=262144

IMP OUTLN/OUTLN FILE=OUTLN_21SEP2001.DMP BUFFER=262144 IGNORE=Y