21). Large Pool is used for what ?
The large pool is an optional memory area and provides large memory allocations for:
- Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
- I/O server processes, buffer area
- Oracle backup and restore operations (RMAN)
- User Global Area (UGA) for shared servers
22). What is PCT Increase setting ?
PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow.
The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.
Preventing tablespace fragmentation
Try to set PCTINCREASE to 0 or 100. Bizarre values for PCTINCREASE will contribute to fragmentation.
For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety.
PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.
Locally Managed tablespaces (available from Oracle 8i onwards) with uniform extent sizes virtually eliminates any tablespace fragmentation.
Note that the number of extents per segment does not cause any performance issue anymore, unless they run into thousands and thousands where additional I/O may be required to fetch the additional blocks where extent maps of the segment are stored.
23). What is PCTFREE and PCTUSED Setting ?
PCTFREE is a block storage parameter used to specify how much space should be left in a database block for future updates.
For example, for PCTFREE=10, Oracle will keep on adding new rows to a block until it is 90% full. This leaves 10% for future updates (row expansion).
When using Oracle Advanced Compression, Oracle will trigger block compression when the PCTFREE is reached. This eliminates holes created by row deletions and maximizes contiguous free space in blocks.
See the PCTFREE setting for a table:
SQL> SELECT pct_free FROM user_tables WHERE table_name = ‘EMP’;
PCTUSED is a block storage parameter used to specify when Oracle should consider a database block to be empty enough to be added to the freelist. Oracle will only insert new rows in blocks that is enqueued on the freelist.
For example, if PCTUSED=40, Oracle will not add new rows to the block unless sufficient rows are deleted from the block so that it falls below 40% empty.
24). What is Row Migration and Row Chaining ?
Row Migration refers to rows that were moved to another blocks due to an update making them too large to fit into their original blocks.
Oracle will leave a forwarding pointer in the original block so indexes will still be able to “find” the row. Note that Oracle does not discriminate between chained and migrated rows, even though they have different causes. A chained row is a row that is too large to fit into a single database data block.
For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces.
Some conditions that will cause row chaining are:
- Tables whose row size exceeds the blocksize
- Tables with long and long raw columns are prone to having chained rows
- Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
Detecting row chaining:
This query will show how many chained (and migrated) rows each table has:
SQL>SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;
To see which rows are chained:
SQL>ANALYZE TABLE tablename LIST CHAINED ROWS;
This will put the rows into the INVALID_ROWS table which is created by the utlvalid.sql script (located in $ORACLE_HOME/rdbms/admin).
25). What is ORA-01555 – Snapshot Too Old error and how do you avoid it ?
The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 11:30 AM, Oracle ensures that all rows are as they appeared at 11:30 AM, even if the query runs until noon!
Oracles does this by reading the “before image” of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear. ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:
ORA-01555: snapshot too old: rollback segment number string with name “string” too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.
You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tables.
However, you can set a super-high value for undo_retention and still get an ORA-01555 error.
The ORA-01555 snapshot too old error can be addressed by several remedies:
- Re-schedule long-running queries when the system has less DML load
- Increasing the size of your rollback segment (undo) size
- The ORA-01555 snapshot too old also relates to your setting for automatic undo retention
- Don’t fetch between commits
26). What is a Locally Managed Tablespace ?
Locally Managed Tablespace is a tablespace that record extent allocation in the tablespace header.
Each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.
Advantages of Locally Managed Tablespaces:
- Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
- Reduce contention on data dictionary tables (single ST enqueue)
- Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
- Changes to the extent bitmaps do not generate rollback information
27). Can you audit SELECT statements ?
YES. But beware, you will need a storage mechanism to hold your SQL SELECT audits, a high data volume that can exceed the size of your whole database, everyday.
SQL SELECT auditing can be accomplished in several ways:
- Oracle audit table command: audit SELECT table by FRED by access;
- Oracle Fined-grained Auditing
In a busy database, the volume of the SELECT audit trail could easily exceed the size of the database every data.
Plus, all data in the audit trail must also be audited to see who has selected data from the audit trail.
28). What does DBMS_FGA package do ?
The DBMS_FGA package provides fine-grained security functions. DBMS_FGA is a PL/SQL package used to define Fine Grain Auditing on objects.
DBMS_FGA Package Subprograms:
- ADD_POLICY Procedure – Creates an audit policy using the supplied predicate as the audit condition
- DISABLE_POLICY Procedure – Disables an audit policy
- DROP_POLICY Procedure – Drops an audit policy
- ENABLE_POLICY Procedure – Enables an audit policy
29). What is Cost Based Optimization ?
The Oracle Cost Based Optimizer (CBO) is a SQL Query optimizer that uses data statistics to identify the query plan with lowest cost before execution. The cost is based on the number of rows in a table, index efficiency, etc.
All applications should be converted to use the Cost Based Optimizer as the Rule Based Optimizer is not be supported in Oracle 10g and above releases.
30). How often you should collect statistics for a table ?
Analyse if it’s necessary!
– Refresh STALE statistics before the batch processes run but only for tables involved in batch run,
– Don’t do it if you don’t have to.
– Oracle databse has default, scheduled job “gather_stats_job” that analyses stats on a daily basis during the maintenance window time.