Vineet's Oracle Blog Spot

Sunday, 15 January 2017

Oracle Exadata

Oracle Exadata & Offloading

New Year: - New Chapter, New Aspirations. Wishing you all a very Happy New Year.

Lastly I got a chance to explore Exadata and I went over documentations to understand how this machine actually works and i must say i was amazed by it’s designed. So I thought why not to share with you all.

So what is Exadata all about and what it offers?? Here is a brief about it.

Exadata precisely “The Database Machine” is high end Oracle system built using Industry standard Oracle hardware. It primarily consists 3 grids i.e. DB Server/compute grid, network grid and a storage grid. Basically it’s a great combination of Intelligent Hardware and smart Software.

Exadata Architecture

Let’s take each component one by one in brief:

DB Server/Compute Grid: This grid is actually a Database layer; this layer is basically made up of multiple SUN servers with running Oracle Db software. RAC is supported. The database servers uses ASM to map the storage and ASM is key component of this software stack. Db Server does have one more component LIBCELL and is linked with Oracle kernel. This allows Oracle to talk to storage tier via netwrok based calls instead of reads/writes at OS level. LIBCELL is designed in such a way that its code know how to interact or requests via iDB.

InfiniBand <Network grid>: It provide a low latency,high throughput communication link. This layer is responsible for the communication between the two tiers<Storage and DB> and here it is done via iDB< Intelligent Database protocol>, which is a network based protocol implemented using InfiniBand. iDB is used to send requests for data along with metadata about the request (including predicates) to cellsrv. iDB is built on Reliable Datagram Sockets (RDS ) protocol and runs over InfiniBand ZDP (Zero-loss Zero-copy Datagram Protocol). The objective of ZDP is to eliminate unnecessary copying of blocks.

Storage Grid: Storage consists of multiple servers and the intelligent part integrated here is the process< Cell Services(Cellsrv)> that runs as a part of storage server. Cellsrv is a multi-threaded program that services I/O requests from a database server. Those requests can be handled by returning processed data or by returning complete blocks depending in the request. Cellsrv is able to use the metadata to process the data prior sending result back to client. Such type of scans are called as Smart Scans.It is not always that Smart scan is used for each and every Db operation; there are certain situations only for which Smart Scans are triggered.

Apart from what I mentioned above there is also a term called Flash Cache; Storage server also equipped with flash based storage. Oracle point this as an Exadata Smart Flash Cache. The whole idea here is to gain on sequential read i.e. single blocks.

There are lot more per component wise; for more I request you to visit Oracle Docs.

Now lets take what Exadata offers at sql level:

One of the Key feature of Exadata is Offloading and this is basically a game changer in my view; I am really impressed in the way Oracle handles the data volume in Exadata . Offloading refers to the key concept of moving the processing from the database servers to the storage layer. By doing this Oracle gains an advantage on reducing the volume of data that must be returned to the DB server. Less the volume at DB layer less will the processing and more we can count on improvement.Smart Scan is another name of offloading and these terms are more or less interchangeable.

But why offloading is important: Large volume tends to be a problem for any system and moving the data from Hardware to DB and processing such large volume is expected to take time. Let say take this via e.g. With a block size of 8K if you want say one row then storage end sends whole 8K block and from that block Oracle software fetches 1 row. Now 8K block can have N number of Odd rows; so from N number of rows Oracle fetched 1 row.

Now suppose you have millions of rows then you can imagine how many blocks will get transferred to satify large no of rows and this certainly becomes a bottleneck and this is where Exadata pinch in; Exadata offloading is designed to eliminate unnecessary data between the two layers.

With Exadata Offloading we usually achieve:

(i) Less volume of data gets transferred to Db server from Storage End

(ii) Less physical IO i.e less disk access.

(iii) CPU utilization is less.

Offloading: Can be achieved by

(i) Predicate Filtering

(ii) Storage Indexes

(iii) Column Projection

But prior going over each; let see how components interacts when SQL initiates an IO in Exadata machine; here I try to come up with block diagram to show its end to end communication.

Exadata SQL Processing

To understand offloading behaviour lets do a practical to see how this offloading works and whether sql’s are getting benefited or not not.

