Approved of 1z0-054 free question materials and vce for Oracle certification for IT professionals, Real Success Guaranteed with Updated 1z0-054 pdf dumps vce Materials. 100% PASS Oracle Database 11g: Performance Tuning exam Today!
2016 Sep 1z0-054 Study Guide Questions:
Q31. You are managing an online transaction processing (OLTP) system. Many users complain about the slow response time. You investigated and found that in the Top 5 events, direct path read accounts for 73.2% of the total wait time.
Which two are the possible reasons for the high direct path read time? (Choose two.)
A. The I/O subsystem is slow.
B. The SQL statements are using high CPU.
C. The SQL statement has a long parsing time.
D. The SQL statements are performing a large number of physical and logical reads.
Q32. 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. increasing the number database writers
B. decreasing the number of database writers
C. increasing the size of the database buffer cache
D. decreasing the size of the database buffer cache
E. decreasing the value set to the FAST_START_MTTR_TARGET parameter
Q33. Which three statements are true about Statspack usage? (Choose three.)
A. If both Statspack and AWR are used, they cannot be scheduled to capture snapshots at the same time.
B. Time Model data cannot be captured by Statspack unless the STATISTICS_LEVEL parameter is set to ALL.
C. Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.
D. Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an instance.
E. For best performance when running the performance reports, optimizer statistics should be gathered on the Statspack schema.
Leading 1z0-054 pdf exam:
Q34. You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.
Why is the query using a full table scan instead of an index scan?
A. because the histogram statistics for the COUNTRY_ID column are not updated
B. because the index statistics for the index on the COUNTRY_ID column are not current
C. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a higher value
D. because the optimizer predicts that most of the blocks in the table are accessed.
Therefore, it uses a full table scan, even though indexes are available.
Q35. Which statement is correct about server-generated alerts?
A. All server-generated alerts are based on threshold levels.
B. Alerts are not issued for locally managed tablespaces in read-only mode.
C. The STATISTIC_LEVEL initialization parameter must be set to ALL to enable server-generated alerts.
D. Alerts for both locally managed tablespaces and dictionary-managed tablespaces are server generated.
Q36. You work in an online transaction processing (OLTP) environment and users are experiencing slow response time on their queries. These users connect as FINANCE to run their application and run hundreds of sessions. Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace and there is no load on other temporary tablespaces. View the Exhibit and examine the details.
After some time you observed that sort operations are running out of temporary space.
What would be a long-term solution?
A. Change the temporary tablespace assigned to this user ID.
B. Create and assign temporary tablespace group to this user ID.
C. Increase the size of the temporary tablespace assigned to this user ID.
D. Make the bigger temporary tablespace the default temporary tablespace ID.
Vivid 1z0-054 bible:
Q37. The users in your online transaction processing (OLTP) environment complain that the response time of the application has increased dramatically.To investigate the problem, view the Exhibit and examine the queries you executed.
What do you infer?
A. Cursor sharing is not enabled.
B. The large pool is not configured.
C. The temporary tablespace is full.
D. The value set for the SHARED_POOL_SIZE initialization parameter is too small.
E. The value set for the PGA_AGGREGATE_TARGET initialization parameter is too small.
Q38. Examine the output of the following query:
SQL>; SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,
3 FROM v$latch a, v$latchholder b, v$latchname c
4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch#
5 AND c.name LIKE '%latch_name%' ORDER BY a.latch#;
LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
———— ——– ——– ——- —— ————– —————-
shared pool 20016544 8520540 14112 3137 0 0
Which two conclusions can you draw from this? (Choose two.)
A. The latch was requested in no wait mode.
B. The latch was requested in willing-to-wait mode.
C. The shared pool operations to allocate and free memory in it caused the misses.
D. Automatic Memory Management is not enabled because the IMMEDIATE_GETS and IMMEDIATE_MISSES columns have zero in them.
Q39. 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 22.214.171.124
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.
Q40. You work as a DBA for a company and manage an online transaction processing (OLTP) system. You received complaints about the performance degradation of SQL statements executed by the application that uses this database.View the Exhibit and examine the output of the queries executed to investigate the performance issues.
Which two actions can improve performance? (Choose two.)
A. increasing the size of the shared pool
B. setting the CURSOR_SHARING parameter to EXACT
C. decreasing the value set for the OPEN_CURSORS parameter
D. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1337874392
E. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117