Feed aggregator

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.

Active session history in PostgreSQL: Say hello to pgSentinel

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

There is new project, currently in beta, which aims to bring active session history (and probably more) to PostgreSQL: pgSentinel. Because PostgreSQL is highly extensible such projects are possible and usually are coming as an extension. pgSentinel is no exception to that so lets see how it can be installed. When you want to try the beta for your own, please connect with the project on twitter.

This is what I got:

postgres@pgbox:/home/postgres/beta/ [pg103] ll
total 120
-rw-r--r--. 1 postgres postgres   1108 Jul  8 22:13 pgsentinel--1.0.sql
-rw-r--r--. 1 postgres postgres    117 Jul  5 22:15 pgsentinel.control
-rwxr-xr-x. 1 postgres postgres 108000 Jul  9 11:12 pgsentinel.so
-rw-r--r--. 1 postgres postgres    623 Jul  9 11:22 readme.txt

You can already see from here that we probably need to load a library because of the pgsentinel.so file. Lets copy that to the correct locations, in my case:

postgres@pgbox:/home/postgres/beta/ [pg103] cp pgsentinel--1.0.sql pgsentinel.control /u01/app/postgres/product/10/db_3/share/extension/
postgres@pgbox:/home/postgres/beta/ [pg103] cp pgsentinel.so /u01/app/postgres/product/10/db_3/lib/

As I plan to run pgbench later to get some load onto the system I’ve created a separate database for installing the extension:

postgres@pgbox:/home/postgres/ [PG10] psql -c "create database bench" postgres
CREATE DATABASE
postgres@pgbox:/home/postgres/ [PG10] pgbench -i -s 10 bench

When we need to load a library we need to tell PostgreSQL about that by setting the shared_preload_libraries parameter. As pgsentinel depends on pg_stat_statements this needs to be installed as well.

postgres=# alter system set shared_preload_libraries='pg_stat_statements,pgsentinel';
ALTER SYSTEM

So once we have that set and the instance is restarted:

postgres@pgbox:/home/postgres/beta/ [PG10] pg_ctl -D $PGDATA restart -m fast

… we should see the new extension in the pg_available_extensions view:

postgres=# select * from pg_available_extensions where name = 'pgsentinel';
    name    | default_version | installed_version |        comment         
------------+-----------------+-------------------+------------------------
 pgsentinel | 1.0             |                   | active session history
(1 row)

Ready to install the extensions:

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# create extension pgsentinel ;
CREATE EXTENSION
postgres=# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.5     | public     | track execution statistics of all SQL statements executed
 pgsentinel         | 1.0     | public     | active session history
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

So what did we get? One solution is to look at the sql file:

cat pgsentinel--1.0.sql
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pgsentinel" to load this file. \quit

CREATE FUNCTION pg_active_session_history(
    OUT ash_time timestamptz,
    OUT datid Oid,
    OUT datname text,
    OUT pid integer,
    OUT usesysid Oid,
    OUT usename text,
    OUT application_name text,
    OUT client_addr text,
    OUT client_hostname text,
    OUT client_port integer,
    OUT backend_start timestamptz,
    OUT xact_start timestamptz,
    OUT query_start timestamptz,
    OUT state_change timestamptz,
    OUT wait_event_type text,
    OUT wait_event text,
    OUT state text,
    OUT backend_xid xid,
    OUT backend_xmin xid,
    OUT top_level_query text,
    OUT query text,
    OUT queryid bigint,
    OUT backend_type text
   
)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'pg_active_session_history'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;

-- Register a view on the function for ease of use.
CREATE VIEW pg_active_session_history AS
  SELECT * FROM pg_active_session_history();

GRANT SELECT ON pg_active_session_history TO PUBLIC;

The other solution is to ask PostgreSQL directly:

bench=# \dx+ pgsentinel 
  Objects in extension "pgsentinel"
          Object description          
--------------------------------------
 function pg_active_session_history()
 view pg_active_session_history
(2 rows)

Basically we got a function and a view over that function. Lets have a look at the view then:

postgres=# \d pg_active_session_history
                   View "public.pg_active_session_history"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 ash_time         | timestamp with time zone |           |          | 
 datid            | oid                      |           |          | 
 datname          | text                     |           |          | 
 pid              | integer                  |           |          | 
 usesysid         | oid                      |           |          | 
 usename          | text                     |           |          | 
 application_name | text                     |           |          | 
 client_addr      | text                     |           |          | 
 client_hostname  | text                     |           |          | 
 client_port      | integer                  |           |          | 
 backend_start    | timestamp with time zone |           |          | 
 xact_start       | timestamp with time zone |           |          | 
 query_start      | timestamp with time zone |           |          | 
 state_change     | timestamp with time zone |           |          | 
 wait_event_type  | text                     |           |          | 
 wait_event       | text                     |           |          | 
 state            | text                     |           |          | 
 backend_xid      | xid                      |           |          | 
 backend_xmin     | xid                      |           |          | 
 top_level_query  | text                     |           |          | 
 query            | text                     |           |          | 
 queryid          | bigint                   |           |          | 
 backend_type     | text                     |           |          | 

