1Z0-054 Exam Questions

After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL...

View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings. View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline. The first...

You are working on an online transaction processing (OLTP) system. You detected free buffer waits events for your database instance running in a machine that has multiple CPUs. You...

You work as a DBA for a company and you have the responsibility of managing one of its online transaction processing (OLTP) systems. The database encountered performance-related pr...

You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequate...

You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database. On further investigation, you find that at the end of each day the contents...

View the Exhibit and examine a portion of the output obtained from the following query: SQL> SELECT * FROM v$sys_time_model; Select three correct interpretations of the time model...

A batch workload that historically completed in the maintenance window between 10:00 PM and midnight is currently showing poor performance and completing at 2 AM. To help in the di...

You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observat...

Identify two correct statements about the Active Session History (ASH) data. (Choose two.)

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...

You identified poorly performing SQL statements that use bind variables. You want to examine the execution plans for the most recently executed statements for further diagnosis. Wh...

Examine the SQL statement given below: SQL> EXPLAIN PLAN for SELECT department_name,count(*) FROM hr.employees e, hr.departments d WHERE e.department_id=d.department_id GROUP BY d....

You enabled tracing for a session by executing the following command: SQL>SET AUTOTRACE TRACEONLY; You issued a SQL query in the same session. Which two statements are true? (Choos...

Examine the values for the following initialization parameters: STATISTICS_LEVEL = TYPICAL TIMED_STATISTICS = true You are managing an online transaction processing (OLTP) system....

You are working on an online transaction processing (OLTP) system. By day most of the application users perform queries accessing the most recently added or modified rows. The appl...

Which three factors influence the optimizer's behavior while choosing an optimization approach and goal for a SQL statement? (Choose three.)

Examine the initialization parameter values for the instance given below: NAME TYPE VALUE ------------------------------------ ----------- ------------------ optimizer_capture_sql_...

Examine the initialization parameter values for the instance given below: NAME TYPE VALUE ------------------------------------ ----------- ------------------ optimizer_capture_sql_...

The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. Y...

Which statements are true regarding configuring multiple buffer pools in the Database Buffer Cache that is using only the standard block size? (Choose all that apply.)

Smith is a DBA with ABC Corp. He is in the process of tuning the database. He has received various problem statements from the customers. Identify three appropriately defined probl...

You execute the following query to check resource consumption in the current plan used by the database: SQL> SELECT name,active_sessions,requests,queue_length,current_undo_consumpt...

You require that a warning is written to a trace file without impacting the application when the session's Java state exceeds 2GB. Which parameter would you modify?

You use the following query to monitor the Shared Servers: SQL>SELECT maximum_connections "MAX CONN", servers_started "STARTED", servers_terminated "TERMINATED", servers_highwater...

Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements....

Which three statements are true about performance analysis by SQL Performance Analyzer? (Choose three.)

The database application developers are planning to make some major schema changes such as creating new indexes and materialized views. They want to check the net impact of these c...

You upgraded database from the Oracle Database 10g to Oracle Database 11g. To test the performance SQL on the upgraded database. You want to build the before-change performance dat...

Examine the following block that executes a SQL Performance Analyzer task: begin dbms_sqlpa.execute_analysis_task(task_name => 'SPA_SKD4', execution_type => 'TEST EXECUTE', executi...

You plan to collect statistics for an important table in your database to help improved query performance. You are uncertain about the optimizer behavior after the statistics have...

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regula...

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection r...

You observe that suboptimal execution plans for the queries are being generated on a table that previously used less resources. You have collected statistics on these tables two da...

The SALES table in your database undergoes frequent changes. While observing the statistics for the table in the middle of the day, you find a change since the last observation. Bu...

You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified fre...

Which is the correct description of SQL profiling while using SQL Tuning Advisor?

You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can be used by the query subsequently for a better execution plan. After implementin...

You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want to investigate the plans for these SQL statements. Which two methods can you use to pull...

A user in a session executed the following SQL statement to set the optimizer mode: ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS What impact would it have on the goal of the optimiz...

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 associ...

Identify the type of recommendation that can automatically be implemented by the Automatic Tuning Advisor as part of automatic SQL tuning task?

View the Exhibit and examine the statistics of a SQL statement.Which two conclusions can you draw from the statistics? (Choose two.)

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...

Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)

Your system is upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version. After running the SQL Performance Analyzer fo...

The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database instance. The space consumption has significantly increased by the automatically captured plan...

Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. The database supports middle-tier applications using connection pooling for conn...

You are working on an online transaction processing (OLTP) system. The middle-tier applications use connection pooling to connect to the database. Presently, you have a single-node...

You are a DBA in ABC Corp. You are working on a DSS system.. The applications directly connect to the database to perform transactions. The application users also perform direct lo...