Questions on Application Tuning

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: What is the OPTIMIZER_DYNAMIC_SAMPING setting default?
A: To enhance Query Optimization, the OPTIMIZER_DYNAMIC_SAMPING is set to 2 by default.

Q: How do you disable the Automatic PGA Memory Management?
A: In order to disable the Automatic PGA Memory Management set the parameter to 0.

Q: How do you gather statistics on dictionary tables in the Oracle 10g Database?
A: In Oracle Database 10g, you can gather statistics on dictionary tables (both fixed and real) to get the best performance. You use the DBMS_STATS.GATHER_DATABASE_STATS procedure with GATHER_SYS argument set to TRUE or DBMS_STATS.GATHER_DICTIONARY_STATS. To use this, you should have the ANALYZE ANY DICTIONARY system privilege. For example:
SQL> BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(options=’GATHER AUTO’);
END;
/ -- Note: you should use GATHER only if you are using release 8i

Q: What is the Automatic Tuning Optimizer (ATO)?
A: It is a SQL automatic tuning optimaizer. When the optimizer is tuning a SQL statement using ATO, it is called Automatic SQL Tuning.

Q: How do you perform automatic SQL tuning?
A:
Create a binding variable and then move your query into it.
SQL> VARIABLE my_query VARCHAR2(1000)
SQL> BEGIN
:my_query := ‘SELECT ename FROM iself.emp WHERE empno = 100;’
END;
/

Q: How do you use the DBMS_SQLTUNE package to create a tuning task by calling the CREATE_TUNING_TASK function?
A: We use the DBMS_SQLTUNE package to create a tuning task by calling the CREATE_TUNING_TASK function. This procedure creates an advisor task and sets its corresponding parameters according to the user-provided input arguments. To execute this you need one more binding variable to keep your task name.
SQL> VARIABLE my_task VARCHAR2(100)
SQL> BEGIN
:my_task := DBMS_SQLTUNE.create_tuning_task (
SQL_TEXT => :my_query,
BIND_LIST => SQL_BINDS(anydata.ConvertNumber(100)),
USER_NAME => ‘ISELF’,
SCOPE => ‘COMPREHENSIVE’,
TIME_LIMIT => 60,
TASK_NAME => ‘my_tuning_task’,
DESCRIPTION => ‘Query on EMP table …’);
END;
/

Q: How do you use the EXECUTE_TUNING_TASK procedure to start the tuning process?
A: You need to invoke the EXECUTE_TUNING_TASK procedure to start the tuning process.
SQL> BEGIN
DBMS_SQLTUNE.execute_tuning_task (TASK_NAME=>:my_task);
END;

Q: How do you call the REPORT_TUNING_TASK function to visualize the tuning results?
A: The following is an example of how to call the REPORT_TUNING_TASK function to visualize the tuning results.
SQL> SQL> SELECT DBMS_SQLTUNE.report_tuning_task
(TASK_NAME=>:my_task)
FROM dual;

Q: How do you store a SQL profile in the data dictionary?
A: When the SQL Tuning Advisor recommends a SQL Profile, then create the SQL Profile by calling the ACCEPT_SQL_PROFILE function, which stores it in the data dictionary. You should have the CREATE ANY SQL PROFILE privilege.
SQL> VARIABLE my_profile VARCHAR2(1000)
SQL> BEGIN
:my_profile := DBMS_SQLTUNE.accept_sql_profile
(TASK_NAME => ’my_tuning_task’);
END;
/
SQL> SELECT :my_profile FROM dual;