Exam Code: 1z0-054 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 11g: Performance Tuning
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0-054 Exam.
2016 Oct 1z0-054 free practice exam
Q71. View the Exhibit and note the workload type selected to quickly configure the metric threshold.
Which statement describes the result after finishing this quick configuration process?
A. It sets thresholds for all basic metrics.
B. All thresholds set are of the Significance Level type.
C. It uses only the SYSTEM_MOVING_WINDOW baseline for thresholds.
D. The thresholds set by the quick configuration process are of the Fixed Value type.
Q72. You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can be used by the query subsequently for a better execution plan. After implementing the profile, you altered the profile to include it in a category as follows:
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name=> 'SYS_SQLPROF_0146ced728214000', attribute_name => 'CATEGORY', value => 'DEV2').
But the performance has not improved for the user session. The profile is enabled and on investigating the query plan, you find that the query from the user session does not use the SQL profile. View the Exhibit and examine the parameters for the user session.
What could be the reason for this?
A. The CONTROL_MANAGEMENT_PACK_ACCESS parameter is not set to DEV2.
B. The OPTIMIZER_MODE parameter is set to ALL_ROWS for the user session.
C. The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session.
D. The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE for the user session.
Q73. You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables.
What would happen to the SQL profile that is created for the SQL statement?
A. It becomes outdated immediately after the tables change.
B. It is maintained automatically along with the changes in the tables.
C. The profile will be used for the changed plans of the SQL statement.
D. The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.
Q74. Identify the situation in which you would use the Automatic Workload Repository (AWR) baseline template.
A. when creating AWR snapshots based on a repeating time schedule
B. when creating and dropping AWR baselines based on a repeating time schedule
C. when computing statistics for all moving window baselines based on a repeating time schedule in the future
D. when resetting the threshold values for the SYSTEM_MOVING_WINDOW baseline based on a repeating time schedule in the future
Q75. Examine the initialization parameter values for the instance given below:
NAME TYPE VALUE
------------------------------------ ----------- ------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 220.127.116.11
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
db_file_multiblock_read_count integer 64
The index created on the column used in the WHERE clause of the query. You notice that the query is not using the index. Instead of an index scan, a full table scan is used. View the Exhibit and examine the autotrace output for a query.
What could be the reason for it? (Choose all that apply.)
A. The OPTIMIZER_INDEX_COST_ADJ initialization parameter has a low value.
B. The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter has a low value.
C. The statistics for the table and all the indexes associated with the table are not current.
D. The table has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high-water mark.
Update 1z0-054 exam fees:
Q76. 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
Q77. You work as a DBA and you have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity:
SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100
2 "Cache Hit ratio"
3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd
4 WHERE cur.name = 'db block gets'
5 AND con.name = 'consistent gets'
6 AND phy.name = 'physical reads'
7 AND phyd.name = 'physical reads direct'
Cache Hit Ratio
Which understanding of this ratio is correct?
A. 98.43% of times the requests have found free buffers.
B. 98.43% of the total number of requests used the buffer cache.
C. 98.43% of times the requests were satisfied by performing physical I/Os.
D. 98.43% of times the requests have found the required data blocks in the buffer cache.
Q78. You observed in your database that the queries formerly executed in 5 seconds now take longer time.View the Exhibit and analyze the output of the query that you executed to find the problem.
Which is the primary concern?
A. the number of disk sorts
B. the number of full table scans
C. the number of chained and migrated rows
D. the number of rows fetched based on rowid
Q79. The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database instance. The space consumption has significantly increased by the automatically captured plan baselines. You are suggested to run the following block:
After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file. Which two actions can be solutions for this? (Choose two.)
A. increasing the AWR retention period
B. purging the older SQL management objects
C. increasing the size of the SYSTEM tablespace
D. increasing the size of the SYSAUX tablespace
Q80. View the Exhibit and examine the plans in the SQL plan baseline.You observe that the last two SELECT statements (outlined in red and blue) are identical, but have two different plans. Also, the second plan (in blue) is not being used by the optimizer.
Which two steps do you perform to ensure that the optimizer uses the second plan (in blue) if the plan is more efficient? (Choose two.)
A. Mark the second plan (in blue) as fixed.
B. Use SQL Tuning Advisor to tune the plan.
C. Use SQL Access Advisor to tune the plan.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.