80/20 rule, more like 40/80 rule..

The pareto principle suggest that with 20% of effort already 80% of the result can be achieved. Translated to business management, it is often reported that 80% of sales come from 20% of clients.

The following case study shows a example flight from Washington to London Heathrow operated by BA:

image

Even though the prices shown are undiscounted trip prices and before and yield optimization to fill the plane, the give a rough indication.

In this case, 45% of the passengers (of the none economy classes) will contribute 84% of the revenue.

image

This shows that the 80/20 rule can sometimes be more like a 40/80 rule.

This can also be visualized with a stacked bar graph:

image

Within Oracle OAC, a dedicated pareto graph is also available to show the relation between effort and results and the importance of the top contributors. This is definitely one of the patterns that each business should know about and beware of:

image

Advertisements

Error with DVD and DVML when installing in directory with spaces

It is recommended to install Oracle DVD into a directory without any spaces. While the installation of DVD will work for a directory with spaces like “C:\Program Files\Oracle\Data Visualization Desktop”, the subsequent installation of Oracle DVML will not work for the same. When installing DVML after installing DVD to a directory without spaces, the following error will be thrown:

"C:\Users\****\AppData\Local\Temp\BiPython_Framework.exe" is not recognized as command file.
Could Not Find C:\Users\****\AppData\Local\Temp\BiPython_Framework.exe
Error: Unable to access jarfile C:\PROGRA~1\Oracle\Data

This behavior is also describes in below Oracle Support Note:

DVML install fails for users with a space in Username Profile (Doc ID 2509892.1)

If the installation into the Program Files directories (like “C:\Program Files” or “C:\Program Files (x86)”) of Windows is intended, the following workaround can be applied to install DVD and DVML into a directory with spaces:

within script:

…\Oracle Data Visualization Desktop\install_dvml.cmd

add double quotes around below pathes (add double quotes shown in red below):

Line 20: … -jar %BI_PRODUCT_HOME%\modules\oracle.bi.dvdesktop.installer\rinstall.jar -download=true       …
Line 27: … -jar %BI_PRODUCT_HOME%\modules\oracle.bi.dvdesktop.installer\rinstall.jar -updateDVMLJSON=true

Afterwards the installation of DVML should run without errors.

ADWC Performance Benchmark on SSB Schema

The SSB Schema is a generated database schema, that can be used to evaluate the Performance of a Datawarehouse system. This is used to evaluate the performance between ADWC in the cloud and a local Oracle Database which is running within a Virtual Box image on a Laptop. This test is not conducted under test lab conditions and should only provide a rough order of magnitude.

System (Hardware) Baseline

The following instances have been used for the Performance test.

ADWC Trial

based on Hardware specification shown:

image

This instances is only used as reference, since the sizing (with 2 CPUs) and query performance for some queries based on the same SSB schema are publically available.

ADWC

instance which only uses 1 CPU

VM Image

with 4GB Ram

image

and 1 CPU

image

Based on Laptop Host system (Lenovo Yoga 370 Laptop) with:

image

The image runs Oracle Database 12c R2 Enterprise Edition, with no specific/tuned configuration (parameters) for Datawarehousing.

System Schema Baseline

image

Benchmark Performance

For Prologue Queries based on these examples. As mentioned before, for the ADW instance “ADWC Trail”, some queries run times are publically available. These queries have also been executed within the other instances. The runtimes of the ADWC Trail have only been used as reference:

image

SSB Queries, as outlined here and here (Oracle Documentation).  The below shows the query run-times for the ADWC and local Oracle Database (running as a Virtual Box image). The connection to the ADWC instance is made with the high connection type. When using the medium or low connection type, all queries were showing longer response times (as expected). Thus, the high connection type has been used for conducting the Benchmark.

image

The Excel file containing the system baseline in terms of number of records, queries and run-time results can be downloaded here.

Below is a galary of the executed SSB Queries on ADW and Virtual Box Oracle Database 12c:

Create the Star Schema Benchmark (SSB) within an Oracle Database

The Star Schema Benchmark (SSB) is a data model/schema with the possibility to use generated data, that can be used to Benchmark the Performance of any Data Warehouse. The following article describes the steps in order to create the Schema within an Oracle Database. Only the following file small zip file needs to be downloaded, since the data will be generated on the machine itself.

