INDEXES

INDEXES

B*Tree index

sql>create index indx_name on table_name (col_name);

Unique index

sql>create unique index ind_name on table_name (col_name);

Composite index

sql>create index ind_name on table_name (col_name,col_name);

Bitmap index

sql>create bitmap index ind_name on table_name (gender);

Functional index

sql>create index ind_name on table_name(upper(col_name));

Reverse Key index

sql>create index ind_name on table_name(col_name) reverse;

IOT (Index Organized Table)

sql>create table table_name (col_name datatype primary key,col_name datatype)

 organization index;

Materialized views: Materialized view store a local copy of the base table records where as normal tables fetch records from the base tables;

SQL> create materialized view mv_emp as select rollno,name from student;  

// materialized view created

SQL> execute DBMS_MVIEW.REFRESH (‘mv_emp’,’c’);

 // refreshing the materialized view

NOTE: Materialized view can also be automatically refreshed by using the START WITh <date> NEXT <date>.

SQL> create materialized view mv1_emp refresh complete start with sysdate next sysdate+1 as select rollno,name,age from student;

// view will be created and refreshed right now (sysdate) and there after once every day at the same time (sysdate+1).

SQL> drop materialized view mv_emp;

 // materialized view dropped SQL> drop view mv_emp; // view dropped

Creating Partition Table:

Range Partition :

sql>create table emp(empno number(10),ename varchar2(10),sal number(10))

  partition by range(empno)

(

partition e1 values less than (100) tablespace tab1,

 partition e2 values less than (200) tablespace tab2,

partition e3 values less than (300) tablespace tab3,

partition e4 values less than (MAXVALUE) tablespace tab4);

Hash Partition

Sql>create table emp(empno number(10),ename varchar2(10),sal number(10))

Partition by hash(empno)

(

Partition p1 tablespace tab1,

Partition p2 tablespace tab2

);  

List Partition

Sql>create table sales_by_region(deptno number(10),deptname varchar2(20),state varchar2(2))

Partition by list(state)

(

Partition north values(‘DL’,’JP’) tablespace tbs1,

Partition south values(‘HY’,’PU’) tablespace tbs2

);

Imp views

dba_tab_partitions

user_tab_partitions

dba_indexes

index_stats

user_indexes

AWR REPORT (Automatic Workload Repository)

AWR REPORT (Automatic Workload Repository)

Taking snapshot

Sql>exec dbms_workload_repository.create_snapshot;

Generating AWR report

Sql>@$ORACLE_HOME/rdbms/admin/awrrpt.sql;

Modifying the default setting of snapshots:

Sql> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200

,interval=>30);

Generating An AWR compare Period Report

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Dropping the snapshots

Sql>exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>22,

        high_snap_id=>32);

Wait event in awr report

a)db_file scattered read :=>this wait event usually cause by large full table scan

b)db file sequential read:=>this wait event usually cause when problem with join

     operation.and wait on range or index scan.

c)buffer busy:=>this event is caused by concurrent acces to buffer in buffer cache.

d)free buffer:=>this event indicate that oracle waited many times for free buffer in buffer

     cache

f)latch free:=>this event is often caused by not using bing variable in sql statement;

ADDM(Automatic Database Diagnostic Monitor)

Generating addm report

Sql>@$ORACLE_HOME/rdbms/admin/addmrpt.sql;

Generating Ash report

Sql>$ORACLE_HOME/rdbms/admin/ashrpt.sql;

SQL Tracing

·         Set the sql_trace parameter

·          Trace the Query

·          From tkprof utility convert trace file to readable output file.

SYS :

Sql>alter session set sql_trace=true;

Sql>select * from u1.emp where empno=1212;

Sql>alter session set sql_trace=false;

A trace file will be generated in  user_dump_dest location .

Converting trace file into human readable format using tkprof utility

$tkprof  u1.emp.trc    trace.output

Tracing the query from user session through dbms_monitor Pl/sql package

 Sql>exec   dbms_monitor.session_trace_enable(SESSION_ID=>34,SERIAL_NUM=>23);

·         Ask the user to run the query from his own session

·         Once the query is processed then disable the trace session.

Sql>exec dbms_monitor.session_trace_disable(SESSION_ID=>34,SERIAL_NUM=>23);

* From tkprof utility convert trace file to readable output file

