Below are some important Memory Management interview questions which are asked in most MNC company interviews for beginners or professionals.
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.
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.
More Questions & Answers :-
No comments:
Post a Comment