Labour Day Special 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: exams65

Oracle Database 19c Performance and Tuning Management Question and Answers

Oracle Database 19c Performance and Tuning Management

Last Update May 6, 2024
Total Questions : 55

We are offering FREE 1z0-084 Oracle exam questions. All you do is to just go and sign up. Give your details, prepare 1z0-084 free exam questions and then go for complete pool of Oracle Database 19c Performance and Tuning Management test questions that will help you more.

1z0-084 pdf

1z0-084 PDF

$35  $99.99
 Add to Cart
1z0-084 Engine

1z0-084 Testing Engine

$42  $119.99
 Add to Cart
1z0-084 PDF + Engine

1z0-084 PDF + Testing Engine

$56  $159.99
 Add to Cart
Questions 1

Which three statements are true about tuning dimensions and details of v$sys_time_model and DB time?

Options:

A.  

Statspack cannot account for high CPU time when CPU TIME is a Top 10 event in DB time. When CPU time is high, SQL tuning may improve performance.

B.  

Systems in which CPU time is dominant need more tuning that those in which WAIT TIME is dominant.

C.  

The proportion of WAIT TIME to CPU TIME always increases with increased system load.

D.  

When WAIT TIME is high, instance tuning may improve performance.

E.  

Parse Time Elapsed accounts for successful soft and hard parse operations only.

F.  

DB Time accounts for all time used by background processes and user sessions.

Discussion 0
Questions 2

Which three types of statistics are captured by statspack with snap level 6?

Options:

A.  

Parent and child latches

B.  

Parent and child latches

C.  

Optimizer execution plans

D.  

Plan usage data

E.  

Enqueue statistics

F.  

Segment-level statistics

Discussion 0
Questions 3

SGA_TARGET and PGA_AGGREGATE_TARGET are configured to nonzero values.

MEMORY_target is then set to a nonzero value but memory_MAX_TARG£T is not set.

Which two statements are true?

A)

B)

C)

D)

E)

F)

G)

Options:

A.  

Option

B.  

Option

C.  

Option

D.  

Option

E.  

Option

F.  

Option

G.  

Option

Discussion 0
Questions 4

During which application lifecycle phase do you take baselines?

Options:

A.  

Testing

B.  

Migration or upgrade

C.  

Design and development

D.  

Deployment

E.  

Production

Discussion 0
Questions 5

For which two actions can SQL Performance Analyzer be used to assess the impact of changes to SQL

performance?

Options:

A.  

storage, network, and interconnect changes

B.  

operating system upgrades

C.  

changes to database initialization parameters

D.  

database consolidation for pluggable databases (PDBs)

E.  

operating system and hardware migrations

Discussion 0
Questions 6

Which three statements are true about using the in Memory (IM) column store?

Options:

A.  

It does not improve performance for queries using cached results of function evaluations on columns from the same table.

B.  

It does not improve performance for queries that use join groups on columns from different tables.

C.  

It can improve OLTP workload performance by avoiding the use of indexes.

D.  

It does not improve performance for queries using user-defined virtual column results.

E.  

It does not require all database data to fit in memory to improve query performance.

F.  

It improves performance for queries joining several tables using bloom filter joins.

Discussion 0
Questions 7

Examine this code block, which executes successfully:

DBMS_SERVER_ALERT. SET_THRESHOLD (

DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT. OPERATOR_GE, '8000',

DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'inst1',

DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.example.com') ;

What will happen?

Options:

A.  

A warning alert will be issued when CPU time exceeds 1 minute for each user call.

B.  

A critical alert will be issued when CPU time exceeds 10000 microseconds for each user call.

C.  

A warning alert will be issued only when CPU time exceeds 10000 microseconds for each user call.

D.  

A critical alert will be issued when CPU time exceeds 2 minutes for each user call.

Discussion 0
Questions 8

Which application lifecycle phase could be managed reactively?

Options:

A.  

Design and development

B.  

Upgrade or migration

C.  

Testing

D.  

Production

E.  

Deployment

Discussion 0
Questions 9

Examine these statements and output:

What parameter change activates the generation and use of SQL Plan Directives7

Options:

A.  

optimizer_features_enable=12.2.0.1

B.  

optimizer_capture_sql_plan_baselines_TRUE

C.  

optimizer_dynamic_sampling=11

D.  

optimizer_adaptive_plans=TRUE

E.  

optimizer_adaptive_statistics = TRUE

Discussion 0
Questions 10

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?

Options:

A.  

Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.

B.  

Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.

C.  

Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.

D.  

Partition index IX_SALES_TIME_ID using hash partitioning.

Discussion 0
Questions 11

18. The application provider has given full indications regarding the procedure to collect statistics.

To reduce the space used in the SYSAUX tablespace, you want to prevent the optimizer statistics Advisor from running.

Which method will allow you to do this?

Options:

A.  

Set the parameter OPTIMIZER_ADAPTIVE_STATISTICS to FALSE.

B.  

Use DBMS_AUTO_TASK_ADMIN. DISABLE to disable the AUTO_STATS_ADVISOR_TASK task.

C.  

Set the AUTO_STATS_ADVISOR_TASK global statistics preference to FALSE.

D.  

Use DBMS STATS.DROP ADVISOR TASK to drop the AUTO_STATS_ADVISOR_TASK task.

Discussion 0
Questions 12

A database instance is suffering poor I/O performance on two frequently accessed large tables.

No Big Table caching occurs in the database.

Examine these parameter settings:

Which are two actions either one of which will allow Big Table caching to occur?

Options:

A.  

Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 50

B.  

Setting DB_KEEP_CACHE_SIZE to at least 50M

C.  

Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 25

D.  

Increasing DB_CACHESIZE to 1 G

E.  

Setting PARALLEL_DEGREE_POLICYADAPTIVE

F.  

Setting PARALLEL_DEGREE_POLICYAUTO

Discussion 0
Questions 13

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?

Options:

A.  

enq: SQ - contention

B.  

enq: TM - contention

C.  

enq: HW - contention

D.  

enq: TX - allocate ITL entry

Discussion 0
Questions 14

You use SQL Tuning Advisor to tune a given SQL statement.

The analysis eventually results in the implementation of a SQL Profile.

You then generate the new SQL Profile plan and enforce it using a SQL PlanBaselinebut forget to disable the SQLProfile and a few days later you find out that the SQL Profile is generating a new execution plan.

Which two statements are true?

Options:

A.  

The existence of two concurrent plan stability methods generates a child cursor for every execution.

B.  

The SQL Profiles as well as SQL Plan Baseline are implemented using hints, so they both generate the same plan.

C.  

The execution plan is the one enforced by the SQL Profile.

D.  

The execution plan is the one enforced by the SQL Plan Baseline.

E.  

The SQL Plan Baseline must be accepted in order to be used for the execution plan.

F.  

The conflict between the two plan stability methods results in an error.

Discussion 0
Questions 15

Which two types of performance problems are reported by ADDM for PDBS?

Options:

A.  

I/O capacity limits

B.  

Excessive checkpoint writes

C.  

SGA sizing issues

D.  

Top SQL statements

E.  

User I/O waits

Discussion 0
Questions 16

What are the least elevated values of statistics_level and C0NTR0LJ4ANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?

Options:

A.  

STATISTICS_LEVEL=ALL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING

B.  

STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC

C.  

STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS-DIAGOSTIC*TUNING

D.  

STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC

Discussion 0