Most of the columns are already in pg_stat_activity but there is more. Before going further lets generate some load:

postgres@pgbox:/home/postgres/ [PG10] pgbench -c 5 -j 4 -T 60 bench 
starting vacuum...end.
transaction type: 
scaling factor: 10
query mode: simple
number of clients: 5
number of threads: 4
duration: 60 s
number of transactions actually processed: 151397
latency average = 1.982 ms
tps = 2522.898859 (including connections establishing)
tps = 2523.280694 (excluding connections establishing)

Now we should see sampled data in the pg_active_session_history view:

bench=# select ash_time,top_level_query,query,queryid,wait_event_type,wait_event from pg_active_session_history limit 10;
           ash_time            |                               top_level_query                               |                                   query                                    |  queryid   | wait_event_type |  wait_event   
-------------------------------+-----------------------------------------------------------------------------+----------------------------------------------------------------------------+------------+-----------------+---------------
 2018-07-09 14:51:48.883599+02 | create database bench                                                       | create database bench                                                      | 3222771996 | CPU             | CPU
 2018-07-09 14:52:37.291115+02 | copy pgbench_accounts from stdin                                            | copy pgbench_accounts from stdin                                           | 4164808321 | CPU             | CPU
 2018-07-09 14:52:38.292674+02 | alter table pgbench_accounts add primary key (aid)                          | alter table pgbench_accounts add primary key (aid)                         | 4164808321 | CPU             | CPU
 2018-07-09 14:55:51.111621+02 | UPDATE pgbench_branches SET bbalance = bbalance + 2228 WHERE bid = 4;       | UPDATE pgbench_branches SET bbalance = bbalance + 2228 WHERE bid = 4       |  553956422 | Lock            | transactionid
 2018-07-09 14:55:51.111621+02 | END;                                                                        | END                                                                        | 3376944276 | CPU             | CPU
 2018-07-09 14:55:51.111621+02 | UPDATE pgbench_accounts SET abalance = abalance + -2408 WHERE aid = 973208; | UPDATE pgbench_accounts SET abalance = abalance + -2408 WHERE aid = 973208 | 2992934481 | CPU             | CPU
 2018-07-09 14:55:52.112507+02 | UPDATE pgbench_tellers SET tbalance = tbalance + -4957 WHERE tid = 87;      | UPDATE pgbench_tellers SET tbalance = tbalance + -4957 WHERE tid = 87      | 3459630226 | Client          | ClientRead
 2018-07-09 14:55:52.112507+02 | END;                                                                        | END                                                                        | 3376944276 | LWLock          | WALWriteLock
 2018-07-09 14:55:52.112507+02 | END;                                                                        | END                                                                        | 3376944276 | CPU             | CPU
 2018-07-09 14:55:52.112507+02 | UPDATE pgbench_branches SET bbalance = bbalance + -3832 WHERE bid = 8;      | UPDATE pgbench_branches SET bbalance = bbalance + -3832 WHERE bid = 8      |  553956422 | Lock            | transactionid
(10 rows)

The important point here is that we have the queryid which we can use to map that back to pg_stat_statements. So if we want to know what the shared_blks_* statistics for the update statement with query id 553956422 are, we can do that (or write a join over the two views, of course):

bench=# select shared_blks_hit,shared_blks_read,shared_blks_dirtied,shared_blks_written from pg_stat_statements where queryid = 553956422;
 shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written 
-----------------+------------------+---------------------+---------------------
          453201 |               29 |                  37 |                   0
(1 row)

Really looks promising, automatic session sampling in PostgreSQL. More tests to come …

 

Cet article Active session history in PostgreSQL: Say hello to pgSentinel est apparu en premier sur Blog dbi services.

Documetum xPlore: Failed to load content file + file system full

Yann Neuhaus - Wed, 2018-07-11 08:33

We had issues with our indexing server when launching batches of indexes to the queue, in the xPlore server. The indexing was taking so much time to index documents and almost all queue items fell down to the warning/error state.

In addition, our file system was growing too fast : 100Go for only 30.000 docs. I was like the temp folders weren’t cleared properly. And in other hand the queue was telling that the temp file couldn’t be loaded because it was cleared too early…

Thoughts

I observed the behavior of the temp folders in the idex agent server and noticed that the files were only 10min old, even after 30min after sending the batch of index requests. Hence, I deduced the clearing was still running, which could explain the index warning telling the file couldn’t be found.

That means the processing of the indexes takes too long while the clearing thread runs anyway… But I noticed that the file system was still growing way to much.

Solution

If you didn’t know, by default, the CPS (which parses the downloaded files) only has 1 thread. It means that if it takes too long (50Mo files in my case), the thread will be busy and other files will not be indexed during this time. But the documents will still be downloaded during this time, and the clearing process will still continue to harvest our beloved files.

