Oracle and Hadoop Blogs: 2012

Friday, December 28, 2012

Building Private Cloud with Oracle VM and Enterprise Manager 12c

With the latest Oracle enterprise manager 12c Oracle can now provide the complete cloud based solutions. The products such as OPS center, latest release of Oracle VM, OS, databases, middleware, RAC, ZFS storage interact with EM 12c to provide complete cloud control solutions and/ or to simplify their management.

In this Blog I am proving step-by step approach to configure Oracle Private cloud with Oracle VM and setup database as a service with self -provisioning feature of EM 12c. This configuration is for learning purposes only and built with minimal hardware available at home, real production systems require better hardware and network infrastructure. I tried to utilize as much hardware as available with me at home but I had to upgrade hardware ( CPU and motherboard) for VM server.

In this setup I will be providing as much screen prints, Captured Installation videos, steps and pain points that I discovered during installation.

Installation Process : To know more about the details, click on the following.

Part 1: Configure NFS, FTP server, Shared storage ( Software : FreeNAS)

Part 2: Download and Install VM Server 3.1.1 and VM manager 3.1.1.

Part 3: Login to VM manager console and Discover VM server(s), NFS Server(s), Setup VM

pool, Configure disks and VM template repository location.

Part 4: Download Linux 5.0 VM templates from Oracle and Import downloaded template
into VM template repository.

Part 5: Create first virtual machine with downloaded template and login to the first virtual
machine.

Part 6: Download Virtual Box , Linux 5.0, 11gr2 and EM12c for cloud control.

Part 7: Install Linux Release 5 and 11gR2 database on Virtual Box.

Part 8: Install Em12c and register VM mananger into it.

Part 9: Configure EM12c for database as service, create first 11gR2 database with self

service provision and login to 11gR2 database instance.

Software Links :

	Software	Download Link
1	Free NAS 8.3.0	http://www.freenas.org/about/news/item/freenas-830-is-released	https://www.freenas.org
2	VM Server 3.1.1	https://edelivery.oracle.com	Select Oracle VM/ X86 64 Bit and Oracle VM 3.1.1 Media Pack
3	VM Manager 3.1.1	https://edelivery.oracle.com	Select Oracle VM/ X86 64 Bit and Oracle VM 3.1.1 Media Pack
4	Enterprise Linux Release 5 update 4	https://edelivery.oracle.com	Select Oracle Linux/ X86-64 Option
5	Oracle VM templates for Enterprise Linux	https://edelivery.oracle.com	Select Oracle VM templates/X86-64 bit and Oracle Linux 5 Update 4 template - PV Small x86_64 (64 bit)
6	Oracle 11gR2 database	https://edelivery.oracle.com	Select Oracle database/Linux x86-64 option and Select Oracle 11gR2
7	Oracle Enterrprise Manager Cloud control 12c Release2(12.1.0.2)	https://edelivery.oracle.com	Select Oracle Enterprise Manager/ Linux- x86 option
8	Oracle Virtual Box 4.2	https://www.virtualbox.org/	Virtual Box to Install Enterprise Manager 12c on Linux x86-64bit release 5

Hardware List and Cost:

Purpose -------------------------	Hardware Specification ------------------------------	Cost -----------
VM Server
	AMD Phenom-II x6 processor	$154.99
	MB GIGABYTE\|GA-78LMT-S2 760G AM3+ R	$39.99
	8 GB RAM
	Other Costs (Power supply etc)	$75.00
	Internal Hard drives
	- 80 GB SATA for OS and VM Software
	- 1 TB for VM repository	$60.00
VM Manager
	Old HP Laptop - Intel Centrino
	4 GB RAM, 80 GB Internal disk
FreeNAS NFS - Server	Old Dell PC - Pentium 4
	1 GB RAM
	2 GB USB drive for FREE NAS OS
	1-80 GB Drive for VM pool disk
	1-250 External drive for FTP	$49.00
Enterprise Manager(Em12c)
	Em12c on Laptop and Virtual Box
	4GB RAM allocated to vbox	$28.00
	Total Cost :	$406.98

Architecture: -

The following architecture is for learning only and built with minimal hardware. This should not be use for production/test systems.

Friday, October 12, 2012

Query not using Index... 1 of 4

I have been asked couple of times by my customers, "Why my index is not being used even though there is an index on a column?"

90% of the time I get this answer - If query is returning more than 15% of rows from table then database choose full table scan vs index scan( index Selectivity). My take on this is: It's one part of the equation and there are other factors that optimizer uses to choose index scan vs table scan. All of the following should be considered carefully and never try these on production before thorough analysis -

Clustering Factor.
Selectivity.
OPTIMIZER_INDEX_COST_ADJ Initialization Parameter.
Parallel Option.

Clustering Factor - It defines how rows in index block align with the data block.

