Oracle Database 11g: Performance Tuning v5.0 (1z0-054)

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.)

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.)

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 "SYS.DBMS_WORKLOAD_REPOSITORY", line
601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?

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:
SQL>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.

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?

Identify the correct statements about the SYSTEM_MOVING_WINDOW baseline. (Choose two.)

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?

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?

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.)

Examine the output of the query given below:
SQL> 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?

You are working as a DBA in ABC Corp. You are working on online transaction processing
(OLTP) system. The database uses ASM storage. One of the ASM disk goes offline because of hardware failure. When the disk is added back again after the fix, because the database is active, rebalance operations impact the I/O subsystem.Which two recommendations would you give to lower the impact on the I/O subsystem? (Choose two.)

View the Exhibit and examine the content.

View the Exhibit and examine the details of the Top 5 Timed Events from an Automatic
Workload Repository (AWR) report.What can be the three possible causes for the latch- related wait events? (Choose three.)

You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity:
SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100
2 "Cache Hit ratio"
3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd
4 WHERE cur.name = 'db block gets'
5 AND con.name = 'consistent gets'
6 AND phy.name = 'physical reads'
7 AND phyd.name = 'physical reads direct';

Cache Hit Ratio -
---------------
98.43
What can you conclude based on this ratio?

You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database.
On further investigation, you find that at the end of each day the contents of the
SALES_RECORDS table are transferred to the SALES table and deleted from the
SALES_RECORDS table. The deleted operations cause the table to be sparsely populated.
The SALES_RECORDS table has Automatic Segment Space Management (ASSM) and row movement enabled. The table is accessible in 24x7 mode.
What is the most efficient method to improve the performance?