Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, December 20, 2012

11gR2 -Oracle Index usage monitoring


To start monitoring the index usage:

ALTER INDEX MONITORING USAGE;


To stop:

ALTER INDEX MONITORING USAGE;


To monitor the status if you can login as owner then use

select * from v$object_usage;

If viewing the stats as other/super user then use below:

select d.username, io.name INDEX_NAME, t.name TABLE_NAME,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') INDEX_USED,
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'CN'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;
 
Ex:-



Tuesday, December 18, 2012

Change Concurrent manager for a Program

How to change a concurrent manager for a program.

Ex:-
1) exclude the program from the standard manager:
SYSADMIN--> Concurrent --> Manger -->Define


 

--> Specialization rules:




Exclude the Program:



Now go to the customer Manager and include the program:

Monday, December 17, 2012

Find Exadata IO saved by smart scan and offload to cells

Find amount of I/O saved by smart scan vs Total I/Oor
Find amount of I/O transported from cell to DB or
Find amount of I/O saved by storage index or
Find amount of I/O saved by predicate offload

Image version: 11.2.3.1.0.120304


Verify what are all the functions and operators qualify for smart_scan
from V$SQLFN_METADATA.



Example-1:

select sid,value/1024/1024 IO_MB,name from v$sesstat st,v$statname sn
where st.statistic#=sn.statistic#
and st.sid=1
and (sn.name like 'cell physical%' or sn.name like 'cell io%' or sn.name like 'physical%total bytes')


INST_ID    SID    IO_IN_MB    STAT_NAME
=======================================================
3    676    0    physical read total bytes
3    676    0    physical write total bytes
3    676    0    cell physical IO interconnect bytes
3    676    0    cell physical IO bytes saved during optimized file creation
3    676    0    cell physical IO bytes saved during optimized RMAN file restore
3    676    0    cell physical IO bytes eligible for predicate offload
3    676    0    cell physical IO bytes saved by storage index
3    676    0    cell physical IO bytes sent directly to DB node to balance CPU
3    676    0    cell physical IO interconnect bytes returned by smart scan



SQL> select count(*) from cn.LINES_API_ALL where processed_period_id< 2011001;

  COUNT(*)
----------
   4300815
Elapsed: 00:00:44.54