The point here is to add more CPS threads to parallelize the process and then avoid missing files. You can also increase the time between two clearing phase but it’s not efficient and increasing the number of threads will improve your overall performances.

To do so, edit the following config file:

$DSEARCH_HOME/dsearch/cps/cps_daemon/PrimaryDsearch_local_configuration.xml

Change the following line from 1 to 4:

<daemon_count>1</daemon_count>

A restart will be needed. You can change the value from 1 to 6 maximum. Please note that xPlore uses two other threads for clearing and other processes, and it allows only 8 threads to run at the same time, then 6 is the maximum cps to run, otherwise you’ll have issues with the clearing thread, and you will end up with a file system full.

 

Cet article Documetum xPlore: Failed to load content file + file system full est apparu en premier sur Blog dbi services.

Media Agencies Conquer Industry Transparency Issues with NetSuite

Oracle Press Releases - Wed, 2018-07-11 08:00
Press Release
Media Agencies Conquer Industry Transparency Issues with NetSuite Forthea Interactive and MediaMax Focus on Creativity and Clients by Eliminating Business Process Complexity

San Mateo, Calif.—Jul 11, 2018

To focus time on rapidly changing client needs, boutique media agencies are selecting Oracle NetSuite to scale their business, without sacrificing creativity. For example, Forthea Interactive and MediaMax implemented Oracle NetSuite to gain a source of efficiency across their growing businesses and provide the support, transparency and creativity that clients demand.

Forthea Interactive Balances Data and Creativity With the Help of the Cloud

Founded in 2006, Forthea Interactive specializes in SEO, pay per click, web analytics, website design and social media marketing. The company prides itself on building successful campaigns for its clients by balancing the creative with the analytical. To help achieve this balance and drive results for clients, Forthea Interactive leverages NetSuite for financial management, customer relationship management (CRM) and project management. With NetSuite, Forthea Interactive has a modern, unified system to integrate its services and finance operations. As a result, collaboration and creativity are able to thrive.

“Marketing is very messy. There is so much data being collected that it’s hard to keep track and it can feel like drinking from a fire hose,” Chris Pappas, founder and CEO, Forthea Interactive. “In order to be more accountable to our clients, we had to be more organized and that’s where NetSuite comes in. NetSuite has provided us with a 360-degree view of our business. This allows us to easily track everything we need to do in order to run efficiently and successfully, and that ultimately helps enhance our client relationships.”

MediaMax Uses NetSuite From the Start to Help Manage Advertising Solutions and Processes

MediaMax provides geo-targeted print, data and digital advertising solutions for national brands, regional marketers, and SMBs across the US and Canada. To help support its continued growth and successfully manage data for its more than 1,000 customers, MediaMax utilizes NetSuite OneWorld for financial management, client management, and post-sale operations. NetSuite continues to be a critical platform and has played an integral part of the company’s infrastructure since the company’s inception in 2003.

“As the media world continues to transform at an unprecedented rate, we have to ensure we keep up,” said Charles Wirth, COO and CFO, MediaMax. “With NetSuite, we worry less about any of our key business processes, even as we continue to grow. What this allows us to do is spend time creating new products, focusing on creative solutions, and most importantly driving results and value for our clients.”

“We know the pressure media agencies are under. They need to deliver the transparency clients are now requiring while maintaining their creative culture,” said Chris Hering, Director of Advertising & Media Product Marketing, Oracle NetSuite. “NetSuite helps boutique media agencies like Forthea Interactive and MediaMax by streamlining agency workflows, allowing them to gain real-time insight into client and project-based accounting and deliver the best possible client experience.”

Oracle NetSuite will be attending AdAge’s Small Agency Conference on July 17-18, 2018 and hosting a networking breakfast. Please register here ASAP, space is limited.

Contact Info
Danielle Tarp
Oracle NetSuite Corporate Communications
650-506-2904
danielle.tarp@oracle.com
About Forthea Interactive

Forthea Interactive is a results-driven agency serving brands both locally and nationally and across many industries. To learn more visit https://www.forthea.com.

About MediaMax

Founded in 2003, MediaMax provides multi-channel, premium local advertising solutions to national brands, regional marketers and local companies in more than 100 local markets in the U.S and Canada. As the exclusive local advertising partner of Condé Nast, MediaMax delivers integrated local ad programs to more than 1,000 clients across a wide range of vertical markets including financial services, travel, healthcare, real estate, pharma, and more. To learn more visit https://mediamaxnetwork.com/.

About Oracle NetSuite

For more than 20 years, Oracle NetSuite has helped organizations grow, scale and adapt to change. NetSuite provides a suite of cloud-based applications, which includes financials / Enterprise Resource Planning (ERP), HR, professional services automation and omnichannel commerce, used by more than 40,000 organizations and subsidiaries in 199 countries and territories.

For more information, please visit http://www.netsuite.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.

Talk to a Press Contact

Danielle Tarp

  • 650-506-2904

Pages

Subscribe to Oracle FAQ aggregator