|
|
|
|
1.
|
What is Database Buffers? |
|
|
Database buffers are cache in the SGA used to hold the
data blocks that are read from the data segments in the
database such as tables, indexes and clusters
DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
|
|
2.
|
What is dictionary cache? |
|
|
Dictionary cache is information about the databse
objects stored in a data dictionary table.
|
|
3.
|
What is meant by recursive hints? |
|
|
Number of times processes repeatedly query the
dictionary table is called recursive hints. It is due to
the data dictionary cache is too small. By increasing
the SHARED_POOL_SIZE parameter we can optimize the size
of Data Dictionary Cache.
|
|
4.
|
What is meant by redo log buffer? |
|
|
Changes made to entries are written to the on-line redo
log files. So that they can be used in roll forward
operations during database recoveries. Before writing
them into the redo log files, they will first brought to
redo log buffers in SGA and LGWR will write into files
frequently. LOG_BUFFER parameter will decide the size.
|
|
5.
|
How will you swap objects into a different table space
for an existing database? |
|
|
Export the user
Perform import using the command imp system/manager
file=export.dmp indexfile=newrite.sql. This will create
all definitions into newfile.sql.
Drop necessary objects.
Run the script newfile.sql after altering the
tablespaces.
Import from the backup for the necessary objects.
|
|
6.
|
List the Optional Flexible Architecture (OFA) of Oracle
database? Or how can we organise the tablespaces in
Oracle database to have maximum performance? |
|
|
SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard
operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.
|
|
7.
|
How will you force database to use particular rollback
segment? |
|
|
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
|
|
8.
|
What is meant by free extent? |
|
|
A free extent is a collection of continuous free blocks
in tablespace. When a segment is dropped its extents are
reallocated and are marked as free.
|
|
9.
|
How free extents are managed in Ver 6.0 and Ver 7.0? |
|
|
Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the
neighboring free extent in Ver 7.0
|
|
10. |
Which parameter in Storage clause will reduce no. of
rows per block? |
|
|
PCTFREE parameter
Row size also reduces no of rows per block.
|
|
11. |
What is the significance of having storage clause? |
|
|
We can plan the storage for a table as how much initial
extents are required, how much can be extended next, how
much % should leave free for managing row updations
etc.,
|
|
12. |
How does Space allocation table place within a block? |
|
|
Each block contains entries as follows
Fixied block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future)
|
|
13. |
What is the role of PCTFREE parameter is Storage clause? |
|
|
This is used to reserve certain amount of space in a
block for expansion of rows.
|
|
14. |
What is the OPTIMAL parameter? |
|
|
It is used to set the optimal length of a rollback
segment.
|
|
15. |
What is the functionality of SYSTEM table space? |
|
|
To manage the database level transactions such as
modifications of the data dictionary table that record
information about the free space usage.
|
|
16. |
How will you create multiple rollback segments in a
database? |
|
|
Create a database which implicitly creates a SYSTEM
Rollback Segment in a SYSTEM tablespace.
Create a Second Rollback Segment name R0 in the SYSTEM
tablespace.
Make new rollback segment available (After shutdown,
modify init.ora file and Start database)
Create other tablespaces (RBS) for rollback segments.
Deactivate Rollback Segment R0 and activate the newly
created rollback segments.
|
|
17. |
How the space utilisation takes place within rollback
segments? |
|
|
It will try to fit the transaction in a cyclic fashion
to all existing extents. Once it found an extent is in
use then it forced to acquire a new extent (No. of
extents is based on the optimal size) |
|
18. |
Why query fails sometimes? |
|
|
Rollback segment dynamically extent to handle larger
transactions entry loads.
A single transaction may wipeout all avaliable free
space in the Rollback Segment Tablespace. This prevents
other user using Rollback segments.
|
|
19. |
How will you monitor the space allocation? |
|
|
By quering DBA_SEGMENT table/view.
|
|
20. |
How will you monitor rollback segment status? |
|
|
Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or
corupted.
PARTLY AVAILABLE - Contains data from an unresolved
transaction involving a distributed database.
|
|
21. |
List the sequence of events when a large transaction
that exceeds beyond its optimal value when an entry
wraps and causes the rollback segment to expand into
another extend. |
|
|
Transaction Begins.
An entry is made in the RES header for new transactions
entry
Transaction acquires blocks in an extent of RBS
The entry attempts to wrap into second extent. None is
available, so that the RBS must extent.
The RBS checks to see if it is part of its OPTIMAL size.
RBS chooses its oldest inactive segment.
Oldest inactive segment is eliminated.
RBS extents
The Data dictionary table for space management are
updated.
Transaction Completes.
|
|
22. |
How can we plan storage for very large tables? |
|
|
Limit the number of extents in the table
Separate Table from its indexes.
Allocate Sufficient temporary storage.
|
|
23. |
How will you estimate the space required by a
non-clustered table? |
|
|
Calculate the total header size
Calculate the available dataspace per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a
block
Calculate the number of blocks and bytes required for
the table.
After arriving the calculation, add 10 % additional
space to calculate the initial extent size for a working
table.
|
|
24. |
It is possible to use raw devices as data files and what
are the advantages over file. System files? |
|
|
Yes.
The advantages over file system files.
I/O will be improved because Oracle is bye-passing the
kernnel whicl writing into disk.
Disk Corruption will be very less.
|
|
25. |
What is a Control file? |
|
|
Database's overall physical architecture is maintained
in a file called control file. It will be used to
maintain internal consistency and guide recovery
operations. Multiple copies of control files are
advisable.
|
|
26. |
How to implement the multiple control files for an
existing database? |
|
|
Shutdown the databse
Copy one of the existing control file to new location
Edit Config ora file by adding new control file.name
Restart the database.
|
|
27. |
What is meant by Redo Log file mirrorring? How it can be
achieved? |
|
|
Process of having a copy of redo log files is called
mirroring.
This can be achieved by creating group of log files
together, so that LGWR will automatically writes them to
all the members of the current on-line redo log group.
If any one group fails then database automatically
switch over to next group. It degrades performance.
|
|
30. |
What is advantage of having disk shadowing/ Mirroring? |
|
|
Shadow set of disks save as a backup in the event of
disk failure. In most Operating System if any disk
failure occurs it automatically switchover to place of
failed disk.
Improved performance because most OS support volume
shadowing can direct file I/O request to use the shadow
set of files instead of the main set of files. This
reduces I/O load on the main set of disks.
|
|
31. |
What is use of Rollback Segments In Database? |
|
|
They allow the database to maintain read consistency
between multiple transactions.
|
|
32. |
What is a Rollback segment entry? |
|
|
It is the set of before image data blocks that contain
rows that are modified by a transaction.
Each Rollback Segment entry must be completed within one
rollback segment.
A single rollback segment can have multiple rollback
segment entries.
|
|
33. |
What is hit ratio? |
|
|
It is a measure of well the data cache buffer is
handling requests for data.
Hit Ratio = (Logical Reads - Physical Reads - Hits
Misses)/ Logical Reads.
|
|
34. |
When will be a segment released? |
|
|
When Segment is dropped.
When Shrink (RBS only)
When truncated (TRUNCATE used with drop storage option)
|
|
35. |
What are disadvanteges of having raw devices? |
|
|
We should depend on export/import utility for
backup/recovery (fully reliable)
The tar command cannot be used for physical file backup,
instead we can use dd command which is less flexible and
has limited recoveries.
|
|
36. |
List the factors that can affect the accuracy of the
estimations? |
|
|
The space used transaction entries and a deleted record
does not become free immediately after completion due to
delayed cleanout.
Trailling nulls and length bytes are not stored.
Inserts of, updates to and deletes of rows as well as
columns larger than a single datablock, can cause
fragmentation an chained row pieces. |
