Oracle Database 11g: Performance Tuning - 1Z0-054 Exam Practice Test
You identified a SQL statement with SQL ID 9g485acn2n30m, which is expensive on the
resources. To view the execution plan for the statement, you executed the following command:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9g485acn2n30m',1));
What would this command accomplish?
resources. To view the execution plan for the statement, you executed the following command:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9g485acn2n30m',1));
What would this command accomplish?
Correct Answer: A
Vote an answer
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?
Correct Answer: A
Vote an answer
Examine the output of the query given below:
SQL> SELECT mutex_type, location, sum(gets), sum(sleeps) FROM v$mutex_sleep_history
GROUP BY mutex_type, location;
MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS)
Library Cache kglhdgn1 62 8669586 4538 Library Cache kglget2 2 2016618 24 Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1 Cursor Pin kkslce [KKSCHLPIN2] 666831 678 Library Cache kgllkdl1 85 3369224 110 Library Cache kglpnal1 90 224199 13 Library Cache kglic1 49 42068 10 Library Cache kglpin1 4 9620087 374 Library Cache kglpndl1 95 2065089 79 9 rows selected. Which statement is true?
SQL> SELECT mutex_type, location, sum(gets), sum(sleeps) FROM v$mutex_sleep_history
GROUP BY mutex_type, location;
MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS)
Library Cache kglhdgn1 62 8669586 4538 Library Cache kglget2 2 2016618 24 Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1 Cursor Pin kkslce [KKSCHLPIN2] 666831 678 Library Cache kgllkdl1 85 3369224 110 Library Cache kglpnal1 90 224199 13 Library Cache kglic1 49 42068 10 Library Cache kglpin1 4 9620087 374 Library Cache kglpndl1 95 2065089 79 9 rows selected. Which statement is true?
Correct Answer: B
Vote an answer
Examine the following query and its output:
SQL>SELECT namespace, gets, gethits, gethitratio, pins, pinhits,
reloads,invalidations
FROM v$librarycache
WHERE namespace = 'SQL AREA';
NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS RELOADS INVALIDATIONS
SQL AREA 11942 2832 .237146207 65900 62486 28 118 Which two observations are correct? (Choose two.)
SQL>SELECT namespace, gets, gethits, gethitratio, pins, pinhits,
reloads,invalidations
FROM v$librarycache
WHERE namespace = 'SQL AREA';
NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS RELOADS INVALIDATIONS
SQL AREA 11942 2832 .237146207 65900 62486 28 118 Which two observations are correct? (Choose two.)
Correct Answer: A,C
Vote an answer
What is the purpose of running the statspack.snap script during the installation of the Statspack package?
Correct Answer: A
Vote an answer
While configuring the snapshot data capture for your database, you have specified the snapshot level setting as 6.Which statistics would be captured by the Statspack report? (Choose all that apply.)
Correct Answer: A,B,C
Vote an answer
View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.
The Shared Pool was shrunk when the Default Buffer Cache required more memory. Why did the Java Pool not shrink when the Default Buffer Cache grew?
The Shared Pool was shrunk when the Default Buffer Cache required more memory. Why did the Java Pool not shrink when the Default Buffer Cache grew?
Correct Answer: C
Vote an answer
You are managing an online transaction processing (OLTP) application database. Some users on the system are reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identify the SQL statements that are expensive on system resources.What would you recommend to accomplish the task? (Choose all that apply.)
Correct Answer: B,D
Vote an answer
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 data distribution in the table and the histograms on the
table.
Further, you query the SALES table thrice more in the following order:
-query rows having product ID 4
-query rows having product ID 2
-query rows having product ID 4
View the Exhibit named QUERY-n to see the details about these queries.
What do you infer from this?
10,000 rows but only four distinct products are sold.
View the Exhibit named HIST to check data distribution in the table and the histograms on the
table.
Further, you query the SALES table thrice more in the following order:
-query rows having product ID 4
-query rows having product ID 2
-query rows having product ID 4
View the Exhibit named QUERY-n to see the details about these queries.
What do you infer from this?
Correct Answer: D
Vote an answer
View the Exhibit and examine the partial output from the following query in an online transaction
processing (OLTP) database:
SQL>SELECT intsize_csec,metric_name,value, metric_unit
FROM v$sysmetric;
Which two conclusions can you draw about the database from these metrics? (Choose two.)
processing (OLTP) database:
SQL>SELECT intsize_csec,metric_name,value, metric_unit
FROM v$sysmetric;
Which two conclusions can you draw about the database from these metrics? (Choose two.)
Correct Answer: C,D
Vote an answer
View Exhibit1 to examine the description of the CUSTOMERS table.
The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement,
you notice that estimated rows and the actual number of rows fetched differ greatly. The
COUNTRY_ID column has an index.
View Exhibit2 and examine the query execution plan.
What would you recommend to improve the optimizer's estimation?
The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement,
you notice that estimated rows and the actual number of rows fetched differ greatly. The
COUNTRY_ID column has an index.
View Exhibit2 and examine the query execution plan.
What would you recommend to improve the optimizer's estimation?
Correct Answer: A
Vote an answer
After an Automatic Database Diagnostic Monitor (ADDM) run, you receive a recommendation for buffer busy wait issue. View the Exhibit. What is the significance of the Benefit(%) column in the Exhibit?
Correct Answer: C
Vote an answer
View the Exhibit and examine the statistics of a SQL statement.Which two conclusions can you draw from the statistics? (Choose two.)
Correct Answer: B,D
Vote an answer