How histogram can affect query plan change hence performance

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 j in 1..10 loop 2 3 4 insert into tt1 […]

Poor index identify and resolve manually or using sql tuning advisor

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 a,mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’; 3.Now create an index […]

RAC one node to another node clone if another rac crash due to file deletion or permission change in grid home

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 to the source node, so you can remove the unnecessary […]

Installation of Oracle 12c RAC Flex cluster infrastructure on Virtual Box

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 3rd server will act as DNS server.My desktop RAM is […]