Oracle Database 11g: Performance Tuning
Question No: 61
You generate an AWR Compare Periods Report for two consecutive time periods as a routine activity to monitor your database performance.View the Exhibit and examine the partial details of the Time Model Statistics generated in the report.Which two statements give a correct inference based on the DB Time and DB CPU in the Time Model Statistics? (Choose two.)
More CPU time was used for background processes in the first time period.
A change to the database configuration reduced the % DB time for transactions.
More of DB Time was consumed by background processes in the first time period.
A reduction in Hard Parse activity resulted in a reduction of DB CPU Time (in seconds).
More transactions were processed in the first time period based on the DB Time values.
Question No: 62
View the Exhibit and examine the Instance Efficiency Percentages that you got on generating an Automatic Workload Repository (AWR) report for your online transaction processing (OLTP) database.What can you deduce from this part of the AWR report? (Choose all that apply.)
A low Library Hit % indicates that cursors are not being reused.
A low Execute to Parse % indicates that most SQL statements are reusing the existing execution plans.
A high In-memory Sort % indicates that most sorts are being done in memory, thereby
causing the performance to degrade.
A high Buffer Hit % indicates that required data blocks were found in the Buffer Cache memory, thereby avoiding expensive disk I/O.
Question No: 63
You plan to use adaptive thresholds as part of the performance tuning activity. You decide toincrease the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager:
Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at quot;SYS.DBMS_WORKLOAD_REPOSITORYquot;, line 601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?
increasing the flashback retention period
increasing the retention period for SQL Management Base
increasing the undo retention period for the database instance
increasing the Automatic Workload Repository (AWR) retention period
Question No: 64
You want to review a summary of all the wait events in an instance since it started over a month ago.
You issued the following query:
SQLgt;SELECT event, total_waits, time_waited, average_wait FROM V$SYSTEM_EVENT
ORDER BY time_waited DESC;
View the Exhibit and examine a portion of the output from the above query.
Which events would you consider from the output for further investigation? (Choose all that apply.)
log file sync
rdbms ipc message
SQL*Net message from client
log file switch (checkpoint incomplete)
Question No: 65
You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables. What would happen to the SQL profile that is created for the SQL statement?
It becomes outdated immediately after the tables change.
It is maintained automatically along with the changes in the tables.
The profile will be used for the changed plans of the SQL statement.
The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.
Question No: 66
Identify the correct statements about the SYSTEM_MOVING_WINDOW baseline. (Choose two.)
It is used for adaptive threshold setting.
The statistics computation cannot be scheduled explicitly for it.
Only significance-level thresholds can be set for metrics under it.
It is the only baseline that can be used on the performance page.
Question No: 67
View the Exhibit and examine the result of Automatic SQL Tuning for a period.None of the recommended SQL profiles are implemented. What would you do to allow the Automatic SQL Tuning implement the SQL profiles automatically?
Set the OPTIMIZER_MODE parameter to ALL_ROWS for the database instance.
Set the SQLTUNE_CATEGORY parameter to DEFAULT for the database instance.
Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE for the database instance.
Use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set
ACCEPT_SQL_PROFILES to TRUE.
Question No: 68
You work in an online transaction processing (OLTP) environment and users are experiencing slow response time on their queries. These users connect as FINANCE to run their application and run hundreds of sessions.
Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace and there is no load on other temporary tablespaces. View the Exhibit and examine the details.
After some time you observed that sort operations are running out of temporary space. What would be a long-term solution?
Change the temporary tablespace assigned to this user ID.
Create and assign temporary tablespace group to this user ID.
Increase the size of the temporary tablespace assigned to this user ID.
Make the bigger temporary tablespace the default temporary tablespace ID.
Question No: 69
You work as a DBA for a company and manage an online transaction processing (OLTP) system. You received complaints about the performance degradation of SQL statements
executed by the application that uses this database.View the Exhibit and examine the output of the queries executed to investigate the performance issues.Which two actions can improve performance? (Choose two.)
increasing the size of the shared pool
setting the CURSOR_SHARING parameter to EXACT
decreasing the value set for the OPEN_CURSORS parameter
replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1337874392
replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117
Question No: 70
Examine the output of the query given below:
SQLgt; SELECT mutex_type, location, sum(gets), sum(sleeps) FROM v$mutex_sleep_history GROUP BY mutex_type, location;
MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS)
——- ——————- —– ——
Library Cache kglhdgn1 62 8669586 4538
Library Cache kglget2 2 2016618 24
Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1
Cursor Pin kkslce [KKSCHLPIN2] 666831 678
Library Cache kgllkdl1 85 3369224 110
Library Cache kglpnal1 90 224199 13
Library Cache kglic1 49 42068 10
Library Cache kglpin1 4 9620087 374
Library Cache kglpndl1 95 2065089 79
9 rows selected.
Which statement is true?
Each row in the output represents a SQL statement that had to wait for mutexes.
The Cursor Stat and Cursor Pin SLEEPS indicate that the CURSOR_SHARING parameter is set to EXACT.
The GETS column shows the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
The sum of numbers in the GETS and SLEEPS columns indicates the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|