Performance Optimization by avoiding Table Locks

Performance Optimization by avoiding Table Locks

Locking is a very important part of any database system. Database table locks are coordinated through global inter-instance communication for RAC. It is the fact that properly designed applications need not to lock entire tables and therefore table locks can be disabled to improve locking efficiency with minimal adverse side effects.

Refer: http://www.articles.freemegazone.com/oracle-table-locks.php

Database Security

Database Security: Step by step guideline

As a database administrator one of my prime responsibilities is to secure my organization's database from unauthorized access. Developing a database is not difficult at all but developing a secure database is really a complicated task.

Refer: http://www.articles.freemegazone.com/databaseSecurity_guidelines.php

Tuning Database Materialized Views

Tuning Database Materialized Views
A materialized view is a piece of replicated data that must be kept synchronized with the master tables. Materialized Views are a wonderful tool for reducing repetitive I/O. They prevent unnecessary repeating large-table full-table scans, as summaries are computed, over and over.
Tuning materialized views is very challenging. In this article I will give some useful tips for tuning the materialized views.
Refer: http://www.articles.freemegazone.com/oracle-materialized-views.php

Hot Database Jobs

Leading companies all over the world are urgently looking for bright professionals in Engineering and IT fields. Visit Your Correspondingcountry page to see full job descriptions.

Australia Jobs http://www.exposedjobs.com/ausjobs.php

Untied states Jobs:- http://www.exposedjobs.com/usajobs.php

India Jobs:- http://www.exposedjobs.com/indiajobs.php

United Kingdom Jobs:- http://www.exposedjobs.com/ukjobs.php

Pakistan Jobs:- http://www.exposedjobs.com/pakjobs.php

Middle East Jobs:- http://www.exposedjobs.com/mejobs.php

Best Setting for Oracle Multiblock Read Count

Refer: http://www.articles .freemegazone. com/multiblock- read-count- MBRC.php

Performance Impact of Low Cardinality Leading Columns in an Index

Refer: http://www.articles .freemegazone. com/low-cardinal ity-leading- columns-in- index.php

Oracle Index Rebuild Locking Issues

Oracle Index Rebuild Locking Issues
Refer: http://www.articles .freemegazone. com/oracle- index-rebuild- locking.php

The Power of 11g Snapshot Standby Database for Oracle 10g

Refer: http://www.articles.freemegazone.com/11g-snapshot-standby-db-10g.php

ORA-28547: connection to server failed, probable Oracle Net admin error

Cause:
--------
A failure occurred during initialization of a network connection from a client process to the Oracle server: The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Oracle Net administrative setup for database links or external procedure calls. The most frequent specific causes are: -- The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle server. -- The connection uses a connect string which includes an (HS=) specification.

Action:
--------
Check Oracle Net administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the client connection to the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID refers to the correct server. -- Confirm in TNSNAMES.ORA or the equivalent service definition that the connect string does NOT contain (HS=).

Partitioning in Oracle 11g

Partitioning is one of the most sought after options for data warehousing. Almost all Oracle data warehouses use partitioning to improve the performance of queries and also to ease the day-to-day maintenance complexities. Starting with 11G, more partitioning options have been provided and these should reduce the burden of the DBA to a great extent.

Refer: http://orafaq.com/node/1912

Table Fragmentation

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

Refer: http://orafaq.com/node/1936

Complete ETL Process - step by step using the Oracle Warehouse Builder

Refer: http://www.hotcoding.com/dbs/oracle/35440.html

Oracle Warehouse Builder

Refer documentation: http://www.oracle.com/technology/documentation/warehouse.html

There is a table which has 2 date columns as follows:-

date1 date2
05-10-07 15-10-07
10-10-07 20-10-07

The output should be as follows:-

date1 date2 count (no.of days between the date range)
01-10-07 03-10-07 3
05-10-07 20-10-07 16

Query
-------

drop table t purge;
create table t (date1 date, date2 date);
truncate table t;insert into t select dt-dbms_random.value(1,9) date1, dt date2from (select sysdate-dbms_random.value(1+5*level,3+5*level) dt from dual connect by level <= 10);

SQL> with
step1 as (
select date1, date2, case when nvl(lag(date2) over (order by date1, date2), date1-1) < date1 then row_number() over (order by date1, date2) end flag from t ),
step2 as (
select date1, date2, max(flag) over (order by date1, date2) grp from step1)
select min(date1) date1, max(date2) date2, round(max(date2)-min(date1)) daycount from step2 group by grp order by 1;

Oracle RAC Listener

Oracle listener listens for users connecting to the database. It is a small yet important part of Oracle RAC configuration. Most database administrators set listeners by default which may result in wrong configuration.
In this article I will give some efficient tips that will help you ensure optimal and secure configuration for your Oracle listener.

Refer: http://www.articles.freemegazone.com/oracle-rac-listener-dba-tips.php

Improved Statistics Gathering in Oracle 11g

Oracle uses dbms_stats to manage optimizer statistics. The dbms_stats estimates statistics in a better way especially for large partitioned tables and thus results in faster SQL execution plans. dbms_stats package provides several pl/sql procedures to gather statistics for a table, schema, or a database.

Refer: http://www.articles.freemegazone.com/improved-statistics-gathering-oracle-11g.php

Recovering Controlfile without Resetlogs

Oracle always requires you to recover and then open the database with resetlogs. However you do not always need to do a resetlogs when you open the database. If you recreate the controlfile rather than restoring a backup then Oracle allows you to open the database normally. Normally all information in controlfile like RMAN configuration and records, incarnation table etc will be lost so it is preferred to keep the old file.

Refer: http://www.articles.freemegazone.com/recovering-controlfile-without-resetlogs.php

How to trim the spaces between the words in oracle

Ex : the word is 'HAI HELLO'
The o/p should be 'HAI HELLO'

SQL> SELECT REGEXP_REPLACE('hai hello', '[[:blank:]]+', ' ') FROM dual;

REGEXP_RE
--------------
hai hello

ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

If the ORA-01555 error occured along with the ora-22924 error, then the issue is not related with the undo segment but with the LOB segment which is being overwritten.

22924, 00000, "snapshot too old"
Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.

Action: Use a larger version pool.

To fix:
-------
ALTER TABLE tablename MODIFY LOB (lobname) (PCTVERSION 100);
If the pctversion is set 100, then lob images will not get overwritten.

Looks like this happens with many concurrent updates. Snapshots and long running queries need to use an old version of a LOB - that version gets overwritten as PCTVERSION is too small.