1Z0-054 무료 덤프문제 온라인 액세스

Examine the initialization parameter values for the instance given below: NAME TYPE VALUE
optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.1.0.6 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS db_file_multiblock_read_count integer 64
You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index unique scan (view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. The cost for a full table scan is more than that for an index unique scan.

Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)

• A.The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.
• B.The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a low value.
• C.The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a low value.
• D.The statistics for the table and all the indexes associated with the table are not current.

View the Exhibit and examine the content of the V$MEMORY_RESIZE_OPS view.Which two conclusions can you draw? (Choose two.)

• A.The memory is transferred in IMMEDIATE mode only if there are entirely empty granules available in other components.
• B.The Default Buffer Cache grew in DEFERRED mode to avoid an out-of-memory error.
• C.The Shared Pool grew in IMMEDIATE mode to avoid an out-of-memory error.
• D.Automatic Memory Management (AMM) is enabled.
• E.Automatic Shared Memory Management (ASMM) is enabled but not AMM.

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 better execution plan. View the Exhibit. But you want certain user sessions not to use this SQL profile for their queries. How would you implement this?

• A.Set the OPTIMIZER_USE_PENDING_STATISTICS to TRUE the desired sessions.
• B.Use database resource manager to preUse the resource management feature in profiles of these users to prevent the use of the SQL profile.
• C.Use database resource manager to prevent the use of the SQL profile by these user sessions.
• D.Alter the SQL profile to change the category of the SQL profile.

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 database. The company plans to migrate the database to a RAC environment. Before you move to a RAC environment, you want to test the performance of the SQL statements and the peak workload on
the new environment. To accomplish the Database Capture for replay, you identified the peak workload period on the existing system and started the Database Capture. Which client requests to the database can be captured as part of the workload capture? (Choose all that apply.)

• A.all DDL statements having bind variables
• B.flashback query
• C.direct path load of data from external files
• D.logging in and logging out of sessions
• E.distributed transactions

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 statistics. (Choose three.)

• A.SQL execute elapsed time includes the time spent in performing fetches of query results.
• B.DB time includes the wait time of all the nonidle and idle user sessions.
• C.DB CPU includes the CPU time spent on database user-level calls and background CPU time.
• D.SQL execute elapsed time includes components of the hard parse elapsed time like bind elapsed time.
• E.DB time includes the connection management call elapsed time excluding the background process time.