Feed aggregator

ADF Postback Payload Size Optimization

Andrejus Baranovski - Sun, 2018-07-15 01:45
Recently I came across property called oracle.adf.view.rich.POSTBACK_PAYLOAD_TYPE. This property helps to optimize postback payload size. It is described in ADF Faces configuration section - A.2.3.16 Postback Payload Size Optimization. ADF partial request is executing HTTP post with values from all fields included. When postback property is set to dirty, it will include into HTTP post only changed values. As result - server will get only changed attributes, potentially this can reduce server time processing and make HTTP request size smaller. This especially can be important for large forms, with many fields.

Let's take a look into example. After clicking on any button in the form, go to network monitor and study Form Data section. You will see ID's and values for all fields included in the UI. All fields are submitted with HTTP request by default, even these fields were not changed:


Postback optimization property can be set in web.xml. By default it's value is full, change it to dirty:


With value set to dirty, try to change at least one field and then press any button. Observe Form Data section in network monitor - only fields with changed values will be submitted:


Try to test it in your project and see the difference.

Check my sample app for this use case on GitHub.

Quiz Night

Jonathan Lewis - Sat, 2018-07-14 13:07

Because it’s been a long time since the last quiz night.  Here’s a question prompted by a recent thread on the ODevCom database forum – how many rows will Oracle sorts (assuming you have enough rows to start with in all_objects) for the final query, and how many sort operations will that take ?


drop table t1 purge;

create table t1 nologging as select * from all_objects where rownum < 50000;

select owner, count(distinct object_type), count(distinct object_name) from t1 group by owner;

Try to resist the temptation of doing a cut-n-paste and running the code until after you’ve thought about the answer.

My top 5 APEX 18.1 Plugins

Dimitri Gielis - Sat, 2018-07-14 05:46
With every new version of Oracle Application Express (APEX) new features are added and the life of a developer is made even easier. If the feature set is not enough or you see you need to build the same functionality more often, you can always extend APEX with plug-ins.

There are six different types of plugins: dynamic action, item,  region, process, authentication scheme and authorization scheme.

Plug-ins are absolutely fantastic to extend the native functionalities of APEX in a declarative way. The APEX plugin becomes a declarative option in the APEX Builder and has the [Plug-in] text next to it. In the next screenshot, you see the dynamic actions being extended by two Plug-ins.


If you are searching for an APEX plug-in, I typically go to APEX World > Plug-ins. The nice thing about that site is that the plug-ins seem to be maintained, so if a plug-in is not supported anymore it gets the status deprecated.

!! And here lays the catch with using plug-ins. When you decide to use a plug-in in your project, you become responsible for this and need to make sure it's compatible with every release of Oracle APEX. Many plug-ins are open source and many plug-in developers maintain their plug-ins, but it's really important you understand that at the end you are responsible for things you put in your application. If the plug-in is not secure or it breaks in the next release of APEX, you need to find a solution. So use plug-ins with care and see for example how many likes the plug-in has or what the comments are about the plug-in or author. Oracle is not reviewing or supporting the plug-ins !!

When I saw the tweet of Travis, I thought to do a blog post on my top 5 plugins I use in almost every project.


Here we go:

1. Built with love using Oracle APEX

I'm proud to built applications with Oracle Application Express, and this plug-in makes it very clear :) At the bottom of the app, you will see this text:


Note that in Oracle APEX 18.1 this text in included by default and you don't even need to add the plugin. Nevertheless, I wanted to include it in this list as it should be there in every app, even the ones built before APEX 18.1 :)

2. Select2

When a select list (or drop-down) has many values, it takes too long to find the right value. Select2 makes it easy to search for values, it also supports lazy loading and multiple select.


3. APEX Office Print

APEX Office Print extends APEX so it becomes possible to export to native Excel files and generate documents in Word, Powerpoint, PDF, HTML and Text, all based on your own template. It has many more features, I blogged about some before.



4. Dropzone

APEX 18.1 has declarative multi-file upload, but still, I love the Dropzone plugin developed by Daniel Hochleitner. You can drag multiple files from your desktop straight in your APEX app. Daniel is one of my favorite plug-in developers. When he releases something, you know it will be good.



5. Modal LOV

This is a newer plugin and I haven't used it that much yet, but I'm sure I will do. The nice thing with this item type plugin is that it also supports Interactive Grid. Where Select2 stays within the page, this Modal LOV comes with a modal list of values (pop-up) which is great if you want to show multiple columns or need more context for the record you need to select.


There are many more plug-ins out there, most of them work on APEX 5.x and upwards. For example, Pretius has some cool plug-ins too, the one to create nested reports I recently used in a project. Another site you can find plug-ins is APEX-Plugin.com.

Categories: Development

PGDay Amsterdam – follow up 2 – Where do null values go to in a hash partitioned table?

Yann Neuhaus - Fri, 2018-07-13 23:17

This is the second follow up which covers this question: When you hash partition a table in PostgreSQL 11 where do null values for the partitioned column go to? Lets go…

In the demo I used this little table:

postgres=# select version();
                                                            version                                                          
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bi
(1 row)
postgres=# create table part2 ( a int, list varchar(10) ) partition by hash (a);
CREATE TABLE
postgres=# create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE
postgres=# create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE
postgres=# create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE
postgres=# \d+ part2
                                          Table "public.part2"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer               |           |          |         | plain    |              | 
 list   | character varying(10) |           |          |         | extended |              | 
Partition key: HASH (a)
Partitions: part2_1 FOR VALUES WITH (modulus 3, remainder 0),
            part2_2 FOR VALUES WITH (modulus 3, remainder 1),
            part2_3 FOR VALUES WITH (modulus 3, remainder 2)

The data we played with was this:

postgres=# insert into part2 (a,list) values (1,'beer');
INSERT 0 1
postgres=# insert into part2 (a,list) values (2,'whine');
INSERT 0 1
postgres=# insert into part2 (a,list) values (3,'schnaps');
INSERT 0 1
postgres=# select * from only part2_1;
 a | list  
---+-------
 2 | whine
(1 row)

postgres=# select * from only part2_2;
 a |  list   
---+---------
 3 | schnaps
(1 row)

postgres=# select * from only part2_3;
 a | list 
---+------
 1 | beer
(1 row)

We have the data evenly distributed over the three partitions. When we insert a row which contains a NULL value for the column we partitioned on:

postgres=# insert into part2 (a,list) values (null,'cocktail');
INSERT 0 1

… where does that column go to?

postgres=# select * from only part2_1;
 a |   list   
---+----------
 2 | whine
   | cocktail
(2 rows)

postgres=# select * from only part2_2;
 a |  list   
---+---------
 3 | schnaps
(1 row)

postgres=# select * from only part2_3;
 a | list 
---+------
 1 | beer
(1 row)

It goes to the first partition and every following NULL key row gets there as well:

postgres=# insert into part2 (a,list) values (null,'rum');
INSERT 0 1
postgres=# select * from only part2_1;
 a |   list   
---+----------
 2 | whine
   | cocktail
   | rum
(3 rows)

I couldn’t find anything in the documentation about that so I did send a mail to the general mailing list and here is the answer: “The calculated hash value for the null value will be zero, therefore, it will fall to the partition having remainder zero.”

 

Cet article PGDay Amsterdam – follow up 2 – Where do null values go to in a hash partitioned table? est apparu en premier sur Blog dbi services.

How To Delete Integrated Extract Replicat

Michael Dinh - Fri, 2018-07-13 19:46

Why is this important?

If processes are not unregistered from database, they are orphaned.

Hence:
unregister replicat $replicat_name DATABASE
unregister extract $extract_name DATABASE

$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     E_DB        00:00:05      00:00:00    
EXTRACT     RUNNING     P_DB        00:00:00      00:00:00    
REPLICAT    RUNNING     R_OWL       00:00:00      00:00:03    
REPLICAT    STOPPED     R_JAY       00:00:00      00:24:15   

GGSCI 2> info E_DB debug

EXTRACT    E_DB      Last Started 2018-06-18 08:48   Status RUNNING
Checkpoint Lag       00:00:05 (updated 00:00:00 ago)
Process ID           365696
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2018-07-13 20:20:45  Seqno 151864, RBA 31183128
                     SCN 188.1068581841 (808522433489)


GGSCI 3> info r_jay debug

REPLICAT   R_JAY     Last Started 2018-06-16 11:42   Status STOPPED
INTEGRATED
Checkpoint Lag       00:00:00 (updated 578:24:33 ago)
Log Read Checkpoint  File ./dirdat/
                     2018-06-19 17:55:08.604509  RBA 409140739


GGSCI 4> info r_owl debug

REPLICAT   R_OWL     Last Started 2018-06-16 11:42   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           284878
Log Read Checkpoint  File ./dirdat/
                     2018-07-13 20:21:09.000628  RBA 132791008

GGSCI 5> exit


