1z0-084

1z0-084 practice questions, page 3 of 5, with answer review and explanations.

Question 11

Multiple sessions are inserting data concurrently into a table that has an LOB column. At some point in time, one of the sessions cannot find available space in the LOB segment and needs to allocate a new extent. Which wait event will be raised in the other sessions that need space in the LOB column?

  1. eng: SQ - contention
  2. eng: TM - contention
  3. eng: HW - contention
  4. eng: TX - allocate ITL entry

Question 12

You manage a 19c database with default optimizer settings. This statement is used extensively as subquery in the application queries: SELECT city_id FROM sh2.sales WHERE city_id=:B1 You notice the performance of these queries is often poor and, therefore, execute: SELECT city_id,COUNT(*) FROM sh2.sales GROUP BY city_id; Examine the results: There is no index on the CITY_ID column. Which two options improve the performance? (Choose two.)

  1. Generate frequency histograms on the CITY_ID column.
  2. Create an index on the CITY_ID column.
  3. Use a SQL Profile to enforce the appropriate plan.
  4. Force the subquery to use dynamic sampling.
  5. Activate the adaptive plans.

Question 13

Database performance has degraded recently. INDEX RANGE SCAN operations on index IX_SALES_TIME_ID are slower due to an increase in buffer gets on SALES table blocks. Examine these attributes displayed by querying DBA_TABLES: Now, examine these attributes displayed by querying DBA_INDEXES: Which action will reduce the excessive buffer gets?

  1. Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.
  2. Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.
  3. Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.
  4. Partition index IX_SALES_TIME_ID using hash partitioning.

Question 14

You must produce a consolidated formatted trace file by combining all trace files generated by all clients for a single service. Which combination of utilities does this?

  1. Trace Analyzer and Trcsess
  2. Trcsess and TKPROF
  3. Autotrace and TKPROF
  4. TKPROF and Trace Analyzer

Question 15

SGA_TARGET and PGA_AGGREGATE_TARGET are configured to nonzero values. MEMORY_TARGET is then set to a nonzero value but MEMORY_MAX_TARGET is not set. Which two statements are true? (Choose two.)

  1. SGA_TARGET and PGA_AGGREGATE_TARGET will define lower size limits for the SGA and PGA, respectively.
  2. MEMORY_MAX_TARGET will default to either the sum of SGA_TARGET and PGA_AGGREGATE_TARGET or to MEMORY_TARGET, whichever is smaller.
  3. PGA_AGGREGATE_TARGET will be ignored but SGA_TARGET will define the lower size limit for SGA size.
  4. MEMORY_MAX_TARGET will default to zero.
  5. SGA_TARGET will be ignored but PGA_AGGREGATE_TARGET will define the lower size limit for PGA size.
  6. MEMORY_MAX_TARGET will default to either the sum of SGA_TARGET and PGA_AGGREGATE_TARGET or to MEMORY_TARGET, whichever is greater.
  7. MEMORY_MAX_TARGET will default to MEMORY_TARGET.