Oracle 10g New Features - Ch 6 Performance and Application Tuning

Chapter 6 - Performance and Application Tuning


1. Which value of the initialization parameter STATISTICS_LEVEL provides the best overall performance?
A. ALL
B. NONE
C. TYPICAL
E. BASIC
---
Ans: C.

A value of TYPICAL for STATISTICS_LEVEL collects all major statistics required for database self-management. Specifying ALL collects all statistics for TYPICAL in addition to timed operating system statistics and plan execution statistics; however, ALL adds a significant amount of overhead and may impact overall database throughput. When you specify BASIC, most statistics collections are turned off, limiting the functionality of the AWR and ADDM. The value NONE is not valid for STATISTICS_LEVEL.

2. In which of the following steps would the Automatic Tuning Advisor (ATO) recommend changes to a query containing predicates with mismatched datatypes or functions?
A. SQL structure analysis.
B. Statistics analysis.
C. Access path analysis.
D. SQL profiling.
E. None of the above; this recommendation would come out of the SQL Access Advisor.
----
Ans: A.
Using a function or mismatched datatypes in a predicate can prevent the use of an index. SQL structure analysis will also recommend things such as using UNION ALL instead of UNION or point out Cartesian joins or other possible design mistakes.


3. The initialization parameter SQLTUNE_CATEGORY provides what functionality for the SQL Tuning Advisor?
A. SQLTUNE_CATEGORY specifies the source for SQL statements used by the SQL Advisor.
B. SQLTUNE_CATEGORY specifies the default category name for the SQL profile that is used by default when a session first connects.
C. SQLTUNE_CATEGORY specifies whether CPU or I/O should have precedence when tuning a SQL statement.
D. There is no such initialization parameter.
-----
Ans: B.
When a SQL profile is created, you assign a category to it. When a session connects to the database, all SQL profiles with a category name that matches the value of SQLTUNE_CATEGORY automatically apply to the session. SQLTUNE_CATEGORY is a dynamic parameter that is modifiable both at the session level and for the instance.

4. The predefined job __________gathers statistics for objects in the database using the procedure ______.
A. GATHER_STATS_JOB, DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC
B. GATHER_STATS, DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC
C. GATHER_STATS_JOB, DBMS_STATS.GATHER_DATABASE_STATS
D. GATHER_STATS_JOB, DBMS_STATS.GATHER_AUTO
---
Ans: A.
The new procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC runs in the predefined job GATHER_STATS_JOB to collect statistics on the objects that need updated statistics the most before the maintenance window closes. There is no such predefined job GATHER_STATS, and while DBMS_STATS.GATHER_DATABASE_STATS operates similarly to DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC with the GATHER AUTO option, it is not called from GATHER_STATS_JOB.


5. Database users can launch the SQL Tuning Advisor from the EM Database Control provided they have which of the following system privileges?
A. DBA
B. SYSDBA
C. SQL_ADVISOR
D. ADVISOR
E. ADMINISTER SQL TUNING SET
----
Ans: D.
The ADVISOR system privilege is required to create a tuning task. DBA is a role and contains more privileges than necessary to use the SQL Tuning Advisor. The system privilege SYSDBA is also not required. SQL_ADVISOR is not a system privilege, and the ADMINISTER SQL TUNING SET privilege is only required to create and maintain SQL tuning sets.

6. Choose the correct statement regarding the SQL Access Advisor.
A. The SQL Access Advisor takes an actual workload as input and recommends changes to SQL statements from the workload.
B. The SQL Access Advisor takes a hypothetical workload from a schema and generates missing statistics.
C. The SQL Access Advisor generates materialized views on the fly for high-load SQL statements identified by ADDM.
D. The SQL Access Advisor takes an actual workload as input and recommends a new index on a table.
E. The SQL Access Advisor takes an individual SQL statement, generates the most efficient execution plan possible, and saves it in a SQL profile.
---
Ans: D.
The SQL Access Advisor either takes an actual workload or derives a workload from a schema and recommends indexes, materialized views, and materialized view logs to speed the execution path for queries in the workload.


7. The performance pages of the EM Database Control provide tuning information about all except which of the following areas?
A. Top sessions
B. High DML activity
C. Top SQL statements
D. CPU and wait classes
----
Ans: B.
While high DML activity can contribute to sessions using high CPU and I/O, it is not specifically measured and reported by the EM Database Control.

8. Which of the following steps is not performed by the enhanced query optimizer in tuning mode?
A. Check objects in the query for stale or missing statistics.
B. Identify SQL statements that tend to have bad execution plans and recommend alternatives.
C. Determine if a new index can improve access to each table in the query.
D. An initial execution plan is generated only for high-load SQL statements.
E. Collect information from previous executions of the SQL statement and build a SQL profile.
----
Ans: D.
While an initial execution plan is generated for both normal mode and tuning mode, it is not limited to high-load SQL statements.

9. Automatic SQL Tuning can be accessed through a command-line interface in addition to the EM Database Control. Identify the PL/SQL package used to access Automatic SQL Tuning.
A. DBMS_MONITOR
B. DBMS_SQLTUNE
C. DBMS_SQL_ADVISOR
D. DBMS_ADVISOR
E. DBMS_SQL
----
Ans: B.
DBMS_SQLTUNE contains a number of packages used to create, drop, and execute tuning tasks, in addition to managing SQL profiles and SQL tuning sets.