SQL> @pr "select * from DBA_APPLY"
APPLY_NAME                    : OGG$R_JAY
QUEUE_NAME                    : OGGQ$R_JAY
APPLY_CAPTURED                : YES
APPLY_USER                    : GGSUSER13
APPLY_TAG                     : 00
STATUS                        : DISABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 19-jun-2018 17:56:28
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Apply
LCRID_VERSION                 : 2
-------------------------
APPLY_NAME                    : OGG$R_MIG - ORPHANED REPLICAT AS PROCESS DOES NOT EXISTS
QUEUE_NAME                    : OGGQ$R_MIG
QUEUE_OWNER                   : GGSUSER12
APPLY_CAPTURED                : YES
APPLY_USER                    : GGSUSER12
APPLY_TAG                     : 00
STATUS                        : DISABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 24-feb-2018 07:12:24
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Apply
LCRID_VERSION                 : 2
-------------------------
APPLY_NAME                    : OGG$E_DB
QUEUE_NAME                    : OGG$Q_E_DB
QUEUE_OWNER                   : GGSUSER13
APPLY_CAPTURED                : YES
RULE_SET_NAME                 : RULESET$_9
APPLY_TAG                     : 00
STATUS                        : ENABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 18-jun-2018 08:48:02
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Capture
LCRID_VERSION                 : 2
-------------------------

PL/SQL procedure successfully completed.

SQL> @pr "select * from DBA_CAPTURE"
CAPTURE_NAME                  : OGG$CAP_E_DB
QUEUE_NAME                    : OGG$Q_E_DB
START_SCN                     : 776572270090
STATUS                        : ENABLED
CAPTURED_SCN                  : 808484653323
APPLIED_SCN                   : 808484649784
USE_DATABASE_LINK             : NO
FIRST_SCN                     : 776572270090
SOURCE_DATABASE               : DB01
SOURCE_DBID                   : 689358028
SOURCE_RESETLOGS_SCN          : 279476595350
SOURCE_RESETLOGS_TIME         : 826720979
LOGMINER_ID                   : 1
MAX_CHECKPOINT_SCN            : 808510343793
REQUIRED_CHECKPOINT_SCN       : 808484621637
LOGFILE_ASSIGNMENT            : IMPLICIT
STATUS_CHANGE_TIME            : 18-jun-2018 08:48:04
VERSION                       : 12.2.0.1.0
CAPTURE_TYPE                  : LOCAL
CHECKPOINT_RETENTION_TIME     : 7
PURPOSE                       : GoldenGate Capture
SOURCE_ROOT_NAME              : DB01
CLIENT_NAME                   : E_DB
CLIENT_STATUS                 : ATTACHED
OLDEST_SCN                    : 776572270090
FILTERED_SCN                  : 773378341423
-------------------------

PL/SQL procedure successfully completed.

SQL*Plus copy command errorring on CLOB

Tom Kyte - Fri, 2018-07-13 17:26
I am trying to move select records from one database to another using the copy command and I am getting this error below. Is there a way around this using the copy command? Thank you, A.J. SQL> copy from saturn/******@test insert SFRAREG ...
Categories: DBA Blogs

Chatbot Tech Round Table: Three Real Life Use Cases on How Oracle Chatbots Can Be Integrated into Cloud Applications

Watch this video to gather more insight into chatbot capabilities.

Chatbots are increasingly becoming an excellent tool for organizations to consider when developing their user experience. They provide a fast and engaging way for users to access information more efficiently. In this video, you will learn just a few ways of how chatbots can be used by being integrated into cloud applications. Fishbowl’s John Sim, an Oracle Ace, demonstrates three different scenarios in which chatbots can improve the user experience for an account manager.

What You’ll See:

  • The day in the life of an account manager onsite with a customer using a chatbot
  • How chatbots make onboarding more efficient by providing new sales reps with interactive training
  • How chatbots enhance an account managers ability to engage with a customer with knowledge from the Oracle mobile cloud

To get even more information about chatbots and how you can better utilize their capabilities, please contact us directly at info@fishbowlsolutions.com or visit our chatbot consulting page.

The post Chatbot Tech Round Table: Three Real Life Use Cases on How Oracle Chatbots Can Be Integrated into Cloud Applications appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Database Vault : Rules, Rule Sets and Command Rules

Yann Neuhaus - Fri, 2018-07-13 14:31

In a previous blog I talked about protecting data using Realms. With Database Vault we can also protect our database against some SQL statements. These statements can include SELECT, ALTER SYSTEM, database definition language (DDL), and data manipulation language (DML) statements.
We can do this with Command Rules. In this blog I am demonstrating how we can use a Command Rule to prevent SYS from creating a new pluggable database in a multitenant environment.

Before starting the demonstration, we can see that there are some predefined Command Rules which apply to all users.

SQL> show user
USER is "C##DBV_OWNER_ROOT"
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> SELECT COMMAND, RULE_SET_NAME FROM DVSYS.DBA_DV_COMMAND_RULE;

COMMAND              RULE_SET_NAME
-------------------- --------------------------------------------------
ALTER PROFILE        Can Maintain Accounts/Profiles
ALTER SYSTEM         Allow Fine Grained Control of System Parameters
ALTER USER           Can Maintain Own Account
CHANGE PASSWORD      Can Maintain Own Account
CREATE PROFILE       Can Maintain Accounts/Profiles
CREATE USER          Can Maintain Accounts/Profiles
DROP PROFILE         Can Maintain Accounts/Profiles
DROP USER            Can Maintain Accounts/Profiles

8 rows selected.
SQL>

Because of these default Command Rules, for example, user sys cannot create a user once Database Vault is enabled.

SQL> conn sys/root@pdb1 as sysdba
Connected.
SQL> create user myuser identified by test;
create user myuser identified by test
                                 *
ERROR at line 1:
ORA-01031: insufficient privileges

To grant a user the ability to use these commands, you can grant the user the role that the rule set checks.

SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DV_ACCTMGR';

PRIVILEGE
----------------------------------------
DROP PROFILE
ALTER PROFILE
ALTER USER
CREATE PROFILE
CREATE USER
CREATE SESSION
DROP USER

7 rows selected.

SQL>

To allow sys to create a user we can grant the DV_ACCTMGR role to SYS

SQL> show user
USER is "C##DBV_ACCTMGR_ROOT"

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>

SQL> grant  DV_ACCTMGR to sys;

Grant succeeded.

And now SYS can create a user

SQL> conn sys/root@pdb1 as sysdba
Connected.
SQL> create user myuser identified by test;

User created.

SQL>

Before starting the demonstration let’s verify that user SYS, by default, can create a pluggable database

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database PDB2 ADMIN USER pdb2adm IDENTIFIED BY root create_file_dest='/u01/app/oracle/oradata/DBSEC/PDB2';

Pluggable database created.

SQL>

To prevent sys from creating a pluggable database, we are first going to create a RULE. This rule will determine when the command rule will be fired.

SQL> exec DVSYS.DBMS_MACADM.CREATE_RULE(rule_name => 'MY_PDB_RULE', 
                                        rule_expr => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''SYS''');

PL/SQL procedure successfully completed.

SQL>

After we have to create a RULE SET which is a collection of one or more rules. We can associate a rule set with a realm authorization, factor assignment, command rule, or secure application role.

SQL> exec DVSYS.DBMS_MACADM.CREATE_RULE_SET(rule_set_name => 'MY_PDB_RULESET', 
                                            description => ' About managing Pdbs', 
                                            enabled => DBMS_MACUTL.G_YES, eval_options => DBMS_MACUTL.G_RULESET_EVAL_ANY,
                                            audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS, 
                                            fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT, fail_message => '', 
                                            fail_code => '', 
                                            handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF, 
                                            handler => '',
                                            is_static => FALSE);

PL/SQL procedure successfully completed.
SQL>

We then add the RULE to the RULE SET

BEGIN
DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(
                                       rule_set_name => 'MY_PDB_RULESET',
                                       rule_name => 'MY_PDB_RULE');
END;
   /

PL/SQL procedure successfully completed.

And finally create a COMMAND RULE which will prevent SYS to execute a CREATE PLUGGABLE DATABASE statement

SQL> exec DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(command=> 'CREATE PLUGGABLE DATABASE', 
                                                rule_set_name => 'MY_PDB_RULESET', 
                                                object_owner => DBMS_ASSERT.ENQUOTE_NAME('%',FALSE), 
                                                object_name => '%',
                                                enabled => 'Y');

PL/SQL procedure successfully completed.

SQL>

And now if we try to create a Pdb with SYS

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>  CREATE PLUGGABLE DATABASE PDB3 ADMIN USER pdb3adm IDENTIFIED BY root create_file_dest='/u01/app/oracle/oradata/DBSEC/PDB3';
 CREATE PLUGGABLE DATABASE PDB3 ADMIN USER pdb3adm IDENTIFIED BY root create_file_dest='/u01/app/oracle/oradata/DBSEC/PDB3'
*
ERROR at line 1:
ORA-47400: Command Rule violation for CREATE PLUGGABLE DATABASE on PDB3

SQL>
 

Cet article Database Vault : Rules, Rule Sets and Command Rules est apparu en premier sur Blog dbi services.

bbcp and rclone for Oracle Linux

Wim Coekaerts - Fri, 2018-07-13 10:20

Last week we packaged up a few more RPMs for Oracle Linux 7 that will help make life easier for Cloud users.

bbcp 15.02.03.01.1-3  in ol7_developer:

# yum install bbcp

bbcp is what I would call ssh on steroids. If you want to copy files from a local node to a remote node (say in Oracle Cloud) then this is a great tool. It might require some tuning but the idea is that you can open up parallel TCP streams. When you do large file transfers this should be able to give you a bit of a performance boost. I would also recommend using UEK5 and enable BBR as the congestion control algo. (see an old blog entry). The combination of enabling BBR (only has to be done on one of the 2 nodes (src or dest)) and using bbcp to copy large files using parallel streams should provide you the best throughput. By making this into an RPM for OL, it makes it easily available for everyone to use.

rclone 1.42 in ol7_developer

# yum install rclone

rclone is a very cool command line tool to move files around from/to local storage and cloud object storage. This works very well with Oracle Cloud Infrastructure's Object Storage. Now that it's packaged as an RPM with OL you can just install it directly from the command line instead of having to go download a file from a website. rclone works like scp.

Example could be  # rclone copy localdir ocistorage:remotedir

In order to configure rclone for Oracle Cloud Infrastructure's Object Storage, you have to create an "Amazon S3 Compatible API Key". This generates a secret key that you have to use during rclone config along with the access key (looks like an OCID in Object Storage   ocid1.credential.oc1.<string>) .

Configuration example:

# sudo yum install -y rclone

-> In the OCI console you go to Identity -> Users -> User Details -> Amazon S3 Compatible API Key and generate a new Secret Key.

-> copy the secret key because you need that to configure rclone, and you will also need the  Access Key (which is an OCID)

-> configure rclone on your OL7 client.

Example :

# rclone config

-> type n (new remote) and give it a name

name> ocistorage

Type of storage to configure.

-> type 3  (Amazon S3 Compliant Storage Providers (AWS, Ceph, Dreamhost, IBM COS, Minio))

Choose your S3 provider.

type 8 (Any other s3 compatible provider)

-> Next type 1 (1 / Enter AWS credentials in the next step) 

For access key provide the ocid

-> access_key_id> ocid1.credential.....

For the secret access key use your secret key that was just generated.

secret_access_key> tyjXhM7eUuB2v........

Region to connect to.

-> hit enter

For endpoint (example, phoenix) enter a https url

example :  https://orclwim.compat.objectstorage.us-phoenix-1.oraclecloud.com

my tenant name is orclwim  so replace it with your tenant name.

The end point URLs are

https://<tenantname>.compat.objectstorage.us-phoenix-1.oraclecloud.com

https://<tenantname>.compat.objectstorage.us-ashburn-1.oraclecloud.com

https://<tenantname>.compat.objectstorage.eu-frankfurt-1.oraclecloud.com

https://<tenantname>.compat.objectstorage.uk-london-1.oraclecloud.com

Location Constraint hit enter

and ACL hit enter

type y OK to store the settings

you should get something like

Current remotes:

Name                 Type
====                 ====
ocistorage           s3

 

That's it - we have some code changes pending that will include oracle and the endpoints in rclone but those are being reviewed still.

 

pushing predicates

Jonathan Lewis - Fri, 2018-07-13 07:05

I came across this odd limitation (maybe defect) with pushing predicates (join predicate push down) a few years ago that made a dramatic difference to a client query when fixed but managed to hide itself rather cunningly until you looked closely at what was going on. Searching my library for something completely different I’ve just rediscovered the model I built to demonstrate the issue so I’ve tested it against a couple of newer versions  of Oracle (including 18.1) and found that the anomaly still exists. It’s an interesting little detail about checking execution plans properly so I’ve written up the details. The critical feature of the problem is a union all view:


rem
rem	Script:		push_pred_limitation.sql
rem	Author:		Jonathan Lewis
rem	Dated:		Jan 2015
rem
rem	Last tested 
rem		18.1.0.0	via LiveSQL
rem		12.2.0.1
rem		12.1.0.2
rem		11.2.0.4
rem

create table t1
as
select	* 
from	all_objects
where	rownum <= 10000 -- > comment to avoid WordPress format issue
;

create table t2
as
select	* 
from	all_objects
where	rownum <= 10000 -- > comment to avoid WordPress format issue
;

create table t3
as
select	* 
from	all_objects
where	rownum <= 10000 -- > comment to avoid WordPress format issue
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1 for columns owner size 254'
	);
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt	 => 'for all columns size 1'
	);
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T3',
		method_opt	 => 'for all columns size 1'
	);
end;
/

create index t2_id on t2(object_id);
-- create index t2_id_ot on t2(object_id, object_type);

create index t3_name_type on t3(object_name, object_type);

create or replace view v1
as
select 
	/*+ qb_name(part1) */
	t2.object_id,
	t2.object_type	object_type_2,
	t3.object_type	object_type_3,
	t2.created	date_2,
	t3.created	date_3
