Oracle World

Index Online Rebuild Fails with ORA-08104: this index object <#> is being online built or rebuilt".

noreply@blogger.com (Abu Karam) — Fri, 2 Nov 2012 11:50:00 +0400

Symptoms

Index online rebuild fails with error "ORA-08104: this index object 556463 is being online built or rebuilt".

Cause

First online rebuild attempt had failed. Until SMON performs the clean up, subsequent rebuilds against the index will fail with ORA-8104 or ORA-8106.

Solution

DBMS_REPAIR.ONLINE_INDEX_CLEAN() is only available starting in release 10.2

For 10.1, you need to wait for SMON process to finish the cleanup job.

Note: SMON will perform the cleanup and does this once every 60 minutes. SMON cleanup is only successful if there are no transactions against the base table [or [sub]partition] at the time of the attempted cleanup. In an environment where there are likely to be uncommitted transactions, this makes cleanup a bit 'hit and miss'. To speed up the process, you can stop your application which uses the table and wait until the cleanup is done.

How to use DBMS_REPAIR.ONLINE_INDEX_CLEAN ?

noreply@blogger.com (Abu Karam) — Fri, 2 Nov 2012 11:19:00 +0400

Goal

When you try to rebuild an index online for the second time, after terminating the first session you tried to rebuild the index online, you get the following error :

ORA-08104: this index object ##### is being online built or rebuilt

Fix

To resolve this issue you should refer to the following method run the rebuild using DBMS_REPAIR.ONLINE_INDEX_CLEAN function:

--Parameters--:

object_id : ALL_INDEX_ID (= 0) : cleanup all index objects that qualify

> 0 : cleanup sepcified index object

wait_for_lock : LOCK_WAIT=1 : retry getting DML locks on underlying table [[sub]partition] object.

There is an internal retry limit, after which the lock get will give up

LOCK_NOWAIT=0: do not retry

--Returns--:

TRUE : all indexes specified were cleaned up

FALSE : one or more indexes could not be cleaned up. Some indexes may have been cleaned up succesfully.

--Exceptions--:

ORA-24120 covers the following:

a negative (or NULL) index object_id was specified

a negative (or NULL) wait_for_lock was specified the given object_id was not an index object that needed cleanup

--Example Usage of online_index_clean--:

DECLARE

isClean BOOLEAN;

BEGIN

isClean := FALSE;

WHILE isClean=FALSE

LOOP

isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID, DBMS_REPAIR.LOCK_WAIT);

DBMS_LOCK.SLEEP(10);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END;

NOTE: This operation is a maintenance operation and it will take a large down window if the indexes are large.

Index Online Rebuild Fails with ORA-08104: this index object <#> is being online built or rebuilt".

noreply@blogger.com (Abu Karam) — Fri, 2 Nov 2012 11:03:00 +0400

Symptoms

Index online rebuild fails with error "ORA-08104: this index object 556463 is being online built or rebuilt".

Cause

First online rebuild attempt had failed. Until SMON performs the clean up, subsequent rebuilds against the index will fail with ORA-8104 or ORA-8106.

Solution

DBMS_REPAIR.ONLINE_INDEX_CLEAN() is only available starting in release 10.2

For 10.1, you need to wait for SMON process to finish the cleanup job.

Physical and Logical Block Corruptions

noreply@blogger.com (Abu Karam) — Tue, 30 Oct 2012 12:24:00 +0400

Oracle classifies the Data File Block corruptions as Physical and Logical. This is also referred as intra block corruptions. This document is intended to provide detailed information and errors example about it.

Physical Block Corruptions

This kind of block corruptions are normally reported by Oracle with error ORA-1578 and the detailed corruption description is printed in the alert log.

Corruption Examples are:

Bad header - the beginning of the block (cache header) is corrupt with invalid values
The block is Fractured/Incomplete - header and footer of the block do not match
The block checksum is invalid
The block is misplaced
Zeroed out blocks / ORA-8103

