-
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
- DIAGNOSTIC+TUNING (Default)
- NONE
-
Who is ADDM disabled?
Set CONTROL_MANAGMENT_PACK_ACCESS=NONE
-
What state of STATISTICS_LEVEL disables ADDM?
BASIC
-
What are the possible values for init parameter STATISTICS_LEVEL?
- TYPICAL (default)
- ALL
- BASIC
-
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?
Instance
-
What is the number of AWR reports required to perform an ADDM analysis?
2
-
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 commanline?
Using DBMS_ADDM package
-
What privilege must a user have to run DBMS_ADDM?
ADVISOR
-
How is an ADDM report generated in SQLPLUS
- DBMS_ADDM.GET_REPORT
- SELECT DBMS_ADDM.GET_REPORT(:task_name) FROM DUAL
-
What are the DBMS_ADDM options?
- DBMS_ADDM.ANALYZE_DB
- DBMS_ADDM.ANALYZE_INST
- DBMS_ADDM.ANALYZE_PARTIAL
-
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?
DBA_ADVISOR_FINDINGS
-
Which view displays the results of completed diagnostic tasks with recommendations?
DBA_ADVISOR_RECOMMENDATIONS
-
Which view provides basic information about tasks
DBA_ADVISOR_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?
MEMORY_TARGET
-
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?
DBMS_STATS.PUBLISH_PENDIND_STATS
-
How are pending stats published for a database?
DBMS_STATS.PUBLISH_PENDIND_STATS(NULL, NULL)
-
How are pending stats published for a schema?
DBMS_STATS.PUBLISH_PENDIND_STATS(SCHEMA_NAME, NULL)
-
How are pending stats published for a table?
DBMS_STATS.PUBLISH_PENDIND_STATS(SCHEMA_NAME, TABLE_NAME)
-
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;
|
|
