Oracle 1Z0-054 問題集

質問 1：
You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command at regular intervals: SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'AUTO');You need statistics to be collected more quickly. How do you achieve this?
A. Increase the value of the STALE_PERCENT preference for the partition table.
B. Set the PUBLISH preference to TRUE for the partition table.
C. Set the NO_VALIDATE preference to TRUE for the partition table.
D. Set the INCREMENTAL preference to TRUE for the partition table.
正解：D

質問 2：
Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements. You are not sure about the elapsed time that the SQL statements will have in the new system due to the version change of the optimizer. Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?
A. SQL Access Advisor
B. Automatic Workload Repository (AWR)compare period report
C. SQL Performance Analyzer (SPA)
D. SQL Tuning Advisor (STA)
正解：C

質問 3：
You work for ABC Pvt Ltd. The company has recently upgraded one of its development databases
to Oracle Database 11g from Oracle Database 10g.
You noticed that the System Global Area (SGA) is undersized as shown in the Exhibit.
To investigate further, you checked the related parameters as shown below:
SQL> show parameter sga_max_size
NAME TYPE VALUE
sga_max_size big integer 500M SQL> show parameter target NAME TYPE VALUE
archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 0 memory_target big integer 0 pga_aggregate_target big integer 384M sga_target big integer 384M Which is the best solution that you would recommend?

A. Increase the value of both SGA_TARGET and PGA_AGGREGATE_TARGET parameters.
B. Set MEMORY_MAX_TARGET and MEMORY_TARGET parameters.
C. Reduce the value of the PGA_AGGREGATE_TARGET parameter.
D. Increase the value of the SGA_MAX_SIZE parameter.
E. Diagnose further to identify which component in SGA is undersized and then resize it.
正解：B

質問 4：
You find that the OS utilization is over 40% in your database server. Examine the following set of performance monitoring-related activities:
1.Investigate the operating system for network transfers, paging, and swapping.
2.Check the SQL and the transactions submitting the SQL for optimal execution.
3.Investigate the top SQL by CPU utilization.
4.Consider rescheduling some work to off-peak hours.
5.Check whether there are any nondatabase jobs consuming CPU time on the machine. What is the correct sequence in which the above activities should be performed to diagnose and reduce potential performance issues?
A. 2,4,5,3,1
B. 1,2,3,4,5
C. 1,5,3,2,4
D. 4,5,3,1,2
正解：C

質問 5：
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 is 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. Which option describes the correct understanding of the main problem?
A. % Non-Parse CPU: andSoft Parse %: are low in the EFFICIENCY Exhibit, which shows that very few statements are found in the cache. This indicates that there were too many hard parses.
B. sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit. This indicates too many soft parses.
C. The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time, which indicates that the CPU is very slow.
D. hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit. This indicates inadequate database buffer cache.
正解：A

質問 6：
View the Exhibit and examine the partial output obtained when you issued the following query: SQL> SELECT name,class,value FROM v$sysstat ORDER BY class;Which two interpretations are correct about the information obtained from this view? (Choose two.)

A. redo log spacerequests indicates the number of times a there were waits for disk space to be allocated for the redo log entries.
B. db block changes indicates the number of data blocks that have been changed.
C. Usage of CACHE and NOCACHE hints affects statistics for table scans (long tables) and table scans (short tables).
D. buffer is pinned count indicates the number of buffers that are currently pinned in the database instance.
正解：A,C

質問 7：
You work as a database administrator to manage an online transaction processing (OLTP)
system.The application code was recently tuned for optimum performance and users
acknowledged the performance improvement. However, the users are now again complaining that
the application response time has gone down considerably.
You used a few queries to investigate the problem and a couple of important queries are shown in
the Exhibit. View the Exhibit and analyze the output shown.
Which actions would improve the performance? (Choose all that apply.)

A. decreasing the value set to the FAST_START_MTTR_TARGET parameter
B. decreasing the number of database writers
C. increasing the size of the database buffer cache
D. increasing the number database writers
E. decreasing the size of the database buffer cache
正解：C,D