Detailed Corruption Description:

Fractured Block

A Fractured block means that the block is incomplete. Information from the block header does not match the block tail.

Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380e573
last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
consistency value in tail: 0x00780601
check value in block header: 0x8739, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data

Bad Checksum

Block Checksums are used to identify if the block was changed by something external to Oracle and after the block was last written by Oracle.
Checksum is calculated by DBWR or direct loader before writing the block to disk and stored in the block header. Every time that the block is read and if db_block_checksum is different than false, Oracle calculates a checksum and compares it to the one stored in the block header. Reference Note 30706.1

Example of a corrupt block due to invalid checksum:

Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
Bad check value found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380a58f
last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
consistency value in tail: 0xc5ee0601
check value in block header: 0x68a7, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data

A value different than zero (0x0) in "computed block checksum" means that the checksum
differs and the result of this comparison is printed.

Block Misplaced

This is when Oracle detected that the content of the block being read belongs to a different block and the checksum is valid:

Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
Bad header found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0d805b08 ----> Block is different than expected 0x0d805a89
last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
consistency value in tail: 0x08e30601
check value in block header: 0x2a6e, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Logical Block Corruptions

This is when block contains a valid checksum and the structure below the beginning of the block is corrupt (Block content is corrupt). It may cause different ORA-600 errors.

The detailed corruption description for Logical Corruptions are not normally printed in the alert.log. DBVerify will report what is logically corrupted in the block.

Corruption Examples are:

row locked by non-existent transaction - ORA-600 [4512],etc
the amount of space used is not equal to block size
avsp bad
etc.

When db_block_checking is enabled, it may produce the internal errors ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError].

If db_block_checking is enabled and the block is already logically corrupt on disk, the next block update will mark the block as Soft Corrupt and future reads of this block will produce the error ORA-1578. In that case DBVerify reports this corruption with error "DBV-200: Block, dba <rdba>, already marked corrupted".

DROP MATERIALIZED VIEW hangs with 'enq: JI - contention'

noreply@blogger.com (Abu Karam) — Tue, 2 Oct 2012 08:43:00 +0400

When running a DROP MATERIALIZED VIEW the session hangs Due to the JI contention.

Solution

1. Killed the running job. Check DBMS_JOBS_RUNNING view here.

2. Take the refresh job offline and removed the job.

3. Finally dropped the MView BR_SEED.ZIP_US which worked , we do not see the job or the Mview any more.

Nonclustered Index Structures

noreply@blogger.com (Abu Karam) — Wed, 26 Sep 2012 09:17:00 +0400

Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

Nonclustered indexes have one row in sys.partitions with index_id >0 for each partition used by the index. By default, a nonclustered index has a single partition. When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit. For more information about allocation units, see Table and Index Organization. The page collections for the B-tree are anchored by root_page pointers in thesys.system_internals_allocation_units system view.

Using Clustered Indexes

noreply@blogger.com (Abu Karam) — Wed, 26 Sep 2012 09:15:00 +0400

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached. This can help increase the performance of this type of query. Also, if there is a column(s) that is used frequently to sort the data retrieved from a table, it can be advantageous to cluster (physically sort) the table on that column(s) to save the cost of a sort each time the column(s) is queried.

Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

Alternatively, a clustered index could be created on lname, fname (last name, first name), because employee records are often grouped and queried in this way rather than by employee ID

Create Filtered Indexes

noreply@blogger.com (Abu Karam) — Wed, 26 Sep 2012 09:14:00 +0400

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

· Improved query performance and plan quality

A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

· Reduced index maintenance costs

An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

· Reduced index storage costs

Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Design Considerations

· When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

· When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

SQL Server Surface Area Configuration in SQL Server 2008

noreply@blogger.com (Abu Karam) — Wed, 26 Sep 2012 08:56:00 +0400

Surface area reduction is a security measure that involves stopping or disabling unused components. Surface area reduction helps to improve security by providing fewer avenues for potential attacks on a system.

