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.
What is dictionary cache ?
Dictionary cache is information about the database objects stored in a data dictionary table.
What is meant by recursive hits ?
Number of times processes repeatedly query the dictionary table is called recursive hits. 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.
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 operation 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.
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.dp indexfile=newfile.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.
List the Optimal 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 - Tool 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 tablespaces.
How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name
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.
How free extents are managed in Ver 6 and Ver 7. ?
Free extents cannot be merged together in Ver 6.0
Free extents are periodically coalesces with the neighboring free extent Ver 7.0.
Which parameter in Storage clause will reduce no of rows per block ?
PCTFREE parameter
Row size also reduces no of rows per block.
What is 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.
How does space allocation take place within a block ?
Each block contains entries as follows :
Fixed block header
Variable block header
Row header, row date (Multiple rows may exists)
PCTFREE (% of free space for row updation in future)
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.
What is the OPTIMAL parameter ?
It is used to set the optimal length of rollback segment.
What is the functionality of SYSTEM tablespace ?
To manage the database level of transactions such as modifications of the data dictionary table that record information about the free space usage.
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 tablespace (RBS) for rollback segments.
Create additional Rollback segment in tablespace (RBS)
Deactivate Rollback Segment R0 and activate the newly created rollback segments.
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).
No comments:
Post a Comment