Architecture -1

Question

  1. List out 10 memory level parameters?
    => SGA_TARGET, SGA_MAX_TARGET
    => PGA_AGGREGATE_TARGET, WORKAREA_SIZE_POLICY, SORT_AREA_SIZE, HASH_AREA_SIZ
    => MEMORY_TARGET, MEMORY_MAX_TARGET (Both SGA & PGA)
    We can Set it like:
    a) alter system set SGA_TARGET=600G scope =both; or (spfile/pfile)
    b) alter system set PGA_AGGREGATE_TARGET = 500G scope =spfile;     Answer
  2. Which commands leads to sorting? What memory area is used for sorting? How will you increase the performance of sorting? Name the parameters?
    a) Order by, Union, distinct, group by, create index
    b) PGA – Program Global Area
    c) By using the below parameters, we can increase the performance of sorting,
    d) PGA_AGGREGATE_TARGET=4G; (AUTO) => 9i Concept
    WORK_AREASIZE_POLICY=MANUAL; (MANUAL)=>8i Concept
    SORT_AREA_SIZE=200000000; (1.9 GB)
    HASH_AREA_SIZE=200000000; (1.9 GB)
    CREATE_BITMAP_AREA_SIZE
    BITMAP_MERGE_AREA_SIZE
    To set PGA in (AUTO):
    Alter system set WORK_AREASIZE_POLICY= AUTO;
    Alter system set PGA _AGGREGATE_TARGET=4G scope = spfile;    Answer
  3. What will happen if you give commit? What are the wait events that affect log buffer?
    a) LGWR writes the data’s from RLBC to Redo log files.
    b) Log file sync (commit/rollback, but lgwr not finish writing),
    Log file parallel write (writing into log file-IO issue (solution: we need to move log to SSD disk)
    Log buffer space (occurs when server process, write data into the log buffer, faster than the LGWR process can write it out).
    Log file switch Completion (Log Switch is slow, Archiving needed –> Archive should be Enabled)     Answer
  4. How will you increase the size of library cache? What is hard and soft parse?
    a) We can’t Increase the Library Cache, instead we can increase the Size of Shared pool in Pfile (or) alter system set shared_pool_size = 200m scope=spfile;
    b) 1. Hard Parsing => First it check the syntax,symatrix keep with memory and execute the execution plan i.e. (Sql id,plan hash value).
    2. Soft Parsing => If we give same query again, it will take the hard parsing query (plan hash value) directly from shared pool memory and execute it.    Answer
  5. What is checkpoint? How will reduce the checkpoint frequency?
    a) It is an event, when check point occurs, It Fresh up all dirty buffers in DBBC and Signals DBWR to write into disk and it takes the current SCN (System Change Number) and updates all headers of datafiles & control files.
    => Every 3 seconds CKPT occurs.
    => Data consistency or Data commit.
    => Every 3 nano seconds SCN will get generate.
    b) LOG_CHECKPOINT_INTERVAL,
    LOG_CHECKPOINT_TIMEOUT    Answer
  6. Explain the steps that involved when bringing up the database? How to drop the database? how to change the mode of database?
    a) When we you give Startup, oracle pass three stages.
    I. Startup NOMOUNT:
    It reads the spfile ($ORACLE_HOME/dbs), if spfile not available then it will check pfile and reads the Control file path/location then allocate the instance.
    II. Startup MOUNT:
    It gets the control file path from pfile/spfile and reads the contents of the control file.
    Control file contains,
    – Names & locations of datafiles and logfiles
    – Checkpoint Information
    – DB name
    – Current SCN
    – All backup information.
    III. alter database OPEN:
    Here, oracle checks the consistency of database. It checks whether same checkpoint number is present in all the datafile and logfile headers. If any mismatch, then oracle stops at mount stage and recovery is needed. If everything is ok it will bring up the database.
    b) export ORACLE_SID=PROD
    ->startup mount restricts exclusive
    ->drop database
    (or) $ dbca -> delete the database -> next -> delete
    c) alter database nomount;
    alter database mount;
    alter database archive log;
    alter database open;(To Bring up the DB)
    alter database close,
    alter database dismount (To Bringdown the DB)    Answer
  7. Explain instance recovery? Who does instance recovery? Where will you check whether the instance recovery happened or not? What files were involved in the Instance recovery?
    a) Instance recovery is the way of bringing back the transactions which are not yet written to the disk by DBWR at the moment of instance failures. Instance failures are possible in multiple ways that are hardware issues, power glitches, network outages, abnormal instance shutdowns (SHUTDOWN ABORT)
    b) SMON does the Instance Recovery.
    => Roll forward the changes in Redo log file.
    => Roll back the uncommitted transaction.
    => De-Allocate or Release the temporary segments that are no longer in use.
    c) We can check it in Alert log.
    d) Redo log file, Archive log, RLBC    Answer
  8. Who read the data into memory? How will you increase the performance of write from DBBC to datafiles?
    a) Server process read the data into Memory (Datafile into DBBC)
    b) To increase the db_writer process,
    Show parameter db_writer
    Sql > alter system set db_writer_processes= 2 scope=spfile;
    and restart the DB.    Answer
  9. What is initrans, maxtrans. What are the default values for table and index?
    a) INITRANS is a block level storage parameter which can be specified while creating an object (table). INITRANS and MAXTRANS parameters are used to control the concurrent access to the same block. There can be a maximum of 255 concurrent sessions that can access a block at any given time. So, the maximum value for MAXTRANS parameter is 255.
    Initrans: Generally, in a block it has one slot in header to fetch the data by a user, at a time single user can fetch the data from a single slot, both users cannot be fetching the data at same time, one user should wait in Queue, so that
    => To Increase the Slots of blocks
    => To access concurrently
    => Min Initrans is (1) and Max upto (255) we can assign
    => For Index we can assign max (2)
    Maxtrans:
    => This is actually, how many users are wait in Queue to enter into a slot to fetch the data in a block.
    => Queue wait to access the block
    b) Default value for Table = 1 and Index = 2    Answer
  10. What is Logging and Nologging? What is use of pctfree/pctused and pct increase?
    a) LOGGING: -Archive log enable (Generated more redo and used undo)
    Everything that write in RLBC to DBBC is Logging.
    NOLOGGING: -Archive log disable (Not generate redo and never used undo)
    It bypasses the writing of the redo log, significantly improving performance. The nologging option is a great way to speed-up inserts and index creation. Unable to do roll forward.
    b) PCTFREE, PCTUSED, and Row Chaining. For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment.
    => PCTFREE is a parameter used for future updates. To avoid Row Migration,10% of free space is available in a block default This means that if the PCTFREE = 10, new rows will be added in the block until it is 90% full.
    => PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the free list. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.
    => PCTINCREASE parameter is used to find how much will each subsequent segment will grow. This value is in %. The Extent will grow with previous extent of 50%.    Answer
  11. How will you find fragmentation? How will you rectify it?
    a) dbms_freespace / dbms_space.space_usage
    b) By using the Move tablespace,CTAS,exp/imp,online redefinition methods     Answer
  12. What is row chain and row migration? How will you find it and rectify it?
    a) Row chaining occurs when a row can’t physically fit into an Oracle block. … Migration of an Oracle row occurs when a row is updated in an Oracle block and the amount of free space in the block is not adequate to store all of the row’s data. The row is migrated to another physical block in the table.
    b) SELECT ‘ANALYZE TABLE ‘||table_name||’ LIST CHAINED ROWS INTO CHAINED_ROWS;     Answer
  13. Why we should analyze a table? How to check if the table/index is analyzed? What is the command to gather stats/analyze?
    a) ANALYZE TABLE causes Oracle to determine how many rows are in the table and how storage is allocated. It also calculates the number of chained rows. The most important pieces of information the optimizer gets from this process are the number of rows and the number of blocks.
    After deleting of any records from the table, we need to analyse the table and gather stat the table, then only it will update in dictionary.
    b) desc user_tables => select last_analyzed,blocks,num_rows, empty_blocks,chain_cnt from user_tables where table_name= ‘tablename’;
    desc user_indexes => select num_rows,last_analyzed from user_indexes where index_name=’indexname’;
    c) -> execute dbms_stats.gather_table_stats (”,’tablename’);
    -> analyse table TABLENAME compute statistics;     Answer
  14. What is difference between SPfile and Pfile?
    SPfile
    SP file is in binary format
    It is maintained in server level
    It should not be edit
    SP file will take backup by using RMAN
    Used above 9i

    Pfile
    Pfile in text format
    It is maintained in Physical/OS level
    It should be edit
    Pfile will not take backup by using RMAN
    Up to 9i     Answer

  15. What is difference between LMT and DMT?

    LMT (Local managed Tablespace)
    DMT (Dictionary managed Tablespace)
    It will store the Extent information in segment header as 0 and 1
    It stores free space usage info as UET
    LMT will create while we create DB using DBCA
    UET$ (Used Extent) FET$ (Free Extent)     Answer
  16. When and how to rebuild index?
    a) When the Index in unusable state, then we need to rebuild the index for usable state.
    Sql > alter Index INDEXNAME rebuild parallel;     Answer
  17. What are the types of partitions? Explain composite partitions?
    a) Range, List, Hash, Interval, Reference, System and Composite partition
    b) Composite partition is nothing but sub-partition     Answer
  18. What is different between system/hash partitions?
    Both are randomly allocating the partitions by its own and there is some slight changes are there. Answer
  19. what is partition PRUNING?
    a) Elimination of the unneeded partitions
    b) In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list.     Answer
  20. How will you check the following?
    -> Table/index/view size => User_segments
    -> free space in database/tablespace =>dba_free_space
    -> used space in database/tablespace => Select sum(bytes)1024/1024 from dba_segments
    -> how many indexes present for a table? check indexes columns and its position? on what function the index is created?
    a) We can create Index more than one Column in a table, it depends upon the column of the table (Unlimited).
    b) desc dba_ind_columns
    c) Global Index – Upper or Lower => desc dba_ind_expressions     Answer






<Main> <Next>