For new installations of Microsoft SQL Server 2005, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state.

Use SQL Server Surface Area Configuration to enable, disable, start, or stop the features, services, and remote connectivity of your SQL Server 2005 installations. You can use SQL Server Surface Area Configuration on local and remote servers.

SQL Server Surface Area Configuration uses Window Management Instrumentation (WMI) to view and change server settings. WMI provides a unified way for interfacing with the API calls that manage registry operations that configure SQL Server. For information about configuring permissions related to WMI, see the topic How to: Configure WMI to Show Server Status in SQL Server Tools.

Page compression in SQL Server 2008

noreply@blogger.com (Abu Karam) — Tue, 25 Sep 2012 13:50:00 +0400

Until SQL Server 7.0 was released, the page size in SQL Server was 2K; now the page size is 8K.

Microsoft introduced page compression in SQL Server 2008. This new feature is available in the Enterprise edition and developer edition. The page compression feature of SQL Server can compress the entire page.

Page compression is applied only when the page gets full. When page compression occurs there are three operations that happen in the following order:

Row compression
Prefix compression
Dictionary compression

Difference between logical & physical standby database?

noreply@blogger.com (Abu Karam) — Mon, 24 Sep 2012 12:48:00 +0400

In Oracle Data Guard you, Oracle transfers data from the main database to a standby database, and in case of failures, Oracle will switch over to the standby database. We have two ways to create a standby database, logical standby and physical standby:

Physical standby differs from logical standby:

Physical standby schema matches exactly the source database.
Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode. Upon arrival, the archived redo logs are applied directly to the standby database.

Logical standby is different from physical standby:

Logical standby database does not have to match the schema structure of the source database.
Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.
Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.
Logical standby database can have additional materialized views and indexes added for faster performance.

Installing Physical standbys offers these benefits:

An identical physical copy of the primary database
Disaster recovery and high availability
High Data protection
Reduction in primary database workload
Performance Faster

Installing Logical standbys offer:

Simultaneous use for reporting, summations and queries
Efficient use of standby hardware resources
Reduction in primary database workload
Some limitations on the use of certain datatypes

Oracle 10g Data Guard – Quick Switchover with Physical Standby Database

noreply@blogger.com (Abu Karam) — Sun, 23 Sep 2012 13:50:00 +0400

Once the standby database is setup using Data Guard and works properly, you may want to test switchover, or perform switchover to reduce primary database downtime during OS upgrades or hardware upgrades. (To set up a Physical Standby database, please refer to my OraFAQ blog "Oracle 10g – Manually Create a Physical Standby Database Using Data Guard".)

A switchover allows the primary database to switch roles with its standby database. There is no data loss during a switchover. You can switch back to the original Primary database later by performing another switchover.

In case of primary database failure, you will need to perform failover to transition the standby database to the primary role. After a failover, the original primary database can no longer participate in the Data Guard configuration. So if the original Primary database is still accessible, you should always consider a switchover first.

This document only talks about switchover involving physical standby database. In this example, the original primary data is called PRIM and the original standby database is called STAN.

I. Before Switchover:

1. As I always recommend, test the Switchover first on your testing systems before working on Production.

2. Verify the primary database instance is open and the standby database instance is mounted.

3. Verify there are no active users connected to the databases.

4. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

SQL>select sequence#, applied from v$archvied_log;

Perform SWITCH LOGFILE if necessary.

In order to apply redo data to the standby database as soon as it is received, use Real-time apply.

II. Quick Switchover Steps

1. Initiate the switchover on the primary database PRIM:

SQL>connect /@PRIM as sysdba

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. After step 1 finishes, Switch the original physical standby db STAN to primary role;

Open another prompt and connect to SQLPLUS:

SQL>connect /@STAN as sysdba

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Immediately after issuing command in step 2, shut down and restart the former primary instance PRIM:

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

4. After step 3 completes:

- If you are using Oracle Database 10g release 1, you will have to Shut down and restart the new primary database STAN.

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

