Oracle Memory Management Interview Questions And Answers

Download Memory Management Interview Questions and Answers PDF

Refine your Memory Management interview skills with our 61 critical questions. These questions will test your expertise and readiness for any Memory Management interview scenario. Ideal for candidates of all levels, this collection is a must-have for your study plan. Download the free PDF now to get all 61 questions and ensure you're well-prepared for your Memory Management interview. This resource is perfect for in-depth preparation and boosting your confidence.

61 Memory Management Questions and Answers:

Memory Management Job Interview Questions Table of Contents:

Memory Management Job Interview Questions and Answers

1 :: 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.

2 :: 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.

3 :: How does Space allocation table place within a block?

Each block contains entries as follows
Fixed block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future).

4 :: 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.

5 :: What is the OPTIMAL parameter?

To avoid the space wastage we use OPTIMAL parameter.

6 :: What is a shared pool?

The Shared Pool environment contains both fixed and variable structures. The Fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements.

Used To Store
Most Recently Executed SQL Statements
Most Recently used Data definitions
It Consists of two Key performance - related memory structures
Library Cache & Data Dictionary Cache

Shared Pool is sized by SHARED_POOL_SIZE

7 :: What is mean by Program Global Area (PGA)?

It is area in memory that is used by a Single Oracle User Process.

8 :: What is a data segment?

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

9 :: What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient Shared SQL pool size.

Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

LOGICAL & PHYSICAL ARCHITECTURE OF DATABASE.

10 :: 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.

11 :: What is dictionary cache?

Dictionary cache is information about the databse objects stored in a data dictionary table.

12 :: Which parameter in Storage clause will reduce number of rows per block?

PCTFREE parameter

Row size also reduces no of rows per block.

13 :: 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.

14 :: How will you force database to use particular rollback segment?

For perticular transaction Alter system set rollback segment 'name';

For database, we can set in pfile. Rollback_segment='name' .

15 :: How can we organize the tablespaces in Oracle database to have maximum performance?

Store data in tablespaces to avoid disk contension.system tablespace-recursive callsuserdata-users objectsindex tablespace-for indexesrollback segmnets-undo tablespace or manual rollback segmentsplace application specific data in respective tablespaces.Place all these tablespaces in saperate disks.Try to implement raid-0 (striping) for better performance.

16 :: 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.

17 :: What is 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.

18 :: 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.

19 :: 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.

20 :: How will you estimate the space required by a non-clustered tables?

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.

21 :: 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 which writing into disk.
Disk Corruption will be very less.

22 :: What is a Control file?

The Control File is a small binary file necessary for the database to start and operate successfully. Each Control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to USE.
The Control file is not accessible, the database does not function properly.

23 :: How will you monitor rollback segment status?

By using dictionaray view's called v$rollstat,dba_rollback_segs.

24 :: How will you monitor the space allocation?

This can be monitored in DB_data_files.

25 :: Why query fails sometimes?

Due to syntax errors.