SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

4 Easy Tips to Dramatically Improve Integration Performance with Oracle

Reply
SAS Employee
Posts: 13

4 Easy Tips to Dramatically Improve Integration Performance with Oracle


face=Calibri>Four simple things to do to improve Data Integration performance
with Oracle



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1"
class=MsoListParagraphCxSpFirst>
style="LINE-HEIGHT: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-bidi-font-size: 13.0pt; mso-bidi-font-weight: bold">
style="mso-list: Ignore">1.
style="FONT: 7pt 'Times New Roman'">      
Change default READBUFF
settings for Oracle!
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Cambria','serif'; COLOR: #4f81bd; FONT-SIZE: 13pt; mso-ascii-theme-font: major-latin; mso-fareast-font-family: 'Times New Roman'; mso-fareast-theme-font: major-fareast; mso-hansi-theme-font: major-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: major-bidi; mso-themecolor: accent1">



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpMiddle>
style="LINE-HEIGHT: 115%; mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-size: 13.0pt; mso-bidi-font-weight: bold">
style="mso-list: Ignore">-
style="FONT: 7pt 'Times New Roman'">         
READBUFF=1 means that only
one row is retrieved at a time. Oracle defaults to 250, in almost every case
significant performance can be obtained changing that to 1000. For example:


face="Courier New">
color=#0000ff size=2 face="Courier New">

libname


face="Courier New"> ora oracle user=sh pass=sh path=ora10g
READBUFF=

color=#008080 size=2 face="Courier New">
face="Courier New">1000

face="Courier New">;


style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Cambria','serif'; COLOR: #4f81bd; FONT-SIZE: 13pt; mso-ascii-theme-font: major-latin; mso-fareast-font-family: 'Times New Roman'; mso-fareast-theme-font: major-fareast; mso-hansi-theme-font: major-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: major-bidi; mso-themecolor: accent1">



size=3 face=Calibri> 



face=Calibri>

 



face=Calibri>

 



size=3>NOTE: DB2 is the exception to this rule, SAS/Access to
DB2 automatically determines the optimal READBUFF setting based on the
row-length of your result set.



size=3 face=Calibri> 



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1"
class=MsoListParagraphCxSpMiddle>
style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin">
style="mso-list: Ignore">2.
style="FONT: 7pt 'Times New Roman'">      
Compress your SAS datasets!



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpMiddle>
style="mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri">
style="mso-list: Ignore">-
style="FONT: 7pt 'Times New Roman'">         
There are two methods by
which to implement data set compression in SAS. The overhead of uncompressing is
negligible compared to the I/O performance gains.



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l1 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">        
Data Set Option – manually
add an option to each Data Set that you create to compress it. For example DATA
A (COMPRESS=YES); SET X; RUN.  In DI
Studio, simply go to the options tab of the table to set the option.



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l1 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">        
System Option – the other
method is to set a system option COMPRESS=YES. When you set the system option
ALL SAS Data Sets are compressed. We recommend using the system option to insure
that all data sets are compressed.



class=MsoListParagraphCxSpMiddle>


face=Calibri> 



class=MsoListParagraphCxSpMiddle>NOTE: While
compress doesn’t improve RDBMS performance it improves the I/O interactions
between SAS and the RDBMS.



class=MsoListParagraphCxSpMiddle>


face=Calibri> 



class=MsoListParagraphCxSpMiddle>NOTE:
style="mso-spacerun: yes"> 
Not only can you compress your SAS Data
Sets but Oracle and other RDBMS also support Table level Compression. Oracle
table compression not only saves disk space, it also helps to increase cache
efficiency since more blocks can now fit in the memory. To do this you simply
need to pass an option when you create the table. For example data

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">ora.myOraTbl(DBCREATE_TABLE_OPTS=COMPRESS).



class=MsoListParagraphCxSpMiddle>


face=Calibri> 



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1"
class=MsoListParagraphCxSpMiddle>
style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin">
style="mso-list: Ignore">3.
style="FONT: 7pt 'Times New Roman'">      
Use Bulk Loaders when you
need to load large stuff fast!



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri">
style="mso-list: Ignore">
style="FONT: 7pt 'Times New Roman'">
 



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri">
style="mso-list: Ignore">
style="FONT: 7pt 'Times New Roman'">
DI
Studio has made bulk loading easy. The Loader transform loads data into tables
using both inserts, updates and bulk load operations. to invoke the bulk loader
simply set the option BULKLOAD = YES. The loader also provides RDBMS specific
options, for example with the Oracle loader you can do direct path loads simply
by setting the direct path optoin BL_DIRECT_PATH=YES.



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>In SAS code,
face=Calibri>SAS has made it simple BULKLOAD=YES will invoke the bulk load
utilities for most RDBMS. Each SAS/Access Engine provides RDBMS specific loader
options for example to get the best performance with Oracle use the
BL_DIRECT_PATH=YES option to perform a direct path load.
style="mso-spacerun: yes"> 
Here are the three most common examples
of bulk loading:



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>Example 1: OC SQL - create a
new table and bulk load it OR
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">append
results to an existing table.