- If you are using Oracle Database 10g release 2, you can open the new Primary database STAN:

SQL>ALTER DATABASE OPEN;

STAN is now transitioned to the primary database role.

5. On the new primary database STAN, perform a SWITCH LOGFILE to start sending redo data to the standby database PRIM.

SQL>ALTER SYSTEM SWITCH LOGFILE;

Reference:

1. Oracle Data Guard Concepts and Administration 10g Release 2 (10.2), B14239-04;

2. Switchover and Failover Best Practices: Oracle Data Guard 10g release 2 by Oracle.

How To Open The Standby Database When The Primary Is Lost

noreply@blogger.com (Abu Karam) — Sun, 23 Sep 2012 13:41:00 +0400

Startup Mount

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 4 19:51:12 2009

Connected to an idle instance.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 218103808 bytes

Fixed Size 1260984 bytes

Variable Size 184549960 bytes

Database Buffers 29360128 bytes

Redo Buffers 2932736 bytes

Database mounted.

Check Status

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE PROTECTION_MODE DATABASE_ROLE

---------- -------------------- ----------------

MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBYHow To Open The Standby Database When The Primary Is Lost

3/5 2/4/2009

Recover if you have logs to apply

In this example the primary is lost and I don't have more archived logs to apply:

SQL> RECOVER STANDBY DATABASE;

ORA-00279: change 794348 generated at 12/29/2008 12:23:02 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/oradata/dgfdb/archive/1_49_633452428.dbf

ORA-00280: change 794348 for thread 1 is in sequence #49

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

'/u01/app/oracle/oradata/dgfdb/archive/1_49_633452428.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> !ls -l /u01/app/oracle/oradata/dgfdb/archive/

total 31072

-rw-r----- 1 oracle oinstall 1158656 Dec 29 11:31 1_37_633452428.dbf

-rw-r----- 1 oracle oinstall 7385600 Dec 29 11:31 1_38_633452428.dbf

-rw-r----- 1 oracle oinstall 4941824 Dec 29 11:31 1_39_633452428.dbf

-rw-r----- 1 oracle oinstall 13739008 Dec 29 11:31 1_40_633452428.dbf

-rw-r----- 1 oracle oinstall 2272256 Dec 29 11:50 1_41_633452428.dbf

-rw-r----- 1 oracle oinstall 1024 Dec 29 11:51 1_42_633452428.dbf

-rw-r----- 1 oracle oinstall 89088 Dec 29 11:51 1_43_633452428.dbf

-rw-r----- 1 oracle oinstall 1847296 Dec 29 12:18 1_44_633452428.dbf

-rw-r----- 1 oracle oinstall 135680 Dec 29 12:18 1_45_633452428.dbf

-rw-r----- 1 oracle oinstall 67584 Dec 29 12:19 1_46_633452428.

-rw-r----- 1 oracle oinstall 34816 Dec 29 12:22 1_47_633452428.dbf

-rw-r----- 1 oracle oinstall 2048 Dec 29 12:22 1_48_633452428.dbf

Finish the Recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Activate the Standby Database

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the new status

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE PROTECTION_MODE DATABASE_ROLE

---------- -------------------- ----------------

MOUNTED MAXIMUM PERFORMANCE PRIMARY

Open the Database

SQL> ALTER DATABASE OPEN

Database altered.

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE PROTECTION_MODE DATABASE_ROLE

---------- -------------------- ----------------

READ WRITE MAXIMUM PERFORMANCE PRIMARY

SQL Server Snapshots

noreply@blogger.com (Abu Karam) — Tue, 28 Aug 2012 10:34:00 +0400

A snapshot is a read-only copy of another database, made at a point in time. Any changes to the original database cause the version of the data when the snapshot was taken to get written to the file used by the snapshot. Therefore, there's a performance hit involved, but it can be very useful for knowing exactly what your database looked like at some point in the past (when you told the snapshot to be created).

