Want to know features? Want to lear more about experience? Study . Gat a success with an absolute guarantee to pass Oracle 1Z0-064 (Oracle Database 12c: Performance Management and Tuning) test on your first attempt.
Free 1Z0-064 Demo Online For Microsoft Certifitcation:
NEW QUESTION 1
You want to capture AWR data to monitor performance variation every Monday between 9:00 AM and 12:00 PM for three months and automatically remove the older AWR data every fortnight.
How would you achieve this? (Choose the best answer.)
- A. Create AWR baselines.
- B. Create SQL plan baselines.
- C. Create repeating baseline templates.
- D. Create database services and make sure that user connections use them to connect to the database instance.
- E. Create a single baseline template.
NEW QUESTION 2
You are administering a database that supports a mixed workload. You upgrade your database from Oracle Database 11g to 12c and after the upgrade, users complain about degraded performance of some queries. The SQL plan baselines imported from the previous version are present for the queries and are loaded to the SQL Management Base as accepted plans. On further investigation, you find that better plans are generated but not used by the optimizer.
Examine the parameters set for the instance:
Which three tasks would you perform to improve the performance of these queries? (Choose three.)
- A. Gather statistics for the objects used in the queries.
- B. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.
- C. Create an SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.
- D. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.
- E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
- F. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the acceptedplans as fixed plans.
NEW QUESTION 3
You observe that queries are performing poorly on the SALES_RECORDS table in your database. On investigation, you find that at the end of each day the contents of the SALES_RECORDS table are moved to the SALES_HISTORY table. The delete operations cause the table to be sparsely populated.
The SALES_RECORDS table is created in a tablespace using Automatic Segment Space Management (ASSM) and row movement is enabled. The table must be accessible 24x7.
Which two tasks would you recommend to improve the performance? (Choose two.)
- A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table.
- B. Shrink the SALES_RECORDS table by using the ALTER TABLE…SHRINK SPACE command.
- C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE…MOVE command.
- D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE…DEALLOCATE UNUSED command.
- E. Move the SALES_RECORDS table to a tablespace by using manual segment space management.
- F. Reorganize the SALES_RECORDS table online by using the DBMS_REDEFINITION package.
NEW QUESTION 4
In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.
Which two methods achieve this? (Choose two.)
- A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
- B. using SQL plan directives to generate an optimal plan
- C. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true
- D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns
NEW QUESTION 5
Which two actions should you take to monitor the throughput generated by the modules of an application? (Choose two.)
- A. Use the Resource Manager.
- B. Enable SQL Trace at the session level.
- C. Create a service.
- D. Use a dedicated server configuration.
- E. Use the DBMS_APPLICATION_INFO package to define the current module and action so that they appear in V$SESSION.
NEW QUESTION 6
Examine the output of the query executed to diagnose the reason for performance degradation of queries:
Which three factors will you investigate further to identify the cause of the performance degradation? (Choose three.)
- A. Check the number of disk sorts.
- B. Check for the causes of the full table scans.
- C. Check the number of chained or migrated rows.
NEW QUESTION 7
Examine the partial TKPROF output for an SQL statement:
Which two inferences can definitely be made from this output? (Choose two.)
- A. Array fetch operations were not performed for this query.
- B. No hard parse was performed for this query.
- C. The number of logical I/Os is almost equal to the number of physical I/Os.
- D. Another transaction held a shared lock on the table, thereby causing a significant delay.
NEW QUESTION 8
A senior DBA asks you to decrease the values of the connect_time_scale and think_time_scale replay processing parameters to 50 to preprocess the workload for replay.
What three could be reasons for this change? (Choose three.)
- A. to reduce the elapsed time between two successive user calls from a session.
- B. to decrease the number of concurrent users during replay
- C. to increase the number of concurrent users during replay
- D. to reduce the time of replay
- E. to decrease the wait for a query, caused by noncommitted transactions
NEW QUESTION 9
Which two statements are true about ADDM? (Choose two.)
- A. It analyzes the performance of a database instance based on the time period covered by the most recent AWR snapshot, and generates recommendations based on hard-coded criteria.
- B. It can analyze performance issues that occurred in past events provided they fall within the AWR retention period.
- C. ADDM resource utilization and cost of analysis depends on the actual load on the database and the number of performance problems analyzed.
- D. It first identifies the performance symptoms, and then refines them to reach the root cause with the singular aim of reducing the DB CPU metric.
- E. It documents only those components and wait classes that are significantly impacting the performance of the database.
NEW QUESTION 10
Which two result in the latest fragmentation within segments and the least external fragmentation within tablespaces? (Choose two.)
- A. automatic segment space managed segments
- B. locally managed tablespaces with uniform extent size
- C. freelist managed segments with one freelist
- D. dictionary managed tablespace
- E. locally managed tablespaces that were converted from dictionary managed tablespaces
- F. freelist managed segments with multiple freelist
Explanation: Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm
NEW QUESTION 11
You have been asked to assess if using column store compression (previously known as hybrid columnar compression or HCC) would help improve the performance of queries on some large tables.
Which three aspects should you consider before you choose this compression method? (Choose three.)
- A. Check whether direct path load operations are used to insert rows in the table.
- B. Check whether the table is frequently queried using full table scans as column store compression only minimizes I/O during full table scans.
- C. Check whether the table is frequently updated because it will have overhead for insert and update operations.
- D. Check whether the table has LOB columns as it will minimize I/O for the queries.
- E. Check whether the table blocks are sparsely populated as this will defragment the blocks.
NEW QUESTION 12
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.
NEW QUESTION 13
Which two statements are true about Compare Period ADDM? (Choose two.)
- A. It is automatically invoked whenever the AWR Compare Period report is invoked.
- B. It is automatically invoked whenever ADDM is run by default.
- C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.
- D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.
NEW QUESTION 14
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:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘SH’,‘CUSTOMERS’);
PL/SQL procedure successfully completed.
SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);
You then perform several INSERT, UPDATE, and DELETE operations, significantly altering the data in the table.
View the Exhibit to examine the query and its execution plan.
Which three options would improve the performance of the query? (Choose three.)
- A. creating a bitmap index on the COUNTRY_ID column
- B. regathering statistics on the CUSTOMERS table
- C. creating a histogram on the COUNTRY_ID column
- D. increasing the size of the PGA
- E. creating an SQL profile
- F. creating a KEEP cache
NEW QUESTION 15
You are administering a database that supports multiple applications, which make dedicated connections to the database instance by using different services.
You execute the command to enable tracing of the ORCL1 service:
Which two statements are true? (Choose two.)
- A. A single trace file is generated for all sessions mapped to the ORCL1 service.
- B. SQL trace is enabled for all modules and actions for sessions mapped to the ORCL1 service.
- C. An SQL trace file is generated for each session that maps to the ORCL1 service.
- D. An SQL trace file is generated for each of the modules using the ORCL1 service.
- E. SQL trace is not enabled for the service because a module name is not specified.
NEW QUESTION 16
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.
P.S. Easily pass 1Z0-064 Exam with 84 Q&As 2passeasy Dumps & pdf Version, Welcome to Download the Newest 2passeasy 1Z0-064 Dumps: https://www.2passeasy.com/dumps/1Z0-064/ (84 New Questions)