style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">


class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">proc

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">sql

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">create

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">table

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
o.PROCSQLBL



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">     
(BULKLOAD=yes
BL_DIRECT_PATH=YES)



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">as

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">select

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
*

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">from

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
sales_1;



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

 



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">INSERT

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">INTO

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
TABLE o.PROCSQLBL



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">     
(BULKLOAD=yes
BL_DIRECT_PATH=YES)



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">as

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">SELECT

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
*

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">from

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
sales_1;



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">

 



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">quit

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>

Example 2:

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">DATA
STEP – create a new table and load it.



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">DATA

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
o.DATASTEPBL



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">    
(BULKLOAD=yes
BL_DIRECT_PATH=YES);



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">     

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">set

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
sales_1;



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">
 



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">Example
3.
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">PROC
APPEND – append results to an existing table.



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">PROC

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">APPEND

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">BASE

style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">=O.PROCAPPBL



class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">          
(BULKLOAD=yes BL_DIRECT_PATH=YES)



class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">           

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">DATA

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">=sales_1;


style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">


class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">run

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;


 



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1"
class=MsoListParagraphCxSpFirst>
style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin">
style="mso-list: Ignore">
size=3>4.
  
Gather, Collect, and Analyze
Table Statistics! The number one performance problem relational database (RDBMS)
is the lack of table level statistics for the RDBMS’ SQL optimizer. SAS
contributes to the problem because SAS makes it so easy to create tables; users
often overlook the gathering of optimizer statistics. You must gather statistics
periodically for table objects where the statistics become stale over time
because of changing data volumes or changes in column values. New statistics
should be gathered after a tables data or structure is modified. For example you
should gather statistics after any of the following:



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">        
After loading a significant number of rows into a
table.



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">        
After updating data in a table you might need new
statistics on the average row length.



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">        
After bulk load operations



style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpLast>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">        
After table structure has changed


Here is
an example of how to do this with Oracle



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">proc

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">sql

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">connect

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">to

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
Oracle( USER=XX PASSWORD=XX path=ORA10g);



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">execute

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
( execute



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes"> 

style="mso-tab-count: 1">   

style="mso-spacerun: yes">  

style="mso-tab-count: 1">   

style="mso-bidi-font-weight: normal">DBMS_STATS.GATHER_TABLE_STATS

(



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 2">           
  ownname =>

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: purple; FONT-SIZE: 10pt">'SH'

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">,



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes"> 

style="mso-tab-count: 1">   

style="mso-spacerun: yes">      

style="mso-spacerun: yes">  
tabname =>

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: purple; FONT-SIZE: 10pt">'SALES'

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">,



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">         

style="mso-spacerun: yes">    
estimate_percent =>

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: teal; FONT-SIZE: 10pt">40

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
) )

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">by

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
oracle;



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 2">           
 

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">disconnect

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">from

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
oracle;



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">quit

style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;



style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">

 


Note: a
best practice is to simply have an administrator setup a scheduled job to gather
schema statistics periodically.



 

SAS Employee
Posts: 51

Re: 4 Easy Tips to Dramatically Improve Integration Performance with Oracle

Posted in reply to Michael_SAS
One more tip to add to Mike's list: When faced with the need to perform a heterogeneous join between Oracle and some other database (SAS, SQL Server, DB2, etc), utilize the following features of the SQL Join transfsormation:

1. "Upload Library Before SQL"
2. "Use Bulkload for Uploading" - Yes
3. "Pre-Upload Action" - Truncate

These options should be applied to the smallest table in the join so that it is uploaded to the database of the larger table prior to join. The join can then occur completely on the host database, taking advantage of indexes and partitioning, while avoiding having all the data pulled down to SAS, which can be very expensive.

Thanks,

Tim Stearn
Super User
Posts: 10,041

Re: 4 Easy Tips to Dramatically Improve Integration Performance with Oracle

Posted in reply to Michael_SAS
Hi.
one side question.How do you make code colorful ?

Ksharp
SAS Employee
Posts: 38

Re: 4 Easy Tips to Dramatically Improve Integration Performance with Oracle

Posted in reply to Michael_SAS
I think you are asking about color coding in the code editor? If so, this should be on by default, but if not, go to Tools/Options/Code Editor, and check the "Document coloring" option. There are other interesting options in there as well that you might be interested in such as line numbers on/off, autoindent, key assignment, and other nice features.
Super User
Posts: 10,041

Re: 4 Easy Tips to Dramatically Improve Integration Performance with Oracle

Hi.
I mean the code posted in this forum,When I posted colored code in code editer to this forum, the color will be lost.


Ksharp
Ask a Question
Discussion stats
  • 4 replies
  • 282 views
  • 0 likes
  • 4 in conversation