Online Access Free 1Z0-054 Exam Questions

You are working as a DBA on an online transaction processing (OLTP) system. This OLTP systems runs on a machine with a single CPU, on which applications primarily perform small random I/Os where 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 the increase in I/O requests queued up against a disk, and an increase in the wait time in queue. What would you recommend to improve the latency of I/O requests?

• A.Increase the value of the DB_WRITER_PROCESSES parameter.
• B.Decrease the value of the DBWR_IO_SLAVES parameter.
• C.Stripe data across multiple disks.
• D.Increase the size of buffer cache.

View the Exhibit and examine the output for the query on V$IOSTAT_FUNCTION.Which two statements are true about the interpretation of the output? (Choose two.)

• A.If there is a conflict of I/O functions, the I/O is placed in the bucket with the higher FUNCTION_I
• B.The I/O statistics are only for the single block read and write operations.
• C.If there is a conflict of I/Ofunctions, the I/O is placed in the bucket with the lower FUNCTION_ID.
• D.The I/O statistics are for single block or multi-block read and write operations.

You noticed a high number of SQL statements with the same PLAN_HASH_VALUES. View Exhibit1 to see the details.

View Exhibit2 to see the details of the SQL text for the statements.

What do you infer from the results?

• A.Removing the bind variable would improve performance.
• B.The statements are not using the same cursor.
• C.The statements are using the same cursor.
• D.The cursor for the statement is aging out of the shared pool very quickly.

Your database has the ACCTG service configured for an accounting application that runs on a
middle tier. The application has three modules LEDGER, ACCOUNTS, and BILLING. You enabled
tracing at the service level by executing the following command:
SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ACCTG');
Which statement is true?

• A.A single trace file would be created for all the sessions for the service.
• B.Three trace files would be created for the three modules in the application.
• C.A trace file is created for each session that uses the service, regardless of the module and action.
• D.The trace file would not be created because the module name is not specified.

You work as a DBA for a company and as a performance improvement measure, you
implemented the result cache in your database.
View the Exhibit named SETTING and note the result cache settings.

Many users in the company state 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 slnoq.currval as "SLNO", prod_id, pdname,
2 cust_name FROM sales WHERE sl_date < sysdate;
View the Exhibit named TEST and examine the testing performed to check this.

Why is the result cache not used? (Choose all that apply.)

• A.because the query uses the SLNOQ.CURRVAL sequence
• B.because the CLIENT_RESULT_CACHE_SIZE parameter is set to 0
• C.because the query uses an alias for a column
• D.because the query uses SYSDATE