In Exadata there are few Oracle parameters that deals with offloading; which when tweaked will give you license to play in terms of Offloading. Here I am concentrating on only on cell_offload_processing which is TRUE by default and is session modifiable.

NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM

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

cell_offload_compaction string ADAPTIVE

cell_offload_decryption boolean TRUE

cell_offload_parameters string

cell_offload_plan_display string AUTO

cell_offload_processing boolean TRUE

cell_offloadgroup_name string

So lets see with < cell_offload_processing =TRUE> how much time below query takes; table is having 485M rows.

/* Formatted on 2017/01/11 18:56 (Formatter Plus v4.8.8) */

SELECT COUNT (*)

FROM smart_test

WHERE (crnt_f = 'Y');

Elapsed: 00:00:26.33 ----------------à excellent elapsed time; in non exadata machine query is expected to take more.

SQL

SQL_PLAN_LINE_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_SE Id SAMPLE_TIME EVT CURRENT_OBJ#

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

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.33.240 AM cell smart table scan 114988

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.34.250 AM cell smart table scan 114989

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.35.250 AM cell smart table scan 114991

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.36.250 AM cell smart table scan 114992

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.37.250 AM cell smart table scan 114994

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.38.250 AM cell smart table scan 114995

3 NOT IN WAIT 47ztspbv68hu0 11-JAN-17 08.23.39.250 AM ON CPU 114996

3 NOT IN WAIT 47ztspbv68hu0 11-JAN-17 08.23.40.250 AM ON CPU 114998

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.41.250 AM cell smart table scan 115000

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.42.250 AM cell smart table scan 115001

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.43.250 AM cell smart table scan 115003

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.44.250 AM cell smart table scan 115005

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.45.260 AM cell smart table scan 115006

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.46.260 AM cell smart table scan 115008

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.47.260 AM cell smart table scan 115010

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.48.260 AM cell smart table scan 115011

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.49.290 AM cell smart table scan 115013

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.50.290 AM cell smart table scan 115015

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.51.290 AM cell smart table scan 115016

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.52.290 AM cell smart table scan 115018

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.53.290 AM cell smart table scan 115020

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.54.300 AM cell smart table scan 115021

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.55.316 AM cell smart table scan 115023

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.56.316 AM cell smart table scan 115025

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.57.316 AM cell smart table scan 115027

3 UNKNOWN 47ztspbv68hu0 11-JAN-17 08.23.58.316 AM cell smart table scan 115028

You might have noticed by now; query is waited on new event ”cell smart table scan” which shows offloading is being done. Oracle uses this event to account for time spent waiting for Full Table Scans that are Offloaded. Its occurrence can be used to verify whether a statement benefited from Offloading or not. Also to notice here that query visited table in a restricted manner i.e not to much visit.

Lets take a look on execution plan; you can see ID 3 operation you can see new access method i.e. ‘TABLE ACCESS STORAGE FULL’ which is very specific to Exadata only. It is worth to note here that this doesn’t mean smart scan is used; Storage keyword here means that this execution plan is Exadata storage aware.

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

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

| 0 | SELECT STATEMENT | | | | 2796K(100)| | | | | | |

| 1 | SORT AGGREGATE | | 1 | 2 | | | | | | | |

| 2 | PARTITION RANGE ALL | | 61M| 117M| 2796K (1)| 00:01:50 | 1 | 67 | | | |

|* 3 | TABLE ACCESS STORAGE FULL| SMART_TEST | 61M| 117M| 2796K (1)| 00:01:50 | 1 | 67 | 1025K| 1025K| 14M (0)|

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

Predicate Information (identified by operation id):

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

3 - storage("CRNT_F"='Y')

filter("CRNT_F"='Y')

But you can see storage clause in predicate information< storage("CRNT_F"='Y')> which shows smart scan is used and Storage grid handled all the pain i.e. major filtering is done at the storage level and DB layer had to do minimal activity<only those blocks which are not handled by cells>. This is basically Predicate Filtering.

Lets see how it works when offloading process is disabled. Exadata will behave like non exadata machine.

SQL> alter session set cell_offload_processing=false; -------------------à disable offloading process;

