A. B-Tree Indexes I have discussed more details in the below blog. http://clouddba.co/index-internal-oracle-using-simple-test-case/ B.Invisible Indexes Deciding which columns to index is hard. The primary key is automatically indexed, the foreign keys should also be indexed, but then what? Even more difficult is deciding which index to remove that might be… Continue Reading Index in 12c by example test case

Index internal oracle using simple test case First of all I will create a table with char(1000) so that in one block I can fit 6-7 entries . Here are some relevant notes regarding Index split For Leaf block kdxlespl: bytes of uncommitted data at time of block split that… Continue Reading Index internal oracle using simple test case

How histogram can affect query plan change hence performance. 1.Let me create a test case.I am loading the table with following distribution.Obviously it means data is somewhat skewed. Value:-1 Range:-10 Value:-2 Range:-100 Value:-3 Range:-1000 Value:-4 Range:-10000 Value:-5 Range:-100000 C##TEST@TESTDB1> create table tt1(c1 number,c2 char(10)); Table created. C##TEST@TESTDB1> declare begin for… Continue Reading How histogram can affect query plan change hence performance

1.Let us create a test case first:- oracle@TST:TESTDB1[/home/oracle]$ sqlplus c##test/test C##TEST@TESTDB1> create table mytst1 as select * from dba_objects; Table created. C##TEST@TESTDB1> create table mytst2 as select * from dba_objects where rownum<5000; Table created. 2.Let us see what application executes SQL query and complaining poor performance select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1… Continue Reading Poor index identify and resolve manually or using sql tuning advisor

1. On the source node to target node, create a copy of the Oracle Grid Infrastructure home. For example, as root on Linux systems, run the cp command:- # cp -prf root@rac1:/u01/app/product/12.1.0/grid/* root@rac2:/u01/app/product/12.1.0/grid 2. Delete unnecessary files from the copy.The Oracle Grid Infrastructure home contains files that are relevant only… Continue Reading RAC one node to another node clone if another rac crash due to file deletion or permission change in grid home

Introduction This document will help to guide you for installation of Oracle 12cR1 RAC Flex cluster infrastructure on Virtual Box which is very popular because of shared storage simulation feature. Below is overall requirement in nut-shell. We need 3 servers for now.I will install RAC on First 2 servers and… Continue Reading Installation of Oracle 12c RAC Flex cluster infrastructure on Virtual Box

  A.Very useful method to upgrade 11gR2 to 12c using transportable=always Full transportable export/import is a new feature in Oracle Database 12c that greatly simplifies the process of database migration. Combining the ease of use of Oracle Data Pump with the performance of transportable tablespaces, full transportable export/import gives you the… Continue Reading EXPDP and IMPDP command reference 12c

Oracle Database Audit using SYSLOG to capture Oracle Database can be configured to log events into a database table, XML files or syslog. To configure Oracle Database to log events using syslog: Configure SYSLOG 1. Execute the following commands: mkdir -p /var/log/oracledb/ touch /var/log/oracledb/oracledb.log 2. Add the following line to… Continue Reading Oracle Database Audit FGA using SYSLOG to capture

with t as ( select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb, round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb from dba_hist_tbspc_space_usage su, (select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot group by trunc(BEGIN_INTERVAL_TIME) ) ss, v$tablespace ts, dba_tablespaces dt where su.snap_id = ss.snap_id and su.tablespace_id = ts.ts# and ts.name =upper(‘USERS’) and ts.name = dt.tablespace_name ) select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb, b.used_size_gb prev_used_size_gb, case when… Continue Reading Script for checking tablespace growth