from
	t2, t3
where
	t3.object_name = t2.object_name
union all
select
	/*+ qb_name(part2) */
	t2.object_id,
	t2.object_type	object_type_2,
	t3.object_type	object_type_3,
	t2.last_ddl_time	date_2,
	t3.last_ddl_time	date_3
from
	t2, t3
where
	t3.object_name = t2.object_name
;

Two points to note so far: first, the view is basically joining the same two tables in the same way twice but selecting different columns. It’s a close model of what the client was doing but so much simpler that it wouldn’t be hard to find a different way of getting the same result: the client’s version would have been much far harder to rewrite. Secondly, I’ve listed two possible indexes for table t2 but commented one of them out. The indexing will make a difference that I’ll describe later.

So here’s the query with execution plan (from explain plan – but pulling the plan from memory gives the same result):


select
	/*+ qb_name(main) */
	t1.object_name, t1.object_type,
	v1.object_id, v1.date_2, v1.date_3
from
	t1,
	v1
where
	v1.object_id = t1.object_id
and	v1.object_type_2 = t1.object_type
and	v1.object_type_3 = t1.object_type
and	t1.owner = 'OUTLN'
;

Plan hash value: 4123301926

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |     7 |   588 |    82   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                            |              |     7 |   588 |    82   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                      | T1           |     7 |   280 |    26   (4)| 00:00:01 |
|*  3 |   VIEW                                   | V1           |     1 |    44 |     8   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE            |              |       |       |            |          |
|   5 |     NESTED LOOPS                         |              |     1 |    77 |     4   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                        |              |     1 |    77 |     4   (0)| 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    41 |     2   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | T2_ID        |     1 |       |     1   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN                   | T3_NAME_TYPE |     1 |       |     1   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID         | T3           |     1 |    36 |     2   (0)| 00:00:01 |
|  11 |     NESTED LOOPS                         |              |     1 |    77 |     4   (0)| 00:00:01 |
|  12 |      NESTED LOOPS                        |              |     1 |    77 |     4   (0)| 00:00:01 |
|  13 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    41 |     2   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN                  | T2_ID        |     1 |       |     1   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN                   | T3_NAME_TYPE |     1 |       |     1   (0)| 00:00:01 |
|  16 |      TABLE ACCESS BY INDEX ROWID         | T3           |     1 |    36 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."OWNER"='OUTLN')
   3 - filter("V1"."OBJECT_TYPE_2"="T1"."OBJECT_TYPE")
   8 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
   9 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T3"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
  14 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
  15 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T3"."OBJECT_TYPE"="T1"."OBJECT_TYPE")

The execution plan appears to be fine – we can see at operation 4 that the union all view has been access with the pushed predicate option and that the subsequent sub-plan has
used index driven nested loop joins in both branches – until we look a little more closely and examine the Predicate section of the plan. What, exactly, has been pushed ?

Look at the predicate for operation 3: “V1″.”OBJECT_TYPE_2″=”T1″.”OBJECT_TYPE”. It’s a join predicate that hasn’t been pushed into the view. On the other hand the original, and similar, join predicate v1.object_type_3 = t1.object_type has been pushed into the view, appearing at operations 9 and 15. There is a difference, of course, the object_type_3 column appears as the second column of the index on table t3.

Two questions then: (a) will the object_type_2 predicate be pushed if we add it to the relevant index on table t2, (b) is there a way to get the predicate pushed without adding it to the index. The answer to both questions is yes. First the index – re-run the test but create the alternative index on t2 and the plan changes to:

Plan hash value: 497545587

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |     7 |   553 |    82   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                            |              |     7 |   553 |    82   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                      | T1           |     7 |   280 |    26   (4)| 00:00:01 |
|   3 |   VIEW                                   | V1           |     1 |    39 |     8   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE            |              |       |       |            |          |
|   5 |     NESTED LOOPS                         |              |     1 |    77 |     4   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                        |              |     1 |    77 |     4   (0)| 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    41 |     2   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | T2_ID_OT     |     1 |       |     1   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN                   | T3_NAME_TYPE |     1 |       |     1   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID         | T3           |     1 |    36 |     2   (0)| 00:00:01 |
|  11 |     NESTED LOOPS                         |              |     1 |    77 |     4   (0)| 00:00:01 |
|  12 |      NESTED LOOPS                        |              |     1 |    77 |     4   (0)| 00:00:01 |
|  13 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    41 |     2   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN                  | T2_ID_OT     |     1 |       |     1   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN                   | T3_NAME_TYPE |     1 |       |     1   (0)| 00:00:01 |
|  16 |      TABLE ACCESS BY INDEX ROWID         | T3           |     1 |    36 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."OWNER"='OUTLN')
   8 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID" AND "T2"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
   9 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T3"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
  14 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID" AND "T2"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
  15 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T3"."OBJECT_TYPE"="T1"."OBJECT_TYPE")

Notice how the predicate at operation 3 has disappeared, and the access predicate at operation 8 now includes the predicate “T2″.”OBJECT_TYPE”=”T1″.”OBJECT_TYPE”.