/* Formatted on 2017/01/11 18:56 (Formatter Plus v4.8.8) */

SELECT COUNT (*)

FROM smart_test

WHERE (crnt_f = 'Y');

Elapsed: 00:06:08.59--------àsee after disabling; elapsed time is in minutes from few seconds.

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

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

| 0 | SELECT STATEMENT | | | | 2796K(100)| | | |

| 1 | SORT AGGREGATE | | 1 | 2 | | | | |

| 2 | PARTITION RANGE ALL | | 61M| 117M| 2796K (1)| 00:01:50 | 1 | 67 |

|* 3 | TABLE ACCESS STORAGE FULL| SMART_TEST | 61M| 117M| 2796K (1)| 00:01:50 | 1 | 67 |

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

Predicate Information (identified by operation id):

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

3 - filter("CRNT_F"='Y')

Look at the predicate storage filter disappeared and that it means no smart scan and no offloading; here all the blocks are shipped to Oracle Db layer from storage layer and all the major filtering is done at Db layer instead of storage layer.

You can also see it from wait events; that query waited on direct path read and visited the table many times as compared to smart scan offloading as I mentioned above.

SQL

SQL_PLAN_LINE_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_SE Id SAMPLE_TIME EVT CURRENT_OBJ#

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

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.29.53.916 AM direct path read 114992

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.29.54.916 AM ON CPU 114992

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.29.55.933 AM ON CPU 114993

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.29.56.933 AM direct path read 114993

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.29.57.933 AM ON CPU 114993

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.29.58.933 AM ON CPU 114993

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.29.59.933 AM direct path read 114993

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.00.933 AM ON CPU 114994

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.01.933 AM direct path read 114994

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.02.943 AM ON CPU 114994

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.03.943 AM direct path read 114994

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.04.943 AM ON CPU 114995

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.05.943 AM ON CPU 114995

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.06.943 AM direct path read 114995

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.07.943 AM direct path read 114996

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.08.943 AM direct path read 114996

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.09.953 AM direct path read 114996

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.10.953 AM ON CPU 114996

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.11.953 AM ON CPU 114997

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.12.953 AM direct path read 114997

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.13.953 AM direct path read 114997

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.14.953 AM direct path read 114997

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.15.953 AM direct path read 114998

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.16.953 AM direct path read 114998

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.17.953 AM ON CPU 114998

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.18.953 AM direct path read 114999

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.19.963 AM direct path read 114999

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.20.963 AM direct path read 114999

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.21.963 AM ON CPU 114999

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.22.963 AM direct path read 115000

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.23.963 AM direct path read 115000

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.24.963 AM direct path read 115000

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.25.963 AM direct path read 115001

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.26.963 AM direct path read 115001

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.27.963 AM ON CPU 115001

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.28.973 AM ON CPU 115001

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.29.973 AM direct path read 115002

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.30.973 AM direct path read 115002

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.31.973 AM direct path read 115002

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.32.973 AM ON CPU 115002

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.33.973 AM direct path read 115003

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.34.973 AM direct path read 115003

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.35.973 AM direct path read 115003

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.36.973 AM ON CPU 115003

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.37.983 AM ON CPU 115004

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.38.983 AM direct path read 115004

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.39.983 AM direct path read 115004

3 NOT IN WAIT 6fxj1rub84bsq 11-JAN-17 08.30.40.983 AM ON CPU 115004

3 UNKNOWN 6fxj1rub84bsq 11-JAN-17 08.30.41.983 AM direct path read 115005

Storage Indexes: Storage Indexes are not like B Tree indexes; infact there is no relationship between them.Storage Index in my view is like anti index which is designed to eliminate the disk IO as they are here to identify the location where requested data is not present rather than B tree index which are designed to pinpoint the location of requested data. Storage Index structure are of 1MB and are stored in memory and never written to disk. Storage index stores minimum and maximum column values for disk storage units; there is one FLAG sort of column which denotes whether any of the records in a storage region contain nulls.

Since Smart Scans pass the query predicates to the storage servers, and Storage Indexes contain a map of values in each 1MB storage region, any region that can’t possibly contain a matching row can be eliminated without ever being read.

Let me show you via diagram what I am trying to say

