[Free] 2018(Jan) EnsurePass Passguide Oracle 1z0-064 Dumps with VCE and PDF 11-20
Oracle Database 12c: Performance Management and Tuning
Question No: 11
Your database supports a DSS workload. In an application, a few complex queries that contain multiple functions and expressions are using materialized views. You notice that some queries are performing poorly because they are not benefiting from query rewrites.
Which three actions would you take to improve the performance of queries? (Choose three.)
Create an SQL Tuning Set (STS) and submit as input to the SQL Access Advisor to generate recommendations about query rewrite and fast refresh for materialized views.
Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to analyze why a query failed to rewrite.
Create an STS and submit as input to the SQL Performance Analyzer to get recommendations about improving the performance of queries.
Use the DBMS_ADVISOR.TUNE_MVIEW procedure to get recommendations about rewriting materialized views.
Use the DBMS_ADVISOR.QUICK_TUNE procedure to analyze queries based on the usage of query rewrite with materialized views.
Question No: 12
Examine the partial PLAN_TABLE output:
Which is the correct sequence of execution? (Choose the best answer.)
A. 3, 2, 1, 4, 0
B. 0, 1, 2, 3, 4, 1
C. 0, 4, 1, 3, 2, 1
D. 3, 2, 4, 1, 0
E. 3, 2, 4, 1, 0, 2
Question No: 13
You are administering a database that supports an OLTP workload. An application regularly creates global temporary tables and a large number of transactions are performed on them. You notice that performance is degraded because of excessive generation of undo due to a large number of transactions on the global temporary tables.
What is the recommended action to improve performance? (Choose the best answer.)
Increase the size of the undo tablespace and enable undo retention guarantee.
Increase the size of the database buffer cache.
Enable temporary undo.
Increase the size of the temporary tablespace or make it autoextensible.
Enable Automatic Segment Space Management (ASSM) for the undo tablespace.
Question No: 14
Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)
Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.
A full table scan can become cheaper than index scans if the database instance has a high enough DB_FILE MULTIBLOCK_READ_COUNT for both OLTP and DSS workloads.
Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the costing of an index probe that is done in conjunction with a nested loop for OLTP workloads.
In DSS workloads where full table scans may run in parallel and bypass the buffer cache, decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of full table scans.
Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the cost of full table scans and can result in the optimizer choosing a full table scan over an index scan for both OLTP and DSS workloads.
Question No: 15
Which two are prerequisites for running the I/O calibration tool? (Choose two.)
The database must be in MOUNT state.
The database should be opened in restricted mode.
For determining latency time, the STATISTICS_LEVEL parameter must be set to TYPICAL or ALL.
The disks to be tested must be configured to use asynchronous I/O for data files.
The database instance must be started using an SPFILE.
Question No: 16
Your database supports an OLTP system.
Examine the parameter values configured in your database:
sga_max_size = 480M sga_target = 480M pga_aggregate_target = 160M
The CUSTOMERS table contains 8,000 rows. The CUST_ID column is the primary key and the COUNTRY_ID column contains only three possible values: 1111, 2222, and 3333.
You execute the commands:
SQLgt; EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’CUSTOMERS’);
PL/SQL procedure successfully completed.
SQLgt; CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);
You then perform a series of INSERT, UPDATE, and DELETE operations on the table. View the Exhibit to examine the query and its execution plan.
Which two options can improve the performance of the query without significantly slowing down the DML operations? (Choose two.)
creating a bitmap index on the COUNTRY_ID column
regathering statistics on the CUSTOMERS table
gathering statistics on the COUNTRY_IDX index
creating a histogram on the COUNTRY_ID column
increasing the size of the PGA
creating an SQL profile
creating a KEEP cache
Question No: 17
Which four objectives are achieved by using Resource Manager to manage multiple concurrent user sessions that are competing for resources? (Choose four.)
distributing available CPU by allocating percentages of CPU time to different users and applications
limiting the degree of parallelism of any operation performed by members of a group of users
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
limiting the number of concurrent sessions for a user
limiting the number of user sessions allowed to be concurrently active within a group of users
limiting the number of parallel executions that can be executed by a user
Question No: 18
Which three statements are true about using Real-Time Database Operations? (Choose three.)
The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.
The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC TUNUNG to use Real-Time Database Operations.
The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.
Real-Time Database Operations can be enabled only at the system level.
Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.
Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.
Question No: 19
Which two statements are true about server-generated alerts? (Choose two.)
They are always logged in the alert log.
They are written to a trace file if the TRACE_ENABLED initialization parameter is set to TRUE.
They are generated only when the STATISTICS_LEVEL initialization parameter is set to ALL.
They can be generated for user-defined metric thresholds.
They appear in the DBA_ALERT_HISTORY view whenever corrective action is taken for an alert.
Question No: 20
Examine the parameters set for a database instance supporting a mixed workload:
The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:
Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)
Use a parallel hint in the queries.
Increase the number of DBWn processes.
Increase the value of the SORT_AREA_SIZE initialization parameter.
Increase the size of the temporary tablespace or add a new temporary tablespace.
Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.
Increase the size of the large pool.
100% Free Download!
–Download Free Demo:1z0-064 Demo PDF
100% Pass Guaranteed!
–Download 2018 EnsurePass 1z0-064 Full Exam PDF and VCE
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|