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.