EXPLAIN PLAN

Sql>explain plan set statement_id=’ID1’ for select * from u1.emp where empno=66;

Sql>select operation,options from plan_table where statement_id=’ID1’;

Performance Tuning -1

                                          Performance Tuning

 

Routine Day–to-Day DB Health Management Tasks:

 

Gathering Statistics :

a)Table Stats(statistics)

Sql> exec dbms_stats.gather_table_stats(‘HZ’,’EMP’);

Sql> select table_name,to_char(last_analyzed,’hh24:mi:ss dd-mon-yy’) from dba_tables where table_name=’EMP’;

b)Schema Stats

Sql> exec dbms_stats.gather_schema_stats(‘HZ’);

c)Database Stats

Sql> exec dbms_stats.gather_database_stats();

d)Dictionary Stats

Sql>exec dbms_stats.gather_dictionary_stats();

e)Index Stats

Sql>exec dbms_stats.gather_index_stats(‘HZ’,’IND_EMP’);

 

Deleting Stats :

 a)Table Stats

 Sql>exec dbms_stats.delete_table_stats(‘HZ’,’EMP’);

 b) Schema Stats

 Sql>exec dbms_stats.delete_schema_stats(‘HZ’);

 c) Database Stats

 Sql>exec dbms_stats.delete_database_stats();

 

Defragmentation :

 

Table :

 

Sql> alter table table_name shrink space compact;

 

Index:

 

Making index invisible/visible

SQL>select index_name,visibility from user_indexes where index_name=’IND_EMP’;

SQL>alter index ind_emp invisible;

SQL>alter index ind_emp visible;

 

 

Analyze First

 

Sql>analyze index index_name validate structure;

sql>select height,name from index_stats where name='IND_NAME';

 

Rebuild/Coalesce:

Sql> alter index index_name rebuild online;

Sql> alter index index_name coalesce;

 

 

 

Pin /Unpin Database objects :

If dbms_shared_pool package is not  there then run the .sql script

Sql> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Pinning  object

Sql> exec dbms_shared_pool.keep(‘DBMS_SPACE_ADMIN’);

Sql> select * from v$db_object_cache where kept=’YES’;

Unpining  object

Sql> exec dbms_shared_pool.unkeep(‘DBMS_SPACE_ADMIN’);

 

 

 

 

Shrinking temporary tablespace

SQL>alter tablespace temp shrink space keep 100m;

 

Checking and Compiling DB Invalid Objects (Packages,Procedures )

 

Compiling DB invalid objects

Sql> select count(*) from dba_objects where status=’INVALID’;

Compiling Packages

Sql > alter package pack_name compile;

Sql> alter view view_name compile;

 

 

 

Oracle Networking-I

 

Utilites to configure Listener and Tns service.

             Netmgr: network manager   

 Netca: network configuration assistance

Location:

Default location of listener.ora and tnsnames.ora is :

$ORACLE_HOME/network/admin

 

Contents of listener.ora

Listener (Listener name)

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp) (HOST=192.168.0.101) (PORT=1600))

     )

    SID_LIST_LISTENER =

     (SID_LIST =

       (SID_DESC =

           (GLOBAL_DBNAME = prod)

           (SID_NAME = prod)

           (ORACLE_HOME=/u01/home/app/product/11.2.0/dbhome_1)

        )

     )

 

Tnsnames.ora

 

 SERVICE_NAME=

(DESCRIPTION=

  (ADDRESS_LIST=

    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.101)(PORT=1600))

 (CONNECT_DATA =

    (SERVICE_NAME/SID =sid)

 )

 

Listener control utility

$lsnrctl

$lsnrctl   <option> listener_name

            -reload   -   to reload the settings of listener file

            -status    -    to see status of listener

            - start     -    to start the listener

            - stop     -     to stop the listener

            -services – db/instances currently serve by

 

$lsnrctl>

Checking listener at os level

$ps –ef |grep tns

 

Testing oracle networking:

 $tnsping  <service_name>        Eg: $tnsping tolist

Oracle Networking-I

 

Utilites to configure Listener and Tns service.

             Netmgr: network manager   

 Netca: network configuration assistance

Location:

Default location of listener.ora and tnsnames.ora is :

$ORACLE_HOME/network/admin

 

Contents of listener.ora

