Online Access Free 1Z0-054 Exam Questions

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 query uses an alias for a column
• C.because the query uses SYSDATE
• D.because the CLIENT_RESULT_CACHE_SIZE parameter is set to 0

Which three statements are true about Statspack usage? (Choose three.)

• A.Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.
• B.If both Statspack and AWR are used, they cannot be scheduled to capture snapshots at the same time.
• C.Time Model data cannot be captured by Statspack unless the STATISTICS_LEVEL parameter is set to ALL.
• 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.

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 the sort operations are running out of temporary space. What would be a long-term solution?

• A.Make the bigger temporary tablespace the default temporary tablespace ID.
• B.Increase the number of database writer (DBWn) processes I
• C.Change the temporary tablespace assigned to this user ID.
• D.Configure the large pool if it is not done already.
• E.Create and assign temporary tablespace group to this user ID.

You work on an online transaction processing (OLTP) database in which the SALES table has
10,000 rows but only four distinct products are sold.
View the Exhibit named HIST to check the distribution of values in the table and the histograms on
the table.

You check the plan table and notice that both the queries that executed on the SALES table used index range scan. The second query retrieved most of the rows in the table but used index range scan.

Why would the second query use the same plan?

• A.because the plan wasbind aware
• B.because the bind peeking never happened
• C.because the OPEN_CURSORS parameter is set to a very low value
• D.because the optimizer did not consider selectivity due to the use of bind variables

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:
SQL>DECLARE
2l_latency INTEGER;
3l_iops INTEGER;
4l_mbps INTEGER;
5 BEGIN
6dbms_resource_manager.calibrate_io (1,10,l_iops,l_mbps,l_latency);
7 END;/
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 maximum sustainable I/O for a single process is 12 MB per second.
• B.The actual latency is 10 milliseconds for one disk.
• 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.