It's definitely worth noting that the snapshot contains no data of its own when first created, as it can reference the original database for it, at least until the original database is changed.

When a snapshot is first created, it is an empty shell that delegates all queries (a snapshot is read only) to the original database.

As changes are made to the original database, the pages involved are copied to the snapshot. Queries of the snapshot at this point will be performed on a logical database that is the result of layering the pages in the snapshot over those in the original database.

The effect is that the snapshot appears to be a complete copy of the original database that was made at the same time as the snapshot was created.

One scenario in which this can be useful is in deploying changes. The snapshot can be a very inexpensive form of insurance if something goes wrong. Assuming that only a subset of the pages within the original database were modified during the deployment, only that subset of the pages will need to be copied back from the snapshot to the original database during a restore

The “NOT FOR REPLICATION” option in SQL Server Replication:

noreply@blogger.com (Abu Karam) — Thu, 23 Aug 2012 14:20:00 +0400

The NOT FOR REPLICATION option allows you to specify that the following database objects are

treated differently when a replication agent performs an operation:

* Foreign key constraints

The foreign key constraint is not enforced when a replication agent performs an insert, update, or

delete operation.

* Check constraints

The check constraint is not enforced when a replication agent performs an insert, update, or delete

operation.

* Identity columns

The identity column value is not incremented when a replication agent performs an insert

operation.

* Triggers

The trigger is not executed when a replication agent performs an insert, update, or delete

What is filtered index

noreply@blogger.com (Abu Karam) — Thu, 23 Aug 2012 14:19:00 +0400

A filtered index is an optimized nonclustered index, especially suited to cover queries that select

from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.

A well-designed filtered index can improve query performance, reduce index maintenance costs,

and reduce index storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

Improved query performance and plan quality

Reduced index maintenance costs

Reduced index storage costs

How to set the instance to utilize one thread for searches which owns an estimated execution cost less than 6.

noreply@blogger.com (Abu Karam) — Thu, 23 Aug 2012 14:18:00 +0400

Change the sp_configure cost threshold for parallelism option.

Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

Only set cost threshold for parallelism on symmetric multiprocessors.

Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate the plan. The cost threshold for parallelism option is actively used when a mix of short and longer queries is executed. The short queries execute serial plans while the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, thus executing only serial plans.

In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. This is because the decision to use a parallel or serial plan, with respect to cost threshold for parallelism, is based on a cost estimate provided before the full optimization is complete.

The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.

If your computer has only one processor, if only a single CPU is available to SQL Server because of the affinity mask configuration value, or if the max degree of parallelism option is set to 1, SQL Server ignores cost threshold for parallelism.

cost threshold for parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cost threshold for parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and

restart).

How to Create Full SQL Database backup commands

noreply@blogger.com (Abu Karam) — Wed, 22 Aug 2012 10:44:00 +0400

Execute the BACKUP DATABASE statement to create the full database backup, specifying:

* The name of the database to back up.

* The backup device where the full database backup is written.

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE database

TO backup_device [ ,...n ]

[ WITH with_options [ ,...o ] ] ;

Option Description

database

Is the database that is to be backed up.

backup_device [ ,...n ]

Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a

physical backup device, or you can specify a corresponding logical backup device, if already

defined. To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } =physical_backup_device_name

For more information, see Backup Devices.

WITH with_options [ ,...o ]

Optionally, specifies one or more additional options, o. For information about some of the basic

with options, see step 2.

Optionally, specify one or more WITH options. A few basic WITH options are described here. For

information about all the WITH options, see BACKUP (Transact-SQL).

* Basic backup set WITH options:

{ COMPRESSION | NO_COMPRESSION }

In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed

on this backup, overriding the server-level default.

Reports Cache Directory in Oracle E-Business R12 is Growing Rapidly

noreply@blogger.com (Abu Karam) — Wed, 22 Aug 2012 09:59:00 +0400

The cache files are not being deleted when the cacheSize property is exceeded and files are also not deleted even after enabling and setting maxCacheFileNumber, which is the documented method.

