Deck 13: Performance Monitoring

Question 1

Performance is really a measure of how quickly a database reacts to at a given request or activity.

Answer 1

True

Question 2

The P$ Views are also known as the Oracle database performance views.

Answer 2

False

Question 3

Solaris utilities such as sar,vmstat,mpstat,and iostat show CPU usage,I / O activity,and memory usage.

Answer 3

True

Question 4

Statistics are a computation or estimation of the exact size and placement of data in tables and indexes.

Question 5

Statistics in an Oracle database can be schema statistics or system statistics.

Question 6

The ANALYZE command is a little more complex to use than the DBMS_STATS package to generate statistics.

Question 7

From a purely performance perspective V$ views provide a picture of statistics,and the ability to drill-down into those statistics.

Question 8

The DBMS_STATS package can be used to copy statistics between databases,allowing accurate simulated tuning between testing and production databases.

Question 9

To monitor the performance of an Oracle database you must use any of the numerous third-party tools offered by companies for this purpose.

Question 10

The STATSPACK is the recommended tool for statistics generation in Oracle 10g.

Question 11

There are operating system tools that provide information about the resources of an underlying Oracle database.

Question 12

Performance monitoring is most easily performed with software tools custom built for the job.

Question 13

There are some non-optimizer statistics that can only be gathered with the DBMS_STATS package and not with the ANALYZE command.

Question 14

The DBMS_STATS package can be used to gather statistics at all object layers of an Oracle database,such as for tables,indexes,even for a whole schema or an entire database.

Question 15

Oracle recommends not using the Database Control for tuning.

Question 16

You can manually gather statistics for snapshot comparison use,using both STATSPACK and the Database Control.

Question 17

That metadata and statistical record of an Oracle database can be used to describe,and thus also monitor,how well the database is performing.

Question 18

Autotrace can be enabled using the SET AUTOTRACE ENABLE EXPLAIN command,executed from within SQL*Plus.

Question 19

The ANALYZE command is more versatile and potentially faster than using the DBMS_STATS package to generate statistics.

Question 20

The ANALYZE command is complex and hard to use.

Question 21

You can use the V$ performance views and the Oracle Wait Event Interface to detect locks.

Question 22

____ statistics parameters generate system statistics essential to tuning.

A) Object
B) System
C) Timed
D) Tuning

Question 23

____ is a set of scripts used to take a snapshot of a database.

A) TKPROF
B) EXPLAIN PLAN
C) TRCSESS
D) STATSPACK

Question 24

____ is simply a formatting tool used to make the content of trace files readable to the human eye.

A) TKPROF
B) EXPLAIN PLAN
C) OPTIMIZE
D) STATSPACK

Question 25

The ____ utility can be used to join the trace activity of a single session,from multiple trace files,when using a shared server configuration.

A) Autotrace
B) TKPROF
C) Trace
D) TRCSESS

Question 26

In general you should resolve lock conflicts manually when those conflicts are caused by applications.

Question 27

SQL ____ allows for generation of trace files for Oracle Database.

A) Autotrace
B) TKPROF
C) Trace
D) Database Control

Question 28

It is recommended to leave the STATISTICS_LEVEL parameter permanently set to ALL.

Question 29

Statistics are very important to the way in which the ____ assesses the best way to execute a query.

A) optimizer
B) RDBMS
C) tuning engine
D) performance engine

Question 30

The ____ command is used to examine potential query plans that the optimizer may utilize,when executing a particular query of DML statement.

A) TKPROF
B) EXPLAIN PLAN
C) OPTIMIZE
D) STATSPACK

Question 31

____ is a particular tool that is very good for monitoring performance of busy production databases.

A) Trace
B) Spotlight
C) Wait Event Interface
D) Database Control

Question 32

Oracle 10g Database uses escalating locks instead of row locks.

Question 33

System statistics gathering is controlled by the TIMED_STATISTICS and ____ configuration parameters.

A) TIMED_CONF_STATISTICS
B) TIMED_OBJ_STATISTICS
C) TIMED_SYS_STATISTICS
D) TIMED_OS_STATISTICS

Question 34

Automatic SQL tuning is new to Oracle 10g Database where the optimizer can be switched from normal mode to a tuning mode.

Question 35

Altering tables in a production database is always risky and should be avoided if possible.

Question 36

Some locking is inevitable,especially in busy,highly concurrent OLTP databases.

Question 37

The internal Oracle 10g Scheduler executes the GATHER_STATS_JOB job on a daily basis,by default.

Question 38

Metrics can be found in many of the V$ performance views.

Question 39

The ____ provides an easy-to-use interface into performance data stored in an Oracle database.

A) Database Control
B) Performance Monitor
C) Enterprise Manager
D) Optimizer

Question 40

Some databases benefit more than others from automation,such as those with a very large data warehouse.

Question 41

In terms of an Oracle 10g database,____________________ is a measure of how fast the database reacts to access data in the database.

Question 42

The ____ option of the ANALYZE command collects only system statistics,not user schema statistics.

A) ESTIMATE
B) SYSTEM
C) CALCULATE
D) COMPUTE

Question 43

A transaction is terminated on the execution of a(n)____ command (explicit or implicit),or when a ROLLBACK command is issued.

A) COMMIT
B) END
C) EXIT
D) FINISH

Question 44

The ____ clause can effectively lock everything within a transaction until the transaction is completed by a commit or rollback.

A) COMMIT
B) FOR UPDATE
C) BLOCK
D) LOCK

Question 45

____ is not a view that can be used for tracking locks.

A) DBA_BLOCKERS
B) DBA_WAITERS
C) DBA_LOCK
D) DBA_DEADLOCKS

Question 46

Statistics can be gathered manually using a command-line tool such as SQL*Plus,with the ____ command or the DBMS_STATS package.

A) OPTIMIZE
B) ANALYZE
C) STATSPACK
D) TRCSESS

Question 47

A(n)____ lock partially locks data where there is still partial access allowed to data by other sessions.

A) exclusive
B) share
C) partial
D) incomplete

Question 48

Automated statistics works (by default on database creation),using the ____ job.

A) STATISTICS_LEVEL
B) STATISTICS_LEVEL_JOB
C) GATHER_STATS
D) GATHER_STATS_JOB

Question 49

_________________________ is the action of monitoring the speed of a database.

Question 50

____ can cause serious performance issues,effectively locking down data.

A) Deadlocks
B) Blocks
C) Latches
D) Transactions

Question 51

A(n)____________________ is the state of all data,in an entire database,at a specific point in time.

Question 52

A(n)____ lock completely prohibits changes to data,but still allows read access.

A) exclusive
B) share
C) full
D) complete

Question 53

The ____ option of the ANALYZE command calculates statistics for all rows and columns in an object,an object being a table or index.

A) ESTIMATE
B) SYSTEM
C) CALCULATE
D) COMPUTE

Question 54

There are two easy ways to find invalid objects in Oracle 10g: (1)use the ____ metadata view and check the STATUS column,and (2)the metrics contained within the Database Control for finding invalid objects.

A) USER_INVALID
B) USER_ERROBJ
C) USER_INVALID_OBJECTS
D) USER_OBJECTS

Question 55

The ____ option of the ANALYZE command calculates statistics on a sample number or percentage of rows,defaulted to 1,064 rows.

A) ESTIMATE
B) SYSTEM
C) CALCULATE
D) COMPUTE

Question 56

SQL Trace controls how much information is generated into trace files (BASIC,TYPICAL,or ____________________).

Question 57

The easiest way to access performance metrics is to administer and analyze them using the ____.

A) Enterprise Manager
B) Optimizer
C) Database Control
D) DBMS_STATS package

Question 58

____ is superseded by the AWR and the ADDM.

A) DBMS_STATS
B) STATSPACK
C) Database Control
D) TKPROF

Question 59

Setting dynamic sampling to ____ switches it off.

A) 0
B) NULL
C) FALSE
D) DISABLE

Question 60

____________________ in SQL*Plus displays a query plan in SQL*Plus every time a query is executed.

Question 61

Database performance views are also known as ____________________ performance views because they change constantly,depending on the state of a database.

Question 62

The DBMS_STATS package is the Oracle recommended method for manual statistic gathering because it is ____________________ executable.

Question 63

Generally,____________________ statistics record the number of rows in tables and the physical size of data.

Question 64

A(n)____________________ is where two interdependent rows are both locked and both rows require the release of the lock on the other row to continue processing.

Question 65

____________________ reads a small number of blocks in a table to make a best guess at the statistical picture of data in a table.

Question 66

Lock ____________________ that require manual resolution are usually caused in error,such as executing a DDL command on an active production database,and locking a busy table.

Question 67

Just like STATSPACK,the _____________________________________________ uses multiple snapshots taken by the AWR,and then performs an analysis between two snapshots.

Question 68

The reason for a deadlock is that a pending ____________________ request cannot be serviced,because the lock required will never be released.

Question 69

AWR stands for ______________________________.

Question 70

Dynamic ____________________ can be a good substitute for tables excluded from automated statistics gathering.

Question 71

The ____________________ is a method of using the V$ performance views,to drill down into database statistics,available in the views.

Question 72

A(n)____________________ is when one thing is waiting for another,but that other thing is also waiting for something else,or even the first thing.

Question 73

The primary objective of automation in statistics gathering for Oracle 10g,is to avoid situations of ____________________ or non-generated statistics.

Question 74

The heart of automated statistics gathering and management for Oracle 10g is in the ____________________ browser interface.

Question 75

ASH stands for ______________________________.

Question 76

A(n)____________________ in Oracle Database is a special type of statistic,defined as a measure of a rate of change on a cumulative statistic.

Question 77

The Windows Performance ____________________ displays graphical representations of resource usage.

Question 78

ADDM stands for ___________________________________.

Question 79

In Oracle 10g the default for statistics collection is ____________________,which is achieved by setting the STATISTICS_LEVEL configuration parameter to TYPICAL or ALL.

Question 80

Rule-based optimization is obsolete in Oracle 10g and ____________________ optimization is now the only option.