Exercises
What You Will Do within This Workshop Within this workshop you will:
Build several SQL statements which include joins and observe the various execution plans that are selected.
Observe many of the operations we have discussed actually being used within execution plans.
Operations-1
Within the next several exercises we will list a SQL statement to be executed and invite you to execute the statement on your own database and then use your preferred method of explaining the statement to review the execution plan. Do your best to decipher the individual steps of the execution plan selected from the Optimizer of your workshop database and reconcile these operations with the explanations provided within the lecture notes of this section.
In our first example we will build a SQL statement which will query transactions within the EQUITIES sample database. For each brokerage office we want to list the number of transactions processed as well as the sum total of transactions handled by the office. List this information ordered by the sum total transaction amount.
SQL> SELECT office_name, COUNT(*),
SUM(number_shares * price) transaction_amount FROM brokerages, transactions
WHERE brokerages.brokerage_id = transactions.brokerage_id GROUP BY office_name
ORDER BY transaction_amount;
121 | P a g e
In the execution plan shown below, and presumably in the execution plan selected by your workshop database when you execute the query locally, notice the
following operations:
The small tables are quickly and ideally accessed with a full table scan.
With these tables entirely stored within memory, a hash join operation represents the best join method.
A sort or a hash is performed in preparation for summarization of data as directed by the GROUP BY clause.
The final result table is sorted as directed by the ORDER BY clause.
The columns chosen are projected.
Oracle Database 11g R2: SQL Tuning – 1
122 | P a g e
The latter part of the calendar year tends to have more trading volume than other parts of the year due to tax-related transactions, post-summer activity, etc. Therefore, modify the SQL statement to include only those transactions which were processed during the
‘Autumn’ or ‘Winter’ seasons.
SQL> SELECT office_name, COUNT(*),
SUM(number_shares * price) transaction_amount FROM brokerages, transactions, time
WHERE brokerages.brokerage_id = transactions.brokerage_id AND time.time_id = transactions.time_id
AND season IN ('Autumn', 'Winter')
In the execution plan produced in our database notice the following operations.
Bear in mind that your local results may differ, but the larger point is the ability to understand whatever execution plan is produced:
The TIME table is scanned to find those rows for which the SEASON column value matches our search condition. Optimizer statistics correctly indicate that the entire table may be scanned and loaded into memory quickly.
The TRANSACTIONS table is subject to a full table scan as well since it had poor selectivity.
The TIME and TRANSACTIONS tables are included in a hash join as we would expect for in-memory tables.
Since information will be presented by brokerage office, the BROKERAGES table is scanned in sorted order using the index.
With BROKERAGES available in a sorted order, a nested loops join is performed between this table and the result of the hash join of TIME and
123 | P a g e
TRANSACTIONS.
The result table is further sorted in preparation for the GROUP BY summarization.
The end result is sorted again for the ORDER BY clause and the columns are projected.
Modify the query again to isolate only those transactions during the latter part of the year which involved equities in non-USA
companies.
SQL> SELECT office_name, COUNT(*),
SUM(number_shares * price) transaction_amount FROM brokerages, transactions, time, stocks
WHERE brokerages.brokerage_id = transactions.brokerage_id AND time.time_id = transactions.time_id
AND stocks.stock_id = transactions.stock_id
Oracle Database 11g R2: SQL Tuning – 1
In the execution plan please notice the following operations:
The STOCKS table is referenced by a full table scan to select only those equities for non-USA companies.
A series of nested joins are performed with each of the tables specified.
Hash joins are preferred unless an operation allows a table to be in sorted order.
125 | P a g e
| 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| BROKERAGES | 1 | 27
| 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | BROKERAGES_PK | 1 |
| 0 (0)| 00:00:01 |
---
Section 5: Managing Optimizer Statistics
Overview
Objectives
Learn More About Optimizer Statistics
Automatic Maintenance Tasks
Manually Gathering Optimizer Statistics
Using Historical Statistics
Dynamic Sampling Of Statistics
Locking Statistics
Section Overview
The Optimizer does a remarkable, albeit not infallible, job of determining the best execution plan for a SQL statement based upon the current state of the application tables involved. However, in order for the Optimizer to function properly, adequate schema statistics must be collected and stored. And these statistics must be
periodically updated as the tables change so that they do not become stale. Within this section we will discuss the following subjects related to Optimizer statistics and their management:
Understand Optimizer statistics and the statistics collection algorithms.
Discuss how the Auto-Task infrastructure is used to automatically gather Optimizer statistics and how one can customize and otherwise manage that infrastructure.
Oracle Database 11g R2: SQL Tuning – 1
126 | P a g e
Manually gather database statistics using the DBMS_STATS() system-supplied package and the EM interface.
Consider customized options available for statistics gathering and how these may be applied both to the Auto-Task automated collection infrastructure and to manual procedures.
Understand the importance of statistics for dictionary and fixed objects.
Implement dynamic sampling
Learn how to use historical statistics
Consider the benefit of locking statistics
Note Collecting or gathering Optimizer statistics for database objects such as tables is sometimes referred to as analyzing a table since essentially a
statistical analysis of the object is performed. In fact, in legacy versions of the database one collected Optimizer statistics using the ANALYZE command, although this method is no longer supported.