Solution

Workaround:

1- Shutdown the Concurrent Processing server.
2- Manually delete all the temporary files under the $INST_TOP/logs/ora/10.1.2/reports/cache path
3- Startup up the Concurrent Processing server.

Change the Oracle Reports configuration file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf and bouncing the services.

CHANGE:
<property name="cacheSize" value="50"/> to
<property name="cacheSize" value="0"/>

NOTE: This change will be lost when Autoconfig is run. Apply patch 8602992 as soon as it becomes available.

Permanent Fix:

1- Apply the following Patch 8602992.

Important Note: If facing Intermittent Oracle Reports REP-0069: REP-57054 Errors after applying the patch above please review Note 1237834.1 - Intermittent Oracle Reports REP-0069: REP-57054: Error

EBS R12 Oracle Reports Cache Directory Not Being Cleaned Up

noreply@blogger.com (Abu Karam) — Wed, 22 Aug 2012 09:59:00 +0400

Applies to:

Oracle Applications Technology Stack - Version 12.0 to 12.0 [Release 12.0]
Information in this document applies to any platform.
Oracle Reports, Cache, Cleaned Up, $INST_TOP, purge, disk space, temporary, files

Symptoms

In E-Business Suite (EBS) Release 12, when concurrent requests of the
execution method of Oracle Reports are submitted, temporary files are present
under the path of $INST_TOP/logs/ora/10.1.2/reports/cache.

The temporary files are identical to the file placed under the
$APPLCSF/$APPLOUT path, however. These temporary files are not purged when
the "Purge Concurrent Request and/or Manager Data" (FNDCPPUR) program runs.

Cause

New Bug 8449763 / 8602992 due to the new architecture of EBS R12

Solution

1. Perform both of the following actions:

a) Apply patch 8602992 "APPS1012: CACHE DIRECTORY NOT BEING CLEANED OUT"--currently available via MOS (Metalink).

b) Apply technology stack template fix patch 9032800 "SET CACHESIZE=0 RWBUILDER_CONF_1012.TMP"--as soon as it becomes available.

As a current workaround to patch 9032800:

Change the Oracle Reports configuration file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf and bouncing the services.

CHANGE:
<property name="cacheSize" value="50"/> to
<property name="cacheSize" value="0"/>

NOTE: This change will be lost when Autoconfig is run. Apply patch 8602992 as soon as it becomes available.

NOTE 2: Patch 8602992 will ensure that the Oracle Report rwrun executable deletes the cache file after the report runs, however. Any existing cache file prior to the fix will need to be deleted manually

Clustered and Non-Clustered Index in SQL

noreply@blogger.com (Abu Karam) — Sat, 18 Aug 2012 00:30:00 +0400

When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a "heap". As you would expect, the data we will insert into the table will be returned in the same order. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Clustered index is good when you know in which order you will be returning the records in most cases. You can create clustered index and after that you don't need to use ORDER BY statement. This will be much more faster. If the order is not important for you and will not create clustered index by yourself, then primary key will be clustered index by default. There is nothing bad not to have the clustered index, it can speed up inserting rows.

You can access www.free-online-exams.com for the whole latest version of the exam

Alter Schema in SQL Server, how to move a table, such as ABC_table, from its current scheme (scheme1) to another scheme (scheme2)

noreply@blogger.com (Abu Karam) — Thu, 16 Aug 2012 17:34:00 +0400

ALTER SCHEMA schema2 TRANSFER schema1.ABC_table;

ALTER SCHEMA can only be used to move securables between schemas in the same database.

To change or drop a securable within a schema, use the ALTER or DROP statement specific to

that securable.

You can access www.free-online-exams.com for the whole latest version of the exam

How to allow users on SQL Server to use the OPENROWSET() function to search remote information sources.

noreply@blogger.com (Abu Karam) — Thu, 16 Aug 2012 17:34:00 +0400

