1z0-054 | Up to date 1z0-054 Exam Study Guides With New Update Exam Questions
Q51. View the Exhibit and analyze the output of the query.What does the high value for the table fetch continued rows statistics indicate?
Exhibit:
A. chained rows fetched only
B. migrated rows fetched only
C. rows fetched from external tables
D. both chained and migrated rows fetched
E. rows fetched from Index Organized Tables (IOT)
Answer: D
Q52. View the Exhibit. You want to set the threshold for the number of transactions (per second)metric with the threshold type as significance level.
Which baseline would you choose to make the threshold adaptive?
Exhibit:
A. LAST_NIGHT
B. THU_SECTION
C. SYSTEM_MOVING_WINDOW
D. MONDAY_1_2008-07-15.02:00
Answer: C
Q53. Your database has the HR and Reporting applications. The HR application does most of the transactions on the database and the Reporting application does most of the batch processing operations.The Reporting application has more workload during nonpeak hours. Both the HR and Reporting applications run on the middle tier using connection pooling for connecting to database.You want to statistically measure the workload generated by both the applications and set priority for the workload generated by the applications.
Which option would help you achieve this?
A. Assigning individual sessions created by the applications to consumer groups and setting relative priority
B. Assigning profiles to individual sessions created by the applications to set the relative priority for the resource usage
C. Creating services for the applications and setting the relative priority of services within an instance by binding the services directly to consumer groups
D. Creating services for the applications and assigning different profiles to the sessions created by the sessions to set the relative priority for the resource usage
Answer: C
Q54. 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.)
Exhibit:
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
Answer: AC
Q55. You are working on an online transaction processing (OLTP) database that runs on a machine having multiple CPUs. The database uses normal file system storage. Examine the following initialization parameters and their respective values for the instance:
NAME TYPE VALUE
------------------------------------ ---------- ----------------------
dbwr_io_slaves integer 0
db_writer_processes integer 1
filesystemio_options string NONE
disk_asynch_io boolean TRUE
The applications perform small, random I/Os in which each foreground process reads a data block into the buffer cache for updates and the changed blocks are written in batches by the DBWR process. You noticed free buffer waits events for your database. To solve the problem, you increased the size of the buffer cache. But after some time, you notice the same event again.
Which three possible actions would you recommend? (Choose three.)
A. configuring multiple DBWR processes
B. decreasing the size of the buffer cache
C. configuring I/O slaves for the DBWR process
D. configuring instances to make asynchronous I/O available to DBWR
E. spread data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem
Answer: ADE
Q56. 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?
Exhibit:
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.
Answer: E
Q57. What type of redundancy would you recommend for the Automatic Storage Management (ASM) of a file system based on RAID level 1 for the optimum performance of database using ASM storage?
A. high redundancy
B. normal redundancy
C. external redundancy
D. use both ASM mirroring and hardware mirroring
Answer: C
Q58. You work for a company as a DBA. The company has an application to manage the details of its business and customer base. However, application users complain that updating the SALES record takes more time than it used to earlier. On investigation, you notice that the application performance degrades when a call is made to the UPDATE_INV procedure. Further, you execute the following query to investigate:
SQL> SELECT name, type, sharable_mem, kept
2 FROM v$db_object_cache
3 WHERE sharable_mem > 4000
4 AND EXECUTIONS > 5
5 AND (type='FUNCTION' OR type='PROCEDURE')
6 /
NAME TYPE SHARABLE_MEM KEPT
------------------- -------------- --------------- -----------
GETEMKEY FUNCTION 13695 YES
UPDATE_INV PROCEDURE 14766 NO
SETEMUSERCONTEXT PROCEDURE 13703 YES
DECRYPT FUNCTION 17790 YES
UPDATE_DEPT PROCEDURE 18765 NO
What would you do to improve the performance?
A. Enable the result cache if not already enabled.
B. Flush the shared pool to make space for this procedure.
C. Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.
D. Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.
E. Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.
Answer: C
Q59. Which two symptoms related to the database buffer cache together indicate that there are many full table scans happening? (Choose two.)
A. too many buffer busy waits
B. very low buffer cache-hit ratio
C. very high buffer cache-hit ratio
D. many waits on the db file scattered read event
Answer: BD
Q60. Which three statements are true about performance analysis by SQL Performance Analyzer? (Choose three.)
A. It detects changes in SQL execution plans.
B. It shows only the overall impact on workload.
C. It produces results that can be used to create the SQL plan baseline.
D. It generates recommendations to run SQL Tuning Advisor to tune regressed SQL statements.
E. The importance of SQL statements to be analyzed is based on the size of the objects accessed.
Answer: ACD