While configuring the snapshot data capture for your database, you have specified the snapshot level setting as 6.Which statistics would be captured by the Statspack report? (Choose all that apply.)

• A. memory statistics
• B. optimizer execution plans
• C. resource limit
• D. parent and child latches
• E. segment-level statistics

You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report are shown in the Exhibits. View the Exhibit named DBTIME and note % DB Time.

View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

View the Exhibit named EFFICIENCY and examine the various percentages shown.
Identify the option that has the correct answers for the questions given below:
1) Which factor indicates the main problem?
2) What is the main problem?
3) What solution would you recommend?

• A. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This
  shows that very few statements are found in the cache.
  2) There are too many hard parses.
  3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.
• B. 1) The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time.
  2) The CPU is very slow.
  3) Increase the number of processors.
• C. 1) hard parse elapsed time has the majority time in parse time elapsed, which is shown in the
  TIMEMODEL Exhibit.
  2) This is due to inadequate Database Buffer Cache.
  3) Increase the size of database buffer cache.
• D. 1) sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the
  TIMEMODEL Exhibit.
  2) There are too many soft parses.
  3) Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.

You are managing an online transaction processing (OLTP) application database. Some users on the system are reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identify the SQL statements that are expensive on system resources.What would you recommend to accomplish the task? (Choose all that apply.)

• A. running SQL Performance Analyzer
• B. creating an Automatic Workload Repository (AWR) report for the problematic period
• C. running SQL Access Advisor
• D. querying the V$SQLSTATS and V$SQL_PLAN views to get the information

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regularly, but not being queried often. However statistics are being gathered for these tables automatically and regularly, thereby affecting the database performance. What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs spend less time during the automatic maintenance task?

• A. Increase the value of the STALE_PERCENT preference for these tables.
• B. Increase the value of the ESTIMATE_PERCENT preference for these tables
• C. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
• D. Mark those associated indexes that are less frequently used as invisible.

Examine the output of the following query:
SQL> SELECT tablespace_name, tablespace_size, allocated_space, free_space
2 FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP_L 157286400 144048128 157220864 TEMP 105906176 105906176 0 After some time you executed the same command and observed a different output, as shown below: SQL> SELECT tablespace_name, tablespace_size, allocated_space, free_space 2 FROM DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP_L 131072 65536 65536 TEMP 105906176 105906176 0 Why has the output changed?

• A. because the TEMP_L tablespace was shrunk
• B. because no sort operations are running currently
• C. because the database instance was restarted
• D. because checkpoint occurred