If Clustering factor=number of blocks in the table then the rows in an index leaf block most likely point to rows in a same data block. In this case, cost of index scan would be much less then cost of table scan that's why optimizer tend to use index scan.

If clustering factor = number of rows in table then rows are randomly assigned in an index leaf block and points to rows in different data block. Optimizer will have to read more blocks and the cost of index scan would be much higher compared to table scan that's why optimizer tend to use full table scan.

You can achieve low clustering factor by re-creating a table with "order by" clause, but this can create high clustering factor on other columns' indexes, extreme precaution should be taken before using this approach( re-creating table with order by ).

Clustering Factor Example:

Example 1 :

We will create two tables with different order by statement.

Table : Test_case1 <--- Order by rownum.

SQL> create table test_case1

2 as

3 select rownum "ROWNUMBER",a.* from dba_objects a where rownum<1001

4 order by rownum;

Table created.

Table : Test_case2 <--- Order by object_name

SQL>

SQL> create table test_case2

2 as

3 select rownum "ROWNUMBER",a.* from dba_objects a where rownum<1001

4 order by object_name;

Table created.

Create index on these two tables and gather statistics.

SQL>

SQL> create index idx_test_case1

2 on test_case1(rownumber);

Index created.

SQL>

SQL> create index idx_test_case2

2 on test_case2(rownumber);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'test_case1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'test_case2');

PL/SQL procedure successfully completed.

SQL> select a.table_name,a.num_rows,a.blocks,b.index_name,b.clustering_factor

2 from user_tables a,user_indexes b

3 where a.table_name=b.table_name

4 and a.table_name like 'TEST_CASE%';

TABLE_NAME NUM_ROWS BLOCKS INDEX_NAME CLUSTERING_FACTOR

---------------------- ------------------ ------------- --------------------------- -------------------------

TEST_CASE1 1000 14 IDX_TEST_CASE1 14

TEST_CASE2 1000 14 IDX_TEST_CASE2 719

In IDX_TEST_CASE1, Clustering factor(14) equal to No of Blocks(14)

In IDX_TEST_CASE2, Clustering Factor(719) approx. equals to No of Rows(1000)

SQL> set autotrace trace explain;

SQL> Select * from test_case1 where rownumber<10;

Execution Plan

----------------------------------------------------------

Plan hash value: 4004684460

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9 | 801 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST_CASE1 | 9 | 801 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TEST_CASE1 | 9 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ROWNUMBER"<10)

The above doing index scan as expected.

SQL> Select * from test_case2 where rownumber<10;

Execution Plan

----------------------------------------------------------

Plan hash value: 1160411121

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 9 | 801 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST_CASE2 | 9 | 801 | 6 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ROWNUMBER"<10)

Example 2:

Create a similar table with different PCTFREE and results are totally different than what we expected. The idea behind this example is to see what happens if a small table scattered across multiple blocks.

SQL>Create Table test( id number, name varchar(10)) pctfree 99 pctused 1

SQL>Insert into test

Select round(DBMS_RANDOM.VALUE(1,15)), upper(DBMS_RANDOM.STRING('A',10)) NAME from user_objects

SQL>exec dbms_stats.gather_table_stats

(user,'TEST')

SQL>Select a.table_name, B.NUM_ROWS, B.BLOCKS,A.CLUSTERING_FACTOR from user_indexes a, user_tables b where a.table_name=b.table_name and a.table_name = 'TEST' ;

TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR

----------------- --------------- ---------- --------------------------

TEST 543 275 526

The number of blocks, 275, are much higher and clustering factor( 526) is near to number of rows(543) in table and optimizer should do full table scan instead of index scan.

SQL> select * from test where id=1;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 36 | 504 | 37 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 36 | 504 | 37 (0)|

| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 36 | | 1 (0)|

--------------------------------------------------------------------------------

SQL> select * from test where id=2;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 36 | 504 | 37 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 36 | 504 | 37 (0)|

| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 36 | | 1 (0)|

--------------------------------------------------------------------------------

Not good. Optimizer doing index scan instead of full table scan, optimizer thinks the cost of index scan is less than full tables scan and it chooses to do index scan.

Create the same table with low PCTFREE parameter.

SQL>Create table test( id number, name varchar(10)) PCTFREE 10 PCTUSED 90;

SQL> Insert into test

Select round(DBMS_RANDOM.VALUE(1,15)), upper(DBMS_RANDOM.STRING('A',10)) NAME from user_objects;

SQL>Create index idx_test on test(id)

SQL>Exec dbms_stats.gather_table_stats(user,'TEST')

SQL>Select a.table_name, B.NUM_ROWS, B.BLOCKS,A.CLUSTERING_FACTOR from user_indexes a, user_tables b where a.table_name=b.table_name and a.table_name = 'TEST' ;

TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR

----------------- --------------- ---------- --------------------------

TEST 542 2 30

2 blocks compared to 275, where PCTFREE was 99, clustering factor(30) is near to number of blocks(2) and optimizer should do index scan.

