Oracle Database 12c: Performance Management and Tuning
Question No: 31
Which two actions should you take to monitor the throughput generated by the modules of an application? (Choose two.)
Use the Resource Manager.
Enable SQL Trace at the session level.
Create a service.
Use a dedicated server configuration.
Use the DBMS_APPLICATION_INFO package to define the current module and action so that they appear in V$SESSION.
Question No: 32
You plan to upgrade your production database from Oracle Database 11g to 12c. As part of the upgrade, you want to introduce new indexes and materialized views. You have already created a test system with Oracle Database 12c, having the same structure and data as the production database, along with new schema objects to be added to the production database.
You want to identify regressed SQL statements, if any, which may have been caused by schema changes and the change in the optimizer version.
Which two methods would you use to achieve this? (Choose two.)
Create an SQL Tuning Set (STS) for the SQL statements on the production database and submit as input to the SQL Tuning Advisor on the test database.
Create an STS for the SQL statements on the production database and submit as input to the SQL Performance Analyzer with the OPTIMIZER_FEATURES_ENABLE parameter first set to 22.214.171.124, and then to 126.96.36.199 on the test database.
Generate an Automatic Workload Repository (AWR) compare periods report with snapshots taken before and after schema changes on the test database.
Capture the production database workload, replay it on the test system by using Database Replay, and analyze by using the workload replay compare period report.
Create an STS for the SQL statements on the production database and submit as input to the SQL Access Advisor on the test database.
Create an STS for the SQL statements on the production database before and after changes and submit as input to the SQL Performance Analyzer on the test database.
Question No: 33
For your database some users complain about not being able to execute transactions. Upon investigation, you find that the problem is caused by some users performing long- running transactions that consume huge amounts of space in the UNDO tablespace.
You want to control the usage of the UNDO tablespace only for these user sessions. How would you avoid the issue from repeating in future? (Choose the best answer.)
Create a profile for the users with the LOGICAL_READS_PER_SESSION and
LOGICAL_READS_PER_CALL limits defined.
Create external roles to restrict the usage of the UNDO tablespace and assign them to the users.
Set the threshold for UNDO tablespace usage for the users.
Implement a Database Resource Manager plan by mapping the users to a resource consumer group with limits defined for UNDO tablespace usage.
Question No: 34
For which two requirements can you always use the V$ACTIVE_SESSION_HISTORY view? (Choose two.)
to investigate intermittent performance problems in a session, only when the problem lasted less than five minutes in the last twelve hours
to find the exact number of executions of a specific query in a session in the last two minutes
to identify which module in an application was being executed in a session
to identify a scheduler job that is not responding
to find the amount of Program Global Architecture (PGA) memory being currently consumed by a session
Question No: 35
Examine the partial TOP 10 Foreground Events by Total Wait Time section of an AWR report:
What should you examine to diagnose the cause of the top three wait events? (Choose the
the V$ACTIVE_SESSION_HISTORY view
the Time Model Statistics section of the AWR report
the SQL statements based on elapsed time from the AWR report
the Latch Activity section
the Segment Statistics section of the AWR report
Question No: 36
Examine an extract from a PGA Memory Advisory for your database:
Which two inferences are correct? (Choose two.)
Automatic management of PGA memory is disabled.
The current PGA size requires the use of a temporary tablespace for sorting operations.
The current PGA size is sufficient and does not require the memory manager to allocate more memory.
PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.
Question No: 37
Your database supports multiple applications. The applications run on the middle tier and use connection pooling for connecting to the database.
You notice that the sessions created by the applications are competing for resources. You want to statistically measure the workload and set priorities.
What action must you perform to achieve this? (Choose the best answer.)
Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.
Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to trace the services.
Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.
Create services for the applications and set a relative priority by assigning them to application users.
Question No: 38
Examine the partial Activity Over Time section of an Active Session History (ASH) report:
Which two inferences are correct? (Choose two.)
In the first time slot, five different sampled sessions were connected to the database instance.
In the second time slot, out of the nine sampled sessions connected to the database instance, only one sampled session was idle at the time of report generation.
In the first time slot, only one sampled session was using the CPU.
In the second time slot, five different sampled sessions were using the CPU.
In the second time slot, 0.14% of the time was spent on the CPU.
Question No: 39
In your database, the locally managed tablespace, USERS, has the default space usage alert set to 85% for the warning level and 97% for the critical level.
Which two statements are true? (Choose two.)
Alerts are recorded in both Oracle Enterprise Manager Cloud Control and DBA_OUTSTANDING_ALERTS only when the critical threshold is exceeded.
Alert settings for the warning and critical levels must be disabled before taking the USERS tablespace offline.
Alerts that are triggered are automatically recorded in DBA_ALERT_HISTORY after they are cleared.
Alerts are triggered when the space usage reaches the warning level, again when it reaches the critical level, and yet again when the space usage falls below the critical level.
Question No: 40
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 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.)
creating a bitmap index on the COUNTRY_ID column
regathering statistics on the CUSTOMERS table
creating a histogram on the COUNTRY_ID column
increasing the size of the PGA
creating an SQL profile
creating a KEEP cache
100% Free Download!
–Download Free Demo:1z0-064 Demo PDF
100% Pass Guaranteed!
–Download 2018 Latesttests 1z0-064 Full Exam PDF and VCE
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|