Oracle OCP 9i to 11g Performance Enhancements

Card Set Information

Author:
Tralala
ID:
208709
Filename:
Oracle OCP 9i to 11g Performance Enhancements
Updated:
2014-05-30 11:17:05
Tags:
Oracle OCP 9i 11g Performance Enhancements
Folders:

Description:
Oracle OCP 9i to 11g Performance Enhancements
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. What does AWR do?
    Captures and stores statistical data needed to diagnose problems with the database
  2. What does ADDM stand for?
    Automatic Database Diagnostic Monitor
  3. What does ADDM do?
    Makes use of AWR data
  4. What controls ADDM?
    init parameters CONTROL_MANAGMENT_PACK_ACCESS and STATISTICS LEVEL
  5. What are the possible values for CONTROL_MANAGMENT_PACK_ACCESS?
    • DIAGNOSTIC
    • DIAGNOSTIC+TUNING (Default)
    • NONE
  6. Who is ADDM disabled?
    Set CONTROL_MANAGMENT_PACK_ACCESS=NONE
  7. What state of STATISTICS_LEVEL disables ADDM?
    BASIC
  8. What are the possible values for init parameter STATISTICS_LEVEL?
    • TYPICAL (default)
    • ALL
    • BASIC
  9. 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
  10. What are the three analysis modes of ADDM in a RAC environment?
    • Database
    • Instance
    • Partial
  11. What are the analysis modes of ADDM in a NON-RAC environment?
    Instance
  12. What is the number of AWR reports required to perform an ADDM analysis?
    2
  13. How often does ADDM perform an analysis?
    Every time AWR runs
  14. How are the results of ADDM viewed?
    • OEM
    • A report in SQLPlus
  15. What must the two AWR reports be to produce an ADDM analysis?
    From the same database startup period
  16. How is ADDM run from commandline?
    Using DBMS_ADDM package
  17. What privilege must a user have to run DBMS_ADDM?
    ADVISOR
  18. How is an ADDM report generated in SQLPLUS
    • DBMS_ADDM.GET_REPORT
    • SELECT DBMS_ADDM.GET_REPORT(:task_name) FROM DUAL
  19. What are the DBMS_ADDM options?
    • DBMS_ADDM.ANALYZE_DB
    • DBMS_ADDM.ANALYZE_INST
    • DBMS_ADDM.ANALYZE_PARTIAL
  20. 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
  21. What to ADDM directives do?
    Alter the recommendations provided by ADDM using filters.
  22. Which view shows findings by ALL advisors?
    DBA_ADVISOR_FINDINGS
  23. Which view displays the results of completed diagnostic tasks with recommendations?
    DBA_ADVISOR_RECOMMENDATIONS
  24. Which view provides basic information about tasks
    DBA_ADVISOR_TASKS
  25. 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
  26. At startup MEMORY_TARGET will allocate what percentage of memory to SGA and PGA?
    • SGA 60%
    • PGA 40%
  27. What does the initialization parameter MEMORY_TARGET_MAX do?
    Set the maximum amount of memory Oracle will grab from the OS
  28. If MEMORY_TARGET_MAX is not set, what does it default to?
    MEMORY_TARGET
  29. 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
  30. What are Pending Statistics?
    • Statistics that are gathered, but not published.
    • The stats can be tested before publishing
  31. How can the Stats publishing settings be found?
    • SELECT DBMS_STATS.GET_PREFS('PUBLISH')
    • TRUE - Publish when gathered
    • FALSE - Kept pending
  32. How is the optimiser forced to use pending statistics?
    OPTIMIZER_USE_PENDING_STATISTICS=TRUE (default FALSE)
  33. How are pending stats published?
    DBMS_STATS.PUBLISH_PENDING_STATS
  34. How are pending stats published for a database?
    DBMS_STATS.PUBLISH_PENDING_STATS(NULL, NULL)
  35. How are pending stats published for a schema?
    DBMS_STATS.PUBLISH_PENDING_STATS(SCHEMA_NAME, NULL)
  36. How are pending stats published for a table?
    DBMS_STATS.PUBLISH_PENDING_STATS(SCHEMA_NAME, TABLE_NAME)
  37. What does GRANULARITY argument do in DBMS_STATS?
    Determines whether stats for a partitioned table are gathered for each portion or globally
  38. 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.
  39. 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!!!
  40. What is a negative aspect of INCREMENTAL for DBMS_STATS?
    More space is required in SYSAUX table to maintain global statistics.
  41. What does DBMS_STATS.AUTO_SAMPLE_SIZE do?
    Allows Oracle to determine the best sample size necessary for good statistics.
  42. What are Multi-Column Statistics?
    Extended statistics against two or more columns to provide better plans when the columns are used in query
  43. What are Expression Statistics?
    Statistics gathered on a column with an expression
  44. How are Expression Statistics gathered?
    • Using DBMS_STATS.CREATE_EXTENDED_STATS
    • SELECT DBMS_STATS.CREATE_EXTENDED_STATS (null, 'cust' '(lower(state))') from dual;
  45. What are the possible values for LOCK_SGA?
    It's Boolean, True/False
  46. Which parameter prevents swapping the SGA?
    Which parameter prevents swapping the SGA?LOCK_SGA
  47. Desc Gathering Pending stats for a table and then publishing the pending stats
    • DBMS_STATS.delete_table_stats('USER','TAB');
    • DBMS_STATS.set_table_prefs('USER','TAB', 'PUBLISH', 'false');
    • ANALYZE TABLE scott.emp COMPUTE STATISTICS;
    • DBMS_STATS.publish_pending_stats('USER','TAB');
  48. What are the possible blanks here: INSERT_??_DIRECTIVE?
    • FINDING
    • PARAMETER
    • SEGMENT
    • SQL
  49. How does once remove an ADDM directive?
    DELETE_??_DIRECTIVE
  50. What does INSERT_SQL_DIRECTIVE do?
    Limit the ADDM to  a specific SQL statement
  51. What does INSERT_PARAMETER_DIRECTIVE do?
    Limits ADDM to report on recommendations to changes for a specific Init parameter
  52. What does INSERT_FINDING_DIRECTIVE do?
    Limits to the ADDM to a specific finding
  53. What does INSERT_SEGMENT_DIRECTIVE do?
    Limit the ADDM to a specific segment
  54. By default, how long are AWR retained retained for?
    7 Days
  55. Which initialization parameters take their memory from the SGA_TARGET but need to be manually sized?
    • LOG_BUFFER
    • DB_nk_CACHE_SIZE
    • DB_KEEP_SIZE
    • DB_RECYCLE_CACHE_SIZE
  56. Which initilization parameters take their memory from the SGA_TARGET and are automayically sized?
    • SHARED_POOL_SIZE
    • LARGE_POOL_SIZE
    • JAVA_POOL_SIZE
    • DB_CACHE_SIZE
    • STREAMS_POOL_SIZE
  57. 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.
  58. Which OS command line utility will produce an ADDM report?
    addmrpt.sql

What would you like to do?

Home > Flashcards > Print Preview