Oracle 1Z0-060 Online Practice Questions and Exam Preparation

Oracle 1Z0-060 Online Questions & Answers

• Question 21:

  You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema.

  Examine the following steps:

  1.

  Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (`SH', `CUSTOMERS') FROM dual statement.

  2.

  Execute the DBMS_STATS.SEED_COL_USAGE (null, `SH', 500) procedure.

  3.

  Execute the required queries on the CUSTOMERS table.

  4.

  Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (`SH', `CUSTOMERS') FROM dual statement.

  Identify the correct sequence of steps.

  A. 3, 2, 1, 4
  B. 2, 3, 4, 1
  C. 4, 1, 3, 2
  D. 3, 2, 4, 1
  B. 2, 3, 4, 1

  ---

  Step 1 (2). Seed column usage Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload. Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for some of your longer running queries to ensure column group information is recorded for these queries. Step 3. (1) Create the column groups At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage information captured during the monitoring window. You simply have to call the DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema name and the table name. From then on, statistics will be maintained for each column group whenever statistics are gathered on the table.

  Note:

  *

  DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object.

  *

  The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same table. While the optimizer has traditionally analyzed the distribution of values within a column, he does not collect value-

  based relationships between columns.

  *

  Creating extended statisticsHere are the steps to create extended statistics for related table columns withdbms_stats.created_extended_stats:

  1 - The first step is to create column histograms for the related columns.2 ?Next, we run dbms_stats.create_extended_stats to relate the columns together. Unlike a traditional procedure that is invoked via an execute ("exec") statement,

  Oracle extended statistics are created via a select statement.

• Question 22:

  Which two statements are true concerning the Resource Manager plans for individual pluggable databases (PDB plans) in a multitenant container database (CDB)? (Choose two.)

  A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB.
  B. In a PDB plan, subplans may be used with up to eight consumer groups.
  C. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups across all PDBs in the CDB.
  D. If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically calculated.
  E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.
  A. If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB.
  E. If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.

  ---

  A: Setting a PDB resource plan is optional. If not specified, all sessions within the PDB are treated equally.

  * In a non-CDB database, workloads within a database are managed with resource plans. In a PDB, workloads are also managed with resource plans, also called PDB resource plans. The functionality is similar except for the following differences: / Non-CDB Database Multi-level resource plans Up to 32 consumer groups Subplans / PDB Database Single-level resource plans only Up to 8 consumer groups (not B) No subplans

• Question 23:

  Examine these parameters for a database instance:

  

  Which two statement are true? (Choose two.)

  A. SGA can grow automatically beyond 4480 MB.
  B. The autotuned components of SGA are dynamically allocated.
  C. Only Automatic Shared Memory management (ASMM) is enabled.
  D. Automatic Memory Management (AMM) and ASMM are enabled.
  E. PGA can grow automatically beyond 1000 MB.
  B. The autotuned components of SGA are dynamically allocated.
  C. Only Automatic Shared Memory management (ASMM) is enabled.

• Question 24:

  Which two are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled? (Choose two.)

  A. reduced CPU utilization
  B. reduced physical I/O
  C. improved Serial Execution performance
  D. reduced logical I/O
  E. reduced virtual memory utilization
  A. reduced CPU utilization
  E. reduced virtual memory utilization

• Question 25:

  Which two statements are true when row-archival management is enabled? (Choose two.)

  A. The ORA_ARCHIVE_STATE column is updated manually or by a program that can reference activity tracking columns, to indicate that a row is no longer considered active.
  B. Visibility of the ORA_ARCHIVE_STATE column is controlled by the ROW ARCHIVAL VISIBILITY session parameter.
  C. The ROW ARCHIVAL VISIBILITY session parameter defaults to all rows.
  D. The ORA_ARCHIVE_STATE column is visible if it is referenced in the select list of a query.
  E. The ORA_ARCHIVE_STATE column is updated automatically by the database based on activity tracking columns, to indicate that a row is no longer considered active.
  A. The ORA_ARCHIVE_STATE column is updated manually or by a program that can reference activity tracking columns, to indicate that a row is no longer considered active.
  D. The ORA_ARCHIVE_STATE column is visible if it is referenced in the select list of a query.

• Question 26:

  Examine the current values for the following parameters in your database instance:

  SGA_MAX_SIZE = 1024M SGA_TARGET = 700M DB_8K_CACHE_SIZE = 124M LOG_BUFFER = 200M

  You issue the following command to increase the value of DB_8K_CACHE_SIZE:

  SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;

  Which statement is true?

  A. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
  B. It succeeds only if memory is available from the autotuned components if SGA.
  C. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET.
  D. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.
  B. It succeeds only if memory is available from the autotuned components if SGA.

• Question 27:

  To enable the Database Smart Flash Cache, you configure the following parameters:

  DB_FLASH_CACHE_FILE = `/dev/flash_device_1' , `/dev/flash_device_2' DB_FLASH_CACHE_SIZE=64G

  What is the result when you start up the database instance?

  A. It results in an error because these parameter settings are invalid.
  B. One 64G flash cache file will be used.
  C. Two 64G flash cache files will be used.
  D. Two 32G flash cache files will be used.
  A. It results in an error because these parameter settings are invalid.

• Question 28:

  Which two statements are true when the listener handles connection requests to an Oracle 12c database instance with multithreaded architecture enabled in UNIX?

  A. The local listener may pass the request to an existing process which in turn will create a thread.
  B. Each multithreaded Oracle process has an SCMN thread.
  C. Each Oracle process runs an SCMN thread.
  D. Thread creation must be routed through a dispatcher process.
  A. The local listener may pass the request to an existing process which in turn will create a thread.
  D. Thread creation must be routed through a dispatcher process.

• Question 29:

  Examine the parameters for your database instance:

  

  You generate an execution plan for this query and the plan table shows a nested loop join in the plan/

  

  After query execution, you see that a hash join was performed: What explain this optimizer behavior?

  A. It used SQL plan baselines for the query.
  B. It chose different plans because extended statistics were created on the filter columns.
  C. It chose different plans because automatic dynamic sampling was enabled.
  D. It used an adaptive execution plan.
  C. It chose different plans because automatic dynamic sampling was enabled.

• Question 30:

  You want to flash back a test database by five hours.

  You issue this command:

  SQL > FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 5/24);

  Which two statements are true about this flashback scenario?

  A. The database must have multiplexed redo logs for the flashback to succeed.
  B. The database must be MOUNTED for the flashback to succeed.
  C. The database must use block change tracking for the flashback to succeed.
  D. The database must be opened in restricted mode for the flashback to succeed.
  E. The database must be opened with the RESETLOGS option after the flashback is complete.
  F. The database must be opened in read-only mode to check if the database has been flashed back to the correct SCN.
  B. The database must be MOUNTED for the flashback to succeed.
  E. The database must be opened with the RESETLOGS option after the flashback is complete.

  ---

  http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmflash.htm#BRADV80055