Listener (Listener name)

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp) (HOST=192.168.0.101) (PORT=1600))

     )

    SID_LIST_LISTENER =

     (SID_LIST =

       (SID_DESC =

           (GLOBAL_DBNAME = prod)

           (SID_NAME = prod)

           (ORACLE_HOME=/u01/home/app/product/11.2.0/dbhome_1)

        )

     )

 

Tnsnames.ora

 

 SERVICE_NAME=

(DESCRIPTION=

  (ADDRESS_LIST=

    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.101)(PORT=1600))

 (CONNECT_DATA =

    (SERVICE_NAME/SID =sid)

 )

 

Listener control utility

$lsnrctl

$lsnrctl   <option> listener_name

            -reload   -   to reload the settings of listener file

            -status    -    to see status of listener

            - start     -    to start the listener

            - stop     -     to stop the listener

            -services – db/instances currently serve by

 

$lsnrctl>

Checking listener at os level

$ps –ef |grep tns

 

Testing oracle networking:

 $tnsping  <service_name>        Eg: $tnsping tolist

Table partitioning

 

                                       Table partitioning:

Table partition is used to improve the performance, manageability and availability of applications

 

Before creating table partitioning:

1. Create user:

SQL>create user user1 identified by user1;

 

2. Grant privileges to user:

SQL>grant connect,resource to user1;

SQL>grant create tablespace to user1;

 

3. Create tablespaces:

SQL> create tablespace t1 datafile ‘/u01/user38/test/t01.dbf’ size 100m autoextend on;

Similarly create 3 more tablespaces t2,t3,t4

 

SQL>alter user user1 quota unlimited on t1, quota unlimited on t2, quota unlimited on t3, quota unlimited on t4;

SQL>conn user1/user1

 

Types of table partitioning:

 

a. Range partitioning: The data is distributed based on a range of values of the partitioning key. For example, if we choose a date column as the partitioning key, the partition “JAN-2007” will contain all the rows that have the partition key values between 01-JAN-2007 and 31-JAN-2007 (assuming the range of the partition is from first of the month to the last date in the month).

SQL>Create table emp_range(empid number(4),empname varchar2(10), empsal(10,2)) partition by range(empid) (partition p1 values less than (10) tablespace t1, partition p2 values less than (20) tablespace t2, partition p3 values less than (30) tablespace t3, partition p4 values less than (40) tablespace t4);

 

b. Hash Partitioning: A hash algorithm is applied to the partitioning key to determine the partition for a given row. This provides I/O balancing, but cannot be used for range or inequality queries.

SQL>Create table emp_hash(empid number(4),empname varchar2(10), empsal(10,2)) 

PARTITION BY HASH (empid) PARTITIONS 4  STORE IN (data1, data2, data3, data4);

NOTE: The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added.

 

c. List Partitioning: The data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. e.g: Regions, States etc.

SQL>Create table emp_list(empid number(4),empname varchar2(10), empsal(10,2), dept_name varchar2(10)) partition by list(dept_name) (partition dept_A va;ues ('sales', 'marketing'), partition dept_B VALUES (‘advt'), Partition  dept_c values (‘manager') partition  college_others VALUES(DEFAULT) );

 

Next: **Insert data into the table

 

Retrieve data from the table:

SQL>select * from emp;

SQL> select * from <table_name>   <partition_name>;

SQL>select * from emp partition(p1);

SQL>select * from emp partition(p2);

 

To add another partition:

SQL>alter table emp add partition p4 values less than (maxvalue) tablespace t4;

 

To merge partition:

SQL>alter table emp merge partitions p1,p2 into partition p5 tablespace t3;

 

Splitting a partition:

SQL> alter table emp split partition p5 at(10) into (partition p1 tablespace t1,partition p2 tablespace t2);

 

Dropping a partition:

SQL> alter table emp drop partition p1;

 

