directly from the SQL command-line interface or the using EM graphical interface.
It is important that valid statistics exist both for schema objects as well as internal dictionary and fixed objects.
On occasion one may wish to revert to a historical set of Optimizer statistics, or to lock the current set of statistics which are in effect.
Whenever Optimizer statistics are missing, as a contingency the Optimizer will perform dynamic sampling within the parse phase of each SQL
statement to avoid reverting to the primitive rules-based optimization.
Workshop Section: Managing Optimizer Statistics
Exercises
What You Will Do within This Workshop Within this workshop you will:
Examine the different execution plans selected by the Optimizer under various circumstances and consider how to influence the plan chosen.
Collect and view the database Optimizer statistics.
Consider the impact of indexes upon chosen access methods.
Note It is certainly possible that due to local circumstances your database will select a different execution plan for the sample queries than that shown within our
Oracle Database 11g R2: SQL Tuning – 1
170 | P a g e
solutions. If this is the case, try to draw conclusions from the spirit of the exercises even if the details may differ. Any differences should not impact the point of each exercise.
Stats-1
To begin this workshop from a known starting point, recreate the Equities application database. Also, write below what impact will this have on statistics for these objects?
Answers
SQL> @ EquitiesDefine.sql ...
SQL> @ EquitiesInsert.sql ...
Any statistics previously collected for the objects which comprise the EQUITIES database will be dropped when the tables are dropped and recreated.
Stats-2
Examine the Optimizer statistics stored within the data dictionary for the BROKERAGES table. It would seem from the previous exercise that no such statistics exist.
Answers
Just as we expected, the statistics were cleared when the table was recently dropped and recreated.
SQL> SELECT num_rows, last_analyzed FROM user_tables
WHERE table_name = 'BROKERAGES';
NUM_ROWS LAST_ANAL --- ---
Stats-3
171 | P a g e
Is it possible at this point that for a query to the BROKERAGES table the Optimizer would still be able to utilize statistics, without any effort on our part to collect such statistics? If so, explain and confirm that such is in effect.
Answers
Yes, if dynamic sampling is enabled. The following query would confirm this option and its level of operation.
SQL> CONNECT dba1/dba1;
Connected.
SQL> SELECT value FROM v$parameter
WHERE name = 'optimizer_dynamic_sampling';
VALUE --- 2
Stats-4
Using any of the methods which you learned earlier, explain the execution plan chosen for the following SQL statement from a database session connected to the sample database schema for EQUITIES. As you can see, this summarizes all transactions by brokerage office. It filters the results by selecting only those
brokerages located in one particular city and only those transactions which pertained to a specific number of shares.
SQL> SELECT office_name, COUNT(*),
SUM(number_shares * price) transaction_amount FROM brokerages NATURAL JOIN transactions
WHERE brokerages.City = 'New York' AND transactions.Number_shares = 210 GROUP BY office_name
ORDER BY transaction_amount;
OFFICE_NAME COUNT(*) TRANSACTION_AMOUNT --- --- --- Wall Street Funds 4 20160
Oracle Database 11g R2: SQL Tuning – 1
172 | P a g e
Review the plan and explain why this was chosen by the Optimizer.
Answers
With dynamic sampling in effect, the Optimizer correctly understands that the tables remain small and a full table scan will quickly load the tables into memory into their entirety. Therefore, this access method is chosen following by an in-memory hash join. The appropriate operations to summarize and order the final result table complete the plan.
Brokerages table and see the impact that this will have on the Optimizer. Execute the script BrokeragesAdd100000.sql.
Answers
SQL> @ BrokeragesAdd100000.sql;
Table altered.
173 | P a g e PL/SQL procedure successfully completed.
Commit complete.
Stats-6
With dynamic sampling in effect, explain the query again and note the new plan chosen. Why was this plan chosen by the Optimizer?
Answers
The TRANSACTIONS table had poor selectivity so a full table scan was still performed.
However, selectivity was much better with the BROKERAGES table. Therefore, the rows were first fetched using INDEX UNIQUE SCAN and TABLE ACCESS BY INDEX ROWID.
Furthermore, since the index presented the rows in a sorted order, a NESTED LOOPS operation was ideal for the join with TRANSACTIONS.
The TRANSACTIONS table is designated the driver table (or the outer table) for the NESTED LOOPS JOIN with the BROKERAGES table. The Optimizer elects to access the BROKERAGES table using its primary key index and considers it to be the inner table within this operation.
Following the join the tables must be sorted for the purpose of reducing the detailed result tables into summarized rows to satisfy the GROUP BY clause.
Finally, the remaining summarized rows are sorted again to comply with the instructions of the ORDER BY clause.
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---
| 0 | SELECT STATEMENT | | 5 | 365 | 13 (16)|
00:00:01 |
| 1 | SORT ORDER BY | | 5 | 365 | 13 (16)|
00:00:01 |
| 2 | SORT GROUP BY | | 5 | 365 | 13 (16)|
00:00:01 |
| 3 | NESTED LOOPS | | 5 | 365 | 11 (0)|
Oracle Database 11g R2: SQL Tuning – 1 the access method chosen, drop the primary key for the
BROKERAGES table. Explain the query again and note the impact of this change.
Answers
SQL> ALTER TABLE Brokerages DROP CONSTRAINT Brokerages_PK CASCADE;
Table altered.
The execution plan reverts back to the original selection without the benefit of the index. will now give attention to collecting statistics. First, using the EM interface, confirm the automatic statistics collection job is defined,
175 | P a g e
active and has run. Note that there are more privileges required for this task than has been granted to a standard EM user; therefore you must connect to EM using the SYS administrator account.
Answers
From the database home page, click Administration Jobs to access the Scheduler jobs. You should see GATHER_STATS_JOB. Click on the History link to see previous runs of this job.
Click on the job name GATHER_STATS_JOB and the resulting links General, Schedule and Options to view the job details.
Stats-9
One cannot rely entirely upon the automatic Optimizer collection of statistics, but there are manual statistics which we must collect and we want these to occur immediately.
Before doing so, however, create once again the PRIMARY KEY constraint on the BROKERAGES table.
Then, collect statistics for these application objects using the DBMS_STATS() package:
Collect statistics for the BROKERAGES and TRANSACTIONS tables.
Collect statistics for the TRANSACTIONS_PK index.
Answers
SQL> ALTER TABLE brokerages
ADD CONSTRAINT brokerages_PK PRIMARY KEY (Brokerage_ID);
Table altered.
SQL> EXECUTE dbms_stats.gather_table_stats (NULL, 'brokerages');
PL/SQL procedure successfully completed.
SQL> EXECUTE dbms_stats.gather_table_stats (NULL, 'transactions');
PL/SQL procedure successfully completed.
Oracle Database 11g R2: SQL Tuning – 1
176 | P a g e SQL> EXECUTE dbms_stats.gather_index_stats (NULL, 'transactions_pk');
PL/SQL procedure successfully completed.
Stats-10
Explain the execution plan once more. What is different this time from all the previous plans?
Answers
While the plan looks the same as one we saw earlier, this is the first time in which dynamic sampling did not occur. Rather the stored Optimizer statistics were used, allowing this plan to be the fastest for the query which we have produced thus far in the workshop.
Use the DBMS_STATS() package to collect statistics for all objects within your schema. This time, though, examine the package output based upon the type dbms_stats.objectTab to determine exactly which objects we processed.
Answers
The following anonymous PL/SQL block will call the
177 | P a g e
GATHER_SCHEMA_STATS() program and output the results indicating which objects were processed.
FOR i IN objectList.FIRST .. objectList.LAST LOOP
dbms_output.put_line (objectList(i).objName || ' ' ||
objectList(i).objType);
Oracle Database 11g R2: SQL Tuning – 1
178 | P a g e