Technical Blog

Mar 23, 2016

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

Mar 17, 2016

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