Parameters that control the behavior of Query Optimizer

1)CURSOR_SHARING: •The optimizer generates the execution plan based on the presence of the bind variables but not the actual literal values. •Based on the settings of this parameter -CURSOR_SHARING it converts literal values in SQL statements to bind variables and affect the execution plan of SQL statements. •This parameter determines what kind of SQL statements [...]

Read Full Post »

About PGA_AGGREGATE_TARGET parameter

In order to make you understand About PGA_AGGREGATE_TARGET parameter let’s have a look at parameter *_AREA_SIZE. SQL> SHOW PARAMETER _AREA_SIZE NAME TYPE VALUE ———————————— ———– —————————— bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 131072 sort_area_size integer 65536 workarea_size_policy string AUTO Here we see the parameter workarea_size_policy is set to AUTO because we have set [...]

Read Full Post »

Pre-Installation RAC environement setup check with CVU

Checking the Network Setup with CVU To verify node connectivity among all nodes in your cluster use following syntax as an oracle user, /mount_point/crs/Disk1/cluvfy/runcluvfy.sh comp nodecon -n node_list [-verbose] If you have two nodes node1 and node2 and your mountpoint is /dev/cdrom then enter following command. /dev/cdrom/crs/Disk1/cluvfy/runcluvfy.sh comp nodecon -n node1,node2 -verbose Checking the Hardware [...]

Read Full Post »

Configure server to install Oracle RAC

Configuring Operating System Users and Groups in All Nodes On windows you don’t need to create separate user to install oracle or don’t need to create groups. OUI automatically do it. However on unix you must create the following operating system groups are required if you are installing Oracle RAC for the first time. •The [...]

Read Full Post »

Overview of Oracle Clusterware and Oracle RAC

Now we are going to learn RAC environment. In a database whenever you query from V$database to know the name of the database and want to know the name of the instance from v$thread they return similar result. Suppose, SQL> select name from v$database; NAME ——— DBASE SQL> select instance from v$thread; INSTANCE ——————————————————————————– dbase [...]

Read Full Post »

Table fragmentation & how to avoid same

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 [...]

Read Full Post »

How to avoide Tablespace fragmentation

In previuos post we disscused how to find tablespace frgmantation.Here we will disscus how to avoide table fragmentation in oracle. Once we identify tablespace fragmentation , what do you do about it?  Honeycomb fragmentation is easy to fix.  All that needs to be done is to combine adjacent free segments into one by issuing a coalesce  statement: [...]

Read Full Post »

Parameters that enable and control Query Optimizer Features

You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now [...]

Read Full Post »