Download dbgen File

The File can be downloaded here: http://www.cs.umb.edu/~poneil/dbgen.zip

image

The zip file was downloaded into a Virtual Box Image (DeveloperDaysVM2017-02-10_19), but this procedure should work with any other Oracle Database and Linux OS.

Unzip dbgen File

After downloading unzip the dbgen.zip file with the below command:

[oracle@vbgeneric ssb]$ unzip dbgen.zip

image

This creates the folder:

image

Compilation of file

[oracle@vbgeneric ssb]$ cd dbgen/

[oracle@vbgeneric dbgen]$ make

image

[oracle@vbgeneric dbgen]$ ls –ltr

image

Generation of Database files

[oracle@vbgeneric dbgen]$ ./dbgen -s 205 -T c

image

This will generate a Customer Table File

image

After executing the remaining commands to create the Database Tables Files:

[oracle@vbgeneric dbgen]$ ./dbgen -s 7 -T p

[oracle@vbgeneric dbgen]$ ./dbgen -s 1000 -T s

[oracle@vbgeneric dbgen]$ ./dbgen -s 1 -T d

[oracle@vbgeneric dbgen]$ ./dbgen -s 16 -T l

image

The following files are created by the dbgen executable:

image

Declare schema and external Tables

Run the following script in SQL Developer:

CREATE USER ssb IDENTIFIED BY ssb;