Alternatively, don’t mess about with the indexes – just tell Oracle to push the predicate. Normally I would just try /*+ push_pred(v1) */ as the hint to do this, but the Outline section of the original execution plan already included a push_pred() hint that looked like this: PUSH_PRED(@”MAIN” “V1″@”MAIN” 3 1), so I first copied exactly that into the SQL to see if it would make any difference. It did – I got the following plan (and the hint in the outline changed to PUSH_PRED(@”MAIN” “V1″@”MAIN” 3 2 1) so this may be a case where the plan produced by a baseline will perform better than the plan that the produced the baseline!):

Plan hash value: 4123301926

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |     7 |   553 |    82   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                            |              |     7 |   553 |    82   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                      | T1           |     7 |   280 |    26   (4)| 00:00:01 |
|   3 |   VIEW                                   | V1           |     1 |    39 |     8   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE            |              |       |       |            |          |
|   5 |     NESTED LOOPS                         |              |     1 |    77 |     4   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                        |              |     1 |    77 |     4   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    41 |     2   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | T2_ID        |     1 |       |     1   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN                   | T3_NAME_TYPE |     1 |       |     1   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID         | T3           |     1 |    36 |     2   (0)| 00:00:01 |
|  11 |     NESTED LOOPS                         |              |     1 |    77 |     4   (0)| 00:00:01 |
|  12 |      NESTED LOOPS                        |              |     1 |    77 |     4   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2           |     1 |    41 |     2   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN                  | T2_ID        |     1 |       |     1   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN                   | T3_NAME_TYPE |     1 |       |     1   (0)| 00:00:01 |
|  16 |      TABLE ACCESS BY INDEX ROWID         | T3           |     1 |    36 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."OWNER"='TEST_USER')
   7 - filter("T2"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
   8 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
   9 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T3"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
  13 - filter("T2"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
  14 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
  15 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND "T3"."OBJECT_TYPE"="T1"."OBJECT_TYPE")

In this case we see that the critical late-joining predicate has disappeared from operation 3 and re-appeared as a filter predicate at operation 7 In many cases you may find that the change in predicate use makes little difference to the performance – in my example the variation in run time over several executions of each query was larger than the average run time of the query; nevertheless it’s worth noting that the delayed use of the predicate could have increased the number of probes into table t3 for both branches of the union all and resulted in redundant data passing up through several layers of the call stack before being eliminated … and “eliminate early” is one of the major commandments of optimisation.

You might notice that the Plan Hash Value for the hinted execution plan is the same as for the original execution plan: the hashing algorithm doesn’t take the predicates into account (just one of many points that Randolf Geist raised in a blog post several years ago). This is one of the little details that makes it easy to miss the little changes in a plan that can make a big difference in performance.

Summary

If you have SQL that joins simple tables to set based (union all, etc.) views and you see the pushed predicate option appearing take a little time to examine the predicate section of the execution plan to see if the optimizer is pushing all the join predicates that it should and, if it isn’t, test the effects of pushing more predicates.

In many cases adding the hint /*+ push_pred(your_view_name) */ at the top of the query may be sufficient to get the predicate pushing you need, but you may need to look at the outline section of the execution plan and add a series of more complicated push_pred() and no_push_pred() hints because the push_pred hint has evolved over time to deal with increasingly complicated transformations.

 

PGDay Amsterdam – follow up 1 – Adding columns with a default value and changing the default value right after

Yann Neuhaus - Fri, 2018-07-13 01:32

As always, this time during my talk about the PostgreSQL 11 new features in Amsterdam, there have been question I could not immediately answer. The first one was this: Suppose we add a column with a default value in PostgreSQL 11, what happens when we change that default afterwards? Does the table get rewritten? Do we have more than on distinct default value for that column? Here we go …

The sample table:

postgres=# select version();
                                                            version                                                            
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11beta1 build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# insert into t1 (a,b) 
           select a.*, md5(a::text) 
             from generate_series(1,1000) a;
INSERT 0 1000

Lets add a new column with a default value:

postgres=# alter table t1 add column c text default 'aa';;
ALTER TABLE

This populates the two columns in pg_attribute as described in a previous post:

postgres=# select atthasmissing,attmissingval 
             from pg_attribute 
            where attrelid = 't1'::regclass and attname = 'c';
 atthasmissing | attmissingval 
---------------+---------------
 t             | {aa}
(1 row)

When we check for the distinct values in column “c” we should only see one result (which is “aa”):

postgres=# select c, count(*) from t1 group by c;
 c  | count 
----+-------
 aa |  1000
(1 row)

When I got the question right the concern was: When we change the default now do we see two results when we ask for the distinct values in column “c”? Of course not and the table is not rewritten:

postgres=# alter table t1 alter column c set default 'bb';
ALTER TABLE
postgres=# select c, count(*) from t1 group by c;
 c  | count 
----+-------
 aa |  1000
(1 row)

postgres=# select atthasmissing,attmissingval from pg_attribute where attrelid = 't1'::regclass and attname = 'c';
 atthasmissing | attmissingval 
---------------+---------------
 t             | {aa}
(1 row)

What does that mean? For the existing rows the value is still “aa” as that was true when the column was added. For new values we will get “bb”:

postgres=# \d t1
                  Table "public.t1"
 Column |  Type   | Collation | Nullable |  Default   
--------+---------+-----------+----------+------------
 a      | integer |           |          | 
 b      | text    |           |          | 
 c      | text    |           |          | 'bb'::text

postgres=# insert into t1 (a,b) values (1001,'aa');
INSERT 0 1
postgres=# select c, count(*) from t1 group by c;
 c  | count 
----+-------
 bb |     1
 aa |  1000
(2 rows)

I hope that answers the question. If not, please leave a comment.

 

Cet article PGDay Amsterdam – follow up 1 – Adding columns with a default value and changing the default value right after est apparu en premier sur Blog dbi services.

What a great event – PGDay Amsterdam

Yann Neuhaus - Fri, 2018-07-13 00:14

PostgreSQL conferences are always cool and this time it was in Amsterdam: PGDay Amsterdam. Beside that meeting all the great people was fun again the location was really outstanding: The TOBACCO Theater:

IMG-20180712-WA0001

Some impressions:

Here you can see Devrim preparing the opening of the event with the introduction session:
dav

… and then it happened: We finally started:

dav

Jan kicked of the sessions with his talk about the reasons he finally arrived in the PostgreSQL community after working years in another one:

Selection_006

Oleksi took over to speak about ACID, transactions and much more, a great talk:

dav
I had the pleasure to speak about PostgreSQL 11 to close the first sessions before the coffee break:

Selection_002

Stefanie followed with foreign data wrappers and data integration with PostgreSQL (another great one):

Selection_005

And then there was something special: You might know Devrim has a real PostgreSQL tattoo and that was taken as an opportunity to offer temporary tattoos to everyone and that looked like this:

Selection_003

Selection_004

Hans rocked the stage right after:
dav

Devrim right after his talk about WAL:
a115fd15-0f21-4173-afcc-1aece9465afd-original

As in Rapperswil two weeks ago Bruce closed the sessions with his talk: Will PostgreSQL live forever:
70c46b91-342d-41cf-af52-19f282fdd170-original1

There have been other session not mentioned here, which also have been great, but I didn’t ask if it as fine to publish the pictures. I could not attend the party after the event but I am sure that was great as well. See you next year. And never forget: PostgreSQL rocks :).

 

Cet article What a great event – PGDay Amsterdam est apparu en premier sur Blog dbi services.

Global synonym not used

Tom Kyte - Thu, 2018-07-12 23:06
Hello, I need some help understanding the synonym behavior. We have a table in production database with a global synonym, which is being referenced by other objects in the database. As part of a weekly process that runs every weekend, we drop this...
Categories: DBA Blogs

MTS Configuration

Tom Kyte - Thu, 2018-07-12 23:06
Hi Tom ! I have configured MTS. My INIT.ORA and tnsnames.ora file configurations are given below. I have given PORT=1528 in the INIT.ORA file and when I query the V$DISPATCHER, I am surprised to see that the PORT numbers are chaning always i.e ...
Categories: DBA Blogs

pgSentinel: the sampling approach for PostgreSQL

Yann Neuhaus - Thu, 2018-07-12 17:12

Here is the first test I did with the beta of pgSentinel. This Active Session History sampling is a new approach to Postgres tuning. For people coming from Oracle, this is something that has made our life a lot easier to optimize database applications. Here is a quick example showing how it links together some information that are missing without this extension.

The installation of the extension is really easy (nore details on Daniel’s post):

cp pgsentinel.control /usr/pgsql-10/share/extension
cp pgsentinel--1.0.sql /usr/pgsql-10/share/extension
cp pgsentinel.so /usr/pgsql-10/lib

and declare it in postgresql.conf

grep -i pgSentinel $PGDATA/postgresql.conf
 
shared_preload_libraries = 'pg_stat_statements,pgsentinel'
#pgsentinel_ash.pull_frequency = 1
#pgsentinel_ash.max_entries = 1000000

and restart:

/usr/pgsql-10/bin/pg_ctl restart

Then create the views in psql:

CREATE EXTENSION pgsentinel;

