Feed aggregator

Oracle Receives Department of Justice Clearance to Buy NetSuite

Oracle Press Releases - Mon, 2016-09-26 12:17
Press Release
Oracle Receives Department of Justice Clearance to Buy NetSuite

Redwood Shores, Calif.—Sep 26, 2016

Oracle (NYSE: ORCL) today announced that it has received the final antitrust clearance approval necessary for the acquisition of NetSuite, Inc. (NYSE: N) from the U.S. Department of Justice. The all-cash tender offer for NetSuite at $109.00 per share, set to expire on October 6, 2016, will proceed as planned assuming the minimum required number of shares are tendered.

Contact Info
Deborah Hellinger
Oracle
+1.212.508.7935
deborah.hellinger@oracle.com
Ken Bond
Oracle
+1.650.607.0349
ken.bond@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Additional Information

This communication does not constitute an offer to buy or solicitation of an offer to sell any securities. This communication is for informational purposes only. The tender offer is not being made to, nor will tenders be accepted from, or on behalf of, holders of shares in any jurisdiction in which the making of the tender offer or the acceptance thereof would not comply with the laws of that jurisdiction. The tender offer is being made pursuant to a tender offer statement on Schedule TO (including the Offer to Purchase, a related Letter of Transmittal and other offer materials) filed by Napa Acquisition Corporation with the U.S. Securities and Exchange Commission ("SEC") on August 18, 2016, as amended from time to time. In addition, on August 18, 2016, NetSuite Inc. filed a Solicitation/Recommendation statement on Schedule 14D-9 with the SEC related to the tender offer. Stockholders of NetSuite Inc. are urged to read these documents, all amendments thereto and other documents filed with the SEC carefully in their entirety because they contain important information about the tender offer. The tender offer statement and certain other offer documents, along with the Solicitation/Recommendation statement, will be made available to all stockholders of NetSuite Inc. at no expense to them. These documents are available at no charge through the web site maintained by the SEC at http://www.sec.gov. The Offer to Purchase, related Letter of Transmittal, the Solicitation/Recommendation statement and other offering documents may also be obtained for free by contacting the Information Agent for the tender offer, Innisfree, M&A Incorporated, toll-free at 888-750-5834.

Cautionary Statement Regarding Forward-Looking Statements

This document contains certain forward-looking statements about Oracle and NetSuite Inc., including statements that involve risks and uncertainties concerning Oracle's proposed acquisition of NetSuite Inc., anticipated customer benefits and general business outlook. When used in this document, the words "anticipates," "can," "will," "look forward to," "expected," and similar expressions and any other statements that are not historical facts are intended to identify those assertions as forward-looking statements. Any such statement may be influenced by a variety of factors, many of which are beyond the control of Oracle or NetSuite Inc., that could cause actual outcomes and results to be materially different from those projected, described, expressed or implied in this document due to a number of risks and uncertainties. Potential risks and uncertainties include, among others, the possibility that the transaction will not close or that the closing may be delayed, the anticipated synergies of the combined companies may not be achieved after closing, the combined operations may not be successfully integrated in a timely manner, if at all, general economic conditions in regions in which either company does business, and the possibility that Oracle or NetSuite Inc. may be adversely affected by other economic, business, and/or competitive factors. Accordingly, no assurances can be given that any of the events anticipated by the forward-looking statements will transpire or occur, or if any of them do so, what impact they will have on the results of operations or financial condition of Oracle or NetSuite Inc. You are cautioned to not place undue reliance on forward-looking statements, which speak only as of the date of this document. Neither Oracle nor NetSuite Inc. is under any duty to update any of the information in this document.

In addition, please refer to the documents that Oracle and NetSuite Inc., respectively, file with the SEC on Forms 10-K, 10-Q and 8-K. These filings identify and address other important factors that could cause Oracle's and NetSuite Inc.'s respective operational and other results to differ materially from those contained in the forward-looking statements set forth in this document. You are cautioned to not place undue reliance on forward-looking statements, which speak only as of the date of this document. Neither Oracle nor NetSuite Inc. is under any duty to update any of the information in this document.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Deborah Hellinger

  • +1.212.508.7935

Ken Bond

  • +1.650.607.0349

SQLLoader DIRECT option and Unique Index

Hemant K Chitale - Mon, 2016-09-26 09:59
The DIRECT parameter for SQLLoader Command-Line enables Direct Path Load which uses a Direct Path API instead of a regular INSERT statement to load data into the target table.

However, one needs to know how it handles a Unique Index on the target table.
It actually leaves the  UNUSABLE if, duplicate values are loaded. The Index rebuild fails but the duplicate values remain in the table.

Here is a quick demo  (this in 12.1.0.2 MultiTenant). I first setup the target table with a Unique Index.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:36:51 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Sep 26 2016 22:26:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> create table test_sqlldr_direct
2 (id_column number,
3 data_column varchar2(15))
4 /

Table created.

SQL> create unique index test_sqlldr_direct_u1 on test_sqlldr_direct(id_column);

Index created.

SQL> insert into test_sqlldr_direct values (1, 'First Row');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$

Next, I setup the datafile with a duplicate record and the controlfile.

[oracle@ora12102 Desktop]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
[oracle@ora12102 Desktop]$ cat load_data.dat
2,'Second Row'
3,'Third Row'
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.ctl
LOAD DATA
INFILE load_data.dat
APPEND INTO TABLE TEST_SQLLDR_DIRECT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id_column,
data_column)
[oracle@ora12102 Desktop]$

I am now ready to run a Direct Path Load.