Figure 1: Denotes employee table; Figure 2 is Storage Index created on Salary predicate; you can see 3 columns minimum,maximum and FLAG column.

Suppose you executed query { SELECT EMPID from EMPLOYEE WHERE SALARY > 5000 }on exadata machine; smart scan will pass the query predicate to storage server and storage index here will help the storage to skip the first 2 part of region as they do not have maximum values that are high enough to contain any records that will satisfy the query predicate. Therefore, those storage regions will not be read from disk only last row<region> is satisfying the requested data so only that will be read.

Storage index Map

cell physical IO bytes saved by storage index: is the statistics which shows how much bytes were saved. For initial run there wont be any saving as storage index map will be created on that execution; subsequent execution will save and shows how much it saved.

SQL> select avg(id) from smart_test where id2 is null;

NAME VALUE

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

cell physical IO bytes saved by storage index 0

SQL> select avg(id) from smart_test where id2 is null;

NAME VALUE

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

cell physical IO bytes saved by storage index 5984949248

Column Projection: Column projection also helps in terms of achieving good performance. Columns that are part of select query are not returned only; there are join columns as well which gets returned.But again this is not something which is very unique to exadata. In non exadata machines also column projection is famous.

Ø select sum(s. ID2),count((s. ID3)) from smart_test s, smart_test s2

where s. ID = s2. ID and s. ID4 > 0 ;

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT("S"." ID3")[22], SUM("S"." ID2")[22]