I was running PGIO (the SLOB method for PostgreSQL from Kevin Closson https://kevinclosson.net/)

Without the extension, here is what I can see about the current activity from the OS point of view, with ‘top -c':

top - 21:57:23 up 1 day, 11:22, 4 users, load average: 4.35, 4.24, 4.16
Tasks: 201 total, 2 running, 199 sleeping, 0 stopped, 0 zombie
%Cpu(s): 27.6 us, 19.0 sy, 0.0 ni, 31.0 id, 19.0 wa, 0.0 hi, 3.4 si, 0.0 st
KiB Mem : 4044424 total, 54240 free, 282220 used, 3707964 buff/cache
KiB Swap: 421884 total, 386844 free, 35040 used. 3625000 avail Mem
 
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9766 postgres 20 0 440280 160036 150328 D 50.0 4.0 10:56.63 postgres: postgres pgio [local] SELECT
9762 postgres 20 0 439940 160140 150412 D 43.8 4.0 10:55.95 postgres: postgres pgio [local] SELECT
9761 postgres 20 0 440392 160088 150312 D 37.5 4.0 10:52.29 postgres: postgres pgio [local] SELECT
9763 postgres 20 0 440280 160080 150432 R 37.5 4.0 10:41.94 postgres: postgres pgio [local] SELECT
9538 postgres 20 0 424860 144464 142956 D 6.2 3.6 0:30.79 postgres: writer process

As I described in a previous post, PostgreSQL changes the title of the process to display the current operation. This looks interesting, but not very detailed (only ‘SELECT’ here) and very misleading because here I’m running PGIO with 50% updates. The ‘SELECT’ here is the user call. Not the actual SQL statement running.

We have more information from PG_STAT_ACTIVITY, but again only the top-level call is displayed, as I mentioned in a previous post:

select * from pg_stat_activity where pid=9766;
-[ RECORD 1 ]----+---------------------------------------------------------
datid | 17487
datname | pgio
pid | 9766
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-07-12 21:28:46.539052+02
xact_start | 2018-07-12 21:28:46.542203+02
query_start | 2018-07-12 21:28:46.542203+02
state_change | 2018-07-12 21:28:46.542209+02
wait_event_type | IO
wait_event | DataFileWrite
state | active
backend_xid | 37554
backend_xmin | 37553
query | SELECT * FROM mypgio('pgio4', 50, 3000, 131072, 255, 8);
backend_type | client backend

Here, I know what the user is doing: a call to mypgio() started at 21:28:46. And I know which resources are involved on the system: DataFileWrite. But again the most important is missing, the link between the user call and the system resources. And you can only guess it here because you know that a SELECT do not write to datafiles. There’s something hidden in the middle, which is actually an UPDATE. Of course, we can see this UPDATE in PG_STAT_STATEMENTS. But there, it will not be linked with the current activity, the mypgio() call, nor the DataFileWrite wait event. And we also need some timing information to be able to see the database load over the time.

Here is where the pgSentinel extension fills the gap, providing:

  • The actual query running, with the queryid which links to PG_STAT_STATEMENTS, but also the full text with all parameter values
  • multiple samples of the activity, with their timestamp information


select ash_time,pid,wait_event_type,wait_event,state,queryid,backend_type,top_level_query,query from pg_active_session_history order by ash_time desc,pid fetch first 10 rows only;
 
ash_time | pid | wait_event_type | wait_event | state | queryid | backend_type | top_level_query | query
-------------------------------+------+-----------------+---------------+--------+------------+----------------+----------------------------------------------------------+--------------------------------------------------------------------------
2018-07-12 21:57:22.991558+02 | 9761 | IO | DataFileWrite | active | 837728477 | client backend | SELECT * FROM mypgio('pgio2', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio2 WHERE mykey BETWEEN 1065 AND 1320
2018-07-12 21:57:22.991558+02 | 9762 | IO | DataFileWrite | active | 1046864277 | client backend | SELECT * FROM mypgio('pgio3', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio3 WHERE mykey BETWEEN 267 AND 522
2018-07-12 21:57:22.991558+02 | 9763 | IO | DataFileRead | active | 1648177216 | client backend | SELECT * FROM mypgio('pgio1', 50, 3000, 131072, 255, 8); | UPDATE pgio1 SET scratch = scratch + 1 WHERE mykey BETWEEN 1586 AND 1594
2018-07-12 21:57:22.991558+02 | 9766 | IO | DataFileWrite | active | 3411884874 | client backend | SELECT * FROM mypgio('pgio4', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio4 WHERE mykey BETWEEN 3870 AND 4125
2018-07-12 21:57:21.990178+02 | 9761 | CPU | CPU | active | 837728477 | client backend | SELECT * FROM mypgio('pgio2', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio2 WHERE mykey BETWEEN 13733 AND 13988
2018-07-12 21:57:21.990178+02 | 9762 | IO | DataFileRead | active | 1046864277 | client backend | SELECT * FROM mypgio('pgio3', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio3 WHERE mykey BETWEEN 4135 AND 4390
2018-07-12 21:57:21.990178+02 | 9763 | IO | DataFileWrite | active | 2994234299 | client backend | SELECT * FROM mypgio('pgio1', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio1 WHERE mykey BETWEEN 4347 AND 4602
2018-07-12 21:57:21.990178+02 | 9766 | CPU | CPU | active | 3411884874 | client backend | SELECT * FROM mypgio('pgio4', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio4 WHERE mykey BETWEEN 14423 AND 14678
2018-07-12 21:57:20.985253+02 | 9761 | IO | DataFileWrite | active | 837728477 | client backend | SELECT * FROM mypgio('pgio2', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio2 WHERE mykey BETWEEN 129 AND 384
2018-07-12 21:57:20.985253+02 | 9762 | IO | DataFileWrite | active | 1046864277 | client backend | SELECT * FROM mypgio('pgio3', 50, 3000, 131072, 255, 8); | SELECT sum(scratch) FROM pgio3 WHERE mykey BETWEEN 3313 AND 3568
(10 rows)

Everything is there. The timeline where each sample links together the user call (top_level_query), the running query (queryid and query – which is the text with parameter values), and the wait event (wait_event_type and wait_event).

Here is, on one sample, what is currently available in the beta version:

select * from pg_active_session_history where pid=9766 order by ash_time desc fetch first 1 rows only;
-[ RECORD 1 ]----+-----------------------------------------------------------------------
ash_time | 2018-07-12 21:57:23.992798+02
datid | 17487
datname | pgio
pid | 9766
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2018-07-12 21:28:46.539052+02
xact_start | 2018-07-12 21:28:46.542203+02
query_start | 2018-07-12 21:28:46.542203+02
state_change | 2018-07-12 21:28:46.542209+02
wait_event_type | IO
wait_event | DataFileExtend
state | active
backend_xid | 37554
backend_xmin | 37553
top_level_query | SELECT * FROM mypgio('pgio4', 50, 3000, 131072, 255, 8);
query | UPDATE pgio4 SET scratch = scratch + 1 WHERE mykey BETWEEN 700 AND 708
queryid | 1109524376
backend_type | client backend

Then, what do we do with this? This is a fact table with many dimensions. And we can drill down on the database activity.

A quick overview of the load shows that I have, on average, 4 foreground sessions running for my user calls, and very low vacuuming activity:

postgres=# select backend_type
postgres-# ,count(*)/(select count(distinct ash_time)::float from pg_active_session_history) as load
postgres-# from pg_active_session_history
postgres-# group by backend_type
postgres-# ;
backend_type | load
-------------------+--------------------
client backend | 4.09720483938256
autovacuum worker | 0.07467667918231
(2 rows)

I’ll show in a future post how to query this view to drill down into the details. For the moment, here is a short explanation about the reason to go to a sampling approach.

Here is an abstract sequence diagram showing some typical user calls to the database. Several components are involved: CPU for the backed process, or for background processes, the OS, the storage… Our tuning goal is to reduce the user call duration. And then to reduce or optimize the work done in the different layers. With the current statistics available on PostgreSQL, like PG_STAT_ACTIVITY or PG_STAT_STATEMENTS, or available from the OS (strace to measure system call duration) we have a vertical approach on the load. We can look at each component individually:
PGASHWHY001

This is basically what we did on Oracle before ASH (Active Session History) was introduced in 10g, 12 years ago. The activity sampling approach takes an orthogonal point of view. Rather than cumulating statistics for each components, it looks at what happens on the system at specific point in times, across all components. We don’t have all measures (such as how many execution of a query) but only samples. However, each sample gives a complete view from the user call down to the system calls. And 1 second samples are sufficient to address any relevant activity, without taking too much space for short retention. For each sample, we cover all layers end-to-end:
PGASHWHY002
This horizontal approach makes the link between the user calls (the user perception of the database performance) and the system resources where we can analyze and optimize. With this, we can ensure that our tuning activity always focuses on the problem (the user response time) by addressing the root cause on the right component.

 

Cet article pgSentinel: the sampling approach for PostgreSQL est apparu en premier sur Blog dbi services.

Fishbowl Solutions Helps Global Communications Company Leverage Oracle WebCenter to Create a Consumer-Grade Portal Experience for its Employees

An international communications, media, and automotive company based in the United States, with over $18 billion in revenue 60,000 employees globally, wanted to implement a new, consumer-grade portal to provide a digital workplace where employees can access company-wide information, as well as share tools and resources.

Business Objective

The company was challenged with having to manage and maintain four different portals across their divisions. Each of these had its own set of features including separate collaboration systems, design that did not comply with the company’s current branding and style guidelines, and ten-year old portal technology that was no longer supported.

Overall, the company envisioned a single portal to engage employees as ambassadors and customers by surfacing news about products and key initiatives. Additionally, provide employees with a broader knowledge of the entire company beyond their divisions. In considering this vision, the company outlined their objectives:

  • Connect and engage employees by providing them with easy access to company, department news, resources and tools
  • Improve user experience (UX) design and content restructuring of employee information systems
  • Enhance and increase employee collaboration
To create a connected, consumer-like digital experience that promotes collaboration, sparks innovation, and helps employees get their jobs done, any place, any time, on any device

Company Mission Statement for the New Portal

Portal Solution Accelerator Implementation

After evaluating several enterprise portal platforms, the company chose Oracle WebCenter as the system they would use to build their employee digital workplace portal. Oracle WebCenter includes content management and portal components. It was particularly chosen due to its scalability and performance (backed by Oracle database), its ability to target and personalize content based on metadata, its flexibility to provide integrations with third-party collaboration systems, and its ability to integrate with Oracle applications including E-Business Suite, PeopleSoft, and Taleo Cloud Service.

The company had limited Oracle WebCenter development and implementation experience and resources, so they sought out partners to help with their new portal implementation. Fishbowl Solutions was chosen based on their vast Oracle WebCenter experience and expertise. Additionally, Fishbowl offered a portal jumpstart framework called Portal Solution Accelerator (PSA) that provided additional software capabilities to drive better user experience and overall performance. This includes integrating content to be consumed on the portal using single page applications (SPA) instead of Oracle Application Development Framework (ADF) taskflows. SPA taskflows are more lightweight and can therefore be more easily consumed on the portal without impacting performance. SPA taskflows also enable the use of other front-end design frameworks, such as Oracle JET (JavaScript Extension Toolkit), enabling web designers and marketers to develop their own components with basic Javascript, HTML, and CSS knowledge.

Fishbowl Solutions leveraged its PSA to address seven critical capabilities the company wanted from the new portal:

  • Hybrid Content Integration – Ability to make quick updates/edits to content on the portal portal via a web inline editor, while having new content be checked in via profiles using Oracle WebCenter Content
  • Personlization – Content targeted to individuals based on such user attributes as Division, Department, Company, Loacation, Management, and Employee Type
  • Security – Leveraged roles and groups from Oracle Unified Directory to drive security. Fishbowl’s Advanced User Security Mapping (AUSM) software was used to ease user management because it enables rules to be created to map LDAP attributes to WebCenter roles (participant, contributor, administrator, etc.)
  • Collaboration – Integrated 3rd-party collaboration system, Jive, into the portal user experience so that users can see activity stream and collaborate with others in-context of the portal
  • Application Integration – Integrated with PeopleSoft Human Capital Management to pull additional employee data onto the portal. This was needed for upper management to be able to quickly view HR-related tasks on mobile devices.
  • Content & People Search – Content indexed by the Google Search Appliance is made available by searching on the portal where secure results are returned
  • Optimal Portal Performance – Leveraged local Oracle Coherence cache available per node in WebCenter Portal, while Redis was used as a means to create a central publishing model for updated content to the cache
Results

WebCenter portal devicesThe company officially launched the new employee portal in July of 2017. Since then, user feedback has been very positive. The value-add capabilities of Fishbowl PSA – standard portal page templates and layouts, mega-menu navigation, role-based content contribution using Oracle WebCenter Content – meant the company could focus on implementation and not custom development. This reduced time-to-market by 25 percent. Typically, the company has around 1,500 concurrent users on the home page, which loads in about 4.5 seconds. Secondary page visits take around 2.5 seconds to load. This performance is easily tracked as the company sees around 40,000 active users each week with minimal complaints or issues reported. The load times have exceeded expectations.

It has been reported that 92 percent of user sessions occur from the desktop, 5 percent from smartphones, and 2 percent from tablets. The most popular portal page is the Home page, followed by Time Reporting, Jobs, My Pay, and Employee Discounts.

Overall, the new portal has provided employees with a broader knowledge of the entire company beyond their position, division, and department, while bringing together one unified message and brand.

This is a very well put together site. I will definitely use it more than the old portal.

Field Service Representative

The post Fishbowl Solutions Helps Global Communications Company Leverage Oracle WebCenter to Create a Consumer-Grade Portal Experience for its Employees appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Cardinality Puzzle

Jonathan Lewis - Thu, 2018-07-12 06:57

One of the difficulties of being a DBA and being required to solve performance problems is that you probably never have enough time to think about how you got to a solution and why the solution works; and if you don’t learn about the process itself , you just don’t get better at it. That’s why I try (at least some of the time) to write articles and books (as I did with CBO Fundamentals) that

  1. explain simple details that can be used as background facts
  2. walk through the steps of solving a problem

So here’s an example from a question on the ODC database forum asking about the cause and workaround for a bad cardinality estimate that is producing a poorly performing execution plan. It’s actually a type of problem that comes up quite frequently on large data sets and explains why a simple “gather stats” is almost guaranteed to leave you with a few headaches (regardless of whether or not you choose to include histograms as part of the process). I’m not going to offer “the answer” – I’m just going to talk about the inferences we can make from the facts supplied and where we have to go from there.

The DBA has a table holding 80,000,000,000 rows. It is list/hash partitioned with 2 partitions and 1,024 sub-partitions (per partition) but neither of the partitioning key columns appears in the query. The query runs parallel and the optimizer (presumably thanks to the specific settings of various parameters related to parallel execution uses dynamic sampling at level 3).

There is an inline view defined in the query and the DBA has isolated this as a key component of the problem and supplied a query and plan (from “explain plan”) against that view.


select * from TAB2 T
WHERE T.DT = to_date(:b1,'MM/DD/YYYY HH24:MI:SS');
 
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |   479M|    76G|  1756K (14)| 05:51:14 |       |       |        |      |            |
|   1 |  PX COORDINATOR              |                          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000                 |   479M|    76G|  1756K (14)| 05:51:14 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION HASH ALL     |                          |   479M|    76G|  1756K (14)| 05:51:14 |     1 |  1024 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS STORAGE FULL| TAB1                     |   479M|    76G|  1756K (14)| 05:51:14 |     1 |  2048 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - storage(COALESCE("TB1"."DFG",'N')='N' AND TRUNC(INTERNAL_FUNCTION("TB1"."DT_TM"))=TO_DATE(:B1,'MM/DD/YYYY
              HH24:MI:SS'))
       filter(COALESCE("TB1"."DFG",'N')='N' AND TRUNC(INTERNAL_FUNCTION("TB1"."DT_TM"))=TO_DATE(:B1,'MM/DD/YYYY
              HH24:MI:SS'))

Note
-----
   - dynamic sampling used for this statement (level=3)

The DBA’s problem is that if the estimated cardinality of this extract goes over roughly 500M the optimizer chooses a bad plan for the overall query – and on occasion this extract has given an estimate of 5 billion rows. Moreover, the actual number of rows returned by this extract is typically in the order of 40M, so the estimate is a long way off even when it’s “good enough”.

So where do we start looking to work out what’s going wrong? You’ll note, of course, that after text expansion the user’s single predicate has changed, and an extra predicate (previously hidden inside the view) has appeared; instead of just T.DT = to_date(:b1,’MM/DD/YYYY HH24:MI:SS’) we now have (cosmetically adjusted):

        COALESCE(DFG,'N')='N' 
AND     TRUNC(DT_TM)=TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')

There are two immediately obvious threats here – first that the combination of predicates means Oracle is likely to make a mistake because it will check the individual selectivities and multiply them together to get the combined selectivity, second that the appearance of predicates of the form “function(column) = constant” means that Oracle will guess 1% as the individual selectivities.

Without checking more details we might assume that a possible quick fix (that would require no changes to existing code) would be to create a couple of virtual columns (or extended stats) to represent the two expressions and gather stats on the resulting columns – though it is a restriction of extended stats that you can’t “double up” and create a column group on the two column expressions, so there’s still some scope for a cardinality estimate that is still sufficiently bad even with this approach. We also note that if we can change the coalesce(DFG,’N’) that must have been hidden in the view to nvl(DFG,’N’) then Oracle would be able to “or expand” the nvl() and use a more appropriate selectivity for that part of the predicate.

However, the points I’ve covered so far tend to produce estimates that are too small and often much too small. So maybe the key to the problem is in the Note section that tells us that Oracle has (successfully) used dynamic sampling for this statement. In other words, all the theory of how the optimizer calculates selectivity may be irrelevant – the estimate will be based on the luck of the sample.

So let’s take a look at the (slightly edited) table stats we’ve been given:

column_name data_type num_distinct low_value      high_value     density   num_null  histogram
DT_TM       DATE           6179571 78740B1E0A383C 7876020B01262B 1.6182E-7 0         NONE
DFG         VARCHAR2             1 4E             4E             1         0         NONE

Notice that the DFG (apparently) has the value ‘N’ for every row in the table (low_value = high_value = 0x4E, num_nulls = 0). The date range is 30-Nov-2016 to 11-Feb-2018, with no histogram but 6.18M distinct values for 80 Billion rows. Neither column has a histogram.

A little arithmetic tells us that (on average) there ought to be about 182M (= 80B / 438 days) rows for any one day – and that’s worth thinking about for three separate reasons.

First, an estimate of 479M against an average of 182M isn’t too surprising if it’s based on a fairly small sample, it’s only out by a factor of 2.6. On the other hand, getting an an estimate of 5 billion – which can happen on bad days – is extremely unlikely if the data is uniformly distributed across dates.

Secondly, the DBA supplied us with some data from the recent past with an aggregate query for “trunc(dt_tm)”, with the following results:

TRUNC(DT_TM)   COUNT(*)
------------ ----------
01-FEB-18    44,254,425
02-FEB-18    46,585,349
03-FEB-18    43,383,099
04-FEB-18    32,748,364
05-FEB-18    37,993,126
06-FEB-18    39,708,994
07-FEB-18    38,696,777
08-FEB-18    41,871,780
09-FEB-18    46,702,852
10-FEB-18    42,744,870
11-FEB-18    34,971,845
12-FEB-18    37,165,983

Recent data seems to follow an average of around 40M rows per day, so the estimate of 182M that we can derive from the stored statistics is a long way off: the present is behaving very differently from the past and that’s a relatively common problem with very large data sets – though it’s more usual for rolling averages to increase from the past to the present because the data is often representing the growth of a business over time. Can we create a hypothesis to explain the discrepancy, and could that hypothesis also account for the sample producing some very strange estimates ?

Finally, slightly more subtle and only included for completeness, if this column is supposed to hold date and time to the nearest second – which is what you might expect from an Oracle date type – there are 38 million possible values (438 x 86,400) it could be holding, and that’s more than the actual number of distinct values by a factor of 6. We can also work out that 80 billion rows over 438 days is 2,000 rows per second (on average). Averages are often misleading, of course, many systems have a pattern where a working day shows most of the data created in a 12 – 16 hour window with a couple of hours of more intense activity. For reference, though: average rows per second for the recent data is roughly 40M/86400 = 460; while the average we derive from the stored statistics is 80B / 6M = 13000 rows per second; this unlikely pattern needs a “non-uniform” explanation.

How do these three thoughts help us to understand or, to be more accurate, to make a sensible guess about why the optimizer can use dynamic sampling and get a wildly variable estimate which can be 1 or 2 orders of magnitude wrong. (varying between 479M and 5,000M compared to the recent actual 40M)?

Here’s one simple idea: extrapolate the 40M rows per day over 80B rows: that’s 2,000 days (possibly rather more since businesses tend to grow). What if the dt_tm is the timestamp for the moment the row was loaded into the database, and a couple of years ago (maybe around “30th Nov 2016”) the data was restructured and the existing five years of data was loaded over a very short period of time – let’s say one week. This would leave you with 17B rows of “new” data with a dt_tm spread at 40M rows per day for most of 438 days, and 63B rows of “historic” data packed into 7 days (at 9B rows per day).

I don’t know how Oracle would have randomly selected its sample from an extremely large table with 2,048 physical data segments but it’s totally believable that a small, widely scattered sample could end up with an extremely unrepresentative subset of the data. A completely random sample of the data would produce an estimate of around 500M rows for the predicate; but it would only take a fairly small variation in the sample (taking a few too many “historic” rows) to produce a large enough change in the estimate to change the execution plan, and a rare, but not extreme variation could easily take the estimate up to 5B.

Next Steps

It would be at this point in a performance assignment that I’d be asking around to find out if my guess about a massive data load operation was correct – if I couldn’t get the answer by talking to people I’d run a query against the whole data set to check the hypothesis, because there’s clearly some sort of skew in the data that’s causing a problem. I’d also run the critical part of the query a couple of times with events 10046/level 4 and 10053 set (but only fetching the first few rows) to find out from the trace file how large a sample Oracle was using, and then run the sampling query a few times to see what the sampled results looked like. Depending on the results I’d either find a way to stop Oracle from sampling for this query or I might create a virtual column (or just extended stats since it’s 11g) on just the trunc(dt_tm), possibly with a histogram in place (maybe coded by hand) if that could isolate the special dates and leave Oracle with a better estimate of the typical date. I might find I had to change the coalesce() to an nvl() as well – or create a virtual  column – to stop the sampling.

Finally, it’s worth noting that in 11g it’s possible to create pending (table preference “PUBLISH” = FALSE) stats for testing purposes; it’s also worth noting that the default histogram on trunc(dt_tm) would be a height-balanced histogram while we could create a frequency histogram in 12c since 12c allows us to specify up to 2,048 columns.

Footnote

If you check the ODC thread you’ll see that the OP has marked as correct a suggestion to change:

    TRUNC (TB1.DT_TM)  = to_date(:b1,'MM/DD/YYYY HH24:MI:SS');  

to

    dt_tm >= trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))
and dt_tm <  trunc(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))+1

Note that that’s “greater than or equal to” at one end and “strictly less than” at the other when using “date + 1”.

This has the effect of giving the optimizer a chance of using the low/high values of the column to produce a better (though perhaps still overlarge) and consistent estimate of the rows in the date range; and it may also stop the optimizer from doing dynamic sampling at level 3 (the “I’m guessing, let’s check” level) though it’s possible that the sampling would be disabled only if the coalesce() were changed to an nvl() as well.

Of course, from the information supplied, this looks like the OP would have to change a view definition and the run-time code to achieve the result. But in an ideal world doing things that avoid confusing the optimizer is usually the sensible strategy provided it doesn’t take an extreme amount of coding and testing.

 

Which Indexes are being used by our Application?

Tom Kyte - Thu, 2018-07-12 04:46
We have just gone live and I have noticed alarming growth in the indexes. Closer investigation shows several large, heavy transactional tables with a large number of indexes. I believe we are duplicating indexes and that some of the could be remove...
Categories: DBA Blogs

SOASuite12c - BPEL: JTA transaction is not in active state

Darwin IT - Thu, 2018-07-12 03:19
Yesterday I ran into this pretty weird problem.

A bit of context...I have two BPEL services to generate documents using BIP. One I created earlier that is based on a generic XML used by BIP to generate multiple letters. Now I had to create another one that is a report, so uses another XML. I generated an XSD for both XML's but since they haven't got a namespace, but same element names, I can't have them in the same composite. So, I duplicated the code.

I created a WSDL with two operations, one for the letters and one for the report, so I wanted to call the report from the service that created the letters. The first service is called 'GenerateDocument', but with an operation 'GenerateLetter', but with an added operation 'GenerateReport'.

So I changed the BPEL and replaced the 'Receive' by a Pick:
In the invoke it calls the 'GenerateReport' BPEL service, that does basically exact the same as in the scope under the 'Generate Letter' OnMessage.

In the 'GenerateReport' BPEL service (and from the 'Generate Letter' scope) I call a Base64Encoding service. It gets an XML in, and it will encode it to string using ora:getContentAsString() and encode that using a Spring bean, based on a quite simple java bean:


But now the problem...
So, called seperately, the 'Generate Report' service functioned just fine. Also the 'Generate Letter' operation of the 'Generate Document' service, thus the 'Generate Lettter' OnMessage from the Pick above, function just fine. But, when I call the 'Generate Document' service using the 'Generate Report' operation, resulting in the other OnMessage I'll get the following message on return from the Base64Encoding service:
<exception class="com.collaxa.cube.engine.EngineException">JTA transaction is not in active state.
The transaction became inactive when executing activity "" for instance "60,004", bpel engine can not proceed further without an active transaction. please debug the invoked subsystem on why the transaction is not in active status. the transaction status is "MARKED_ROLLBACK".
The reason was The execution of this instance "60004" for process "GenereerMachtigingenRapportProcess" is supposed to be in an active jta transaction, the current transaction status is "MARKED_ROLLBACK", the underlying exception is "EJB Exception: " .
Consult the system administrator regarding this error.
<stack>
<f>com.oracle.bpel.client.util.TransactionUtils.throwExceptionIfTxnNotActive#126</f>
<f>com.collaxa.cube.ws.WSInvocationManager.invoke#398</f>
<f>com.collaxa.cube.engine.ext.common.InvokeHandler.__invoke#1460</f>
<f>com.collaxa.cube.engine.ext.common.InvokeHandler.handleNormalWSDLInvoke#806</f>
<f>com.collaxa.cube.engine.ext.common.InvokeHandler.handleNormalInvoke#497</f>
<f>com.collaxa.cube.engine.ext.common.InvokeHandler.handle#158</f>
<f>com.collaxa.cube.engine.ext.bpel.common.wmp.BPELInvokeWMP.__executeStatements#78</f>
<f>com.collaxa.cube.engine.ext.bpel.common.wmp.BaseBPELActivityWMP$1.call#197</f>
<f>com.collaxa.cube.engine.ext.bpel.common.wmp.BaseBPELActivityWMP$1.call#195</f>
<f>com.collaxa.bpel.sws.SWSComponentProcessActivityWrapper$1.call#74</f>
<f>com.collaxa.bpel.sws.SWSCallableActivityWrapper.execute#89</f>
<f>com.collaxa.bpel.sws.SWSComponentProcessActivityWrapper.execute#82</f>
<f>com.collaxa.cube.engine.ext.bpel.common.wmp.BaseBPELActivityWMP.perform#205</f>
<f>com.collaxa.cube.engine.CubeEngine.performActivity#2922</f>
<f>com.collaxa.cube.engine.CubeEngine._handleWorkItem#1289</f>
<f>com.collaxa.cube.engine.CubeEngine.handleWorkItem#1178</f>
<f>...</f>
</stack>
</exception>
<root class="oracle.fabric.common.FabricInvocationException">EJB Exception: <stack>
<f>oracle.fabric.CubeServiceEngine.handleRequestResponseServerException#3920</f>
<f>oracle.fabric.CubeServiceEngine.request#653</f>
<f>oracle.integration.platform.blocks.mesh.SynchronousMessageHandler.doRequest#151</f>
<f>oracle.integration.platform.blocks.mesh.MessageRouter.request#217</f>
<f>oracle.integration.platform.blocks.mesh.MeshImpl.request#283</f>
<f>sun.reflect.NativeMethodAccessorImpl.invoke0</f>
<f>sun.reflect.NativeMethodAccessorImpl.invoke#62</f>
<f>sun.reflect.DelegatingMethodAccessorImpl.invoke#43</f>
<f>java.lang.reflect.Method.invoke#498</f>
<f>org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection#318</f>
<f>org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint#183</f>
<f>org.springframework.aop.framework.ReflectiveMethodInvocation.proceed#150</f>
<f>oracle.integration.platform.metrics.PhaseEventAspect.invoke#57</f>
<f>org.springframework.aop.framework.ReflectiveMethodInvocation.proceed#172</f>
<f>org.springframework.aop.framework.JdkDynamicAopProxy.invoke#202</f>
<f>com.sun.proxy.$Proxy428.request</f>
<f>...</f>
</stack>
</root>
</fault>
</messages>

Most blogs or forums I found suggest increasing the JTA time out, as this one. However, in those cases also the time out is mentioned as a cause in the exception.
In my case though, there's no mention of a time-out. Nevertheless I did try the suggestion, but as expected, with no luck.
The investigationHow to proceed? Well, in those cases, I just 'undress' or 'strip-down' my code. Cut out all code to the point it works again. Then, piece by piece, I dress it again, until the point it breaks again. That way you can narrow to the exact point where it goes wrong.

It turns out it indeed just breaks again when I add the scope with the call to the Base64Encoding service. So, I had to investigate that a bit further. I fiddled with the transaction properties of the Exposed Service:
I don't want transaction support in this service actually: it doesn't do anything that needs to be transacted. But this wasn't it either.

A closer look to the composite then:
...
<component name="Base64Process" version="2.0">
<implementation.bpel src="BPEL/Base64Process.bpel"/>
<componentType>
<service name="base64process_client_ep" ui:wsdlLocation="WSDLs/Base64ServiceWrapper.wsdl">
<interface.wsdl interface="http://nl.darwin-it.service/wsdl/Base64Service/1.0#wsdl.interface(Base64ServicePortType)"/>
</service>
<reference name="Based64EncoderDecoder.Base64EncoderDecoder"
ui:wsdlLocation="WSDLs/IBase64EncoderDecoderWrapper.wsdl">
<interface.wsdl interface="http://base64.utils.darwin-it.nl/#wsdl.interface(IBase64EncoderDecoder)"/>
</reference>
</componentType>
<property name="bpel.config.transaction" type="xs:string" many="false">required</property>
<property name="bpel.config.completionPersistPolicy" type="xs:string" many="false">deferred</property>
</component>
<component name="Based64EncoderDecoder">
<implementation.spring src="Spring/Based64EncoderDecoder.xml"/>
<componentType>
<service name="Base64EncoderDecoder">
<interface.java interface="nl.darwin-it.utils.base64.IBase64EncoderDecoder"/>
</service>
</componentType>
</component>
...

And there my the following caught my eye:
...
<component name="Base64Process" version="2.0">
...
<property name="bpel.config.transaction" type="xs:string" many="false">required</property>
<property name="bpel.config.completionPersistPolicy" type="xs:string" many="false">deferred</property>
</component>

...

As said, I don't want a transaction, and I'm not interested in deferred persistence. So, I commented this out, and all worked.

What did I learn?I'm not sure. But apparently, when called from the main-flow directly, these properties don't hurt. The BPEL Engine doesn't feel the need to do a persist directly and therefor a transaction. But with one level deeper, called from another flow, on return from the Base64Encoding flow, it just felt the need to do a persist and thus needed a transaction. That was not there.

All the services in the composition of the 3 composites (GenerateDocument -> GenerateReport -> Base64Encoding) are synchronous, created with default settings. And therefor I did not expect this behavior.



Keep Calm and Code On: Four Ways an Enterprise Blockchain Platform Can Improve Developer ...

OTN TechBlog - Thu, 2018-07-12 01:45

A guest post by Sarabjeet (Jay) Chugh, Sr. Director Product Marketing, Oracle Cloud Platform

Situation

You just got a cool new Blockchain project for a client. As you head back to the office, you start to map out the project plan in your mind. Can you meet all of your client’s requirements in time? You're not alone in this dilemma.

You attend a blockchain conference the next day and get inspired by engaging talks, meet fellow developers working on similar projects. A lunchtime chat with a new friend turns into a lengthy conversation about getting started with Blockchain.

Now you’re bursting with new ideas and ready to get started with your hot new Blockchain coding project. Right?

Well almost…

You go back to your desk and contemplate a plan of action to develop your smart contract or distributed application, thinking through the steps, including ideation, analysis, prototype, coding, and finally building the client-facing application.

Problem

It is then that the reality sets in. You begin thinking beyond proof-of-concept to the production phase that will require additional things that you will need to design for and build into your solution. Additional things such as:
 

These things may delay or even prevent you from getting started with building the solution. Ask yourself the questions such as:

  • Should I spend time trying to fulfill dependencies of open-source software such as Hyperledger Fabric on my own to start using it to code something meaningful?
  • Do I spend time building integrations of diverse systems of record with Blockchain?
  • Do I figure out how to assemble components such as Identity management, compute infrastructure, storage, management & monitoring systems to Blockchain?
  • How do I integrate my familiar development tools & CI/CD platform without learning new tools?
  • And finally, ask yourself, Is it the best use of your time to figure out scaling, security, disaster recovery, point in time recovery of distributed ledger, and the “illities” like reliability, availability, and scalability?

If the answer to one or more of these is a resounding no, you are not alone. Focusing on the above aspects, though important, will take time away from doing the actual work to meet your client’s needs in a timely manner, which can definitely be a source of frustration.

But do not despair.

You need to read on about how an enterprise Blockchain platform such as the one from Oracle can make your life simpler. Imagine productivity savings multiplied hundreds of thousands of times across critical enterprise blockchain applications and chaincode.

What is an Enterprise Blockchain Platform?

The very term “enterprise”  typically signals a “large-company, expensive thing” in the hearts and minds of developers. Not so in this case, as it may be more cost effective than spending your expensive developer hours to build, manage, and maintain blockchain infrastructure and its dependencies on your own.

As the chart below shows, the top two Blockchain technologies used in proofs of concept have been Ethereum and Hyperledger.


 

Ethereum has been a platform of choice among the ICO hype for public blockchain use. However, it has relatively lower performance, is slower and less mature compared to Hyperledger. It also uses a less secure programming model based on a primitive language called Solidity, which is prone to re-entrant attacks that has led to prominent hacks like the DOA attack that lost $50M recently.  

Hyperledger Fabric, on the other hand, wins out in terms of maturity, stability, performance, and is a good choice for enterprise use cases involving the use of permissioned blockchains. In addition, capabilities such as the ones listed in Red have been added by vendors such as Oracle that make it simpler to adopt and use and yet retain the open source compatibility.

Let’s look at how enterprise Blockchain platform, such as the one Oracle has built that is based on open-source Hyperledger Fabric can help boost developer productivity.

How an Enterprise Blockchain Platform Drives Developer Productivity

Enterprise blockchain platforms provide four key benefits that drive greater developer productivity:

 
Performance at Scale

  • Faster consensus with Hyperledger Fabric
  • Faster world state DB - record level locking for concurrency and parallelization of updates to world state DB
  • Parallel execution across channels, smart contracts
  • Parallelized validation for commit

Operations Console with Web UI

  • Dynamic Configuration – Nodes, Channels
  • Chaincode Lifecycle – Install, Instantiate, Invoke, Upgrade
  • Adding Organizations
  • Monitoring dashboards
  • Ledger browser
  • Log access for troubleshooting

Resilience and Availability

  • Highly Available configuration with replicated VMs
  • Autonomous Monitoring & Recovery
  • Embedded backup of configuration changes and new blocks
  • Zero-downtime patching

Enterprise Development and Integration

  • Offline development support and tooling
  • DevOps CI/CD integration for chaincode deployment, and lifecycle management
  • SQL rich queries, which enable writing fewer lines of code, fewer lines to debug
  • REST API based integration with SaaS, custom apps, systems of record
  • Node.js, GO, Java client SDKs
  • Plug-and-Play integration adapters in Oracle’s Integration Cloud

Developers can experience orders of magnitude of productivity gains with pre-assembled, managed, enterprise-grade, and integrated blockchain platform as compared assembling it on their own.

Summary

Oracle offers a pre-assembled, open, enterprise-grade blockchain platform, which provides plug-and-play integrations with systems of records and applications and autonomous AI-driven self-driving, self-repairing, and self-securing capabilities to streamline operations and blockchain functionality. The platform is built with Oracle’s years of experience serving enterprise’s most stringent use cases and is backed by expertise of partners trained in Oracle blockchain. The platform rids developers of the hassles of assembling, integrating, or even worrying about performance, resilience, and manageability that greatly improves productivity.

If you’d like to learn more, Register to attend an upcoming webcast (July 16, 9 am PST/12 pm EST). And if your ready to dive right in you can sign up for $300 of free credits good for up to 3500 hours of Oracle Autonomous Blockchain Cloud Service usage.

Pages

Subscribe to Oracle FAQ aggregator