Execution Plan
----------------------------------------------------------
Plan hash value: 616062978


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("PROCESSED_PERIOD_ID"<2011001 br="br">       filter("PROCESSED_PERIOD_ID"<2011001 br="br">




INST_ID    SID    IO_IN_MB    STAT_NAME
=======================================================
3    676    6873.57    physical read total bytes
3    676    0    physical write total bytes
3    676    6873.57    cell physical IO interconnect bytes
3    676    0    cell physical IO bytes saved during optimized file creation
3    676    0    cell physical IO bytes saved during optimized RMAN file restore
3    676    0    cell physical IO bytes eligible for predicate offload
3    676    0    cell physical IO bytes saved by storage index
3    676    0    cell physical IO bytes sent directly to DB node to balance CPU
3    676    0    cell physical IO interconnect bytes returned by smart scan

Now force it to use direct path reads by using parallel hint.

SQL>  set timing on echo on linesize 1000 pages 300
SQL> set autot trace exp stat
SQL>  select /*+ PARALLEL(T,64) */count(*) from cn.LINES_API_ALL T where processed_period_id< 2011001;

Elapsed: 00:00:01.53

Execution Plan
----------------------------------------------------------
Plan hash value: 859133999



Predicate Information (identified by operation id):
---------------------------------------------------

   6 - storage("PROCESSED_PERIOD_ID"<2011001 br="br">       filter("PROCESSED_PERIOD_ID"<2011001 br="br">

Statistics
----------------------------------------------------------
        192  recursive calls
       9920  db block gets
   37668038  consistent gets
   37347799  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


INST_ID    SID    IO_IN_MB    STAT_NAME
=======================================================
3    676    291779.69    physical read total bytes
3    676    0                  physical write total bytes
3    676    92.42            cell physical IO interconnect bytes
3    676    0                  cell physical IO bytes saved during optimized file creation
3    676    0                     cell physical IO bytes saved during optimized RMAN file restore
3    676    291779.68    cell physical IO bytes eligible for predicate offload
3    676    279727.9    cell physical IO bytes saved by storage index
3    676    0                     cell physical IO bytes sent directly to DB node to balance CPU
3    676    92.41            cell physical IO interconnect bytes returned by smart scan


From the above it is clear that smart scan offloaded the IO to the cell and transported only
the necessary IO to the db server i.e around 92 mb and overall elapsed time is apprx 2 secs down from 44 secs.

Example-2

Create tables forces direct path reads, lets check an example:

SQL> drop table sshaik_api_all;

Table dropped.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shareD_pool;

System altered.




SQL> alter session set cell_offload_processing=false;

Session altered.

Elapsed: 00:00:00.00
SQL> create table sshaik_api_all as select * from cn.LINES_API_ALL where processed_period_id<=2011002;

Table created.

Elapsed: 00:05:27.32

No smart scan or cell offloading and it took around 5mins 27 secs

INST_ID    SID    IO_IN_MB    STAT_NAME
=======================================================
3    112    298494.52    physical read total bytes
3    112    6697.1             physical write total bytes
3    112    311888.73    cell physical IO interconnect bytes
3    112    0                    cell physical IO bytes saved during optimized file creation
3    112    0                    cell physical IO bytes saved during optimized RMAN file restore
3    112    0                   cell physical IO bytes eligible for predicate offload
3    112    0                    cell physical IO bytes saved by storage index
3    112    0                   cell physical IO bytes sent directly to DB node to balance CPU
3    112    0                    cell physical IO interconnect bytes returned by smart scan




SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.20
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.07
SQL> drop table sshaik_api_all;

Table dropped.

Elapsed: 00:00:00.81
SQL> purge dba_recyclebin;

DBA Recyclebin purged.



Elapsed: 00:00:00.00
SQL> alter session set cell_offload_processing=true;

Session altered.

Elapsed: 00:00:00.00
SQL> show parameter cell_offload_processing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_processing              boolean     TRUE


SQL> create table sshaik_api_all as select * from cn.LINES_API_ALL where processed_period_id<=2011002;

Table created.



Elapsed: 00:01:21.11

With smart scan and cell offloading, it took around 1min 21 secs

Plan hash value: 3947898682



Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("PROCESSED_PERIOD_ID"<=2011002)
       filter("PROCESSED_PERIOD_ID"<=2011002)



INST_ID    SID    IO_IN_MB    STAT_NAME
=======================================================
3    112    298615.73    physical read total bytes
3    112    20694.93    physical write total bytes
3    112    19844.59    cell physical IO interconnect bytes
3    112    13966            cell physical IO bytes saved during optimized file creation
3    112    0                    cell physical IO bytes saved during optimized RMAN file restore
3    112    312442.61    cell physical IO bytes eligible for predicate offload
3    112    94806.78       cell physical IO bytes saved by storage index
3    112    0                    cell physical IO bytes sent directly to DB node to balance CPU
3    112    6219.6            cell physical IO interconnect bytes returned by smart scan


SQL> select count(*) from sshaik_api_all;

  COUNT(*)
----------
  11968704

Monday, December 10, 2012

oracle Move datafile into ASM


How to move a datafile that was created on the OS to ASM.

Issue:
Datafile created on the OS instead of in ASM

Given:
RAC 3 nodes
tablespace has multiple datafiles 

 Check the status of the file:
select df.file#,to_char(df.creation_time,'mm-dd-yyyy hh24:mi:ss') created,df.name,ts.name,df.status from v$datafile df,v$tablespace ts where df.ts#=ts.ts# and df.file#=127

FILE#    CREATION_TIME        NAME                TS_NAME        STATUS
127    12-10-2012 05:06:27    dbhome_1/dbs/DBNAME_DATA_01    APPS_TS_TX_IDX    ONLINE


RMAN> connect target /

connected to target database: DBNAME (DBID=2919937482)

RMAN> copy datafile 127 to '+DBNAME_DATA_01';

Starting backup at 10-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=854 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_2

channel ORA_DISK_1: starting datafile copy
input datafile file number=00127 name=/icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs/DBNAME_DATA_01
output file name=+DBNAME_DATA_01/DBNAME/datafile/apps_ts_tx_idx.480.801661731 tag=TAG20121210T114844 RECID=54 STAMP=801661781
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
Finished backup at 10-DEC-12

Starting Control File and SPFILE Autobackup at 10-DEC-12
piece handle=+DBNAME_FRA_01/DBNAME/autobackup/2012_12_10/s_801661785.5682.801661787 comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-12

RMAN> switch datafile 127 to copy;

datafile 127 switched to datafile copy "+DBNAME_DATA_01/DBNAME/datafile/apps_ts_tx_idx.480.801661731"

RMAN> exit


Recovery Manager complete.


SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:50:32 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


FILE#    CREATION_TIME        NAME                TS_NAME        STATUS
127    12-10-2012 05:06:27    +apps_ts_tx_idx.480.801661731   APPS_TS_TX_IDX    RECOVER


>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 10 11:51:22 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: DBNAME (DBID=2919937482)

RMAN> recover datafile 127;

Starting recover at 10-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3679 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3955 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=4524 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=4799 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=5082 instance=DBNAME1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished recover at 10-DEC-12

RMAN> exit


Recovery Manager complete.


SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:52:06 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


FILE#    CREATION_TIME        NAME                TS_NAME        STATUS
127    12-10-2012 05:06:27    +apps_ts_tx_idx.480.801661731   APPS_TS_TX_IDX    OFFLINE


SQL> alter database datafile 127 online;

Database altered.

SQL>

FILE#    CREATION_TIME        NAME                TS_NAME        STATUS
127    12-10-2012 05:06:27    +apps_ts_tx_idx.480.801661731   APPS_TS_TX_IDX    ONLINE


DBNAMEb01cdp(DBNAME1)  /icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs
>rm DBNAME_DATA_01