SQL> select * from test where id=1;

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------

| 0 | SELECT STATEMENT | | 36 | 504 | 2 (0)|

| 1 | TABLE ACCESS FULL| TEST | 36 | 504 | 2 (0)|

---------------------------------------------------------------

SQL> select * from test where id=2;

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------

| 0 | SELECT STATEMENT | | 36 | 504 | 2 (0)|

| 1 | TABLE ACCESS FULL| TEST | 36 | 504 | 2 (0)|

---------------------------------------------------------------

Nope. Optimizer thinks cost of doing full table scan is lower than index scan.

The idea behind this example is that a careful consideration should be taken if you think of changing PCTFREE or PCTUSED parameters as it can change your execution plans completely.

Please look into my next blog http://vkoracle.blogspot.com/2012/08/query-not-using-index-2-of-4.html for Selectivity

Wednesday, October 10, 2012

Query not using Index... 4 of 4

This is continuation of my previous post : http://vkoracle.blogspot.com/2012/08/query-not-using-index-1-of-4.html

Parallel Option:

With Parallel option optimizer thinks full table scans are inexpensive than index scans. Caution should be taken setting Parallelism at table level as it can have profound impact on your system as it forces optimizer to use full table scan then to use index scan wherever possible..

Parallelism should be set at session level or as a query hint.

Example:

SQL>Drop table test

SQL>Create table test( id number, name varchar(10));

SQL>Insert into test

Select round(DBMS_RANDOM.VALUE(1,5)), upper(DBMS_RANDOM.STRING('A',10)) NAME from all_objects;

SQL>commit;　

-- Lets create index on a table

SQL>Create index idx_test_id on test(id);

SQL>Create index idx_test_name on test(name);

--Gather statistics on tables

SQL>EXEC

dbms_stats.gather_table_stats(user, 'test', method_opt=>'for all columns size auto' );

SQL> Select * from test where id=5;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8653 | 118K| 33 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8653 | 118K| 33 (0)|

| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 8653 | | 4 (0)|

--------------------------------------------------------------------------------

Query using index scan and in next step enable parallelism at table level.

SQL>alter table test parallel 4;

Table altered.

SQL> select * from test where id=5;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

-------------------

N-OUT| PQ Distrib |

--------------------------------------------------------------------------------

-------------------

| 0 | SELECT STATEMENT | | 8653 | 118K| 14 (0)| |

| |

| 1 | PX COORDINATOR | | | | | |

| |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8653 | 118K| 14 (0)| Q1,00 |

P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 8653 | 118K| 14 (0)| Q1,00 |

PCWC | |

| 4 | TABLE ACCESS FULL| TEST | 8653 | 118K| 14 (0)| Q1,00 |

PCWP | |

--------------------------------------------------------------------------------

-------------------

Same query from previous example doing full table scan instead of index scan. Lets try same query with parallel hint.

SQL> select /*+ Parallel(test,4)*/ * from test where id=1;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

-------------------

N-OUT| PQ Distrib |

--------------------------------------------------------------------------------

-------------------

| 0 | SELECT STATEMENT | | 8482 | 115K| 14 (0)| |

| |

| 1 | PX COORDINATOR | | | | | |

| |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8482 | 115K| 14 (0)| Q1,00 |

P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 8482 | 115K| 14 (0)| Q1,00 |

PCWC | |

| 4 | TABLE ACCESS FULL| TEST | 8482 | 115K| 14 (0)| Q1,00 |

PCWP | |

--------------------------------------------------------------------------------

-------------------

SQL> select /*+ Parallel(test,4)*/ * from test where id=2;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

-------------------

N-OUT| PQ Distrib |

--------------------------------------------------------------------------------

-------------------

| 0 | SELECT STATEMENT | | 17810 | 243K| 14 (0)| |

| |

| 1 | PX COORDINATOR | | | | | |

| |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 17810 | 243K| 14 (0)| Q1,00 |

P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 17810 | 243K| 14 (0)| Q1,00 |

PCWC | |

| 4 | TABLE ACCESS FULL| TEST | 17810 | 243K| 14 (0)| Q1,00 |

PCWP | |

--------------------------------------------------------------------------------

-------------------

SQL> select /*+ Parallel(test,4)*/ * from test where id=5;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------------------

-------------------

N-OUT| PQ Distrib |

--------------------------------------------------------------------------------

-------------------

| 0 | SELECT STATEMENT | | 8653 | 118K| 14 (0)| |

| |

| 1 | PX COORDINATOR | | | | | |

| |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8653 | 118K| 14 (0)| Q1,00 |

P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 8653 | 118K| 14 (0)| Q1,00 |

PCWC | |

| 4 | TABLE ACCESS FULL| TEST | 8653 | 118K| 14 (0)| Q1,00 |

PCWP | |

--------------------------------------------------------------------------------

-------------------

As you can see the parallel hint doing full table scan.