Review Questions
1. Which of the following components of Oracle architecture stores the statistics gathered by the
MMON process?
A. ADDM
B. AWR
C. Data dictionary views
D. Dynamic performance views
----
The MMON process gathers statistics from the SGA and stores them in the AWR. The
ADDM process then uses these statistics to compare the current state of the database with baseline and historical performance metrics before summarizing the results on the EM Database
Control screens.
Ans: B
2. Which of the following options for the PFILE/SPFILE parameter STATISTICS_LEVEL turns off AWR statistics gathering and ADDM advisory services?
A. OFF
B. TYPICAL
C. ALL
D. BASIC
---
Setting STATISTICS_LEVEL = BASIC disables the collection and analysis of AWR statistics.
TYPICAL is the default setting, and ALL gathers information for the execution plan and operating system timing. OFF is not a valid value for this parameter.
Ans: D.
3. The following graphic is from the Sessions: Waiting And Working section of the EM Database Control Performance screen. Using this output, which of the following is the primary source of
user wait activity?
A. CPU Used
B. User I/O
C. System I/O
D. Configuration
----
Ans: B.
The I/O caused by user activity is the primary source of user waits because it is listed first in
the graph's legend. Clicking the User I/O link opens a screen in which you can examine which
SQL statements are contributing the most to the user waits.
4. The following graphic shows the SQL statements that are having the greatest impact on overall DB Time. Which statement has had the greatest impact?
A. 9d87jmt7vo6nb(2.
B. 8acv8js8kr574(24.
C. b6usrq82hwsa3(73.
D. None of the above was highest.
---
Ans: C.
The pie graph shows that the SQL statement that has been assigned the identifier of
b6usrq82hwsa3(73) contributed to 73 percent of the total time spent servicing the top three
SQL statements.
5. Suppose you have used EM Database Control to drill down into ADDM findings and have found that a single SQL statement is causing the majority of I/O on your system. Which of the following advisors is best suited to troubleshoot this SQL statement?
A. SQL Tuning Advisor
B. SQL Access Advisor
C. Both A and B
D. Neither A or B
----
Ans: C.
You can use the SQL Tuning Advisor and SQL Access Advisor together to determine if I/O
can be minimized and overall DB Time reduced to the targeted SQL statement.
6. Nearly all the advisors submit their analysis activities to the database in the form of a job. When the analysis job is submitted, which option for job scope adds the least overhead to the system?
A. Limited
B. Restricted
C. Comprehensive
D. Thorough
----
Ans: A.
The Limited scope has the least impact on the system. The Comprehensive scope is the most
detailed, but also makes the greatest demands on the system. There are no job scope options
called Restricted or Thorough.
7. Using the Top SQL link of the EM Database Control Performance screen produces the output shown in the following graphic. Approximately which time interval produced the highest activity for this monitored event?
A. 9:45 to 9:50
B. 10:00 to 10:45
C. 9:55 to 10:10
D. 10:00 to 10:05
---
Ans: D.
The shaded area shows that the time interval from approximately 10:00 to 10:05 will be analyzed for Top SQL statements.
8. Which data dictionary view contains information explaining why ADDM made its recommendations?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_OBJECTS
C. DBA_ADVISOR_RECOMMENDATIONS
D. DBA_ADVISOR_RATIONALE
---
Ans: D. DBA_ADVISOR_RATIONALE provides the rationale for each ADDM
recommendation. The ADDM findings are stored in DBA_ADVISOR_FINDINGS. The object related to the findings are shown in DBA_ADVISOR_OBJECTS. The actual ADDM recommendations are found in DBA_ADVISOR_RECOMMENDATIONS.
9. Which of the following advisors determines if the space allocated to the Shared Pool, Large Pool, or Buffer Cache are adequate?
A. SQL Tuning Advisor
B. SGA Tuning Advisor
C. Memory Advisor
D. Pool Advisor
---
Ans C.
The Memory Advisor can help determine whether the overall size of the SGA is appropriate
and whether memory is properly allocated to the SGA components.
10. Which of the following advisors determines if the estimated instance recovery duration is within the expected service-level agreements?
A. Undo Management Advisor
B. SQL Access Advisor
C. SQL Tuning Advisor
D. MTTR Advisor
---
Ans: D.
The Mean Time To Recover (MTTR) Advisor provides recommendations that you can use
to configure the database so that the instance recovery time fits within the service levels that
you specified.
11. If no e-mail address is specified, where will alert information be displayed?
A. In the DBA_ALERTS data dictionary view
B. In the V$ALERTS dynamic performance view
C. In the EM Database Control main screen
D. No alert information is sent or displayed.
-----
Ans C.
By default, alerts are displayed in the Alerts section of the EM
Database Control main screen,
even when e-mail notifications are not displayed.
12. When you configure an alert, which of the following types of alert thresholds can you use to monitor a tablespace for diminishing free space?
A. Warning threshold
B. Critical threshold
C. Both A and B
D. Neither A or B
---
Ans: C.
You can specify both warning and critical thresholds for monitoring the available free space in a tablespace. In this situation, the warning threshold is generally a lower number than the critical
threshold.
13. Multiple baseline metrics can be gathered and stored in the AWR. Why might you want more than one metrics baseline?
A. You might want a separate baseline metric for each user.
B. You might want a separate baseline metric for daytime usage versus off-hours usage.
C. You might want a separate baseline metric for each schema.
D. You would never want more than one baseline metric, even though it is possible to gather
and store them.
---
Ans: B.
Because many transactional systems run batch processing during off-hours, having a relevant
baseline for each type of usage pattern yields better results in terms of alerts and ADDM recommendations.
14. Using EM Database Control, you discover that two application PL/SQL functions and a view are currently invalid. Which of the following might you use to fix these objects? (Choose two.)
A. Shut down and restart the database.
B. Use EM Database Control to recompile the object.
C. Export the invalid objects, drop them, and then import them.
D. Use the ALTER FUNCTION ... COMPILE and ALTER VIEW ... COMPILE
commands.
---
Ans: B, D.
After fixing the issue that originally caused the invalid status, you can use both EM Database
Control and SQL to compile an invalid object. Starting and stopping the database will not fix
invalid objects. Export/import is also not an appropriate technique for recompiling invalid objects.
15. You have just created a database using scripts that you wrote. Now
you notice that the automatic collection of database statistics by the EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS procedure is not running. What might be the cause?
A. The PFILE/SPFILE parameter GATHER_STATS=FALSE.
B. Only databases created using Database Configuration Assistant have automatic statistics
collection enabled.
C. The SYSMAN user who owns the AWR is not logged in.
D. The operating system does not support automatic statistics collection.
----
Ans: B.
Automatic statistics collection can be started on databases created outside the Database Configuration Assistant by using the Automatic Workload Repository link in the EM
Database Control Performance screen.
16. Which of the following is a performance metric that could be defined as 'the amount of work that a system can perform in a given amount of time'?
A. Response time
B. Uptime
C. Throughput
D. Runtime
---
Ans: C.
Throughput is an important performance metric because it is a overall measure of performance
that can be compared against similar measures taken before and after tuning changes are
implemented.
17. Which of the following is not one of the three primary sources of performance metric information in the EM Database Control Performance screen?
A. Host
B. Session
C. Instance
D. Network
---
Ans: D.
Network information may be contained in the Session Information section of the EM Database
Control Performance screen, but only if network issues contributed to session wait times.
18. By default, how long will database statistics be retained in the AWR?
A. 7 days
B. 30 days
C. 7 hours
D. Indefinitely
----
Ans: A.
By default, database statistics are retained in the AWR for seven days. You can change the
default duration using the EM Database Control Automatic Workload Repository link in
the Performance screen or using the DBMS_WORKLOAD_REPOSITORY PL/SQL package.
19. Your users have called to complain that system performance has suddenly decreased markedly. Where would be the most likely place to look for the cause of the problem in the EM
Database Control?
A. Main screen
B. Performance screen
C. Administration screen
D. Maintenance screen
---
Ans: B.
The Performance screen of the EM Database Control provides a quick overview of how the
host system, user sessions, and throughput are impacted by the system slowdown. You can also
drill down into any of these three areas to take a look at details about this slowdown.
20. Using EM Database Control, you've identified that the following SQL
statement is the source of a high amount of disk I/O:
SELECT NAME, LOCATION, CREDIT_LIMIT FROM CUSTOMERS
What might you do first to try to improve performance?
A. Run the SQL Tuning Advisor.
B. Run the SQL Access Advisor.
C. Check the EM Database Control main screen for alerts.
D. Click the Alert Log Content link in the EM Database Control main
screen.
----
Ans: A.
Running the SQL Tuning Advisor provides the most information about how the performance
of this SQL statement might be improved. The SQL Access Advisor is run only after the output
from the SQL Tuning Advisor indicates that it will be useful. EM Database Control does not
store detailed information about I/O activity in either its alerts or the Alert log.
No comments:
Post a Comment