[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@pdb1 control=load_control.ctl direct=TRUE

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Direct

Load completed - logical record count 3.

Table TEST_SQLLDR_DIRECT:
3 Rows successfully loaded.

Check the log file:
load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$

What is that ? 3 rows loaded successfully ?  So, the duplicate row also did get loaded ?  Let's check the log file.

[oracle@ora12102 Desktop]$ ls -ltr
total 12
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 1833 Sep 26 22:47 load_control.log
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: load_control.ctl
Data File: load_data.dat
Bad File: load_data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN FIRST * , O(") CHARACTER
DATA_COLUMN NEXT * , O(") CHARACTER

The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Table TEST_SQLLDR_DIRECT:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Mon Sep 26 22:47:09 2016
Run ended on Mon Sep 26 22:47:11 2016

Elapsed time was: 00:00:01.88
CPU time was: 00:00:00.01
[oracle@ora12102 Desktop]$

Did you notice the section in the log file that says :
The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Apparently, the Index is left UNUSABLE.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:50:51 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Sep 26 2016 22:47:09 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
UNUSABLE

SQL> select * from test_sqlldr_direct order by 1;

ID_COLUMN DATA_COLUMN
---------- ---------------
1 First Row
2 'Second Row'
3 'Third Row'
3 'Oops !'

SQL> alter index test_sqlldr_direct_u1 rebuild;
alter index test_sqlldr_direct_u1 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


SQL>

We can see the duplicated row for ID_COLUMN=3 and find that the Index cannot be rebuilt.  Oracle has allowed duplicate rows to load and left the Index UNUSABLE.

So, if you are planning to use DIRECT=TRUE and have a Unique Index, make sure you check the status of the Index and/or check the Log file before you proceed with processing the data.


Conversely, here is how the data is handled without DIRECT=TRUE :.

SQL> truncate table test_sqlldr_direct;

Table truncated.

SQL> insert into test_sqlldr_direct values (1,'First Row');

1 row created.

SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
VALID

SQL>

[oracle@ora12102 Desktop]$ rm load_control.log
[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@PDB1 control=load_control.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 3

Table TEST_SQLLDR_DIRECT:
2 Rows successfully loaded.

Check the log file:
load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 11 Sep 26 22:59 load_data.bad
-rw-r--r-- 1 oracle oinstall 1668 Sep 26 22:59 load_control.log
[oracle@ora12102 Desktop]$ cat load_data.bad
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: load_control.ctl
Data File: load_data.dat
Bad File: load_data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN FIRST * , O(") CHARACTER
DATA_COLUMN NEXT * , O(") CHARACTER

Record 3: Rejected - Error on table TEST_SQLLDR_DIRECT.
ORA-00001: unique constraint (HEMANT.TEST_SQLLDR_DIRECT_U1) violated


Table TEST_SQLLDR_DIRECT:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Mon Sep 26 22:59:58 2016
Run ended on Mon Sep 26 22:59:58 2016

Elapsed time was: 00:00:00.07
CPU time was: 00:00:00.00
[oracle@ora12102 Desktop]$

SQL> select * from test_sqlldr_direct
2 order by id_column
3 /

ID_COLUMN DATA_COLUMN
---------- ---------------
1 First Row
2 'Second Row'
3 'Third Row'

SQL>
SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
VALID

SQL>

The duplicate row was rejected and went to the BAD file and was REJECTED.
.
.
.
So, the next time you chose to use SQLLDR DIRECT=TRUE for its performance benefits, make sure you know how to validate the status of Unique Indexes and check the log file ! Else, you might allow duplicates if the incoming data has not been cleansed before loading into the database.
.
.
.
If you are running 11g, the behaviour is the same in 11g/
.
.
.
.

Categories: DBA Blogs

Version Control for PL/SQL

Gerger Consulting - Mon, 2016-09-26 07:13
Today, we are happy to announce the new version of Gitora, Gitora 2.0, the version control tool for PL/SQL developers.



Gitora helps PL/SQL developers manage their source code in Git easily. It helps them lock database objects to prevent edits from other users regardless of the editor they use, create, change branches, merge code from other schemas or databases and see the source code in the database change instantly, generate diff scripts automatically and automate processes for continuous integration.

Gitora is the easiest and fastest way to put your source code in version control and improve your database development practices. It requires no client machine installation (all installation is server side), and is free to use for one database.

Learn more and download at http://www.gitora.com
Categories: Development

Oracle Marketing Cloud Extends Leadership with Next Generation of Cross-Channel Orchestration Canvas

Oracle Press Releases - Mon, 2016-09-26 07:00
Press Release
Oracle Marketing Cloud Extends Leadership with Next Generation of Cross-Channel Orchestration Canvas Marketers are able to more quickly and easily deliver personalized experiences at scale

Redwood Shores, Calif.—Sep 26, 2016

Oracle today announced industry-first innovations to the Oracle Marketing Cloud that will transform the way consumer marketers interact with customers across channels. First to market in 2009 with the ‘Program Canvas’, these new enhancements make it easier for marketers to develop personalized customer experiences across web, mobile web, display, mobile apps and email, and to deliver these experiences based on unified consumer identities from different channels. For the first time, brands will be able to orchestrate a consistent and personalized experience to consumers at scale.

Consumer marketers are under intense pressure to deliver consistent and personalized experiences, but are challenged with working in teams that operate in silos using only channel specific customer data. Without the context of a unified consumer identity, marketers are just scaling batch and blast practices across multiple channels. To address this challenge, Oracle has introduced the next evolution in campaign management with its ‘Orchestration Canvas’, which helps brands align and manage marketing activities across channels and gain a holistic view of the customer. Oracle is making it quicker and easier to deliver the best experiences by offering a single platform for marketers to test, optimize and execute marketing campaigns for any digital touch point across the customer journey.

“Effectively orchestrating cross-channel marketing activities can be extremely complex for brands, but it is an area that marketing must tackle as discrepancies in experiences, and sometimes prices, erode trust and distance customers from a brand,” said Steve Krause, Group Vice President Product Management, Oracle Marketing Cloud. “To help our customers address these challenges, we have broken down the silos that typically separate marketing teams with our new cross-channel marketing solution. This will help improve collaboration and empower marketing to orchestrate relevant real-time experiences.”

The ‘Orchestration Canvas’ is a simple, yet sophisticated user interface of the Oracle Marketing Cloud that enables consumer marketers to efficiently manage experiences at every touch point throughout the entire consumer lifecycle. Additional enhancements include a Mobile App Visual Editor where marketers can test and optimize mobile experiences across their mobile apps and as a part of the cross-channel experience. With the new additions to the Oracle Marketing Cloud, marketers can now benefit from:

  • Adaptive Customer Path Creation and Optimization: The customer purchase path is unpredictable, inconsistent and spread across an increasingly diverse set of digital and offline channels. Orchestration Canvas allows marketers to create event- and behavior-driven adaptive paths that let customers dictate the next step in their interaction with a brand. Customers optimize their own path to purchase, creating less friction in the buying process and better buying experiences.
  • True Cross Channel Orchestration: Messages across channels can be generic, repetitive and irrelevant, which can weaken a customer relationship. Orchestration Canvas helps marketers connect all of a customer's identities across channels to a single, unified profile with a rich understanding of each individual. Marketers can use these enhanced profiles to provide more personalized and relevant customer experiences across channels.
  • Powerful, Marketer-Friendly Mobile Testing: Marketers can use a code-free, drag and drop interface to test and optimize mobile application experiences using everything from simple A/B tests, to complex Multivariate Testing and Segment Personalization. In addition, marketers use rich customer preference and behavior data from mobile campaigns to inform other marketing campaigns being managed on the Oracle Marketing Cloud.
  • Integrated Stack and Ecosystem: Marketers can unify customer data and applications through Oracle Marketing Cloud's integrated stack, helping teams across the customer experience chain – from marketing to sales, commerce and customer service - bring a rich, unified and personalized experience to customers. In addition, marketers can use prebuilt integrations with the Oracle Customer Experience (CX) Cloud to leverage preference and behavioral data from across all interactions to further enhance customer profiles.

An end-to-end solution that includes Oracle Commerce, Oracle Configure, Price, and Quote Cloud (Oracle CPQ Cloud), Oracle Marketing Cloud, Oracle Sales Cloud, Oracle Service Cloud, and Oracle Social Cloud, Oracle CX Cloud is one of the industry’s most complete CX solutions. Part of Oracle Applications Cloud, Oracle CX Cloud empowers organizations to improve experiences, enhance loyalty, differentiate their brands, and drive measurable results by creating consistent, connected, and personalized brand experiences across all channels and devices.

Contact Info
Erik Kingham
Oracle
+1.650.506.8298
erik.kingham@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Erik Kingham

  • +1.650.506.8298

Order by - varchar column storing numeric values

Tom Kyte - Mon, 2016-09-26 06:06
Hi Tom I have a table 'LOCS' with 2 columns of varchar datatype (col1 is varchar yet at the moment we have only numeric values stored): col1 col2 2272 ABC 22722 ABCD 1000 dgdfg 10001 dfm Now, when I query the table: select * from locs...
Categories: DBA Blogs

Record count on basis of insert/delete triggers

Tom Kyte - Mon, 2016-09-26 06:06
Hi, We have a pagination requirement across the UI foothold where we need to show the total# of records for significant db facts off the tables in view of numbers. And the query are not utilizing query filter criteria as triggerd by a menu option ...
Categories: DBA Blogs

dbms_job taking a long time

Tom Kyte - Mon, 2016-09-26 06:06
Hi, There is a SQL program whose job is to pick up data from multiple tables based on an application ID and populate an MIS table. There are 25-30 queries written to fetch data from multiple tables for an application ID. Data is pulled into col...
Categories: DBA Blogs

How to set the INITTRANS value when Isolation level as Serializable

Tom Kyte - Mon, 2016-09-26 06:06
Hi Tom, We current need to set the serializable isolation level, and will enable the ROWDEPENDENCIES when create the table like as below. My question is what is an optimal value for INITRANS? Or what should we consider when set this parameter? Tha...
Categories: DBA Blogs

How to Calculate the Mean, Median, and Mode in Oracle

Complete IT Professional - Mon, 2016-09-26 06:00
Calculating the mean, median, and mode in Oracle SQL is a task often performed by report developers or anyone else working with SQL. Learn how to calculate these three values from a range of data in this article. What is the Mean, Median, and Mode? Mean, median, and mode are three kinds of average values […]
Categories: Development

PK Histogram

Jonathan Lewis - Mon, 2016-09-26 04:25

One of the little myths of Oracle appeared on the Oracle-L list server a few days ago – the one that says: “you don’t need a histogram on a single column unique/primary key”.

Not only can a histogram be helpful on a column that’s declared to hold unique values, the optimizer may even spot the need automatically. It’s a little unusual (and probably the result of poor programming practice) but it does happen. Here’s an example demonstrating the principle:


rem
rem     Script:         pk_histogram.sql
rem     Author:         Jonathan Lewis
rem
rem     Last tested
rem             12.1.0.2
rem             11.2.0.4
rem             11.1.0.7
rem             10.2.0.5
rem

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        rownum  id
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4
;

insert into t1 select id + 1e6 from t1;
insert into t1 select id + 1e7 from t1;

alter table t1 add constraint t1_pk primary key(id);

select
        /*+ dynamic_sampling(0) */
        *
from    t1
where
        id between 12000 and 13000
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1'
        );
end;
/


select
        column_name, sample_size,
        num_distinct, num_nulls, density,
        histogram, num_buckets
from   
        user_tab_cols
where
        table_name = 'T1'
order by
        column_name
;

I’ve created a small data set some large gaps in the ranges of values used, then queried the data with a range-based predicate that will return no rows. After that I’ve let Oracle do it’s own thing with gathering stats (I think all the preferences are at their defaults). This is the result I got:

COLUMN_NAME          SAMPLE_SIZE NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM       NUM_BUCKETS
-------------------- ----------- ------------ ---------- ---------- --------------- -----------
ID                          5555        40000          0 .000024752 HEIGHT BALANCED         254

Oracle will gather a histogram automatically if it can see that the distribution of the data you are querying is skewed; but people tend to think of “skewed” as meaning there is wide variation in the number of rows for a given value and forget that it can also mean a variation in the number of rows within a given size of range. All I’ve done with my example is cue Oracle to the fact that I have big gaps in the range of known values – so it has produced a histogram that let’s it know where the gaps are.

Note: The results above are from 11.2.0.4, in 12.1.0.2 I got a HYBRID histogram, on 10.2.0.5 the sample size was 40,000


Making Hadoop easier

Pat Shuff - Mon, 2016-09-26 02:07
Last week we looked at provisioning a Hadoop server and realized that the setup was a little complex and somewhat difficult. This is what people typically do the first time when they want to provision a service. They download the binaries (or source if you are really crazy) and install everything from scratch. Our recommendation is to do everything this way the first time. It does help you get a better understanding of how the setup works and dependencies. For example, Hadoop 2.7.3 required Java 1.8 or greater. If we go with Hadoop 2.7.2 we can get by with Java 1.7.

Rather than going through all of the relationships, requirements, and libraries needed to get something working we are going to do what we would typically do to spin up a server if we suddenly need one up and running. We go to a service that provides pre-compiled and pre-configured public domain code sandboxes and get everything running that way. The service of choice for the Oracle Compute Cloud is Bitnami We can search for a Hadoop configuration and provision it into our IaaS foundation. Note that we could do the same using the Amazon EMR and get the same results. The key difference between the two are configurations, number of servers, and cost. We are going to go through the Bitnami deployment on the Oracle Cloud in this blog.

Step 1 Search for Hadoop on http://oracle.bitnami.com and launch the instance into your region of choice.

Step 2 Configure and launch the instance. We give the instance a name, we increase the default disk size from 10 GB to 60 GB to have room for data, we go with the hadoop 2.7.2-1 version, select Oracle Linux 6.7 as the OS (Ubuntu is an alternative), and go with a small OC3 footprint for the compute size. Don't change the security rules. A new one will be generated for you as well as the ssh keys when you provision through this service.

Step 3 Log into your instance. To do this you will need ssh and use the keys that bitnami generates for you. The instance creation takes 10-15 minutes and should show you a screen with the ip address and have links for you to download the keys.

Step 4 Once you have access to the master system you can execute the commands that we did last week. The only key difference with this implementation is that you will need to install java-1.8 with a yum install because by default the development kit is not installed and we need the jar functionality as part of configuration. The steps needed to repeat our tests from the previous blog entry.

 --- setup hdfs file system 
   hdfs namenode -format
   hdfs getconf -namenodes
   hdfs dfs -mkdir input
   cp /opt/bitnami/hadoop/etc/hadoop/*.xml input
   hdfs dfs -put input/*.xml input
 --- setup simple test with wordcount
   hdfs dfs -mkdir wordcount
   hdfs dfs -mkdir wordcount/input
   mkdir ~/wordcount
   mkdir ~/wordcount/input
   vi file01
   mv file01 ~/wordcount/input
   vi ~/wordcount/input/file02
   hdfs dfs -put ~/wordcount/input/* wordcount/input
   vi WordCount.java
 --- install java-1.8 to get all of the libraries
   sudo yum install java-1.8\*
 --- create ec.jar file
   export HADOOP_CLASSPATH=/opt/bitnami/java/lib/tools.jar
   hadoop com.sun.tools.javac.Main WordCount.java
   jar cf wc.jar WordCount*.class
   hadoop jar wc.jar WordCount wordcount/input wordcount/output
   hadoop fs -cat wordcount/output/part-r-00000
 --- download data and test pig
   mkdir data
   cd data
   w get http://stat-computing.org/dataexpo/2009/1987.csv.bz2
   w get http://stat-computing.org/dataexpo/2009/1988.csv.bz2
   bzip2 -d 1987.csv.bz2
   bzip2 -d 1988.csv.bz2
   hdfs dfs -mkdir airline
   hdfs dfs -copyFromLocal 19*.csv airline
   vi totalmiles.pig
   pig totalmiles.pig
   hdfs dfs -cat data/totalmiles/part-r-00000

Note that we can do the exact same thing using Amazon AWS. They have a MapReduce product called EMR. If you go to the main console, click on EMR at the bottom of the screen, you can create a Hadoop cluster. Once you get everything created and can ssh into the master you can repeat the steps above.

I had a little trouble with the WordCount.java program in that the library version was a little different. The JVM_1.7 libraries had a problem linking and adding the JVM_1.8 binaries did not properly work with the Hadoop binaries. You also need to change the HADOOP_CLASSPATH to point to the proper tools.jar file since it is in a different location from the Bitnami install. I think with a little tweaking it would all work. The pig sample code works with no problem so we were able to test that without changing anything.

In summary, provisioning a Hadoop server or cluster in the cloud is very easy if someone else has done the heavy lifting and pre-configured a server or group of servers for you. I was able to provision two clusters before lunch, run through the exercises, and still have time to go through it again to verify. Using a service like private Marketplaces, Bitnami, or the AWS Marketplace makes it much simpler to deploy sandbox images.

Oracle 12cR2: IS_ROLLING_INVALID in V$SQL

Yann Neuhaus - Sun, 2016-09-25 11:51

In a previous post I published a test case to show when a cursor is not shared anymore after a rolling invalidation. Basically the dbms_stats marks the cursor as ‘rolling invalid’ and the next execution marks it as ‘rolling invalid executed’. Looking at 12cR2 there is a little enhancement in V$SQL with an additional column displays those states.

Note that 12cR2 full documentation is not yet available, but you can test this on the Exadata Express Cloud Service.

I set the invalidation period to 5 seconds instead of 5 hours to show the behavior without waiting

17:43:52 SQL> alter system set "_optimizer_invalidation_period"=5;
System altered.

I’ll run a statement with dbms_sql in order to separate parse and execute phases

17:43:53 SQL> variable c number
17:43:53 SQL> exec :c := dbms_sql.open_cursor;
PL/SQL procedure successfully completed.
17:43:53 SQL> exec dbms_sql.parse(:c, 'select (cast(sys_extract_utc(current_timestamp) as date)-date''1970-01-01'')*24*3600 from DEMO' , dbms_sql.native );
PL/SQL procedure successfully completed.

Here is the cursor from V$SQL including the new IS_ROLLING_INVALID column:

17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 0 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 N

Statement is parsed (one parse call + load) but IS_ROLLING_INVALID is N

Now I execute it:

17:43:53 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
0
PL/SQL procedure successfully completed.
 
17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 1 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 N

Statement has one execution.

I’m now gathering statistics with default rolling invalidation:

17:43:53 SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
 
17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 1 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 Y

The cursor is now marked as rolling invalid (IS_ROLLING_INVALID=”Y”) but wait, this is not a “Y”/”N” boolean, there’s another possible value.

I execute the statement again (no parse call, only execution):

17:43:53 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
0
PL/SQL procedure successfully completed.
 
17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X

Cursor is now marked as rolling invalid executed (“X”) and this is where the rolling window starts (which I’ve set to 5 seconds instead of 5 hours)

I wait 5 seconds and the cursor has not changed:

17:43:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X
 

I execute it again (no parse call, only re-execute the cursor):

17:43:58 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
0
PL/SQL procedure successfully completed.

For this execution, a new child has been created:

17:43:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X
0 1 0 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 17:43:57 N

So rolling invalidation do not require a parse call. Execution can start the rolling window and set the invalidation timestamp, and first execution after this timestamp creates a new child cursor.

I’ll now test what happens with parse calls only.

I set a longer rolling window (2 minutes) here:

17:43:58 SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
 
17:43:58 SQL> alter system set "_optimizer_invalidation_period"=120;
System altered.

The last child has been marked as rolling invalid but not yet executed in this state:

17:43:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X
0 1 0 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 17:43:57 Y

From a new session I open another cursor:

17:43:58 SQL> connect &_user./demo@&_connect_identifier
Connected.
17:43:58 SQL> exec :c := dbms_sql.open_cursor;
PL/SQL procedure successfully completed.

And run several parse calls without execute, one every 10 seconds:

17:43:58 SQL> exec for i in 1..12 loop dbms_sql.parse(:c, 'select (cast(sys_extract_utc(current_timestamp) as date)-date''1970-01-01'')*24*3600 from DEMO' , dbms_sql.native ); dbms_lock.sleep(10); end loop;
PL/SQL procedure successfully completed.

So two minutes later I see that I have a new child created during the rolling window:

17:45:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTI IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- --------- ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 X
0 1 3 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 Y
0 1 9 0 2016-09-25/17:43:53 2016-09-25/17:44:27 25-SEP-16 N

Here, at the third parse call (17:44:27) during the invalidation window, a new child cursor has been created. The old one is still marked as rolling invalid (“Y”), but not ‘rolling invalid executed’ (“X”) because it has not been executed.

So it seems that both parse or execute are triggering the rolling invalidation, and the IS_ROLLING_INVALID displays which one.

An execute will now execute the new cursor:

17:45:58 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
 
PL/SQL procedure successfully completed.
 
17:45:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTI IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- --------- ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 X
0 1 3 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 Y
0 1 9 1 2016-09-25/17:43:53 2016-09-25/17:44:27 25-SEP-16 N

Of course, when new cursors have been created we can see the reason in V$SQL_SHARED_CURSOR:

17:45:58 SQL> select child_number,reason from v$sql_shared_cursor where sql_id='61x2h0y9zv0r6';
 
CHILD_NUMBER REASON
------------ --------------------------------------------------------------------------------
0 <ChildNode><ChildNumber>0</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win
dow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></Ch
ildNode><ChildNode><ChildNumber>0</ChildNumber><ID>33</ID><reason>Rolling Invali
date Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1472658232<
/invalidation_window><ksugctm>1472658237</ksugctm></ChildNode>
 
1 <ChildNode><ChildNumber>1</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win
dow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></Ch
ildNode><ChildNode><ChildNumber>1</ChildNumber><ID>33</ID><reason>Rolling Invali
date Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1472658266<
/invalidation_window><ksugctm>1472658268</ksugctm></ChildNode>
 
2

The last child cursor has been created at 5:44:28 (invalidation_window=1472658268) because invalidation timestamp (ksugctm=1472658266)

So what?

We love Oracle because it’s not a black box. And it’s good to see that they continue in this way by exposing in V$ views information that can be helpful for troubleshooting.

Rolling invalidation has been introduced for dbms_stats because we have to gather statistics and we don’t want hard parse storms after that.
But remember that invalidation can also occur with DDL such as create, alter, drop, comment, grant, revoke.

You should avoid running DDL when application is running. However, we may have to do some of those operations online. It would be nice to have the same rolling invalidation mechanisms and it seems that it will be possible:


SQL> show parameter invalid
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_invalidation string IMMEDIATE
 
SQL> alter session set cursor_invalidation=XXX;
ERROR:
ORA-00096: invalid value XXX for parameter cursor_invalidation, must be from among IMMEDIATE, DEFERRED

That’s interesting. I’ll explain which DDL can use that in a future blog post.

 

Cet article Oracle 12cR2: IS_ROLLING_INVALID in V$SQL est apparu en premier sur Blog dbi services.

Replacement of environment variables or properties in Bash

Darwin IT - Sun, 2016-09-25 10:47
Earlier I wrote about the automatic installation of Fusion Middleware components using response files. A thing that lacked in my scripts was that although I had a FMW_HOME variable set in my enviroment shell script, the response files had the location hard coded in them. At the time I hadn't had the chance to figure out how to do property/variable replacement in shell. I do know how to do it with ANT. But I figured that installing ANT for only this was a bit too much, since with the installation of FMW you already get ANT as a module.

For an upgrade of my scripts to FMW 12.2.1.1, I did a Google-search on it and found: http://stackoverflow.com/questions/415677/how-to-replace-placeholders-in-a-text-file. The top 2 suggestions were:

  1. sed -e "s/\${i}/1/" -e "s/\${word}/dog/" template.txt
  2. i=32 word=foo envsubst < template.txt
Although the first option was favoured by many and considered the answer on the querstion, I personally favour the second. It turns out that sed does not accept references to the environment variables as a replacement. And that makes the replacements hardcoded again. The second does accept environment variable references. Actually, if the variable-reference in the template file  is already present in the environment, no actual replacement assignment have to be provided.

So let's say my response file template looks like:

[ENGINE]

#DO NOT CHANGE THIS.
Response File Version=1.0.0.0.0

[GENERIC]

#Set this to true if you wish to skip software updates
DECLINE_AUTO_UPDATES=true

#
MOS_USERNAME=

#
MOS_PASSWORD=<SECURE VALUE>

#If the Software updates are already downloaded and available on your local system, then specify the path to the directory where these patches are available and set SPECIFY_DOWNLOAD_LOCATION to true
AUTO_UPDATES_LOCATION=

#
SOFTWARE_UPDATES_PROXY_SERVER=

#
SOFTWARE_UPDATES_PROXY_PORT=

#
SOFTWARE_UPDATES_PROXY_USER=

#
SOFTWARE_UPDATES_PROXY_PASSWORD=<SECURE VALUE>

#The oracle home location. This can be an existing Oracle Home or a new Oracle Home
ORACLE_HOME=${FMW_HOME}

#Set this variable value to the Installation Type selected. e.g. Fusion Middleware Infrastructure, Fusion Middleware Infrastructure With Examples.
INSTALL_TYPE=Fusion Middleware Infrastructure

#Provide the My Oracle Support Username. If you wish to ignore Oracle Configuration Manager configuration provide empty string for user name.
MYORACLESUPPORT_USERNAME=

#Provide the My Oracle Support Password
MYORACLESUPPORT_PASSWORD=<SECURE VALUE>

#Set this to true if you wish to decline the security updates. Setting this to true and providing empty string for My Oracle Support username will ignore the Oracle Configuration Manager configuration
DECLINE_SECURITY_UPDATES=true

#Set this to true if My Oracle Support Password is specified
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

#Provide the Proxy Host
PROXY_HOST=

#Provide the Proxy Port
PROXY_PORT=

#Provide the Proxy Username
PROXY_USER=

#Provide the Proxy Password
PROXY_PWD=<SECURE VALUE>

#Type String (URL format) Indicates the OCM Repeater URL which should be of the format [scheme[Http/Https]]://[repeater host]:[repeater port]
COLLECTOR_SUPPORTHUB_URL=



Saved as 'fmw_12.2.1.1.0_infrastructure.rsp.tpl'; note the reference ORACLE_HOME=${FMW_HOME}. And I have set FMW_HOME with an fmw12c_env.sh script, as described in former posts. Then I only have to do:
envsubst < fmw_12.2.1.1.0_infrastructure.rsp.tpl >>fmw_12.2.1.1.0_infrastructure.rsp
To have the file copied to fmw_12.2.1.1.0_infrastructure.rsp with a replaced FMW_HOME variable:

...
#
SOFTWARE_UPDATES_PROXY_PASSWORD=<SECURE VALUE>

#The oracle home location. This can be an existing Oracle Home or a new Oracle Home
ORACLE_HOME=/u01/app/oracle/FMW12211

#Set this variable value to the Installation Type selected. e.g. Fusion Middleware Infrastructure, Fusion Middleware Infrastructure With Examples.
INSTALL_TYPE=Fusion Middleware Infrastructure
...

Couldn't be more simple, I'd say. Nice thing is that this enables me to do more directives. So, learned something again, from a question dated 7,5 years ago...

Thoughts/Info on Essbase/EssCS after Oracle Open World 2016

Tim Tow - Sat, 2016-09-24 21:59
I was at Oracle Open World last week and have some notes to share on the upcoming EssCS (Essbase Cloud Service) product and on Essbase in general.

EssCS will be Infrastructure as a Service (IaaS) product and will be priced on the number of cores and memory.  I believe there will be a metered and an unmetered pricing as well (metered meaning pay per cycle or some other usage measure).  According to presentations at Open World, which did have safe harbor statements meaning "do not make decisions based on this info as it could change", there will be options for 1, 2, 4, or 16 physical cores ("OCPU's") and 7.5 to 15 Gb of RAM.  In addition, it will be an updated version of Essbase that is not the current on-prem version.  It will feature, among other things:


  • The new Java Agent running in Weblogic that moves security from the essbase.sec file to a relational database.
  • Simplified security
  • ASO / BSO / Hybrid
  • Sandboxing and scenario management - what if's without copying data with workflow for approval to merge into given scenarios
  • Cloud-based UI
  • Ability to specify/create an Essbase cube from Excel 
  • A cube template library ("app store" type of thing)
  • A web-based outline editor (though most editing should be done in Excel)
  • EssCLI (Essbase Command Line Interface) - a sort of EPMAutomate for Essbase
  • The Essbase Java API and a new REST API (which is currently being engineered)

I do not remember hearing any dollar amount for EssCS at Open World.  I expect availability in the next 3 to 6 months though it wouldn't surprise me if it were to slip further.

As far as on-prem Essbase updates, I would expect that the updates we see in EssCS will go on-prem as part of the EPM 2017 on-prem release which Oracle currently believes will be delivered late in 2017 (also subject to safe harbor, etc).

As far as how Oracle is selling Essbase, Essbase is now firmly in the BI organization and is being sold by the BI reps; EPM reps do not generally sell Essbase.  To the Essbase team, EPM is important as they are an internal customer, but EPM is not their only customer.  As such, I saw at least one presentation that promoted the idea of customers writing custom planning applications using Essbase.  While some people I talked with thought that approach muddled the EPM message for customers, I see it as a situation where if they don't compete in the custom market, then someone else will.  As someone who frequently is involved in complex applications where the EPM Planning product may not be a fit, I am thrilled to see that message from Oracle.




Categories: BI & Warehousing

Video Tutorial: XPLAN_ASH Active Session History - Part 12

Randolf Geist - Sat, 2016-09-24 17:27
The final part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.


This is the final part of this tutorial, but there are more tutorials coming - about configuring the script, script internals and also the Rowsource Statistics mode of the script.

Modern software architecture – what is a database?

Yann Neuhaus - Sat, 2016-09-24 17:03

This blog post is focused at developers and software architects. I’m probably not writing at the right place. You’re on an infrastructure experts blog and the author is an Oracle DBA. So what can you learn from someone working on that 30 years old technology talking about that old SQL language ? You run with modern languages, powerful frameworks, multi-layer architecture, micro-services, distributed database and of course all open-source. You hate your DBA because he is the major slow-down for your agile development. You don’t want SQL. You don’t want databases. You don’t want DBA.

How can I encourage you to read this blog post? I was not always an DBA. I started as a developer, more than 20 years ago. And believe me, it was not prehistory at all. Object-Oriented design, Rapid Application Development, Automatic programming (remember C.A.S.E.?), visual programming (have you ever seen an IDE like IBM Visual Age?), query generation (early days of Business-Objects). All these evolved with more and more languages, frameworks, layers, micro-services, XML, SOA, JSON, REST,… but only one technology remained: the critial persistent data is still in a relational database and accessed by SQL.

What is a database

Most of developers think that a database is there to store and retrieve data. I’m sorry but that’s wrong. That may have been right a long time ago, with key-value storage and hierarchical databases, but that’s too old for me. When I started to work, databases were already doing far more than that. Let me explain. With those prehistoric databases, you retrieved data in the same way you stored it. You insert with a key, you fetch with that key. It is easy to explain to modern developers because they “invented” it few years ago, calling it CRUD (Create Read Update Delete). First argument of those CRUD methods is a key value. And you store unformatted data as XML or JSON associated to that value. If this is the only feature that you need, then for sure you don’t want a database.

Relational database management systems (RDBMS) are doing a lot more than that. First, you can query data in a completely different way than you inserted it. And this is the real life-cycle of data. For example, You take orders, one by one, with customer and product information for each of them. Of course you can update and read it with the order ID that has been generated, but that’s only a small use case and probably not the most critical. Warehouse users will query (and update) orders by product. Delivery users will query (and update) orders by customer. Marketing users will query by channels and many other dimensions. Finance will join with accounting. With a persistence only system, you have to code all that. Of course if you declared the mapping of associations, you can navigate through them. But the user requirement is to get a set of orders, or a quantity of products in stock, or a subset of customers, which is different from navigating through orders one by one. With a database, you dont need to code anything. With a proper data model what you inserted can be manipulated without its key value. All data that you have inserted can be accessed from any different point of view. And you don’t have to code anything for that. Imagine a Data Access Object with ‘QueryBy methods covering any combination of columns and operators.

A database system does not only store data, it processes data and provide a service to manipulate data.

SQL

SQL is not a language to code how to get the information. SQL only describes what you want. It’s a question you ask to a data service. Same idea as Uber where you enter your destination and desired car and the service manages everything for you: the path, the communication, the paiement, the security. You may not like the SQL syntax, but it can be generated. I’m not talking about generating CRUD statements here, but generating SQL syntax from a SQL semantic expressed in Java or example. There’s a very good example for that: jOOQ (look at the exemples there).

I understand that you can hate SQL for it’s syntax. SQL was build for pre-compilers, not for execution time parsing of text, and I’ll come back on that later with static SQL. But you can’t say that SQL semantic is not modern. It’s a 4th generation language that saves all the procedural coding you have to do with 3rd generation languages. SQL is a declarative language build on a mathematics theory. It goes far beyond the for() loops and if()else.

In SQL you describe the result that you want. How to retrieve the data is done by the database system. The optimizer builds the procedural code (know as the execution plan) and the execution engine takes care of everything (concurrency, maintaining redundant structures for performance, caching, multithreading, monitoring, debugging, etc). Do you really want to code all that or do you prefer to rely on a data service that does everything for you?

You know why developers don’t like SQL? Because SQL has not been designed for programmers. It was for users. The goal was that a non-programmer can ask its question to the system (such as “give me the country of the top customers having bought a specific product in last 3 months”) without the need of a developer. There was no GUI at that time, only Command Line Interface, and SQL was the User Friendly Interface to the database. Today we have GUIs and we don’t need SQL. But it is there so programmers build tools or framework to generate SQL from a programming language. Sure it is ridiculous and it would be better to have a programming language that directly calls the SQL semantic without generating plain old English text. We need a Structured Query Language (SQL) we just don’t need it to be in English.

Set vs loops

So why do people prefer to code everything in procedural language (3GL)? Because this is only what they learned. If at school you learned only loops and comparisons, then you are going to access data in loops. If you learned to think about data as sets, then you don’t need loops. Unfortunately, the set concepts are teached in mathematics classes but not in IT.

Imagine you have to print “Hello World” 5 times. Which pseudo-code so you prefer?


print("Hello World\n")
print("Hello World\n")
print("Hello World\n")
print("Hello World\n")
print("Hello World\n")

or


print ( "Hello World\n" + "Hello World\n" + "Hello World\n" + "Hello World\n" + "Hello World\n" )

I’ve put that in pseudo-code. I don’t want to play with String and StringBuffer here. But the idea is only to explain that if you have to process a set of things it is more efficient to process them as a set rather than one-by-one. That works for everything. And this is where databases rocks: they process sets of rows. If you have to increment the column N by one in every row of your table T, you don’t need to start a loop and increment the column row-by-row. Just ask your RDBMS data service to do it: ‘/* PLEASE */ UPDATE T set N=N+1′. The “please” is in comment because everything that is not there to describe the result is not part of SQL. You have to use hints to force the way to do it, but they are written as comments because SQL do not allow any way to tell how to do it. This was a joke of course, the “please” is not mandatory because we are talking to a machine.

ACID

I’m not sure you get all the magic that is behind:

UPDATE T set N=N+1;

it’s not a simple loop as:

for each row in T
set N=N+1

The RDBMS does more than that. Imagine that there is a unique index on the column N. How many lines of code do you need to do that N=N+1 row by row and be sure that at any point you don’t have duplicates? Imagine that after updating half of the rows you encounter someone else currently updating the same row. You have to wait for his commit. But then, if he updated the value of N, do you increment the past value or the new one? You can’t increment the old one or his modification will be lost. But if you increment the new one, your previous incremented rows are inconsistent because they were based on a previous state of data.
I was talking about having an index. You have to maintain this index as well. You have to be sure that what is in cache is consistent with what is in disk. That modifications made in the cache will not be lost in case of server failure. And if you run in a cluster, those caches must be synchronized.

Coding the same as this “UPDATE T set N=N+1″ in a procedural language is not easy and can become very complex in a multi-user environment.

Maybe you have all the tools you need to generate that code. But if you code it you have to test it. Are your tests covering all concurrency cases (sessions updating or reading same rows, or different rows from same table,…). What is already coded within the database has already been tested. It’s a service and you just have to use it.

Static SQL

I claimed above that SQL is there to be pre-compiled. Yes, SQL is witten in plain text, like most of programming languages, and must be parsed, optimized, compiled. It’s not only for performance. The main reason is that you prefer to get errors at compile time than at runtime. If you put SQL in text strings in your code it will remain text until execution time when it will be prepared. And only then you will get errors. The second reason is that when the SQL is parsed, it is easy to find the dependencies. Want to see all SQL statements touching to a specific column? Do you prefer to do guess on some text search or to methodically follow dependencies?

Yes, SQL is there to be static and not dynamic. That claim may look strange for an Oracle DBA because all statements are dynamic in Oracle. Even at the time of precompilers (such as Pro*C) the statements were parsed but were put as text in the binary. And at first execution, they are parsed again and optimized. If you want the execution plan to be defined at deployment time, you have to use Outlines or SQL Plan Baselines. There is no direct way to bind the execution plan at deployment time in Oracle. In my opinion the static SQL as it is known on DB2 for example is really missing in Oracle. OLTP Software Vendors would love to ship the optimized execution plans with their application. Imagine that all SQL statements in an OLTP application are parsed and optimized, compiled as bound procedures, similar to stored procedures, with procedural access (the execution plan) and you just have to call them. For reporting, DSS, BI you need the plans to adapt to the values and volume of data, but for OLTP you need stability. And from the application, you just call those static SQL like a data service.

Talking about procedural execution stored in the database, I’m coming to stored procedures and PL/SQL of course.

Stored Procedures

When you code in your 3GL language, do you have functions that update global variables (BASIC is the first language I learned and this was the idea) or do you define classes which encapsulate the function and the data definition? The revolution of Object Oriented concepts was to put data and logic at the same place. It’s better for code maintainability with direct dependency procedural code and data structures. It’s better for security because data is accessible only through provided methods. And it’s better for performance because procedural code access data at the same place.

Yes Object Oriented design rocks and this why you need to put business logic in the database. Putting the data on one component and running the code on another component of an information system is the worst you can do. Exactly as if in your Object Oriented application you store the object attributes on one node and run the methods on another one. And this is exactly what you do with the business logic outside of the database. Your DAO objects do not hold the data. The database does. Your objects can hold only a copy of the data, but the master copy where are managed concurrency management, high availability and persistance is in the database.

We will talk about the language later, this is only about the fact that the procedural code run in the same machine and the same processes than the data access.
There are a lot of myths about running business logic in the database. Most of them come from ignorance. Until last Monday I believed that one argument against running business logic in the database was unbeatable: You pay Oracle licences on the number of CPU, so you don’t want to use the database CPUs to run something that can run on a free server. I agreed with that without testing it, and this is where myths come from.

But Toon Koppelaars has tested it and he proved that you use more database CPU when you put the business logic outside of the database. I hope his presentation from Oak Table World 2016 will be available soon. He proved that by analyzing exactly what is running in the database, using linux perf and flame graphs: https://twitter.com/ChrisAntognini/status/778273744242352128

All those rountrips from remote compute server, all those row-by-row processing coming from that design have an higher footprint on the database CPUs that directly running the same on the database server.

PL/SQL

Running business logic on the database server can be done with any language. You can create stored procedures in Java. You can code external procedures in C. But those languages have not been designed for data manipulation. It is sufficient to call SQL statements but not when you need procedural data access. PL/SQL is a language made for data processing. It’s not only for stored procedure. But it’s the only language that is coupled with your data structure. As I said above, it’s better to think in sets with SQL. But it may be sometimes complex. With PL/SQL you have a procedural language which is intermediate between row-by-row and sets because it has some bulk processing capabilities.

In pseudo-code the Hello World above is something like that:


forall s in ["Hello World\n","Hello World\n","Hello World\n","Hello World\n","Hello World\n"] print(s)

It looks like a loop but it is not. The whole array is passed to the print() function and loop is done at lower level.

In PL/SQL you can also use pipeline functions where rows are processed with a procedural language but as a data stream (like SQL does) rather than loops and calls.

I’ll go to other advantages of PL/SQL stored procedures but here again there is one reason frequently raised to refuse PL/SQL. You can find more developers in .Net or Java than in PL/SQL. And because they are rare, they are more expensive. But there is a counter argument I heard this week at Oracle Open World (but I don’t remember who raised that point unfortunately). PL/SQL is easy to learn. Really easy. You have begin – exception – end blocks, you declare all variables, you can be modular with procedures and inline procedures, you separate signature and body, you have very good IDE, excellent debugger and easy profiler,… and you can run it on Oracle XE which is free. So, if you have a good Java developer he can write efficient PL/SQL in a few days. By good developer, I mean someone who understands multi-user concurrency problems, bulk processing, multi-threading, etc.

There are less PL/SQL developers than Java developers because you don’t use PL/SQL. It’s not the opposite. If you use PL/SQL you will find developers and there are many software vendors that code their application in PL/SQL. Of course PL/SQL is not free (except in Oracle XE) but it runs on all platforms and on all editions.

Continuous Integration and Deployment, dependency and versioning

I come back quickly to the advantages of using a language that is coupled with your data.

PL/SQL stored procedures are compiled and all dependencies are stored. With one query on DBA_DEPENDENCIES you can know which tables your procedure is using and which procedures use a specific table. If you change the data model, the procedures that have to be changed are immediately invalidated. I don’t know any other language that does that. You don’t want to break the continuous integration build every time you change something in a table structure? Then go to PL/SQL.

Let’s go beyond continuous integration. How do you manage database changes in continuous deployment? Do you know that with PL/SQL you can modify your data model online, with your application running and without breaking anything? I said above that procedures impacted by the change are invalidated and the must be adapted to be able to be compiled. But this is only for the new version. You can deploy a new version of those procedures while the previous version is running. You can test this new version and only when everything is ok you switch the application to the new version. The feature is called Edition Based Redefinition (EBR) it exists since 11g in all Oracle editions. It’s not known and used enough, but all people I know that use it are very happy with it.

In development environment and continuous integration, it is common to say that the database always cause problem. Yes it is true but it’s not inherent to the database but the data. Data is shared and durable and this is what makes it complex. The code can be deployed in different places, and can be re-deployed if lost. Data can be updated at only one place and visible to all users. Upgrading to a new version of application is easy: you stop the old version and start the new version. For data it is different: you cannot start from scratch and you must keep and upgrade the previous data.

Object-Relational impedance

I’m probably going too far in this blog post but the fact that data is shared and durable is the main reason why we cannot apply same concepts to data objects (business objects) and presentation objects (GUI components). Application objects are transient. When you restart the application, you create other objects. The identity of those objects is an address in memory: it’s different on other systems and it’s different once application is restarted. Business objects are different. When you manipulate a business entity, it must have the same identity for any users, and this identity do not change when application is restarted, not even when application is upgraded. All other points given as “object-relational impedance” are minor. But the sharing and durability of business object identity is the reason why you have to think differently.

Where to put business logic?

If you’re still there, you’ve probably understood that it makes sense to run the data logic in the database, with declarative SQL or procedural PL/SQL stored procedures, working in sets or at least in bulk, and with static SQL as much as possible, and versioned by EBR.

Where to put business logic then? Well, business logic is data logic for most of it. But you’ve always learned that business logic must be in the application tier. Rather than taking reasons given one by one and explain what’s wrong with them, let me tell you how came this idea of business logic outside of the database. The idea came from my generation: the awesome idea of client/server.

At first, data was processed on the servers and only the presentation layer was on the user side (for example ISAM was very similar with what we do with thin web pages). And this worked very well, but it was only green text terminals. Then came PCs and Windows 3.11 and we wanted graphical applications. So we built applications on our PCs. But that was so easy that we implemented all business logic there. Not because it’s a better architecture, but because anyone can build his application without asking to the sysops. This was heaven for developers and a nightmare for operations to deploy those applications on all the enterprise PCs.
But this is where offloading business logic started. Application written with nice IDEs (I did this with Borland Paradox and Delphi) connecting directly to the database with SQL. Because application was de-correlated from the database everything was possible. We even wanted to have applications agnostic of the database, running in any RDBMS. Using standard SQL and standard ODBC. Even better: full flexibility for the developer by using only one table with Entity-Value-Attribute.

Actually, the worst design anti-patterns have been invented at that time and we still see them in current applications – totally unscalable.

When finally the deployment of those client/server applications became a nightmare, and because internet was coming with http, html, java, etc. we went to 3-tier design. Unfortunately, the business logic remained offloaded in the application server instead of being part again of the database server.

I mentioned ODBC and it was another contributor to that confusion. ODBC looks like a logical separation of the application layer and the database layer. But that’s wrong. ODBC is not a protocol. ODBC is an API. ODBC do not offer a service: it is a driver running on both layers and that magically communicates through network: code to process data on one side and data begin on the other.

A real data service encapsulates many SQL statements and some procedural code. And it is exactly the purpose of stored procedures. This is how all data applications were designed before that client/server orgy and this is how they should be designed today when we focus on centralization and as micro-services applications.

So what?

This blog post is already too long. It comes from 20 years experience as developer, application DBA, and operation DBA. I decided to write this when coming back from the Oracle Open World where several people are still advocating for the right design, especially Toon Koppelaars about Thick Database at Oak Table World and the amazing panel about “Thinking clearly about application architecture” with Toon Koppelaars, Bryn Llewellyn, Gerald Venzl, Cary Millsap, Connor McDonald

The dream of every software architect should be to attend that panel w/ @ToonKoppelaars @BrynLite @GeraldVenzl @CaryMillsap @connor_mc_d pic.twitter.com/npLzpnktMK

— Franck Pachot (@FranckPachot) September 22, 2016

Beside the marketing stuff, I was really impressed by the technical content around the Oracle Database this year at OOW16.

 

Cet article Modern software architecture – what is a database? est apparu en premier sur Blog dbi services.

Wiederholung APEX 5.0 Best Practices

Denes Kubicek - Fri, 2016-09-23 10:30
Wegen der großen Nachfrage veranstalten wir jetzt einen Zusatztermin für den APEX 5.0 Best Practice Kurs am 21.Nov - 23. Nov. 2016.

Anmeldung zum Kurs

Categories: Development

#OOW16: Recap in Photos!

WebCenter Team - Fri, 2016-09-23 05:44

Even if you attended all 5 days of Oracle OpenWorld 2016 this past week, there was WAY more content and goings on over the span of 5 days than one could possibly hope to keep track of.  But if we had to sum it all up in one word for our customers and partners, it would be "Cloud". We want to sincerely thank everyone who attended and supported us before, during and after the event. We look forward to continuing to work together to expand on our Content and Experience Management solutions. Here are some photos to recap the week!

Executed PL/SQL kept in Stored procedure but while compile Compilation error is coming.

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, Below PL/SQL is working fine. while same code of PL/SQL as below is used in Stored procedure compilation error is coming while compiling stored procedure. Could you please make a suggestion to keep below PL/SQL in below Stored Procedure so...
Categories: DBA Blogs

How to compare two tables of data????

Tom Kyte - Fri, 2016-09-23 04:46
Hi Tom, I have two tables of values(dept1 and dept2). How do I compare all the data in these two tables??? It will return true if both tables contain the same data & false for otherwise... Another thing is that I do not know how to use CREATE OPE...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator