Online Test on Performance Tuning of Oracle 9i

You have several tables that are updated frequently. These updates are populating null columns with data. Which sizing would be least likely to cause migration?
DB_BLOCK_SIZE = 8192; PCTFREE 5
DB_BLOCK_SIZE = 2048; PCTFREE 20
DB_BLOCK_SIZE = 4096; PCTFREE 10
DB_BLOCK_SIZE = 8192; PCTFREE 0
Which statement is not true about the database storage hierarchy?
One or more extents make up a segment.
In the Oracle database the block is the smallest unit of data files I/O.
An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks.
In the Oracle database the block is the smallest unit of space that can be allocated
While you were on vacation, several new applications were added to a database that you manage. Another DBA did his best to tune the database while you were gone, and increased the buffer cache hit ratio from 85 percent to 94 percent after increasing the size of the DB_CACHE_SIZE from 96 MB to 128 MB. As a result, the size of the SGA is now 256 MB, which is the maximum amount of memory that can be allocated for this database. You have set the init.ora parameter SGA_MAX_SIZE = 256 MB to meet that restriction. In addition, the init.ora parameterSHARED_POOL_SIZE is set to 48 MB. Your problem is that the OLTP applications are not performing with their normal 3-second response time as they did in the past. What can you do to try to get the response time for OLTP applications to less than three seconds?
Check the buffer cache hit ratio to make sure that it is greater than 95 percent. If it is not greater than 95 percent, decrease the SHARED_POOL_SIZE to 32 MB and allocate the remaining 16 MB to the buffer cache by setting the DB_CACHE_SIZEparameter to 144 MB.
Check that the percentage of misses in the entire data dictionary cache is less than two percent by querying the V$ROWCACHE view. If it is not less than two percent, then decrease the DB_CACHE_SIZE to 112 MB and allocate the remaining 16 MB to the shared pool by setting the init. ora parameter SHARED_POOL_SIZE to 64 MB.
Query the V$LIBRARYCACHE view to obtain the number of RELOADS and PINS that have taken place.If the number of reloads is greater than 1 percent of the pins, de-allocate 16 MB from the buffer cache by setting the DB_CACHE_SIZE parameter to 112 MB and add the 16 MB to the shared pool by setting the init.ora parameter SHARED_POOL_SIZE to 64 MB.
Query the V$LIBRARYCACHE view and look at the GETHITRATIO column. If the GETHITRATIO is less than 99 percent for the SQL_AREA namespace, de-allocate 16 MB from the buffer cache by setting the DB_CACHE_SIZE parameter to 112 MB and add the 16 MB to the shared pool by setting the init.ora parameter SHARED_POOL_SIZE to 64 MB.
You are considering Operating System striping for your table spaces. Which statement is true?
The stripe is dependent on the Oracle block size.
Controller based striping will usually perform better than O/S based striping.
O/S based striping will usually perform better than controller bases striping & You should choose a stripe size that will give you one physical read for each Oracle server read.
You should choose a stripe size that will give you one physical read for each Oracle server read.
Which statement is not true with respect to hash clusters?
A. Full table scans are generally faster on cluster tables than on non-clustered tables.
B. Hash clusters may be desirable when the number of key values is predictable and key values are evenly distributes.
C. If tables in a hash cluster require more space than the initial allocation for the cluster, performance degradation can be substantial because overflow blocks are required.
D. Storing a single table in a hash cluster can be useful regardless of whether the table is
Which two statements is true regarding the use of DB CACHE_ADVICE init.ora parameter?
Setting the parameter to READY reserves space in the buffer cache to store information about different buffer cache sizes, but no CPU overhead is incurred.
Setting the parameter to READY reserves space in the shared pool to store information about different buffer cache sizes, but no CPU overhead is incurred.
Setting the parameter to OFF reserves space in the buffer cache to store information about different buffer cache sizes, and CPU overhead is incurred as statistics are Collected.
The V$DB CACHE ADVICE view contains information that predicts the estimated number of physical reads for different cache sizes for each buffer cache setup in the SGA.
Which statement is a valid settings for the initialization parameter
SQL> ALTER SESSION SET USE_STORED_OUTLINES=TRUE;
SQL> ALTER SESSION SET USE_STORED_OUTLINES=PUBLIC;
SQL> ALTER SESSION SET USE_STORED_OUTLINES=PRIVATE;
SQL> ALTER SESSION SET USE_STORED_OUTLINES=DEFAULT;
There are two users, John and Susan, who are updating the EMPLOYEE table in the following order:
1. First, John issues this SQL update: UPDATE EMPLOYEE SET LAST_ NAME ='SMITH' where ID=200;
2. Next, Susan issues this SQL update: UPDATE EMPLOYEE SET SALARY=50000 WHERE ID=250;
3. Next, John issues this SQL update: UPDATE EMPLOYEE SET LAST_ NAME `BAKER' WHERE ID=250;
4. Finally, Susan issues this SQL update: UPDATE EMPLOYEE SET SALARY=60000 WHERE ID=200;
What will be the result?
Oracle kills Susan's session to prevent a deadlock.
Oracle will detect a deadlock and roll back Susan's session.
Oracle kills both John's and Susan's statements to prevent a deadlock.
Both John's and Susan's sessions would hang indefinitely because of a deadlock.
Which three are used when administering systems with the database Resource Manager?
Resource Plan, Resource Consumer Group &Resource Plan Directives
Histograms, Explain Plan&. Resource Plan
Resource Consumer Group, Resource Plan Directives& Resource Plan Assignments
Explain Plan, Resource Plan& Resource User Group
When performing a sort operation, you notice that there are a large number of sorts requiring I/O to the disk. Which parameter could be increased to allow more sorts to be performed in memory?
SORT_AREA_SIZE
LARGE_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_MULTIBLOCK_READ_COUNT
Which action could potentially cause checkpoints to take longer?
Increasing the number of redo log groups.
Increasing the size of rollback segments.
Decreasing the value of the REDO_LOG_BUFFERS parameter.
Increasing the value of the FAST_START_IO_TARGET parameter.
When a deadlock shutdown is detected by Oracle, where is the trace file generated? A. SQL_TRACE B. TRACE_DEST C. USER_DUMP_DEST D. CORE_DUMP_DEST
SQL_TRACE
TRACE_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
Which statement is true of the buffer cache?
The buffer pools in Oracle9i can be resized dynamically.
The blocks in the buffer cache are managed using one list.
The blocks in the buffer cache are managed using one list.
In Oracle9i buffer caches can be dynamically added.
For which reason would you query V$SYSSTAT?
Name of the sort segment.
Free space available for a sort segment.
Number of disk sorts performed since startup.
Number of users active on individual sort segments.
Which statement is about plan stability is true?
Plan stability only wants when SQL statements match textually.
You can only have one stored outline per SQL statement
Stored outlines are saved in the data dictionary (SYS schema
Stored outlines become invalid when you analyze the associated objects.
What does this statement do? SQL> ANALYZE INDEX index name VALIDITY STRUCTURE;
It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.
It provides information in the INDEX_HISTOGRAM view to indicate whether an index is invalid or valid
It provides information in the DBA_INDEXES view for the COST BASED Optimizer when choosing an execution plan.
It checks to see if each index entry points to a valid row in the underlying table and saves the results in the DBA_INDEXES view
Which one of the statements is true regarding OLTP systems?
Use literals for optimally shared SQL rather than bind variables to keep the overhead of parsing to a minimum.
To avoid the performance load of dynamic space allocation, allocate space explicitly so tables, clusters and indexes.
Use hash clusters especially on tables that are heavily inserted into, because of the use of space and the number of blocks that need to be visited.
Use hash clusters especially on tables that are heavily inserted into, because of the use of space and the number of blocks that need to be visited.
When performing a sort operation, you notice that there are a large number of sorts requiring I/0 to the disk. Which parameter could be increased to allow more sorts to be performed in memory?
SORT_AREA_SIZE
LARGE_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_MULTIBLOCK_READ_COUNT
Which statement could require a sort?
SELECT DISTINCT dept_id FROM emp:
UPDATE emp SET salary=salary*1.1 WHERE id =7722;
SELECT emp_id, name FROM emp WHERE emp-id= 7722;
SELECT emp_id, name FROM emp WHERE emp_id BETWEEN 7722 and 7100;
Which two views can be used to detect lock contention?
V$LOCK
V$LOCKED_OBJECT
V$LOCK_CONTENTION
V$LOCK & V$LOCKED_OBJECT
Description:

Tests how skilled you are in Performance Tuning
Disclaimer: Content, such as images used in the questions (if any), have been picked up from various places for the sole purpose of Instruction.

Discussion

Bidyadhar Jena

i got

2058 days 8 hours 48 minutes ago

Bidyadhar Jena

i got 15/20. can i qualify oca exam......................

2058 days 8 hours 49 minutes ago

mani

WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3271 days 48 minutes ago

mani

WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3271 days 48 minutes ago

mani

WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3271 days 48 minutes ago

mani

WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3271 days 50 minutes ago

mani

WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3271 days 50 minutes ago

mani

WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3271 days 51 minutes ago

Asad

hi ]

3316 days 22 hours 3 minutes ago

somesh

I WANT PREPARE AND WANT TO MAKE A GOOD RESULT

3676 days 7 hours 10 minutes ago

Your Facebook Friends on WizIQ

More Tests By Author

ASP Server Controls
10 Questions | 667 Attempts

ASP.NET, Namespaces ASP.NET
10 Questions | 2028 Attempts

An ASP.NET Application
10 Questions | 437 Attempts

Explore Similar Courses

Oracle ADF Online

Price:$100