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

Page:    1 / 13   
Total 199 questions

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

  • A. More CPU time was used for background processes in the first time period.
  • B. A change to the database configuration reduced the % DB time for transactions.
  • C. More of DB Time was consumed by background processes in the first time period.
  • D. A reduction in Hard Parse activity resulted in a reduction of DB CPU Time (in seconds).
  • E. More transactions were processed in the first time period based on the DB Time values.

Answer : B,D

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. A low Library Hit % indicates that cursors are not being reused.
  • B. A low Execute to Parse % indicates that most SQL statements are reusing the existing execution plans.
  • C. A high In-memory Sort % indicates that most sorts are being done in memory, thereby causing the performance to degrade.
  • D. A high Buffer Hit % indicates that required data blocks were found in the Buffer Cache memory, thereby avoiding expensive disk I/O.

Answer : A,D

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?

  • A. increasing the flashback retention period
  • B. increasing the retention period for SQL Management Base
  • C. increasing the undo retention period for the database instance
  • D. increasing the Automatic Workload Repository (AWR) retention period

Answer : D

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

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

  • A. log file sync
  • B. rdbms ipc message
  • C. SQL*Net message from client
  • D. log file switch (checkpoint incomplete)

Answer : A,D

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?

  • A. It becomes outdated immediately after the tables change.
  • B. It is maintained automatically along with the changes in the tables.
  • C. The profile will be used for the changed plans of the SQL statement.
  • D. The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.

Answer : C

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

  • A. It is used for adaptive threshold setting.
  • B. The statistics computation cannot be scheduled explicitly for it.
  • C. Only significance-level thresholds can be set for metrics under it.
  • D. It is the only baseline that can be used on the performance page.

Answer : A,B

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?

  • A. Set the OPTIMIZER_MODE parameter to ALL_ROWS for the database instance.
  • B. Set the SQLTUNE_CATEGORY parameter to DEFAULT for the database instance.
  • C. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE for the database instance.

Answer : D

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?

  • A. Change the temporary tablespace assigned to this user ID.
  • B. Create and assign temporary tablespace group to this user ID.
  • C. Increase the size of the temporary tablespace assigned to this user ID.
  • D. Make the bigger temporary tablespace the default temporary tablespace ID.

Answer : B

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

  • A. increasing the size of the shared pool
  • B. setting the CURSOR_SHARING parameter to EXACT
  • C. decreasing the value set for the OPEN_CURSORS parameter
  • D. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1337874392
  • E. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117

Answer : A,E

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;
-------------- ------------------------------------- ---------- -----------
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?

  • A. Each row in the output represents a SQL statement that had to wait for mutexes.
  • B. The Cursor Stat and Cursor Pin SLEEPS indicate that the CURSOR_SHARING parameter is set to EXACT.
  • C. The GETS column shows the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
  • D. 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.

Answer : C

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

  • A. Increase the number of ASMB processes.
  • B. Increase the number of DBWR_IO_SLAVES.
  • C. Decrease the value for the ASM_POWER_LIMIT parameter.
  • D. Increase the value for the ASM_POWER_LIMIT parameter.
  • E. Set the DISK_REPAIR_TIME disk attribute to a lower value.
  • F. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.

Answer : C,F

View the Exhibit and examine the content.

ALLOCATED_SPACE and FREE_SPACE indicate that a large amount of space is allocated to the temporary tablespace that is being used.Which action would reclaim the space and refresh these values?

  • A. shrinking the tablespace
  • B. closing all the current sessions
  • C. restarting the database instance
  • D. forcing a checkpoint or waiting for a checkpoint to occur

Answer : A

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

  • A. Shared pool size is too small.
  • B. Cursors are not being shared.
  • C. There are frequent logons/logoffs.
  • D. A large number of COMMITs are being performed.
  • E. A large number of data definition language (DDL) and query statements are being executed simultaneously by different sessions.

Answer : A,B,C

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 = 'db block gets'
5 AND = 'consistent gets'
6 AND = 'physical reads'
7 AND = 'physical reads direct';

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

  • A. The database performance is very good because of reduced logical I/Os.
  • B. The database performance cannot be determined based only on this ratio.
  • C. Good database performance is guaranteed because very few physical I/Os are performed.
  • D. The database performance is very good because most of the requested data blocks are found in the buffer cache.

Answer : B

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?

  • A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table sequentially.
  • 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.

Answer : B

Page:    1 / 13   
Total 199 questions