The flashcards below were created by user
on FreezingBlue Flashcards.
What are the two modes of query optimizer?
What are the four modes of Automatic Tuning Advisor?
- Statistical Analysis
- SQL Profiling
- Access Path Analysis
- SQL Structural Analysis
How is query optimizer referred to when in TUNING MODE?
Automatic Tuning Optimizer
What are the step to run SQL Repair Advisor
What does SQL Access Advisor do?
Offers recommendations for Indexes, Materialized Views, Materialized View Logs and Partitions for a given workload.
What are the modes of operation for SQL Access Advisor?
- Problem Solving (Default)
In which mode of operation will SQL Access Advisor suggest new objects?
In which mode of operation will SQL Access Advisor identify unused indexes and MVs?
How is an advisor tasks task deleted?
DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'tash_name');
Which Oracle product can help make recommendation about new objects to improve performance?
SQL Access Advisor
What are the steps in creating a SQL Access Advisor analysis session?
- Create an Advisor task
- Create a SQL Tuning Set workload
- Link SQL Tuning Set workload to Advisor task
- Add any Workload parameters
- Execute taskView results
When a SQL Access Advisor task has been run, how are the results viewed?
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('script_name') FROM DUAL;
Which view has basic information about existing advisor tasks?
Which view s shows status information about existing advisor tasks?
Which view shows findings identified for an existing advisor task?
Which view shows recommendations for the problems identified by an existing advisor task?
Which view show actions based on recommendations for the problems identified by an advisor task?
Which Procedure is used to create an Access Advisor Task?
What permissions is required to create an Access Advisor Task?
What procedure is used to copy workload to a SQL Tuning Set?
What procedure is used to link a task to a SQL Tuning Set?
What procedure is used to unlink a task to a SQL Tuning Set?
What procedure is used to define task parameters?
How is an Access Advisor Task run?
How is candidate SQL chosen for Automatic SQL Tuning?
High repeating, high load statement from AWR
What SQL is ignored for Automatic SQL Tuning?
- Recursive SQL
- Recently tunes statements (within 1 month)
- Parallel Queries
In Automatic SQL Tuning when are SQL Profiles automatically accepted?
If performance is improved X3ACCEPT_SQL_PROFILES is TRUE
If there are stale statistics, how are SQL Profiles accepted?
They are not
How can SQL Profiles that have automatically been accepted be identified?
TYPE=AUTO in DBA_SQL_PROFILES
How is ACCEPT_SQL_PROFILES set?
- DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
- parameter => ‘ACCEPT_SQL_PROFILES’,
- values => ‘TRUE’);
In which view can current tuning advisor parameter be viewed?
How is a SQL Tuning Task report viewed?
What are the secions on a SQL Tuning Task report?
- General information - High-level information about the SQL tuning task.
- Tuning findings
- Explain plans The old and new execution plans.