2 - (#keys=1; rowset=200) "S"." ID2"[NUMBER,22], "S"." ID3"[NUMBER,22]

3 - "S2"." ID"[NUMBER,22]

4 - "S2"." ID"[NUMBER,22]

5 - (rowset=200) "S"." ID"[NUMBER,22], "S"." ID2"[NUMBER,22],

"S"." ID3"[NUMBER,22]

6 - (rowset=200) "S"." ID"[NUMBER,22], "S"." ID2"[NUMBER,22],

"S"." ID3"[NUMBER,22]

You can see join columns and select columns are part of projection but not all columns selected i.e. ID4 column is not part of projection so this is excellent in terms of performance gain.

I hope you like this brief cover on Exadata. For more I request you to go over documentation as this machine has lot to offer.

Enjoy Learning!!!!

Saturday, 19 November 2016

Oracle 12C: Lob Enhancement & Parallelism Supported.

With Oracle 11g, when creating a table containing LOB data, LOB are stored as a part of BASIC FILE feature and the default value for the init parameter DB_SECUREFILE is set to PERMITTED.

But in 12C; by default storage is changed to Secure file when compatible parameter is set 12.0 or high and it does support Parallelism <Non partitioned table Insert add on feature>

SQL> sho parameter comp

NAME TYPE VALUE

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

compatible string 12.1.0

SQL> show parameter db_secure

NAME TYPE VALUE

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

db_securefile string PREFERRED

SQL> create table CLOB_TEST ( x clob) tablespace POOL_DATA;

SQL> select table_name, securefile from user_lobs where table_name='CLOB_TEST';

TABLE_NAME SEC

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

CLOB_TEST YES

If you change compatible parameter to 11G or <12C then parameter db_securefile default value gets transitioned back to PERMITTED mode; where in LOB will get store in Secure file if and only if it is explicitly mentioned while creating of the segment.

There is one more condition that we must not forget i.e. the tablespace where you are creating the secure file needs to be be Automatic Segment Space Management (ASSM). In Oracle Database 11g, the default mode of tablespace creation is ASSM so it may already be so for the tablespace. If it's not, then you have to create the Secure File on a new ASSM tablespace.

Same applies to 12C as well i.e. even though db_securefile value is PREFERRED; if your tablespace is not ASSM then LOB will not be store as a part of Secure file.

SQL> select SEGMENT_SPACE_MANAGEMENT,TABLESPACE_NAME from dba_tablespaces;

SEGMEN TABLESPACE_NAME

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

MANUAL SYSTEM

AUTO SYSAUX

MANUAL UNDOTBS

MANUAL TEMP

AUTO POOL_DATA

AUTO POOL_IX

MANUAL TOOLS

SQL> create table CLOB_TEST ( x clob) tablespace TOOLS;

Table created.

SQL> select table_name, securefile from user_lobs where table_name='CLOB_TEST';

TABLE_NAME SEC

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

CLOB_TEST NO

In Oracle 11g, Parallel Insert was not supported both with Basic and Secure file.

insert /*+ parallel(testclob,4) enable_parallel_dml */ into testclob select /*+ parallel(t,4) full(t) */ * from testclob_pump t;

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

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

| 0 | INSERT STATEMENT | | 82 | 321K| 2 (0)| 00:00:01 | | | |

| 2 | PX COORDINATOR | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 4 | PX BLOCK ITERATOR | | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 5 | TABLE ACCESS FULL | TESTCLOB_PUMP | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |

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

But starting with 12c, Parallel INSERTs<parallel dml> is allowed into non-partitioned tables with LOB columns provided that those columns are declared as SecureFiles LOBs.

Let’s start with 12C test case : Created two tables and then inserted the data equivalent a big lob. Note below table is created with Secure file feature<db_securefile=PERMITTED>

CREATE TABLE testclob

(

x NUMBER,

y CLOB,

z VARCHAR2(4000)

) tablespace POOL_DATA;

CREATE TABLE testclob_bk

(

x NUMBER,

y CLOB,

z VARCHAR2(4000)

) tablespace POOL_DATA;

DECLARE

textstring CLOB := '123';

i INT;

BEGIN

WHILE Length(textstring) <= 60000 LOOP

textstring := textstring

|| '000000000000000000000000000000000';

END LOOP;

begin

FOR I IN 1..100000 LOOP

INSERT INTO testclob

(x,

VALUES (0,

textstring,

'done');

END LOOP;

commit;

end;

END;

Here is the insert and you can clearly see Parallel DML is supported as compared to 11g.

insert /*+ parallel(testclob_bk,4) enable_parallel_dml */ into testclob_bk select /*+ parallel(t,4) full(t) */ * from testclob t;

SQL> @plan

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

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

| 0 | INSERT STATEMENT | | | | 2 (100)| | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |

| 4 | OPTIMIZER STATISTICS GATHERING | | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |

| 5 | PX BLOCK ITERATOR | | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |

|* 6 | TABLE ACCESS FULL | TESTCLOB | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |

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

Predicate Information (identified by operation id):

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

6 - access(:Z>=:Z AND :Z<=:Z)

Even from below we can see in famous Db file sequential read is replaced with direct path read which says it all.

EVENT Reported on SQL

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

direct path read

direct path write

SQL_PLAN_LINE_ID BLOCKING_SE SQL_ID SAMPLE_TIME EVT CURRENT_OBJ#

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

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.05.195 PM direct path read 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.06.195 PM ON CPU 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.07.195 PM log file switch (checkpoi 367192

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.08.195 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.09.205 PM direct path read 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.10.215 PM log file switch (checkpoi 367192

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.11.215 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.12.245 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.13.245 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.14.435 PM direct path read 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.15.435 PM log file switch completio 367192

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.16.445 PM direct path read 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.17.445 PM ON CPU 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.18.455 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.19.465 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.20.465 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.21.465 PM direct path read 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.22.465 PM log file switch (checkpoi 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.23.465 PM log file switch (checkpoi 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.24.475 PM log file switch (checkpoi 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.25.475 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.26.475 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.27.485 PM direct path read 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.28.485 PM ON CPU 367192

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.29.495 PM ON CPU 367192

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.30.505 PM direct path read 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.31.505 PM ON CPU 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.32.535 PM direct path read 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.33.535 PM ON CPU 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.34.545 PM log file switch (checkpoi 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.35.555 PM ON CPU 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.36.555 PM direct path read 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.37.555 PM log file switch (checkpoi 367192

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.38.565 PM ON CPU 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.39.575 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.40.575 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.41.575 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.42.575 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.43.585 PM direct path read 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.44.595 PM log file switch completio 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.45.595 PM log file switch (checkpoi 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.46.650 PM ON CPU 367345

3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.47.660 PM ON CPU 367192

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.48.660 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.49.660 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.50.680 PM direct path read 367345

3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.51.680 PM log file switch (checkpoi 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.52.680 PM direct path read 367345

3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.53.690 PM direct path read 367345

SQL> @objnm

Enter value for enter_obj_id: 367345

OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE

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

AIM_DBA 367345 SYS_LOB0000367344C00002$$ LOB

SQL> select TABLE_NAME,SEGMENT_NAME from user_lobs where SEGMENT_NAME='SYS_LOB0000367344C00002$$';

TABLE_NAME SEGMENT_NAME

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

TESTCLOB SYS_LOB0000367344C00002$$

I tried to do export as well via Datapump to see if secure feature is able to export the data with parallelism or not; but seems a worthless effort; I see two workers started and only one worked; the other process is just waited. It might get work with partition table but that I leave up to you.

expdp username/password dumpfile=clobdump%u.dmp logfile=dumpfile.log tables=testclob exclude=statistics directory=DUMP parallel=4

Job: SYS_EXPORT_TABLE_01

Operation: EXPORT

Mode: TABLE

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 4

Job Error Count: 0

Dump File: /DUMP/clobdump01.dmp

bytes written: 4,096

Dump File: /DUMP/clobdump%u.dmp

Worker 1 Status:

Process Name: DW00

State: WORK WAITING

Worker 2 Status:

Process Name: DW01

State: EXECUTING

Object Schema: AIM_DBA

Object Name: TESTCLOB

Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

Completed Objects: 1

Total Objects: 1

Completed Rows: 13,102

Worker Parallelism: 1

In my view secure file enhancement is very helpful!!

Hope you enjoyed!!!

Tuesday, 26 April 2016

Oracle Data-Pump : Optimising LOB Export Performance

LOBs as we all know that are used to store large, unstructured data, such as video, audio, photo images etc. With a LOB you can store up to 4 Gigabytes of data. They are similar to a LONG or LONG RAW but differ from them in quite a few ways.

Category that LOB can think of is

• Simple structured data

• Complex structured data

• Semi-structured data

• Unstructured data

LOB are effective but it comes with performance impact in few of the scenario.

Lately there was a task related to taking a backup of LOB table<non partitioned.> of size around 41 GB where LOB itself took around 33 GB. There are many options to do backup of the table and team started with EXPDP approach and it took very long time around 3 hours and this was not acceptable.

Lob won’t use parallel at all so there was no point in using parallel degree in expdp command.

Since the activity was critical and clock was ticking, somehow I managed to deduce the way to do it in concurrent way i.e. Run the data pump in chunks and execute in concurrent way so that each data pump will work on small chunk rather than full volume. Execution in concurrent way in any case will reduce the time.

Here is the very simple and effective script that I designed and to my surprise it reduced the time line from 3 Hours to 10-15 minutes.

What I did is that I logically divided the table based on rowids block using DBMS function in to 10 chunks and these chunks are internally balanced that is data is divided almost equally among the chunks.

Below snippet executed in 10 threads i.e. 10 data pump threads and each one is getting executed concurrently in background. Resulted in 10 different dumps and took just 10-20 minutes for complete export.

#!/bin/bash

chunk=10

for ((i=0;i<=9;i++));

expdp USERNAME/Password@DB_NAME TABLES=LOB_TEST QUERY=LOB_TEST:\"where mod$dbms_rowid.rowid_block_number\(rowid$\, ${chunk}\) = ${i}\" directory=DMP dumpfile=lob_test_${i}.dmp logfile= log_test_${i}.log &

echo $i

done

Since there are 10 different dumps; import will happen in chunk only and it will reduce the time during import activity as well.

Off course there are other ways to do it like<in relation to expdp>;

a) DBMS_PARALLEL_EXECUTE where we can define the task and generate the rowids and use those rowids during the export.

b) If table is partitioned then do take the backup partition wise in concurrent mode.

c) Use dbms_rowid.rowid_create.

d) Use Secure file for storing the lob rather than using basic file.

Sunday, 15 January 2017

Oracle Exadata

Oracle Exadata & Offloading

Saturday, 19 November 2016

Oracle 12C: Lob Enhancement & Parallelism Supported.

Tuesday, 26 April 2016

Oracle Data-Pump : Optimising LOB Export Performance

About Me