Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 91:

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

  A. Cursor cache
  B. Stored outline
  C. SQL Tuning Set
  D. Automatic Workload Repository (AWR) snapshots
  A. Cursor cache
  C. SQL Tuning Set

• Question 92:

  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 been collected because currently there are no statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collected statistics. How would you prevent the optimizer from using the newly collected statistics for other sessions immediately after statistics collection?

  A. Set the PUBLISH preference to FALSE for the table.
  B. Set the NO_VALIDATE preference to TRUE for the table.
  C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
  D. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE for the session.
  E. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE for the session.
  A. Set the PUBLISH preference to FALSE for the table.

• Question 93:

  You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report are shown in the Exhibits. View the Exhibit named DBTIME and note % DB Time.

  

  View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

  

  View the Exhibit named EFFICIENCY and examine the various percentages shown.

  

  Identify the option that has the correct answers for the questions given below:

  1) Which factor indicates the main problem?

  2) What is the main problem?

  3) What solution would you recommend?

  A. 1) The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time. 2) The CPU is very slow. 3) Increase the number of processors.
  B. 1) hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit. 2) This is due to inadequate Database Buffer Cache. 3) Increase the size of database buffer cache.
  C. 1) sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit. 2) There are too many soft parses. 3) Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.
  D. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that very few statements are found in the cache. 2) There are too many hard parses. 3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.
  D. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that very few statements are found in the cache. 2) There are too many hard parses. 3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.

• Question 94:

  You are hired by ABC Pvt Ltd., and assigned the task of managing one of its development databases. The company has recently upgraded this database to Oracle Database 11g from Oracle Database 10g. You noticed that SGA is undersized as shown in Exhibit 1.

  

  To investigate further, you checked the related parameters as shown in Exhibit 2.

  

  You executed the following command to solve the problem: ALTER SYSTEM SET MEMORY_TARGET=800M;

  What would happen?

  A. The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.
  B. It implements AMM by setting MEMORY_TARGET and MEMORY_MAX_TARGET parameters to 800M.
  C. It implements Automatic Memory Management (AMM) by setting MEMORY_TARGET to 768M and the MEMORY_MAX_TARGET parameter to 800M.
  D. The implementation of AMM fails because the MEMORY_TARGET parameter cannot be set to a value less than the sum of the current PGA_AGGRAGATE_TARGET and SGA_MAX_SIZE values.
  A. The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.

• Question 95:

  You are working on a database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

  

  You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. The following are the related parameter settings: SQL> show parameter sga name TYPE VALUE ------------------------------ --------------- ----------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 300M sga_target big integer 0 SQL> show parameter target name TYPE VALUE ------------------------------ --------------- ----------------- ..... fast_start_mttr_target integer 0 memory_max_target big integer 0 memory_target big integer 0 pga_aggregate_target big integer 100M sga_target big integer 0

  You want to balance the memory between the System Global Area (SGA) components within the SGA depending on the workloads. Which two options would solve this problem? (Choose two.)

  A. Set the SGA_TARGET parameter to 300M.
  B. Set the SGA_MAX_SIZE parameter to 400M.
  C. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 400M.
  D. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 100M.
  E. Set the PGA_AGGREGATE_TARGET parameter to 200M and the SGA_MAX_SIZE parameter to 400M.
  A. Set the SGA_TARGET parameter to 300M.
  C. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 400M.

• Question 96:

  You observed in your database that the queries formerly executed in 5 seconds now take longer time.View the Exhibit and analyze the output of the query that you executed to find the problem.Which is the primary concern?

  

  A. the number of disk sorts
  B. the number of full table scans
  C. the number of chained and migrated rows
  D. the number of rows fetched based on rowid
  C. the number of chained and migrated rows

• Question 97:

  View the Exhibit and examine portions of two ASH reports generated for the same time period. The second report was generated after the ASH data is flushed to disk.Why is the sample count reduced in the second report?

  A. because the AWR is not able to store all the samples.
  B. because the samples have been filtered from ASH buffer.
  C. because a portion of the ASH rolling buffer has been overwritten.
  D. because the shared pool size is not enough to hold ASH information.
  B. because the samples have been filtered from ASH buffer.

• Question 98:

  You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given below: SQL> SELECT table_name ,blocks, pct_used, pct_free, num_rows 2 FROM all_tables 3* WHERE table_name = 'CUST'; TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS ---------- ---------- ---------- ---------- ---------- CUST 13768 80 111060 What would you recommend to reduce the I/O?

  

  A. reorganizing the table to use fewer blocks
  B. rebuilding the index on the CUST_FIRST_NAME column
  C. increasing the value for the PCTFREE attribute for the CUST table
  D. increasing the value for the PGA_AGGREGATE_TARGET initialization parameter
  A. reorganizing the table to use fewer blocks

• Question 99:

  In your organization, there are two key databases. One of them is used primarily for an online transaction processing (OLTP) application and is directly accessed by the users in a client/server configuration. The application must process an average of 4,000 small transactions per day. The users enter frequent queries on whose results they depend for completing their transactions. One of the SLA requirements is that any query or transaction processing in the OLTP system should not exceed 15 seconds. The second database supports a decision support system (DSS) as well as batch report programs. It is accessed by client-side users, who submit ad hoc queries through SQL*Plus and by a browser- based,third party application that provides pooled connections for a large user base. The queries vary: some fetch only a few records, whereas others fetch and sort hundreds of records from several tables. As per the SLA specification the average turnaround time for a query on the DSS returning 10000 records should be 24 hours. For both the systems, the number of users is not consistent and may vary from time to time.The management wants to ensure maximum resource usage is allocated for the OLTP processing. Which two considerations should you give priority to when tuning these databases? (Choose two.)

  A. Minimize parallelism for the OLTP systems.
  B. Maximize throughput for batch processing.
  C. Minimize the response time for OLTP users.
  D. Maximize parallelism for the decision support systems.
  E. Minimize response time for the decision support systems.
  B. Maximize throughput for batch processing.
  C. Minimize the response time for OLTP users.

• Question 100:

  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 implementing the profile, you altered the profile to include it in a

  category as follows:

  SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name=>

  'SYS_SQLPROF_0146ced728214000, attribute_name => 'CATEGORY', value => 'DEV2'). But the performance has not improved for the user session. The profile is enabled and on investigating the query plan, you find that the query from

  the user session does not use the SQL profile. View the Exhibit and examine the parameters for the user session. What could be the reason for this?

  

  A. The CONTROL_MANAGEMENT_PACK_ACCESS parameter is not set to DEV2.
  B. The OPTIMIZER_MODE parameter is set to ALL_ROWS for the user session.
  C. The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session.
  D. The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE for the user session.
  C. The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session.