GRANT CREATE SESSION,
CREATE TABLE,
CREATE ANY DIRECTORY,
UNLIMITED TABLESPACE
TO ssb;
CREATE OR REPLACE DIRECTORY ssb_dir AS '/home/oracle/Downloads/ssb/dbgen';
GRANT READ, WRITE ON DIRECTORY ssb_dir TO ssb;
--external Line Order Table
CREATE TABLE ssb.ext_lineorder
(
lo_orderkey        INTEGER,
lo_linenumber      NUMBER(1, 0),
lo_custkey         INTEGER,
lo_partkey         INTEGER,
lo_suppkey         INTEGER,
lo_orderdate       INTEGER,
lo_orderpriority   CHAR(15),
lo_shippriority    CHAR(1),
lo_quantity        NUMBER(2, 0),
lo_extendedprice   NUMBER,
lo_ordtotalprice   NUMBER,
lo_discount        NUMBER(2, 0),
lo_revenue         NUMBER,
lo_supplycost      NUMBER,
--lo_ordsupplycost   NUMBER, -- this is mentioned in 2.2 Notes(c) but isn't in the layout or sample queries, so not needed?
lo_tax             NUMBER(1, 0),
lo_commitdate      INTEGER,
lo_shipmode        CHAR(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('lineorder.tbl*'))
PARALLEL 4;

--Target Lineorder Table, the external Table will be loaded into the below Table
CREATE TABLE ssb.lineorder
(
lo_orderkey        INTEGER NOT NULL,
lo_linenumber      NUMBER(1, 0) NOT NULL,
lo_custkey         INTEGER NOT NULL,
lo_partkey         INTEGER NOT NULL,
lo_suppkey         INTEGER NOT NULL,
lo_orderdate       NUMBER(8,0) NOT NULL,
lo_orderpriority   CHAR(15) NOT NULL,
lo_shippriority    CHAR(1) NOT NULL,
lo_quantity        NUMBER(2, 0) NOT NULL,
lo_extendedprice   NUMBER NOT NULL,
lo_ordtotalprice   NUMBER NOT NULL,
lo_discount        NUMBER(2, 0) NOT NULL,
lo_revenue         NUMBER NOT NULL,
lo_supplycost      NUMBER NOT NULL,
--lo_ordsupplycost   NUMBER not null, -- this is mentioned in 2.2 Notes(c) but isn't in the layout or sample queries, so not needed?
lo_tax             NUMBER(1, 0) NOT NULL,
lo_commitdate      NUMBER(8,0) NOT NULL,
lo_shipmode        CHAR(10) NOT NULL
);

CREATE TABLE ssb.ext_part
(
p_partkey     INTEGER,
p_name        VARCHAR2(22),
p_mfgr        CHAR(6),
p_category    CHAR(7),
p_brand1      CHAR(9),
p_color       VARCHAR2(11),
p_type        VARCHAR2(25),
p_size        NUMBER(2, 0),
p_container   CHAR(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('part.tbl'));

CREATE TABLE ssb.part
(
p_partkey     INTEGER NOT NULL,
p_name        VARCHAR2(22) NOT NULL,
p_mfgr        CHAR(6) NOT NULL,
p_category    CHAR(7) NOT NULL,
p_brand1      CHAR(9) NOT NULL,
p_color       VARCHAR2(11) NOT NULL,
p_type        VARCHAR2(25) NOT NULL,
p_size        NUMBER(2, 0) NOT NULL,
p_container   CHAR(10) NOT NULL
);

CREATE TABLE ssb.ext_supplier
(
s_suppkey   INTEGER,
s_name      CHAR(25),
s_address   VARCHAR2(25),
s_city      CHAR(10),
s_nation    CHAR(15),
s_region    CHAR(12),
s_phone     CHAR(15)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('supplier.tbl'));

CREATE TABLE ssb.supplier
(
s_suppkey   INTEGER NOT NULL,
s_name      CHAR(25) NOT NULL,
s_address   VARCHAR2(25) NOT NULL,
s_city      CHAR(10) NOT NULL,
s_nation    CHAR(15) NOT NULL,
s_region    CHAR(12) NOT NULL,
s_phone     CHAR(15) NOT NULL
);

CREATE TABLE ssb.ext_customer
(
c_custkey      INTEGER,
c_name         VARCHAR2(25),
c_address      VARCHAR2(25),
c_city         CHAR(10),
c_nation       CHAR(15),
c_region       CHAR(12),
c_phone        CHAR(15),
c_mktsegment   CHAR(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('customer.tbl'));

CREATE TABLE ssb.customer
(
c_custkey      INTEGER NOT NULL,
c_name         VARCHAR2(25) NOT NULL,
c_address      VARCHAR2(25) NOT NULL,
c_city         CHAR(10) NOT NULL,
c_nation       CHAR(15) NOT NULL,
c_region       CHAR(12) NOT NULL,
c_phone        CHAR(15) NOT NULL,
c_mktsegment   CHAR(10) NOT NULL
);

CREATE TABLE ssb.ext_date_dim
(
d_datekey            NUMBER(8,0),
d_date               CHAR(18),
d_dayofweek          CHAR(9),    -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
d_month              CHAR(9),
d_year               NUMBER(4, 0),
d_yearmonthnum       NUMBER(6, 0),
d_yearmonth          CHAR(7),
d_daynuminweek       NUMBER(1, 0),
d_daynuminmonth      NUMBER(2, 0),
d_daynuminyear       NUMBER(3, 0),
d_monthnuminyear     NUMBER(2, 0),
d_weeknuminyear      NUMBER(2, 0),
d_sellingseason      CHAR(12),
d_lastdayinweekfl    NUMBER(1, 0),
d_lastdayinmonthfl   NUMBER(1, 0),
d_holidayfl          NUMBER(1, 0),
d_weekdayfl          NUMBER(1, 0)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ssb_dir
ACCESS PARAMETERS (
FIELDS
TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION('date.tbl'));

CREATE TABLE ssb.date_dim
(
d_datekey            NUMBER(8,0) NOT NULL,
d_date               CHAR(18) NOT NULL,
d_dayofweek          CHAR(9) NOT NULL,    -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
d_month              CHAR(9) NOT NULL,
d_year               NUMBER(4, 0) NOT NULL,
d_yearmonthnum       NUMBER(6, 0) NOT NULL,
d_yearmonth          CHAR(7) NOT NULL,
d_daynuminweek       NUMBER(1, 0) NOT NULL,
d_daynuminmonth      NUMBER(2, 0) NOT NULL,
d_daynuminyear       NUMBER(3, 0) NOT NULL,
d_monthnuminyear     NUMBER(2, 0) NOT NULL,
d_weeknuminyear      NUMBER(2, 0) NOT NULL,
d_sellingseason      CHAR(12) NOT NULL,
d_lastdayinweekfl    NUMBER(1, 0) NOT NULL,
d_lastdayinmonthfl   NUMBER(1, 0) NOT NULL,
d_holidayfl          NUMBER(1, 0) NOT NULL,
d_weekdayfl          NUMBER(1, 0) NOT NULL
);

image

Check that the external files can be accessed by the Database. For testing execute the following query:

select count (*) from ssb.ext_date_dim;

image

Copy from External Tables to Database Tables

From the external Table the data can be loaded to the Database tables using the following commands:

ALTER TABLE ssb.lineorder PARALLEL 4;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND */ INTO  ssb.date_dim  SELECT * FROM ssb.ext_date_dim;
commit;
DROP TABLE ssb.ext_date_dim;
select count (*) from ssb.date_dim;

INSERT /*+ APPEND */ INTO  ssb.part SELECT * FROM ssb.ext_part;
commit;
DROP TABLE ssb.ext_part;
select count (*) from ssb.part;

INSERT /*+ APPEND */ INTO  ssb.supplier  SELECT * FROM ssb.ext_supplier;
commit;
DROP TABLE ssb.ext_supplier;
select count (*) from ssb.supplier;

INSERT /*+ APPEND */ INTO  ssb.customer  SELECT * FROM ssb.ext_customer;
commit;
DROP TABLE ssb.ext_customer;
select count (*) from ssb.customer;

INSERT /*+ APPEND */ INTO  ssb.lineorder SELECT * FROM ssb.ext_lineorder;
commit;
DROP TABLE ssb.ext_lineorder;
select count (*) from ssb.lineorder;

Create Indexes and Constraints on Database Tables

Execute the following commands in SQL Developer:

ALTER TABLE ssb.lineorder
ADD CONSTRAINT pk_lineorder PRIMARY KEY(lo_orderkey, lo_linenumber);

ALTER TABLE ssb.part
ADD CONSTRAINT pk_part PRIMARY KEY(p_partkey);

ALTER TABLE ssb.supplier
ADD CONSTRAINT pk_supplier PRIMARY KEY(s_suppkey);

ALTER TABLE ssb.customer
ADD CONSTRAINT pk_customer PRIMARY KEY(c_custkey);

ALTER TABLE ssb.date_dim
ADD CONSTRAINT pk_date_dim PRIMARY KEY(d_datekey);

ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_customer FOREIGN KEY(lo_custkey) REFERENCES ssb.customer(c_custkey);

ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_part FOREIGN KEY(lo_partkey) REFERENCES ssb.part(p_partkey);

ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_supplier FOREIGN KEY(lo_suppkey) REFERENCES ssb.supplier(s_suppkey);

ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_orderdate FOREIGN KEY(lo_orderdate) REFERENCES ssb.date_dim(d_datekey);

ALTER TABLE ssb.lineorder
ADD CONSTRAINT fk_lineitem_commitdate FOREIGN KEY(lo_commitdate) REFERENCES ssb.date_dim(d_datekey);

After running all commands, the Schema is created and the tables can be queried:

image

The following also shows the checked Table Size within the Oracle Database:

image

This Article is based on:

An alternative approach of loading the SSB data is described here:

Comparing Database Table Size, records and Export Files

The is a listing a an Example Database Table, the size it consumes within the Database and the Size an Export of the same will consume. The “Part” Table contains information of certain Parts which are supplied by different manufactures and looks like the following:

image 

The Table contains 1.4 Mio records:

image

Within the Database this Table consumes “only” 25 MB of storage:

image

When exporting the Table using the Export Wizard of SQL Developer and choosing the loader format,

image

the same Table consumes 147 MB of storage:

image

Thus, the is nearly a factor of 6 between the space a Table consumes within the Database and the exported information of the same.


retrieve Table Sizes in Oracle Database

To retrieve the Table Size of a Database Table in Oracle Database, the following SQL code can be used (also without DBA rights to DBA_Segments Data Dictionary Table):

image

select bytes/1024/1024 MB 
from user_segments 
where segment_name='<Table Name>';

E.g.

select bytes/1024/1024 MB 
from user_segments 
where segment_name='CUSTOMER';