1Z0-064 Online Practice Questions and Answers

Examine the parameters set for your database instance: You upgrade your database to Oracle Database 12c. The database supports a mixed workload and works with different workloads at different times. You notice in an ADDM report that the shared pool is inadequately sized. You resize the shared pool by decreasing the sizes of other pools, which results in inadequate sizes for other pools. You want to automate the sizing of SGA components.

Which two actions should you perform? (Choose two.)

A. Set the SGA_TARGET parameter equal to SGA_MAX_SIZE.

B. Set the SGA_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

C. Set the MEMORY_MAX_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

D. Set DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE to their minimum required values.

E. Set the PGA_AGGREGATE_TARGET parameter to 0 and the SGA_TARGET parameter to 1.5G.

Which two statements are true about DB time in V$SYS_TIME_MODEL? (Choose two.)

A. DB time cannot exceed the total elapsed time (walk clock time) since the database instance started.

B. DB time cannot exceed the maximum number of concurrent sessions multiplied by the actual elapsed time for each session.

C. DB time includes the time spent on client processes and background processes.

D. Reducing DB time allows a database instance to support more user requests by using the same resources.

E. DB time is always greater than or equal to the DB CPU time.

Examine the parameters set for your database instance: You notice that for one particular SQL statement, the optimizer generates a new better plan than the plans in the SQL Plan Management Base.

Which action is taken by the optimizer? (Choose the best answer.)

A. It adds the newly generated plan as an accepted but non-fixed plan.

B. It adds the newly generated plan as enabled and accepted.

C. It adds the newly generated plan as enabled but not accepted.

D. It adds the newly generated plan as a fixed plan, which will be used each time the SQL statement is executed.

Your database supports a mixed workload. In an application, multiple complex queries with functions and expressions are executing. You want to analyze the queries that are currently cached in the library cache to receive recommendations about the usage of indexes and materialized views.

What should you do to achieve this? (Choose the best answer.)

A. Create an STS for the queries cached in the library cache and submit it as an input to SQL Tuning Advisor.

B. Create an STS for the queries cached in the library cache and submit it as an input to SQL Access Advisor.

C. Capture the workload in an STS and submit to SQL Tuning Advisor for recommendations.

D. Create an STS for the queries cached in the library cache and submit it as an input to SQL Performance Analyzer.

You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.

Which four statements would be true if you use bind variables for these queries? (Choose four.)

A. Mutex contention in the library cache will be reduced.

B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.

C. Hard parses will be reduced for the queries.

D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.

E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.

F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.

You execute this query twice in a session:

Then you query V$SQL_SHARED_CURSOR for details about child cursors as shown.

Which two statements are true? (Choose two.)

A. No statistics were collected during the first execution of the query.

B. A subsequent execution of the query in this session is likely to undergo a soft parse.

C. The second execution of the query was hard parsed because the estimated cardinality was inaccurate.

D. A subsequent execution of the query in this session will undergo a hard parse.

E. The second execution of the query was hard parsed because extended statistics were collected after the first execution of the query.

Examine the command to change a parameter value from the default to 50:

SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 50;

What is the effect of changing the value of the parameter? (Choose the best answer.)

A. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.

B. It influences the optimizer to use bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.

C. It influences the optimizer to always use fast full index scans as the estimated cost of using an index is reduced.

D. It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index is reduced.

Which four objectives are achieved by using Resource Manager to manage multiple concurrent user sessions that are competing for resources? (Choose four.)

A. distributing available CPU by allocating percentages of CPU time to different users and applications

B. limiting the degree of parallelism of any operation performed by members of a group of users

C. limiting queries based on resource consumption of runaway sessions or calls that consume more than a specified amount of CPU, physical I/O, logical I/O, or elapsed time

D. limiting the number of concurrent sessions for a user

E. limiting the number of user sessions allowed to be concurrently active within a group of users

F. limiting the number of parallel executions that can be executed by a user

In your database, supporting an OLTP workload, the SALES table is range-partitioned on the SALES_DATE column. A new partition is created every quarter and is frequently used for updates and queries. You recently noticed degraded performance of queries on a new partition due to stale statistics. A local partitioned index exists on the SALES_DATE column.

What would you recommend to improve performance?

A. dropping the local partitioned index and creating a global partitioned index on the SALES_DATE column

B. setting the INCREMENTAL preference to TRUE and the NO_INVALIDATE preference to TRUE for the partitioned table while gathering statistics

C. setting the INCREMENTAL preference to TRUE and the CASCADE preference to FALSE for the partitioned table while gathering statistics

D. setting the INCREMENTAL preference to TRUE and the GRANULARITY preference to AUTO for the partitioned table while gathering statistics

You want to set a priority for the workloads generated by the applications in your database instance such that report-generating tasks are assigned a lower priority.

How would you do this?

A. by using job classes with the Resource Manager

B. by creating an active session pool using the Resource Manager

C. by using services that are assigned different priorities with the Resource Manager

D. by using services for the applications and creating job classes associated with each service

The CUSTOMERS table has 55,500 rows and 620 distinct values in the CUST_CITY_ID column. The number of popular values is 54 and less than 99% of the rows contain popular values.

Which type of histogram should you create to accurately determine the cardinality estimate on the CUST_CITY_ID column?

A. high-frequency histogram

B. height-balanced histogram

C. hybrid histogram

D. frequency histogram

Your database supports an OLTP workload where applications primarily perform small random I/Os. You notice an increase in the I/O requests queued up against the storage, and an increase in the wait time in queue. The database uses file system storage.

What would you recommend to decrease the wait time of I/O requests?

A. Decrease the value of the DBWR_IO_SLAVES parameter.

B. Increase the size of the buffer cache.

C. Increase the value of the DB_WRITER_PROCESSES parameter.

D. Suggest storage admin to add more disks (spindles) to storage and stripe data across disks.

In which three scenarios would you recommend the use of the SQL Performance Analyzer?

A. to identify SQL statements that require SQL profiles

B. to analyze the impact of network and interconnect changes on database workload

C. to identify SQL statements whose performance may have regressed due to a hardware upgrade

D. to analyze the impact of new indexes and materialized views on the SQL statements executed by an application

E. to identify SQL statements in an application whose performance may have regressed due to migration from Oracle Database 11g to 12c

F. to analyze the impact of using bind variables for syntactically similar SQL statements

Queries on the SALES table frequently use the PROD_ID and COST_ID columns together in the WHERE clause. Examine the command:

Which three statements are true about executing this command?

A. It creates extended statistics for the column group (PROD_ID and CUST_ID).

B. It improves the cardinality estimates when both the PROD_ID and CUST_ID columns are used in the WHERE clause.

C. It creates a virtual column for the column group (PROD_ID and CUST_ID).

D. It improves the selectivity estimates of a single predicate in the WHERE clause.

E. It gathers individual column statistics for the PROD_ID and CUST_ID columns.

Which two statements are true about wait events?

A. A single resource wait event may be recorded as multiple waits, depending on the number of session timeouts during that wait.

B. A wait event can be defined in multiple wait classes.

C. Wait event statistics are cumulatively collected only at the instance level.

D. Wait events for an instance include statistics for both background and foreground processes.

E. Wait event countries are incremented by the server process that waits.