Proper study guides for Rebirth Oracle Oracle Database 11g: Performance Tuning certified begins with Oracle 1z0-054 preparation products which designed to deliver the Breathing 1z0-054 questions by making you pass the 1z0-054 test at your first time. Try the free 1z0-054 demo right now.
2016 Jun 1z0-054 download
Q1. You generate an AWR Compare Periods Report for two consecutive time periods as a routine activity to monitor your database performance.View the Exhibit and examine the partial details of the Time Model Statistics generated in the report.
Which two statements give a correct inference based on the DB Time and DB CPU in the Time Model Statistics? (Choose two.)
A. More CPU time was used for background processes in the first time period.
B. A change to the database configuration reduced the % DB time for transactions.
C. More of DB Time was consumed by background processes in the first time period.
D. A reduction in Hard Parse activity resulted in a reduction of DB CPU Time (in seconds).
E. More transactions were processed in the first time period based on the DB Time values.
Q2. View the Exhibit and examine the content of the V$PGA_TARGET_ADVICE_HISTOGRAM view.
What would you infer from this?
A. If the PGA size is set to 4 MB, then all the work areas would execute in optimal mode.
B. If the PGA size is doubled, then all the work areas that are less than 2 MB would execute in optimal mode.
C. If the PGA size is set to 32 MB, then the number of work areas would reduce. So it is the appropriate size for PGA.
D. If the size of the Program Global Area (PGA) is set to more than 64 MB, then all the work areas would execute in optimal mode. So it is the appropriate size for PGA.
Q3. You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database. Many users in the company say that performance has improved on the queries they use, but some users complain that they have not got any performance benefit on the queries they use. You checked all the queries they use and the following is one of them:
SQL> SELECT /*+ RESULT_CACHE */ slnoq.currval as "SLNO", prod_id, pdname, cust_name
FROM sales WHERE sl_date < sysdate;
View the Exhibit and examine the testing performed to check this.
Why is the result cache not used? (Choose all that apply.)
A. because the query uses SYSDATE
B. because the query uses an alias for a column
C. because the query uses the SLNOQ.CURRVAL sequence
D. because the table might have an index on the SL_DATE column
Q4. View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.
You executed the following command to increase the size of the Large Pool:
SQL> ALTER SYSTEM SET large_pool_size=8M;
Which component would shrink to increase the size of the Large Pool?
A. the Java Pool
B. the Shared Pool
C. KEEP Buffer Cache
D. Default Buffer Cache
Q5. 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) 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.
B. 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.
C. 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.
D. 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.
Most up-to-date 1z0-054 exam fees:
Q6. View the Exhibit and analyze the content of V$BUFFER_POOL_STATISTICS.
Why does the query on the EMP table not access buffers from the keep pool?
A. because the query used SCOTT.EMP instead of EMP
B. because the query with * always uses the default pool
C. because the query with * always forces physical I/Os that bypass the keep pool
D. because the blocks of the EMP table are already available in one of the other buffer pools
Q7. You look at the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report and find that the wait event buffer busy waits is consuming a significant portion of DB time.
What does this wait event indicate?
A. buffer cache too small
B. insufficient DBWn processes
C. excessive block replacement
D. a large number of locally-managed tablespaces in the database
E. block contention on segment headers, data blocks, undo header, or undo blocks
Q8. The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. On investigation, you find that the Program Global Area (PGA) is undersized and using temporary segments for sort operations. You plan to increase the size of the PGA and there is ample amount of memory available to you. You used the PGA advisor to check the correct size for PGA. View the Exhibit and examine the content of the PGA advisor.
What size of PGA would give the maximum benefit?
A. 125 MB
B. 3009 MB
C. 8024 MB
D. 1003 MB
E. 1204 MB
F. 1404 MB
Q9. You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want to investigate the plans for these SQL statements.
Which two methods can you use to pull out the execution plan from the library cache for the already executed SQL? (Choose two.)
A. Query V$SQL_PLAN to view the execution plan.
B. Query DBA_HIST_SQL_PLAN to view the execution plan.
C. Copy and paste the SQL text from the V$SQL view and use EXPLAIN PLAN to generate the execution plan.
D. Use the dbms_xplan.display_cursor function with the SQL ID and child number to generate the execution plan.
Q10. You are working on a single node database. The storage for the database was recently upgraded to new, faster hard disks and new disk controllers. You ran the I/O calibration procedure to check the I/O metrics for the upgraded storage. The details for the execution of the I/O calibration procedure are given below:
2 l_latency INTEGER;
3 l_iops INTEGER;
4 l_mbps INTEGER;
6 dbms_resource_manager.calibrate_io (1,10,l_iops,l_mbps,l_latency);
You queried the DBA_RSRC_IO_CALIBRATE data dictionary view to check the results of the I/O calibration. The output for the query is given as follows:
SQL> SELECT max_iops, max_mbps, max_pmbps, latency FROM DBA_RSRC_IO_CALIBRATE;
MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY
---------- ---------- ---------- ----------
137 12 6 64
What do you infer from the output?
A. The actual latency is 10 milliseconds for one disk.
B. The maximum sustainable I/O for a single process is 12 MB per second.
C. The system can sustain a maximum of 137 I/O operations per second with a throughput of 6 MB per second.
D. The system can sustain a maximum of 137 I/O operations per second with a throughput of 12 MB per second.
see more 1z0-054 dumps