10. The SQL Access Advisor can provide all except which of the following as output from its analysis?
A. Considers storage and maintenance costs if new objects are recommended
B. Generates drop recommendations even for a partial workload
C. Recommends combining multiple indexes into one index
D. Recommends materialized view logs where possible for fast refresh
---
Ans B.
The SQL Access Advisor will not generate drop recommendations for partial workloads.


11. Which of the following procedures will gather statistics on dictionary objects? (Choose all that apply.)
A. DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
B. DBMS_STATS.GATHER_SCHEMA_STATS with the GATHER_SYS argument set to TRUE
C. DBMS_STATS.GATHER_DATABASE_STATS with the GATHER_SYS argument set to TRUE
D. DBMS_STATS.GATHER_DICTIONARY_STATS
---
Ans: A, B, C, D.
Several of the procedures in DBMS_STATS will gather statistics on all or some of the objects in the data dictionary, which can include statistics on fixed tables.


12. The SQL Tuning Advisor uses one or more SQL statements as input. Which of the following is not a source of SQL statements for the SQL Tuning Advisor?
A. SQL statements that were in the cursor cache during the previous seven days
B. High-load SQL statements identified by ADDM
C. A user's custom workload
D. SQL statements captured by the AWR
---
Ans: A.
The SQL Tuning Advisor cannot access SQL statements that are no longer in the cursor cache, only those that are currently in the cursor cache, unless they were captured by AWR or identified by ADDM.

13. Which of the following columns is new to the table PLAN_TABLE?
A. CPU
B. TIME
C. IO
D. SPACE
---
Ans B.
The table PLAN_TABLE has only one new column, TIME.


14. Statistics cannot be gathered for which of the following types of tables?
A. Fixed tables
B. Real tables in the Data Dictionary
C. Real tables in non-SYSTEM user schemas
D. None of the above, statistics can be gathered on all tables, in any schema, regardless of whether they are fixed or real.
---
Ans: D. Statistics can be gathered on any table in any schema, fixed or real. In addition to gathering statistics on fixed tables using the GATHER_FIXED argument of GATHER_DATABASE_STATS, statistics on individual fixed tables can be gathered by using the existing procedures within DBMS_STATS with a fixed table name as an argument.


15. If the initialization parameter STATISTICS_LEVEL is set to BASIC, what is the monitoring level for DML operations on tables?
A. Table DML changes are not monitored
B. The monitoring level is the same as using the command ALTER TABLE … MONITORING
C. The monitoring level is the same as using the command ALTER TABLE … NOMONITORING
D. BASIC is not a valid value for this parameter, only NONE, TYPICAL and ALL are allowed values
---
Ans: A.
When STATISTICS_LEVEL is set to BASIC, table monitoring for DML changes is disabled. MONITORING and NOMONITORING are still part of the ALTER TABLE syntax, but they are ignored because monitoring is no longer allowed only at the object level. NONE is not a valid value for the STATISTICS_LEVEL parameter.

16. Which of the following values are no longer supported for the OPTIMIZER_MODE initialization parameter?
A. CHOOSE and RULE
B. COST and RULE
C. FIRST_ROWS and ALL_ROWS
D. FIRST_ROWS and FIRST_ROWS_n
---
Ans: A.
CHOOSE and RULE are no longer supported; the only valid values for OPTIMZER_MODE are ALL_ROWS, FIRST_ROWS, and FIRST_ROWS_n, where n is 1, 10, 100, or 1000.

17. Which of the following SQL statements is not a good candidate for the SQL Tuning Advisor?
A. Transactions that have SQL statements with multiple large sorts
B. SQL statements with heavy I/O requirements
C. A user's ad-hoc query against the data warehouse
D. A statement that consumes a relatively high amount of CPU time every day of the week
---
Ans: C.
The SQL Tuning Advisor is not useful for an occasional ad-hoc query from a user; it is most suited for frequently executed queries that have high CPU, I/O or temporary space requirements.

18. Which of the following statements about a SQL profile is not true?
A. A SQL profile is stored persistently in the data dictionary
B. A SQL profile can be accepted or rejected
C. A SQL profile is used in conjunction with existing statistics to generate a good execution plan
D. A SQL profile is used in tuning mode to produce a good execution plan
---
Ans: D.
A SQL profile is used in normal mode to produce a good execution plan; the profile is created in tuning mode.

19. During the SQL Structure Analysis phase of the SQL Tuning Advisor, which of the following is not recommended by the ATO? Choose two.
A. Substituting a UNION ALL for a UNION
B. A recommendation to create an index
C. Using NOT IN instead of EXISTS
D. Data type mismatches between predicates and indexed columns
E. A recommendation to create a materialized view
----
Ans: B, E.
Recommendations to create an index comes from the ATO's Access Path Analysis or the SQL Access Advisor; recommendations for new materialized views comes from the SQL Access Advisor

20. Which of the following values is not valid for the ATTRIBUTE_NAME parameter of the procedure DBMS_SQLTUNE.ALTER_SQL_PROFILE?
A. STATUS
B. CATEGORY
C. VALUE
D. DESCRIPTION
E. NAME
----
Ans: C.
VALUE is not a valid value for ATTRIBUTE_NAME, however, it is another one of the parameter names for DBMS_SQLTUNE.ALTER_SQL_PROFILE.