11g New Features Contd

                                                11g New Features Contd

        

        Rman cloning

        Rman cloning from active database

        $rman target sys/manager@totarget nocatalog auxiliary sys/manager@totest

        Rman> duplicate target database to ‘test’ from active database;

 

        Configuring physical standby from active backup

        $rman target sys/manager@totarget nocatalog auxiliary sys/manager@tostdby

        Rman>duplicate target database for standby from active database;

       Converting physical standby to snapshot standby

   1.configure FRA (flashback recovery area) at standby side

        sql>alter database recover managed standby database disconnect;

        sql> alter database recover managed standby database cancel;

        sql>alter database convert to snapshot standby;

        open the database

        sql>select database_role from v$database;

        converting snapshot standby database back to physical standby

        sql>startup mount

        sql>alter database convert to physical standby;

        sql>select database_role from v$database;

      

        Parallel backup through section(chunks)           

        Rman>run

      {

      Allocate channel c1 device type disk;

      Allocate channel c2 device type disk;

      Allocate channel c3 device type disk;

      Backup section size 500m datafile 10;

      }

      Rman> list backup of datafile 10;

      Rman>list failure; (list block corruption and data failure)

      Rman>list failure <id>

      Rman>advise failure;

      Rman >repair failure;

 

      Faster backup compression using new algorithm ZLIB

      Rman>CONFIGURE COMPRESSION ALGORITHM 'ZLIB';

     Virtual catalog

-          prod (catalog db),  target db –> proda , prodb

 

     At catalog side –

-          create one more catalog user.

(prodb)

-          From any target db  (ex. proda)

-          $rman target / catalog rman/rman@tocatalog

-           Rman>grant catalog for database prodb to prodb;

Rman > list db_unique_name all;

     From prodb database

          $rman target / catalog prodb/prodb@tocatalog;

          Rman>create virtual catalog;

          Rman > list db_unique_name all;

 

      Now for prodb database backup information will  be store  in virtual catalog as well as base   catalog.

 

        ASM related new features:

 

        Asmcmd>lsdsk (shows the available no of disk)

        For Diskgroup meta data  backup

        Asmcmd>md_backup –b <file_name> -g <disk_group_name>

        Restore diskgroup meta data

        Asmcmd>md_restore –b <file_name>

11g New Features

11g New Features

 

  ADR = Automatic Diagnostic Repository

 

*in 11g user_dump_dest,core_dump_dest,background_dump_dest are replace by diagnostic_dest

 

Sub-dirs under diagnostic_dest  

         Alert rdbms incidents

 

  Alert log file in two format

  Log.xml

  Alert_<SID>.log

 

  adrci Utility  to see the content of alert log or incident details

  $adrci

  Adrci>show alert

  Adrci>help

  Adrci>show incident

 

Changes in SGA & PGA memory management

 

Memory related parameter

  Memory_max_target (this is the upper limit for sga and pga combined)

  Memory_target (this is the actual size of sga and pga combined)

 

In 11g by default password is case sensitive, to disable it set the following parameter to false          

Sec_case_sensitive_logon=false;

 

To make a table read only/ read write

SQL>alter table emp read only;

SQL>alter table emp read write;

SQL>select  table_name,read_only from user_tables where table_name='EMP';

 

Table compression

SQL>alter table emp compress for all operations;

SQL>alter table emp nocompress;

SQL>select table_name ,compression from user_tables where table_name=’EMP’;

 

Making index invisible/visible

SQL>alter index ind_emp invisible;

SQL>alter index ind_emp invisible;

SQL>select index_name,visibility from user_indexes where index_name=’IND_EMP’;

 

Shrinking temporary tablespace

SQL>alter tablespace temp shrink space keep 100m;

 

Pending and publish statistics

By default statistics is published immediately so we use dbms_stats package to change default behaviour

SQL>exec dbms_stats.set_table_prefs('DEMO','SALES','PUBLISH','FALSE');

SQL> select dbms_stats.get_prefs('PUBLISH', 'DEMO', 'SALES' ) FROM DUAL;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('DEMO','SALES');

SQL>SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

To see the pending stats

SQL> SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED  FROM DBA_TAB_PENDING_STAT;

Then we have to test the stats 

SQL> alter session set optimizer_use_pending_statistics=true;

 

Check that stats is working fine or not if working fine then publish;

SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('DEMO','SALES');

SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

SQL> SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;

SQL>  alter session set optimizer_use_pending_statistics=FALSE;

Flashback archive 

Creatng Flashback Archive 

SQL>create flashback archive flash_arc tablespace <tablespace_name> quota 1g Retention 1 year ;

SQL>alter table u1.emp flashback archive flash_arc;

Sql> flashback table emp to timestamp to_timestamp(’16:12:10 02-NOV-2010’ ,’hh24:mi:ss dd-mon-yyyy);