01. sp_configure 'show advanced options', 1

02. RECONFIGURE

03.sp_configure 'Ad Hoc Distributed Queries', 1

04. RECONFIGURE

05. GO

06.

07. SELECT a.*

08. FROM OPENROWSET('SQLNCLI', 'Server=Madrid;Trusted_Connection=yes;',

09. 'SELECT GroupName, Name, DepartmentID

10. FROM AdventureWorks.HumanResources.Department

11. ORDER BY GroupName, Name') AS a;

12. GO

By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and

OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this

option is not set or is set to 0, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to

connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE

should be used only to reference OLE DB data sources that are accessed infrequently. For any

data sources that will be accessed more than several times, define a linked server.

You can access www.free-online-exams.com for the whole latest version of the exam

ALTER LOGIN CHECK_POLICY = { ON | OFF }

noreply@blogger.com (Abu Karam) — Thu, 16 Aug 2012 17:33:00 +0400

If you have the following below requirements:

1. SQL Server 2008 logins must have the same password complexity rules which are enforced by

Windows Server for authentication.

2. Full compliance of all users with regarding to adhering to the password complexity rules

Answer:

Applies only to SQL Server logins. Specifies that the Windows password policies of the computer

on which SQL Server is running should be enforced on this login. The default value is ON.

ALTER LOGIN CHECK_POLICY = { ON | OFF }

You should consider using the ALTER LOGIN ... CHECK_POLICY = ON statement to change

the entire login of all users.

Create a policy using Policy-Based Management that matches the requirements

Policy-Based Management is a system for managing one or more instances of SQL Server 2008.

When SQL Server policy administrators use Policy-Based Management, they use SQL Server

Management Studio to create policies to manage entities on the server, such as the instance of

SQL Server, databases, or other SQL Server objects.

You can access www.free-online-exams.com for the whole latest version of the exam

How to find out the SQL with most redo log generation

noreply@blogger.com (Abu Karam) — Wed, 7 Mar 2012 08:38:00 +0400

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.
We can query Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;

We need to run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

This just give me an idea about what is currently happening. But I want more historical information. Let us say for last 3 days, what was the SQLs involed in most redo.
I did generate an AWR for last two days but that did not gave me the information that I was looking for. So I decided to directly query DBA_HIST views.

I found a way to get the hostorical db block change information from DBA_HIST views. dba_hist_seg_stat view has a column called db_block_changes_delta column. We can query this view and find out the sum of block changes per object.

SELECT dhso.object_name,
sum(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('2011_05_25 08′,'YYYY_MM_DD HH24′) AND to_date('2011_05_27 00′,'YYYY_MM_DD HH24′)
GROUP BY dhso.object_name
order by sum(db_block_changes_delta) desc
/
WINDOW_EVENTS 809200
WINDOWS 153968
HISTGRM$ 65040
I_H_OBJ#_COL# 61168
MODEL_REPORT 31840
DATA_PROCESS 16784
RULE_EXECUTE_INFO 12448
PK_MODEL_REPR 9088

Above query gives the sum(db_block_changes_delta). Now we can use dba_hist_sqlstat view to get the SQL statements. So I did run another query to see what SQLs are run on WINDOW_EVENTS or WINDOW during same period.

SELECT distinct dbms_lob.substr(sql_text,4000,1),
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%WINDOW%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2;
INSERT INTO WINDOWS (
EVENT_ID,
DATA_PROCESS_ID,
EVENT_TIME,
DENOMINATOR_STR,
TIME_WINDOW_ID,
TIME_WINDOW_PANE,
OWNER_ORG_ID
)
INSERT INTO WINDOW_EVENTS (
EVENT_ID ,
DATA_PROCESS_ID ,
EVENT_TIME ,
EVENT_EPOCH_TIME ,
OWNER_ORG_ID ,
DENOMINATOR_STR
)

This is just the part of the output. There was a lot this insert during that same period. Now I know which SQL was actually generating a lot of redo log.