What does AWR do?
Captures and stores statistical data needed to diagnose problems with the database
What does ADDM stand for?
Automatic Database Diagnostic Monitor
What does ADDM do?
Makes use of AWR data
What controls ADDM?
init parameters CONTROL_MANAGMENT_PACK_ACCESS and STATISTICS LEVEL
What are the possible values for CONTROL_MANAGMENT_PACK_ACCESS?
- DIAGNOSTIC+TUNING (Default)
Who is ADDM disabled?
What state of STATISTICS_LEVEL disables ADDM?
What are the possible values for init parameter STATISTICS_LEVEL?
- TYPICAL (default)
What functions does ADDM perform?
- Periodically analyze the AWR
- Diagnose root cause of performance problems
- Provide recommendations for fixing problems
- Identify non-problem areas in the system
What are the three analysis modes of ADDM in a RAC environment?
What are the analysis modes of ADDM in a NON-RAC environment?
What is the number of AWR reports required to perform an ADDM analysis?
How often does ADDM perform an analysis?
Every time AWR runs
How are the results of ADDM viewed?
What must the two AWR reports be to produce an ADDM analysis?
From the same database startup period
How is ADDM run from commandline?
Using DBMS_ADDM package
What privilege must a user have to run DBMS_ADDM?
How is an ADDM report generated in SQLPLUS
- SELECT DBMS_ADDM.GET_REPORT(:task_name) FROM DUAL
What are the DBMS_ADDM options?
What are the four classes of findings for ADDM?
- Problem - root cause of a database problem
- Symptom - symptoms of a problem
- Information - background information
- Warning - issues that may affect accuracy of ADDM analysis
What to ADDM directives do?
Alter the recommendations provided by ADDM using filters.
Which view shows findings by ALL advisors?
Which view displays the results of completed diagnostic tasks with recommendations?
Which view provides basic information about tasks
What does the initialization parameter MEMORY_TARGET do?
- Allows database to tune target memory size, distribution between SGA and PGA
- MEMORY_TARGET is the ONLY memory parameter to be set to allow Oracle to automatically manage memory
At startup MEMORY_TARGET will allocate what percentage of memory to SGA and PGA?
What does the initialization parameter MEMORY_TARGET_MAX do?
Set the maximum amount of memory Oracle will grab from the OS
If MEMORY_TARGET_MAX is not set, what does it default to?
If MEMORY_TARGET and SGA_TARGET AND/OR PGA_AGGREGATE_TARGET are set, what happens?
SGA_TARGET AND/OR PGA_AGGREGATE_TARGET become the minimum amount of memory allocated to the SGA AND/OR PGA
What are Pending Statistics?
- Statistics that are gathered, but not published.
- The stats can be tested before publishing
How can the Stats publishing settings be found?
- SELECT DBMS_STATS.GET_PREFS('PUBLISH')
- TRUE - Publish when gathered
- FALSE - Kept pending
How is the optimiser forced to use pending statistics?
OPTIMIZER_USE_PENDING_STATISTICS=TRUE (default FALSE)
How are pending stats published?
How are pending stats published for a database?
How are pending stats published for a schema?
How are pending stats published for a table?
What does GRANULARITY argument do in DBMS_STATS?
Determines whether stats for a partitioned table are gathered for each portion or globally
If INCREMENTAL is set to TRUE and GRANULARITY to AUTO for DBMS_STATS, what happens when collecting data in a partitioned table?
If data loaded in a new partition, stats are INCREMENTALLY gathered for that partition and update the global statistics.
If INCREMENTAL is set to FALSE and GRANULARITY to AUTO for DBMS_STATS, what happens when collecting data in a partitioned table?
A FTS is used to maintain global statistics....very costly!!!
What is a negative aspect of INCREMENTAL for DBMS_STATS?
More space is required in SYSAUX table to maintain global statistics.
What does DBMS_STATS.AUTO_SAMPLE_SIZE do?
Allows Oracle to determine the best sample size necessary for good statistics.
What are Multi-Column Statistics?
Extended statistics against two or more columns to provide better plans when the columns are used in query
What are Expression Statistics?
Statistics gathered on a column with an expression
How are Expression Statistics gathered?
- Using DBMS_STATS.CREATE_EXTENDED_STATS
- SELECT DBMS_STATS.CREATE_EXTENDED_STATS (null, 'cust' '(lower(state))') from dual;
What are the possible values for LOCK_SGA?
It's Boolean, True/False
Which parameter prevents swapping the SGA?
Which parameter prevents swapping the SGA?LOCK_SGA
Desc Gathering Pending stats for a table and then publishing the pending stats
- DBMS_STATS.set_table_prefs('USER','TAB', 'PUBLISH', 'false');
- ANALYZE TABLE scott.emp COMPUTE STATISTICS;
What are the possible blanks here: INSERT_??_DIRECTIVE?
How does once remove an ADDM directive?
What does INSERT_SQL_DIRECTIVE do?
Limit the ADDM to a specific SQL statement
What does INSERT_PARAMETER_DIRECTIVE do?
Limits ADDM to report on recommendations to changes for a specific Init parameter
What does INSERT_FINDING_DIRECTIVE do?
Limits to the ADDM to a specific finding
What does INSERT_SEGMENT_DIRECTIVE do?
Limit the ADDM to a specific segment
By default, how long are AWR retained retained for?
Which initialization parameters take their memory from the SGA_TARGET but need to be manually sized?
Which initilization parameters take their memory from the SGA_TARGET and are automayically sized?
Session 1 adds row to MYTAB but not committed. Session 1 issues 'LOCK TABLE mytab IN EXCLUSIVE MODE WAIT 10'. What happens?
Session two will wait to aquire the lock for 10secs before erroring.
Which OS command line utility will produce an ADDM report?