Oracle in Big Data World

High Water Mark

High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.


When an INSERT statement cannot find a free block to populate, it goes straight to the High Water Mark and allocates a new block.When rows are deleted, the High Water Mark does not come down.

Inorder to remove the empty blocks in the HWM you need to issue the following two commands


ALTER TABLE emp ENABLE ROW MOVEMENT;
ALTER TABLE emp SHRINK SPACE;

ROWIDs are normally assigned to a row for the life time of the row at insert time.  Oracle can move a table row using the first command .

It will read the table, it will delete/insert the rows at the bottom of the table to move them up, it will generate redo, it will generate undo thus can relocate and reorganize rows in a table including it will change rowids


The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.
 - can be done online
 - indexes will be maintained and remain usable

Monitoring Index

CREATE TABLE t1 (c1 NUMBER);
CREATE INDEX t1_idx ON t1(c1);
ALTER INDEX t1_idx MONITORING USAGE;
SELECT table_name, index_name, monitoring, used FROM v$object_usage;

SELECT * FROM t1 WHERE c1 = 1;


SELECT table_name, index_name, monitoring, used FROM v$object_usage;

ALTER INDEX indexname NOMONITORING USAGE;
ALTER INDEX indexname MONITORING USAGE;

Some points in Performance Tuning

Try to avoid using  following in triggers

1) UTL_ packages .  They do not roll back
2) Pragma autonomous_transaction only use if it is unavoidable


Wrong cardinality = Wrong Plan
Check Constraints can rewrite queries

If you want to limit the creation of index however want to increase performance degredated query by refering explain plan . One main point is that you want to check the the highest cardinality which is having FULL TABLE ACCESS in the explain plan , for that particular cardinality check the access predicates in the explain plan and try to create index for those columns in the join (access predicates)

Creating index will help in the performance of a query even though the join is right or left outer join .

Dimensions convey information to the optimizer.The presence of Dimensions open up access paths that would not be otherwise available.


The Forms' PL/SQL code runs in the Forms PL/SQL engine, which is different in PL/SQL engine in database , this is why you cannot access database package's variable, which result in the following error
Implementation Restriction: 'Cannot directly access remote package variable or cursor while compiling oracle forms

More about Indexes

In B tree index single column indexes do not include entries for null values ; but bitmap and function based index can store all null entries .
Multi column indexes will store null values but only if at least one column is not null


1) If all columns contain null values , the entry will not be stored in the index

2) Adding a frequently null valued column to an index can impact performance by allowing the index to filter the nulls

3) If range scanning a column ie: < or >  , put those columns at the end of the composite index