Feed aggregator
Oracle SOA Suite: Profiles (SOA Foundation, BPEL only, Orchestration, Enterprise, BPM Classic, ...)
The full set of adapters:
- file,
- FTP,
- database,
- JMS,
- MQSeries,
- AQ,
- E-Business Suite,
- User Messaging Service,
- socket,
- LDAP,
- Coherence,
- MSMQ,
- JDE
- file,
- FTP,
- database,
- JMS,
- MQSeries,
- AQ,
- E-Business Suite,
- User Messaging Service
After motherboard change on BDA server eth0 network interface is missing
Recently I had a BDA server that was refusing to come up. Every trial to run a start /SYS failed.
We were able to identify this as a motherboard failure, then the motherboard was replaced and we were able to bring up the server using the ILOM.
Back on track I've tried to setup the management network, but this failed because the eth0 NIC was missing.
[root@bdanode01 ~]# ethtool -i eth0 Cannot get driver information: No such deviceBut I've noticed we had eth1 available, nevertheless not functioning.
[root@bdanode01 ~]# ethtool -i eth1 driver: igb version: 5.3.0-k firmware-version: 3.25, 0x80000681 bus-info: 0000:02:00.0 supports-statistics: yes supports-test: yes supports-eeprom-access: yes supports-register-dump: yes supports-priv-flags: noFirst step was to check that the mac address of eth0 was correct, for that I've checked it on the ILOM cli and on file /etc/sysconfig/network-scripts/ifcfg-eth0
-> show /SYS/MB/NET0 /SYS/MB/NET0 Targets: Properties: type = Network Interface ipmi_name = MB/NET0 fru_description = 1G Ethernet Controller fru_manufacturer = INTEL fru_part_number = i210 fru_macaddress = 00:10:e0:de:5d:84 fault_state = OK clear_fault_action = (none) On /etc/sysconfig/network-scripts/ifcfg-eth0 we have a missing mac address, so I've added it.Then I've checked /etc/udev/rules.d/70-persistent-net.rules
On this file we had the mismatch. It seems the mac address from the previous motherboard remained in place for eth0 while the new one was assigned to eth1
Then I've edited the file leaving only one line for eth0 and with the correct mac address
[root@bdanode01 ~]# cat /etc/udev/rules.d/70-persistent-net.rules
# This file was automatically generated by the /lib/udev/write_net_rules # program, run by the persistent-net-generator.rules rules file. ## You can modify it, as long as you keep each rule on a single # line, and change only the value of the NAME= key. # PCI device 0x8086:0x1533 (igb) (custom name provided by external tool) SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:10:e0:de:5d:84", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"After reboot eth0 was back to work and I was able to setup the network.
ADF Postback Payload Size Optimization
Let's take a look into example. After clicking on any button in the form, go to network monitor and study Form Data section. You will see ID's and values for all fields included in the UI. All fields are submitted with HTTP request by default, even these fields were not changed:
Postback optimization property can be set in web.xml. By default it's value is full, change it to dirty:
With value set to dirty, try to change at least one field and then press any button. Observe Form Data section in network monitor - only fields with changed values will be submitted:
Try to test it in your project and see the difference.
Check my sample app for this use case on GitHub.
Quiz Night
Because it’s been a long time since the last quiz night. Here’s a question prompted by a recent thread on the ODevCom database forum – how many rows will Oracle sorts (assuming you have enough rows to start with in all_objects) for the final query, and how many sort operations will that take ?
drop table t1 purge; create table t1 nologging as select * from all_objects where rownum < 50000; select owner, count(distinct object_type), count(distinct object_name) from t1 group by owner;
Try to resist the temptation of doing a cut-n-paste and running the code until after you’ve thought about the answer.
My top 5 APEX 18.1 Plugins
There are six different types of plugins: dynamic action, item, region, process, authentication scheme and authorization scheme.
Plug-ins are absolutely fantastic to extend the native functionalities of APEX in a declarative way. The APEX plugin becomes a declarative option in the APEX Builder and has the [Plug-in] text next to it. In the next screenshot, you see the dynamic actions being extended by two Plug-ins.
If you are searching for an APEX plug-in, I typically go to APEX World > Plug-ins. The nice thing about that site is that the plug-ins seem to be maintained, so if a plug-in is not supported anymore it gets the status deprecated.
!! And here lays the catch with using plug-ins. When you decide to use a plug-in in your project, you become responsible for this and need to make sure it's compatible with every release of Oracle APEX. Many plug-ins are open source and many plug-in developers maintain their plug-ins, but it's really important you understand that at the end you are responsible for things you put in your application. If the plug-in is not secure or it breaks in the next release of APEX, you need to find a solution. So use plug-ins with care and see for example how many likes the plug-in has or what the comments are about the plug-in or author. Oracle is not reviewing or supporting the plug-ins !!
When I saw the tweet of Travis, I thought to do a blog post on my top 5 plugins I use in almost every project.
Here we go:
1. Built with love using Oracle APEX
I'm proud to built applications with Oracle Application Express, and this plug-in makes it very clear :) At the bottom of the app, you will see this text:
Note that in Oracle APEX 18.1 this text in included by default and you don't even need to add the plugin. Nevertheless, I wanted to include it in this list as it should be there in every app, even the ones built before APEX 18.1 :)
2. Select2
When a select list (or drop-down) has many values, it takes too long to find the right value. Select2 makes it easy to search for values, it also supports lazy loading and multiple select.
3. APEX Office Print
APEX Office Print extends APEX so it becomes possible to export to native Excel files and generate documents in Word, Powerpoint, PDF, HTML and Text, all based on your own template. It has many more features, I blogged about some before.
4. Dropzone
APEX 18.1 has declarative multi-file upload, but still, I love the Dropzone plugin developed by Daniel Hochleitner. You can drag multiple files from your desktop straight in your APEX app. Daniel is one of my favorite plug-in developers. When he releases something, you know it will be good.
5. Modal LOV
This is a newer plugin and I haven't used it that much yet, but I'm sure I will do. The nice thing with this item type plugin is that it also supports Interactive Grid. Where Select2 stays within the page, this Modal LOV comes with a modal list of values (pop-up) which is great if you want to show multiple columns or need more context for the record you need to select.
There are many more plug-ins out there, most of them work on APEX 5.x and upwards. For example, Pretius has some cool plug-ins too, the one to create nested reports I recently used in a project. Another site you can find plug-ins is APEX-Plugin.com.
PGDay Amsterdam – follow up 2 – Where do null values go to in a hash partitioned table?
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
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
Chatbot Tech Round Table: Three Real Life Use Cases on How Oracle Chatbots Can Be Integrated into Cloud Applications
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.
Database Vault : Rules, Rule Sets and Command Rules
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
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
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.
SummaryIf 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
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
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:
Some impressions:
Here you can see Devrim preparing the opening of the event with the introduction session:
… and then it happened: We finally started:
Jan kicked of the sessions with his talk about the reasons he finally arrived in the PostgreSQL community after working years in another one:
Oleksi took over to speak about ACID, transactions and much more, a great talk:
I had the pleasure to speak about PostgreSQL 11 to close the first sessions before the coffee break:
Stefanie followed with foreign data wrappers and data integration with PostgreSQL (another great one):
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:
Hans rocked the stage right after:
Devrim right after his talk about WAL:
As in Rapperswil two weeks ago Bruce closed the sessions with his talk: Will PostgreSQL live forever:
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
MTS Configuration
pgSentinel: the sampling approach for PostgreSQL
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:
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:
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 ObjectiveThe 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
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
The 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.
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.
Cardinality Puzzle
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
- explain simple details that can be used as background facts
- 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 StepsIt 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.
FootnoteIf 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?
Pages
