Feed aggregator

Connection Storm with Inactive sessions on ORACLE RAC

Tom Kyte - Thu, 2019-06-06 10:06
Hi All, We recently migrated from Solaris to AIX and moved to Oracle RAC : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for...
Categories: DBA Blogs

Using Oracle best way

Tom Kyte - Thu, 2019-06-06 10:06
Hi, We have set of Mainframe programs which connects to Oracle database and queries set of tables. Our application and DB servers are in 2 different locations (which we cannot change at the moment), which i believe is causing considerable delays w...
Categories: DBA Blogs

PFCLATK - Audit Trail Toolkit - Checksums

Pete Finnigan - Thu, 2019-06-06 09:46
We have a toolkit called PFCLATK that is used in customer engagements to assist our customers to create comprehensive and useful audit trails for their databases. The toolkit is used in consulting engagements at the moment but will be adding....[Read More]

Posted by Pete On 06/06/19 At 03:08 PM

Categories: Security Blogs

Discover target database 18c with EM12c

Yann Neuhaus - Thu, 2019-06-06 08:34

Working on Enterprise Manager 12.1.0.4 version at a client’s site, we would like to know if oracle database target in 18c version could be discovered and monitored, even if Enterprise Manager 12.1.0.4 does not support Oracle 18c database targets.

Installing the agent 12c on the target host did not cause any problem, the oracle database 18c discovery ran successfully, but the database was seen as down in the Enterprise Manager 12.1.0.4 console.

We tried several tricks without any positive results, but running the following command shows us that this was a connection problem:


oracle@em12c:/home/oracle/:> emctl getmetric agent DB18,oracle_database,Response
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.
All rights reserved.
Status,State,oraerr,Archiver,DatabaseStatus,ActiveState0,UNKNOWN,
Failed to connect: java.sql.SQLException: 
ORA-28040: No matching authentication protocol,UNKNOWN,UNKNOWN,UNKNOWN

With Oracle 18c, the default value for SQLNET.ALLOWED_LOGON_VERSION parameter is 12, it means that database clients using pre-12c jdbc thin drivers cannot authenticate to 18c database servers.

The workaround is to add in the database server sqlnet.ora the following lines:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

We restart the database and the agent, and the Oracle database 18c is displayed up and running in Enterprise Manager 12.1.0.4:

Some more tests showed us that everything is working fine: incident management, performance, top activity ..aso

Nevertheless do not forget that oracle database target version 18c are not supported with Enterprise Manager 12.1.0.4. I will recommand to use the last Enterprise Manager 13.3 last version in order to administer and monitor Oracle database 18c.

Cet article Discover target database 18c with EM12c est apparu en premier sur Blog dbi services.

Get Up to Speed with Oracle ACEs on the Kscope Database Track

OTN TechBlog - Thu, 2019-06-06 08:03
All Aboard for Database Expertise...

This second post in a series on Kscope 2019 sessions presented by members of the Oracle ACE program focuses on the database track. Kscope 2019 arrives on time, June 23-27 in Seattle. Click here for information and registration.

Click the session titles below for time, date, and location information for each session.

We'll cover sessions in the other tracks in upcoming posts. Stay tuned!

 

Oracle ACE Directors

Oracle ACE Director Alex NuijtenAlex Nuijten
Director, Senior Oracle Developer, allAPEX
Oosterhout, Netherlands

 

Oracle ACE Director Debra LilleyDebra Lilley
Associate Director, Accenture
Belfast, United Kingdom

 

Oracle ACE Director Dimitri GielisDimitri Gielis
Director, APEX R&D
Leuven, Belgium

 

Oracle ACE Director Francisco AlvarezFrancisco Munoz Alvarez
CEO, CloudDB
Sydney, Australia

 

Oracle ACE Director Heli HelskyahoHeli Helskyaho
CEO, Miracle Finland Oy
Finland

 

Oracle ACE Director Jim CzuprynskiJim Czuprynski
Senior Enterprise Data Architect, Viscosity North America
Bartlett, Illinois

 

Oracle ACE Director Kim Berg HansenKim Berg Hansen
Senior Consultant, Trivadis
Funen, Denmark

 

Oracle ACE Director Martin Giffy D'SouzaMartin Giffy D’Souza
Director of Innovation, Insum Solutions
Calgary, Alberta, Canada

 

Oracle ACE Director Mia UrmanMia Urman
CEO, AuraPlayer Ltd
Brookline, Massachusetts

 

Oracle ACE Director Patrick BarelPatrick Barel
Sr. Oracle Developer, Alliander via Qualogy
Haarlem, Netherlands

 

Oracle ACE Director Peter KoletzkePeter Koletzke
Technical Director, Principal Instructor
Independent Consultant

 

Oracle ACE Director Richard NiemiecRichard Niemiec
Chief Innovation Officer, Viscosity North America
Chicago, Illinois

 
Oracle ACEs

Oracle ACE Dani SchniderDani Schnider
Senior Principal Consultant, Trivadis AG
Zurich, Switzerland

 

Oracle ACE Holger FriedrichHolger Friedrich
CTO, sumIT AG
Zurich, Switzerland

 

Oracle ACE Liron AmitziLiron Amitzi
Senior Database Consultant, Self Employed
Vancouver, Canada

 

Oracle ACE Philipp SalvisbergPhilipp Salvisberg
Senior Principal Consultant, Trivadis AG
Zurich, Switzerland

 

Oracle ACE Robert MarzRobert Marz
Principal Technical Architect, its-people GmbH
Frankfurt, Germany

 
Oracle ACE Associates

Oracle ACE Associate Alfredo AbateAlfredo Abate
Senior Oracle Systems Architect, Brake Parts Inc LLC
McHenry, Illinois

 

Oracle ACE Associate Eugene FedorenkoEugene Fedorenko
Senior Architect, Flexagon
De Pere, Wisconsin

 
Additional Resources

PostgreSQL partitioning (6): Attaching and detaching partitions

Yann Neuhaus - Thu, 2019-06-06 01:00

Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are:

  1. PostgreSQL partitioning (1): Preparing the data set
  2. PostgreSQL partitioning (2): Range partitioning
  3. PostgreSQL partitioning (3): List partitioning
  4. PostgreSQL partitioning (4) : Hash partitioning
  5. PostgreSQL partitioning (5): Partition pruning

From time to time it might be required that you attach a partition to an existing partitioned table. A reason for that might be that you partitioned by year and suddenly you get data for the next year because your data is based on imported data from an external source. Of course you could just create a new partition for that and then load the data but there is a more elegant way of doing that.

Our range partitioned table currently has partitions for the years 2012 to 2020:

postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),
            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_default DEFAULT

Lets assume we suddenly get data for the year 2021 and we want that data to be part of the range partitioned table. If we keep the partitioned table as it is, new data would go to the default partition and this is not what we want. What we can do is to create a regular table with exactly the same structure as the partitioned table. With PostgreSQL’s create table command you have the “like_option” which is very handy for this use case:

postgres=# create table traffic_violations_p_2021 ( like traffic_violations_p including all );
CREATE TABLE
postgres=# \d traffic_violations_p_2021
                     Table "public.traffic_violations_p_2021"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 seqid                   | text                   |           |          | 
 date_of_stop            | date                   |           |          | 
 time_of_stop            | time without time zone |           |          | 
 agency                  | text                   |           |          | 
 subagency               | text                   |           |          | 
 description             | text                   |           |          | 
 location                | text                   |           |          | 
 latitude                | numeric                |           |          | 
 longitude               | numeric                |           |          | 
 accident                | text                   |           |          | 
 belts                   | boolean                |           |          | 
 personal_injury         | boolean                |           |          | 
 property_damage         | boolean                |           |          | 
 fatal                   | boolean                |           |          | 
 commercial_license      | boolean                |           |          | 
 hazmat                  | boolean                |           |          | 
 commercial_vehicle      | boolean                |           |          | 
 alcohol                 | boolean                |           |          | 
 workzone                | boolean                |           |          | 
 state                   | text                   |           |          | 
 vehicletype             | text                   |           |          | 
 year                    | smallint               |           |          | 
 make                    | text                   |           |          | 
 model                   | text                   |           |          | 
 color                   | text                   |           |          | 
 violation_type          | text                   |           |          | 
 charge                  | text                   |           |          | 
 article                 | text                   |           |          | 
 contributed_to_accident | boolean                |           |          | 
 race                    | text                   |           |          | 
 gender                  | text                   |           |          | 
 driver_city             | text                   |           |          | 
 driver_state            | text                   |           |          | 
 dl_state                | text                   |           |          | 
 arrest_type             | text                   |           |          | 
 geolocation             | point                  |           |          | 
 council_districts       | smallint               |           |          | 
 councils                | smallint               |           |          | 
 communities             | smallint               |           |          | 
 zip_codes               | smallint               |           |          | 
 municipalities          | smallint               |           |          | 

For now this table has no relational at all to the partitioned table, it just has the very same structure, that’s it. Lets populate the new table with some data for 2021:

insert into traffic_violations_p_2021 (date_of_stop)
       select * from generate_series ( date('01.01.2021')
                                     , date('12.31.2021')
                                     , interval '1 day');

Now that we have 365 rows in the new table we can attach it to the partitioned table (of course you could already attach it before loading the data as well):

alter table traffic_violations_p
      attach partition traffic_violations_p_2021
      for values from ('2021-01-01') to ('2022-01-01');

So you can prepare the whole new data without touching the partitioned table. Once you are happy with it a simple attach command is enough and the partitioned table contains the new partition holding the data:

postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),
            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            traffic_violations_p_default DEFAULT

Having the data for 2021 it might not be required anymore to have the data for 2012. Without partitioning you would need to delete from the table for getting rid of those rows. With partitioning you can detach the partition from the partitioned table:

postgres=# alter table traffic_violations_p detach partition traffic_violations_p_2012;
ALTER TABLE
postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            traffic_violations_p_default DEFAULT

Whatever you want to do with the data from now on: The old partition became a regular table and you can either drop, truncate or move it to low cost storage for archiving.
This was attaching and detaching of partitions. In the next post we’ll look at indexing and constraints when it comes to partitioned tables.

Cet article PostgreSQL partitioning (6): Attaching and detaching partitions est apparu en premier sur Blog dbi services.

Local Install rlwrap for OEL 7.6

Michael Dinh - Wed, 2019-06-05 19:13

Installing rlwrap 7.6, requires python34 local install

yum install rlwrap

[root@SLC02PNY ~]# yum install rlwrap
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package rlwrap.x86_64 0:0.43-2.el7 will be installed
--> Processing Dependency: perl(Data::Dumper) for package: rlwrap-0.43-2.el7.x86_64
--> Processing Dependency: /usr/bin/python3.4 for package: rlwrap-0.43-2.el7.x86_64

****************************************************************************************************
Package python34 is obsoleted by python36, but obsoleting package does not provide for requirements
****************************************************************************************************

--> Running transaction check
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
---> Package rlwrap.x86_64 0:0.43-2.el7 will be installed
--> Processing Dependency: /usr/bin/python3.4 for package: rlwrap-0.43-2.el7.x86_64
Package python34 is obsoleted by python36, but obsoleting package does not provide for requirements
--> Processing Dependency: /usr/bin/python3.4 for package: rlwrap-0.43-2.el7.x86_64
Package python34 is obsoleted by python36, but obsoleting package does not provide for requirements
--> Finished Dependency Resolution

yum install python34

root@SLC02PNY ~]# yum install python34
Loaded plugins: ulninfo

****************************************************************************************************
Package python34 is obsoleted by python36, trying to install python36-3.6.8-1.el7.x86_64 instead
****************************************************************************************************

Resolving Dependencies
--> Running transaction check
---> Package python36.x86_64 0:3.6.8-1.el7 will be installed
--> Processing Dependency: python36-libs(x86-64) = 3.6.8-1.el7 for package: python36-3.6.8-1.el7.x86_64
--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: python36-3.6.8-1.el7.x86_64
--> Running transaction check
---> Package python36-libs.x86_64 0:3.6.8-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                         Arch                     Version                        Repository                            Size
====================================================================================================================================
Installing:
 python36                        x86_64                   3.6.8-1.el7                    ol7_developer_EPEL                    66 k
Installing for dependencies:
 python36-libs                   x86_64                   3.6.8-1.el7                    ol7_developer_EPEL                   8.6 M

Transaction Summary
====================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 8.6 M
Installed size: 36 M
Is this ok [y/d/N]: n

cat /etc/system-release

[root@ADC6160274 ~]# cat /etc/system-release
Oracle Linux Server release 7.6
[root@ADC6160274 ~]#

yumdownloader python34-3.4.5-4.el7.x86_64

[root@ADC6160274 ~]# yumdownloader python34-3.4.5-4.el7.x86_64
python34-3.4.5-4.el7.x86_64.rpm                                                                              |  50 kB  00:00:00

yumdownloader python34-libs-3.4.5-4.el7.x86_64
[root@ADC6160274 ~]# yumdownloader python34-libs-3.4.5-4.el7.x86_64
python34-libs-3.4.5-4.el7.x86_64.rpm                                                                         | 8.2 MB  00:00:01

yum localinstall python34-libs-3.4.5-4.el7.x86_64.rpm python34-3.4.5-4.el7.x86_64.rpm

[root@ADC6160274 ~]# yum localinstall python34-libs-3.4.5-4.el7.x86_64.rpm python34-3.4.5-4.el7.x86_64.rpm
Loaded plugins: ulninfo
Examining python34-libs-3.4.5-4.el7.x86_64.rpm: python34-libs-3.4.5-4.el7.x86_64
Marking python34-libs-3.4.5-4.el7.x86_64.rpm to be installed
Examining python34-3.4.5-4.el7.x86_64.rpm: python34-3.4.5-4.el7.x86_64
Marking python34-3.4.5-4.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package python34.x86_64 0:3.4.5-4.el7 will be installed
---> Package python34-libs.x86_64 0:3.4.5-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                     Arch                 Version                     Repository                                       Size
====================================================================================================================================
Installing:
 python34                    x86_64               3.4.5-4.el7                 /python34-3.4.5-4.el7.x86_64                     36 k
 python34-libs               x86_64               3.4.5-4.el7                 /python34-libs-3.4.5-4.el7.x86_64                29 M

Transaction Summary
====================================================================================================================================
Install  2 Packages

Total size: 29 M
Installed size: 29 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python34-libs-3.4.5-4.el7.x86_64                                                                                 1/2
  Installing : python34-3.4.5-4.el7.x86_64                                                                                      2/2
  Verifying  : python34-3.4.5-4.el7.x86_64                                                                                      1/2
  Verifying  : python34-libs-3.4.5-4.el7.x86_64                                                                                 2/2

Installed:
  python34.x86_64 0:3.4.5-4.el7                                  python34-libs.x86_64 0:3.4.5-4.el7

Complete!

yum install rlwrap

[root@ADC6160274 ~]# yum install rlwrap
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package rlwrap.x86_64 0:0.43-2.el7 will be installed
--> Processing Dependency: perl(Data::Dumper) for package: rlwrap-0.43-2.el7.x86_64
--> Running transaction check
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                           Arch                    Version                        Repository                           Size
====================================================================================================================================
Installing:
 rlwrap                            x86_64                  0.43-2.el7                     ol7_developer_EPEL                  118 k
Installing for dependencies:
 perl-Data-Dumper                  x86_64                  2.145-3.el7                    ol7_latest                           47 k

Transaction Summary
====================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 165 k
Installed size: 378 k
Is this ok [y/d/N]: y
Downloading packages:
(1/2): perl-Data-Dumper-2.145-3.el7.x86_64.rpm                                                               |  47 kB  00:00:00
(2/2): rlwrap-0.43-2.el7.x86_64.rpm                                                                          | 118 kB  00:00:00
------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                               311 kB/s | 165 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Data-Dumper-2.145-3.el7.x86_64                                                                              1/2
  Installing : rlwrap-0.43-2.el7.x86_64                                                                                         2/2
  Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                                                                              1/2
  Verifying  : rlwrap-0.43-2.el7.x86_64                                                                                         2/2

Installed:
  rlwrap.x86_64 0:0.43-2.el7

Dependency Installed:
  perl-Data-Dumper.x86_64 0:2.145-3.el7

Complete!
[root@ADC6160274 ~]#

Simplifying Troubleshooting Process for Oracle Service Cloud Admins and Agents (BUI Version)

OTN TechBlog - Wed, 2019-06-05 11:39
What is Troubleshoot Extension?

If you are an Oracle Service Cloud administrator, you are likely the first person in your company your agents will go to when they are experiencing some error with their Oracle Service Cloud. Troubleshooting errors can quickly spawn into a bigger investigative effort to identify the root cause (e.g., network issue, configuration issue, training, defect), but the troubleshooting process usually starts with getting the right details about the error from your agents. Your agents are already frustrated because an error could be impeding their work and impacting their metrics. And now you’re asking them to provide detailed information on the error: steps to reproduce this issue, workstation information or data traffic, much of which they aren’t familiar with.

Recognizing the dynamics of this common scenario, we decided to walk a mile in both your shoes, as the administrator, and your agents’ shoes. As a result of this experience, we came up with the idea of automating the process for gathering the needed information from agents instead of requiring the administrators and agents to try and overcome the current challenges. We developed a sample code named as “Troubleshoot Extension” for the Oracle Service Cloud Browser UI (Similar what we did before in Agent Desktop) to address this need.

The “Troubleshoot Extension Sample Code” was created to automatically capture information, such as browser navigator, agent basic information and console log, error, warn and debug information in one fell swoop instead of requiring agents to install or use different tools outside of Oracle Service Cloud. All the agents need to do is push the "start" button (located in the status bar) and push the “stop” when the agents have completed all steps to reproduce the error.

The sample code is available to download in the bottom of the article, but before going further, I'd like to encourage you to understand the importance of web console to troubleshoot a web-based application so that the approach used here makes more sense.

Why we are sharing this sample code?
  • First, because we believe that by automating this process, we can simplify your communication with agents experiencing errors, accelerate the potential solution for the process.
  • Second, Agents don't need to understand how to gather technical information for troubleshooting.
  • Lastly, because through this sample code, we can achieve:
    • sharing a complex sample code where you can apply and reuse for other needs;
    • sharing a start point where you can enhance this troubleshooting tool to adapt to your requirement.
How does Troubleshoot Extension work?

Let’s take a more in-depth look at what the sample code delivers. The sample code is implementing an Extension StatusBar with a start and stop button, plus a timer that provides the duration of how long your agent is capturing the steps to reproduce.

By clicking on the start button, the troubleshoot extension will automatically set the log level (see developer tools logs for more information). Everything that is registered in the web console will be captured.

Once you have finished capturing your steps to reproduce click on the stop button and the Troubleshoot Extension takes care of compile all web console information to present to you in a window modal.

**This extension is not compatible with IE11. 

The window modal presents a friendly version of the result captured, but you can save the result by clicking on the download button on top. The download button will create a text file with the information that was captured in and can share with your technical team to help them on the investigation.

The information capture is read-only, but there is an additional field on top where your agent can add more information such as steps to reproduce or any other comment.

How to install?

1. Upload the extension code as a BUI extension.

  1. Download the extension file.
  2. Open your Admin console and upload the zip file as Agent Browser UI Extensions.
  3. Name your extension. E.g.: Troubleshoot Extension. 
  4. Select "Console" as an extension type.
  5. Select  TsExnteion/ViewModel/js/ts-statusbar.js as the Init File.
  6. Go to profile and assign this extension to the profile you'd like to use this extension.

2. Create custom configuration settings.

  1. Go to Site Configuration > Configuration Settings.
  2. Click on New > Text to create a custom configuration setting.
  3. The Key name is "CUSTOM_CFG_TS".
  4. The site value is the following JSON: {"debugLevel":3, "performance": true}

Ultimately, this solution should simplify your communication with agents experiencing errors, accelerate troubleshooting by having the required information in one easy step, and save everyone time and frustration that surrounds these issues.

The source code is available here for download, and you take advantage to build a better troubleshoot model integrated into your Oracle Service Cloud. 

If you are a developer and want to contribute to this sample code, you are welcome to join the Github.

Simplifying Troubleshooting Process for Oracle Service Cloud Admins and Agents (.NET Add-In Version)

OTN TechBlog - Wed, 2019-06-05 11:31

If you are an Oracle Service Cloud administrator, you are likely the first person in your company your agents will go to when they are experiencing some error with their agent desktop. Troubleshooting errors can quickly spawn into a bigger investigative effort to identify the root cause (e.g., network issue, configuration issue, training, defect), but the troubleshooting process usually starts with getting the right details about the error from your agents. Your agents are already frustrated because an error could be impeding their work and impacting their metrics. And now you’re asking them to provide detailed information on the error: steps to reproduce this issue, workstation information or data traffic, much of which they aren’t familiar with.

Recognizing the dynamics of this common scenario, we decided to walk a mile in both your shoes, as the administrator, and your agents’ shoes. As a result of this experience, we came up with the idea of automating the process for gathering the needed information from agents instead of requiring the administrators and agents to try and overcome the current challenges. We developed a sample code named as “Troubleshoot Add-In” for the Oracle Service Cloud Agent Desktop (For the Browser UI version) to address this need.

The “Troubleshoot Add-In Sample Code” was created to automatically capture information, such as steps-to-reproduce, and workstation information in one fell swoop instead of requiring agents to install or use different tools outside of Oracle Service Cloud. All that agents need to do is push the "start" button (located in the status bar) and push the “stop” when the agents have completed all steps to reproduce the error.

Take a look at these two scenarios and see how the “Troubleshoot Add-In” can improve communication between administrators and agents who are encountering an issue.

Let’s take a more in-depth look at what the sample code delivers. The sample code is implementing an Extension StatusBar with a start and stop button, plus a timer that provides the duration of how long your agent is capturing the steps to reproduce.

How Troubleshoot Add-In Sample Code Works

By clicking on the start button, a friendly loading form shows up. You can personalize your message by changing a ServerConfigProperty used in this sample code. At this moment, the sample code will start a standard windows application called PSR (Problem Steps to Reproduce). As a sample code, this code is limited to use a windows standard application and from here, I would encourage you as a developer to expand this sample code for your needs.

For instance, If you want to get rid of Fiddler installation needs, you can use Fiddler Core and embed their .dll to capture data traffic plus steps to reproduce. Check out for Fiddler Demo Code and try yourself, I am pretty sure it will be useful.

Once you have finished capturing your steps to reproduce click on the stop button and the sample code closes PSR and starts to capture workstation information such as .NET version, windows version, capacity. Also, you can run the OSCinfo.bat as described in the answer 2412. This sample code is providing this option if you need to capture more information as ping and traces. See for the ServerConfigProperty options.

Lastly, a message pops up to inform the agent where the results were saved. The sample code takes care of compile and compresses all files resulted from PSR and Workstation Information in a local folder or wherever folder you have specified in a ServerConfigProperty.

Okay, this is true, I like to use ServerConfigProperty and there is more fo them. With that, you can set up your add-in without changing your code. 

Ultimately, this solution should simplify your communication with agents experiencing errors, accelerate troubleshooting by having the required information in one easy step, and save everyone time and frustration that surrounds these issues.

The source code is available here for download, and you take advantage to build a better troubleshoot model integrated into your Oracle Service Cloud. 

If you are a developer and want to contribute to this sample code, you are welcome to join the Github.

Microsoft and Oracle to Interconnect Microsoft Azure and Oracle Cloud

Oracle Press Releases - Wed, 2019-06-05 07:00
Press Release
Microsoft and Oracle to Interconnect Microsoft Azure and Oracle Cloud

REDMOND, Wash., and REDWOOD SHORES, Calif.—Jun 5, 2019

Microsoft Corp. and Oracle Corp. on Wednesday announced a cloud interoperability partnership enabling customers to migrate and run mission-critical enterprise workloads across Microsoft Azure and Oracle Cloud. Enterprises can now seamlessly connect Azure services, like Analytics and AI, to Oracle Cloud services, like Autonomous Database. By enabling customers to run one part of a workload within Azure and another part of the same workload within the Oracle Cloud, the partnership delivers a highly optimized, best-of-both-clouds experience. Taken together, Azure and Oracle Cloud offer customers a one-stop shop for all the cloud services and applications they need to run their entire business.

Connecting Azure and Oracle Cloud through network and identity interoperability makes lift-and-improve migrations seamless. This partnership delivers direct, fast and highly reliable network connectivity between two clouds, while continuing to provide first-class customer service and support that enterprises have come to expect from the two companies. In addition to providing interoperability for customers running Oracle software on Oracle Cloud and Microsoft software on Azure, it enables new and innovative scenarios like running Oracle E-Business Suite or Oracle JD Edwards on Azure against an Oracle Autonomous Database running on Exadata infrastructure in the Oracle Cloud.

“As the cloud of choice for the enterprise, with over 95% of the Fortune 500 using Azure, we have always been first and foremost focused on helping our customers thrive on their digital transformation journeys,” said Scott Guthrie, executive vice president of Microsoft’s Cloud and AI division. “With Oracle’s enterprise expertise, this alliance is a natural choice for us as we help our joint customers accelerate the migration of enterprise applications and databases to the public cloud.”

“The Oracle Cloud offers a complete suite of integrated applications for sales, service, marketing, human resources, finance, supply chain and manufacturing, plus highly automated and secure Generation 2 infrastructure featuring the Oracle Autonomous Database,” said Don Johnson, executive vice president, Oracle Cloud Infrastructure (OCI). “Oracle and Microsoft have served enterprise customer needs for decades. With this partnership, our joint customers can migrate their entire set of existing applications to the cloud without having to re-architect anything, preserving the large investments they have already made.”

As a result of this expanded partnership, the companies are today making available a new set of capabilities:

  • Connect Azure and Oracle Cloud seamlessly, allowing customers to extend their on-premises datacenters to both clouds. This direct interconnect is available starting today in Ashburn (North America) and Azure US East, with plans to expand additional regions in the future.
  • Unified identity and access management, via a unified single sign-on experience and automated user provisioning, to manage resources across Azure and Oracle Cloud. Also available in early preview today, Oracle applications can use Azure Active Directory as the identity provider and for conditional access.
  • Supported deployment of custom applications and packaged Oracle applications (JD Edwards EnterpriseOne, E-Business Suite, PeopleSoft, Oracle Retail, Hyperion) on Azure with Oracle databases (RAC, Exadata, Autonomous Database) deployed in Oracle Cloud. The same Oracle applications will also be certified to run on Azure with Oracle databases in Oracle Cloud.
  • A collaborative support model to help IT organizations deploy these new capabilities while enabling them to leverage existing customer support relationships and processes.
  • Oracle Database will continue to be certified to run in Azure on various operating systems, including Windows Server and Oracle Linux.
 

“The alliance between Microsoft and Oracle is welcome news as we accelerate Albertsons’ digital transformation and leverage the full value of the public cloud,” said Anuj Dhanda, executive vice president and chief information officer at Albertsons Companies. “This will allow us to create cross-cloud solutions that optimize many of our current investments while maximizing the agility, scalability and efficiency of the public cloud.”

“As we look to bring our omnichannel experience closer together and transform the technology platform that powers the Gap Inc. brands, the collaboration between Oracle and Microsoft will make it easier for us to scale and deliver capabilities across channels,” said Sally Gilligan, chief information officer at Gap. “The interoperability between Azure and Oracle Cloud allows us to deploy Oracle or custom-built applications on Azure and Oracle databases on Oracle Cloud.”

“At Halliburton, we have a long history of running both Oracle and Microsoft technologies for our most critical applications. Our deep experience with these two strategic vendors has yielded consistently stable and performant application deployments,” said Ken Braud, senior vice president and CIO at Halliburton. “This alliance gives us the flexibility and ongoing support to continue leveraging our standard architectures, while allowing us to focus on generating business outcomes that maximize returns for our shareholders.”

Those wanting to learn more should visit https://aka.ms/OracleOnAzure or https://www.oracle.com/cloud/oci-azure.html.

Contact Info
Celina Bertallee
Oracle
(650) 506-6542
celina.bertallee@oracle.com
Microsoft Media Relations
WE Communications for Microsoft
(425) 638-7777
rrt@we-worldwide.com
About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

About Microsoft

Microsoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.

Future Product Disclaimer

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

Trademarks

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

Note to editors: For more information, news and perspectives from Microsoft, please visit the Microsoft News Center at http://news.microsoft.com. Web links, telephone numbers and titles were correct at time of publication, but may have changed. For additional assistance, journalists and analysts may contact Microsoft’s Rapid Response Team or other appropriate contacts listed at http://news.microsoft.com/microsoft-public-relations-contacts.

Talk to a Press Contact

Celina Bertallee

  • (650) 506-6542

Microsoft Media Relations

  • (425) 638-7777

PostgreSQL partitioning (5): Partition pruning

Yann Neuhaus - Wed, 2019-06-05 01:00

This is the next post in the PostgreSQL partitioning series. If you missed the previous ones here they are:

  1. PostgreSQL partitioning (1): Preparing the data set
  2. PostgreSQL partitioning (2): Range partitioning
  3. PostgreSQL partitioning (3): List partitioning
  4. PostgreSQL partitioning (4): Hash partitioning

This time we will have a look at partition pruning. Never heard of that? Don’t worry, hopefully it will be clear at the end of this blog post.

When you ask Wikipedia for pruning you get this: “Pruning is a horticultural and silvicultural practice involving the selective removal of certain parts of a plant, such as branches, buds, or roots. Reasons to prune plants include deadwood removal, shaping (by controlling or redirecting growth), improving or sustaining health, reducing risk from falling branches, preparing nursery specimens for transplanting, and both harvesting and increasing the yield or quality of flowers and fruits.”

Although this is about plants it almost exactly describes partition pruning as well: “selective removal of certain parts of a plant”. In our case it is removal of partitions when it is known that the partition(s) can not contain data we are asking for.

Lets come back to our range partitioned table. We partitioned the table by the “time_of_stop” column:

postgres=# select * from pg_partition_tree('traffic_violations_p');
            relid             |     parentrelid      | isleaf | level 
------------------------------+----------------------+--------+-------
 traffic_violations_p         |                      | f      |     0
 traffic_violations_p_default | traffic_violations_p | t      |     1
 traffic_violations_p_2012    | traffic_violations_p | t      |     1
 traffic_violations_p_2013    | traffic_violations_p | t      |     1
 traffic_violations_p_2014    | traffic_violations_p | t      |     1
 traffic_violations_p_2015    | traffic_violations_p | t      |     1
 traffic_violations_p_2016    | traffic_violations_p | t      |     1
 traffic_violations_p_2017    | traffic_violations_p | t      |     1
 traffic_violations_p_2018    | traffic_violations_p | t      |     1
 traffic_violations_p_2019    | traffic_violations_p | t      |     1
 traffic_violations_p_2020    | traffic_violations_p | t      |     1

Each partition contains data from one year. If we ask for data from 2013 PostgreSQL should only read that partition and just ignore the others.

postgres=# explain select count(*) from traffic_violations_p where date_of_stop = date('02-FEB-2013');
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11393.96..11393.97 rows=1 width=8)
   ->  Gather  (cost=11393.75..11393.96 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10393.75..10393.76 rows=1 width=8)
               ->  Parallel Seq Scan on traffic_violations_p_2013  (cost=0.00..10393.29 rows=185 width=0)
                     Filter: (date_of_stop = '2013-02-02'::date)
(6 rows)

Indeed that is happening and only the traffic_violations_p_2013 is considered. All other partitions will just be ignored and that of course is a performance improvement. This is the simple case and it is partition pruning at planning time. Because we have a literal in the where clause PostgreSQL can already decide at planning time which partitions it needs to read and which can be skipped.

Consider this example:

select count(*) 
  from traffic_violations_p 
 where date_of_stop = select date('01-FEB-2013');

In this case PostgreSQL can not know what will be the actual values coming back from the sub-select at planning time. If you are on PostgreSQL 10 there is not much you can do as partition pruning at execution time made it into PostgreSQL 11. As I am on PostgreSQL 12 Beta 1 it should work:

postgres=# postgres=# explain         
select count(*) 
  from traffic_violations_p 
 where date_of_stop = ( select to_date('01.01.2014','DD.MM.YYYY'));
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=85047.50..85047.51 rows=1 width=8)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4)
   ->  Gather  (cost=85047.28..85047.49 rows=2 width=8)
         Workers Planned: 2
         Params Evaluated: $0
         ->  Partial Aggregate  (cost=84047.28..84047.29 rows=1 width=8)
               ->  Parallel Append  (cost=0.00..84042.52 rows=1901 width=0)
                     ->  Parallel Seq Scan on traffic_violations_p_2015  (cost=0.00..12924.89 rows=269 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2014  (cost=0.00..12235.54 rows=255 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2016  (cost=0.00..12097.57 rows=252 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2018  (cost=0.00..12051.87 rows=249 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2017  (cost=0.00..10996.34 rows=228 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2013  (cost=0.00..10393.29 rows=218 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2012  (cost=0.00..8351.41 rows=172 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2019  (cost=0.00..4959.83 rows=246 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_default  (cost=0.00..11.54 rows=15 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2020  (cost=0.00..10.74 rows=1 width=0)
                           Filter: (date_of_stop = $0)
(28 rows)

The execution plan shows that all partitions will be scanned and that is no surprise. When you want to see partition pruning at execution time you actually have to execute the statement, so explain(analyze):

postgres=# explain(analyze)
select count(*) 
  from traffic_violations_p 
 where date_of_stop = ( select to_date('01.01.2014','DD.MM.YYYY'));
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=85047.50..85047.51 rows=1 width=8) (actual time=149.747..149.747 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)
   ->  Gather  (cost=85047.28..85047.49 rows=2 width=8) (actual time=145.730..150.004 rows=3 loops=1)
         Workers Planned: 2
         Params Evaluated: $0
         Workers Launched: 2
         ->  Partial Aggregate  (cost=84047.28..84047.29 rows=1 width=8) (actual time=119.148..119.149 rows=1 loops=3)
               ->  Parallel Append  (cost=0.00..84042.52 rows=1901 width=0) (actual time=119.052..119.127 rows=189 loops=3)
                     ->  Parallel Seq Scan on traffic_violations_p_2015  (cost=0.00..12924.89 rows=269 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2014  (cost=0.00..12235.54 rows=255 width=0) (actual time=119.024..119.077 rows=189 loops=3)
                           Filter: (date_of_stop = $0)
                           Rows Removed by Filter: 74405
                     ->  Parallel Seq Scan on traffic_violations_p_2016  (cost=0.00..12097.57 rows=252 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2018  (cost=0.00..12051.87 rows=249 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2017  (cost=0.00..10996.34 rows=228 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2013  (cost=0.00..10393.29 rows=218 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2012  (cost=0.00..8351.41 rows=172 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2019  (cost=0.00..4959.83 rows=246 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_default  (cost=0.00..11.54 rows=15 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2020  (cost=0.00..10.74 rows=1 width=0) (never executed)
                           Filter: (date_of_stop = $0)
 Planning Time: 0.604 ms
 Execution Time: 150.120 ms
(32 rows)

The keywords here are “(never executed)” and this is partition pruning at execution time. There are some limitations with this but this will be covered in the last post of this series when we will look at corner cases when it comes to partitioning in PostgreSQL.

The next post will cover attaching and detaching of partitions.

Cet article PostgreSQL partitioning (5): Partition pruning est apparu en premier sur Blog dbi services.

Geoffrey Moore to Keynote Oracle Retail Cross Talk 2019

Oracle Press Releases - Tue, 2019-06-04 12:46
Press Release
Geoffrey Moore to Keynote Oracle Retail Cross Talk 2019 Over 200 global retailers will gather to discuss the state of retail and how brands are using technology to innovate and ignite customer success

Redwood Shores, Calif.—Jun 4, 2019

Oracle Retail announced today that Geoffrey Moore, Bestselling Author, Consultant and Disruptive Technology Specialist, will deliver the keynote address at Oracle Retail Cross Talk. The event brings together a global community of over 200 retail executives to share deep domain expertise and insights on technology that is core to their retail businesses. Cross Talk provides a unique and authentic networking forum where over 35 global retailers including Bata, Best Buy, the Estée Lauder companies, Kendra Scott, and more will share their success stories and lessons learned. The event will take place June 10-12, 2019 at the JW Marriott in Minneapolis, MN. 

“‘How can retailers succeed and thrive in a fast-paced and continually evolving business environment?’ Cross Talk focuses on collectively addressing this very question and enabling retailers to hear directly from their peers about how they are rising above today’s most pressing industry challenges to deliver results and delight customers,” said Mike Webster, senior vice president and general manager, Oracle Retail.

Attendees will gain insight from talented retail executives representing the world’s leading brands including:

  • Bata will discuss its digital transformation and how it is unlocking the business potential of omnichannel
  • Best Buy will share critical insights into how it is optimizing supply chain strategies
  • Estée Lauder will highlight its approach to delivering a seamless shopping experience
  • International Shoppes will demonstrate how it is modernizing the customer experience
  • Gap, Inc. will talk about its latest brand to go live with Oracle Retail cloud offerings
  • James Avery will illustrate how it is upgrading the customer experience
  • Kendra Scott will discuss how it built a platform for growth with cloud 
  • Maui and Sons will share key learning as it rides the wave to operational agility
  • Stein Mart will talk about its migration of planning to the cloud

Oracle PartnerNetwork members and strategic solution integrators are critical in helping deliver customer success. A number of key collaborators are sponsoring the show, including Accenture and Deloitte as platinum sponsors; gold sponsors BTM Retail and Retail Consult; and Aspire, CAI, Ignitiv, Logic, Quickborn, and SkillNet as silver sponsors. Solution level sponsors, Adyen and OneDoor, will also showcase their integrations into Oracle’s platform for modern retail.

“Our community is thrilled for the return of Oracle Retail Cross Talk,” said Rose Spicer, senior director of retail marketing, Oracle Corporation. “The response has been amazing and we cannot wait to assemble some of the biggest brands in the industry with new faces to discuss the issues and opportunities that are impacting them most today. We have also added some compelling networking elements to connect the retailers to one another.”

For more information about how Oracle delivers a modern platform for retail organizations through best-in-class, industry-specific business solutions, visit www.oracle.com/retail.

To learn more about the Oracle Retail Cross Talk 2019, visit: https://www.oracle.com/industries/retail/cross-talk/.

Contact Info
Kris Reeves
Oracle PR
+1.925.787.6744
kris.reeves@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website www.oracle.com/retail.

About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at 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

Kris Reeves

  • +1.925.787.6744

ADF Faces and Client Side Value with innerHTML

Andrejus Baranovski - Tue, 2019-06-04 07:24
In ADF Faces you can leverage the full power of JavaScript. I will explain how you could assign a value from ADF Faces component to the plain HTML div.

The sample app is available on GitHub repo. It doesn't require DB connection, you can run it straight away in Oracle JDeveloper.

Look into JSF page. I have implemented ADF Faces input component with value change listener. Below this component, there is HTML div with ID ot1. We will assign a text value to this div programmatically from JS function passClientSideValue:


JavaScript function reads the value by client ID from ADF Faces component and assigns it to the innerHTML property of HTML div:


When ADF Faces value is changed, value change listener is invoked through ADF auto-submit event. In value change listener, we extract client ID of the input component and pass it to JS function through JavaScript call from Java:


This is how the end result looks like:


In particular, this approach can be useful, when you want to bypass ADF Faces validation lifecycle and display updated value despite current validation errors in the form.

Kscope 2019 APEX Track Sessions by Oracle ACE Program Members

OTN TechBlog - Tue, 2019-06-04 05:00
A Towering Achievement...

On June 23rd ODTUG Kscope 2019 kicks off in Seattle. Among the vast army of session presenters for the event you'll find more than 50 active members of the Oracle ACE Program, and they'll be presenting more than 100 sessions across all the various tracks. This post is the first of a series that will highlight those ACE sessions. This post focuses on sessions on the APEX track.

For the details on each session, including time, location, and session descriptions, just click the title link.

Oracle ACE Directors

Oracle ACE Director Dimitri GielisDimitri Gielis
Director, APEX R&D
Leuven, Belgium

 

Oracle ACE Director Francis MignaultFrancis Mignault
CTO/VP Technologies, Insum Solutions
Montreal, Canada

 

Oracle ACE Director John ScottJohn Scott
Founder/Director, APEX Evangelists
Leeds, United Kingdom

 

Oracle ACE Director Martin Giffy D'SouzaMartin Giffy D’Souza
Director of Innovation, Insum Solutions
Calgary, Alberta, Canada

 

Oracle ACE Director Niels de BruijnNiels de Bruijn
Business Unit Manager, MT AG
Cologne, Germany

 

Oracle ACE Director Peter RaganitschPeter Raganitsch
CEO, FOEX GmbH
Austria

 

Oracle ACE Director Roel HartmanRoel Hartman
Director/Senior APEX Developer, APEX Consulting
Apeldoorn, Netherlands

 

Oracle ACE Director Scott SpendoliniScott Spendolini
Vice President, Viscosity North America
Austin, Texas

 
Oracle ACEs

Oracle ACE Alan ArentsenAlan Arentsen
Senior Oracle Developer, Arentsen Database Consultancy
Breda, Netherlands

 

Oracle ACE Christian RokittaChristian Rokitta
Managing Partner, iAdvise
Breda Area, Netherlands

 

Oracle ACE Jorge RimblasJorge Rimblas
Senior APEX Consultant, Insum
Minneapolis-St. Paul, Minnesota

 

Oracle ACE Scott WesleyScott Wesley
Systems Consultant/Trainer, SAGE Computing Services
Perth, Australia

 

Oracle ACE Vincent MorneauVincent Morneau
Front-End Lead, Insum
Montreal, Canada

 

Oracle ACE Niall Mc PhillipsNiall Mc Phillips
Owner/CEO, Long Acre sàrl

 
Oracle ACE Associates

Oracle ACE Associate Kai DonatoKai Donato
Senior Consultant, MT AG
Cologne, Germany

 

Oracle ACE Associate Lino SchildenfeldLino Schildenfeld
NZ/AU Director, APEX R&D
Australia/New Zealand

 

Oracle ACE Associate Moritz KleinMoritz Klein
Principal APEX Consultant, MT AG
Frankfurt, Germany

 

Oracle ACE Associate Adrian PngAdrian Png
Senior Consultant/Database Administrator, Insum
Canada

 
Additional Resources

PostgreSQL partitioning (4): Hash partitioning

Yann Neuhaus - Tue, 2019-06-04 01:00

The last partitioning strategy we will look at is: hash partitioning. If you missed the first posts in this series here they are:

  1. PostgreSQL partitioning (1): Preparing the data set
  2. PostgreSQL partitioning (2): Range partitioning
  3. PostgreSQL partitioning (3): List partitioning

Usually hash partitioning is used when you do not have a natural way of partitioning your data or you want to evenly distribute the data based on hash.

In PostgreSQL hash partitioning might seem a bit strange in the beginning because you define a modulus and a remainder and that defines where the data is going to. Having a look at our materialized view which contains our data set which columns might be a good candidate for that?

postgres=# \d mv_traffic_violations 
                 Materialized view "public.mv_traffic_violations"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 seqid                   | text                   |           |          | 
 date_of_stop            | date                   |           |          | 
 time_of_stop            | time without time zone |           |          | 
 agency                  | text                   |           |          | 
 subagency               | text                   |           |          | 
 description             | text                   |           |          | 
 location                | text                   |           |          | 
 latitude                | numeric                |           |          | 
 longitude               | numeric                |           |          | 
 accident                | text                   |           |          | 
 belts                   | boolean                |           |          | 
 personal_injury         | boolean                |           |          | 
 property_damage         | boolean                |           |          | 
 fatal                   | boolean                |           |          | 
 commercial_license      | boolean                |           |          | 
 hazmat                  | boolean                |           |          | 
 commercial_vehicle      | boolean                |           |          | 
 alcohol                 | boolean                |           |          | 
 workzone                | boolean                |           |          | 
 state                   | text                   |           |          | 
 vehicletype             | text                   |           |          | 
 year                    | smallint               |           |          | 
 make                    | text                   |           |          | 
 model                   | text                   |           |          | 
 color                   | text                   |           |          | 
 violation_type          | text                   |           |          | 
 charge                  | text                   |           |          | 
 article                 | text                   |           |          | 
 contributed_to_accident | boolean                |           |          | 
 race                    | text                   |           |          | 
 gender                  | text                   |           |          | 
 driver_city             | text                   |           |          | 
 driver_state            | text                   |           |          | 
 dl_state                | text                   |           |          | 
 arrest_type             | text                   |           |          | 
 geolocation             | point                  |           |          | 
 council_districts       | smallint               |           |          | 
 councils                | smallint               |           |          | 
 communities             | smallint               |           |          | 
 zip_codes               | smallint               |           |          | 
 municipalities          | smallint               |           |          | 

A good candidate would be the councils column. When we setup the hash partitioning correctly the result would be 5 partitions:

postgres=# select distinct councils from mv_traffic_violations ;
 councils 
----------
         
        1
        3
        5
        2
        4
(6 rows)

The partitioned table becomes:

create table traffic_violations_p_hash
( seqid text
, date_of_stop date  
, time_of_stop time
, agency text
, subagency text
, description text 
, location text
, latitude numeric
, longitude numeric
, accident text
, belts boolean
, personal_injury boolean 
, property_damage boolean
, fatal boolean
, commercial_license boolean
, hazmat boolean
, commercial_vehicle boolean
, alcohol boolean
, workzone boolean
, state text
, vehicletype text 
, year smallint
, make text
, model text
, color text
, violation_type text 
, charge text
, article text
, contributed_to_accident boolean
, race text
, gender text
, driver_city text
, driver_state text
, dl_state text
, arrest_type text
, geolocation point
, council_districts smallint 
, councils smallint
, communities smallint
, zip_codes smallint
, municipalities smallint
)
partition by hash (councils);

As usual psql will report the partitioning strategy for the table as well:

postgres=# \d traffic_violations_p_hash 
               Partitioned table "public.traffic_violations_p_hash"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 seqid                   | text                   |           |          | 
 date_of_stop            | date                   |           |          | 
 time_of_stop            | time without time zone |           |          | 
 agency                  | text                   |           |          | 
 subagency               | text                   |           |          | 
 description             | text                   |           |          | 
 location                | text                   |           |          | 
 latitude                | numeric                |           |          | 
 longitude               | numeric                |           |          | 
 accident                | text                   |           |          | 
 belts                   | boolean                |           |          | 
 personal_injury         | boolean                |           |          | 
 property_damage         | boolean                |           |          | 
 fatal                   | boolean                |           |          | 
 commercial_license      | boolean                |           |          | 
 hazmat                  | boolean                |           |          | 
 commercial_vehicle      | boolean                |           |          | 
 alcohol                 | boolean                |           |          | 
 workzone                | boolean                |           |          | 
 state                   | text                   |           |          | 
 vehicletype             | text                   |           |          | 
 year                    | smallint               |           |          | 
 make                    | text                   |           |          | 
 model                   | text                   |           |          | 
 color                   | text                   |           |          | 
 violation_type          | text                   |           |          | 
 charge                  | text                   |           |          | 
 article                 | text                   |           |          | 
 contributed_to_accident | boolean                |           |          | 
 race                    | text                   |           |          | 
 gender                  | text                   |           |          | 
 driver_city             | text                   |           |          | 
 driver_state            | text                   |           |          | 
 dl_state                | text                   |           |          | 
 arrest_type             | text                   |           |          | 
 geolocation             | point                  |           |          | 
 council_districts       | smallint               |           |          | 
 councils                | smallint               |           |          | 
 communities             | smallint               |           |          | 
 zip_codes               | smallint               |           |          | 
 municipalities          | smallint               |           |          | 
Partition key: HASH (councils)
Number of partitions: 0

The setup for the hash partitions will be as follows:

create table traffic_violations_p_hash_p1
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 0);

create table traffic_violations_p_hash_p2
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 1);

create table traffic_violations_p_hash_p3
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 2);

create table traffic_violations_p_hash_p4
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 3);

create table traffic_violations_p_hash_p5
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 4);

Hash partitioning can not have a default partition as that would not make any sense because of the modulus and the remainder. When you try to do that you will get an error:

postgres=# create table traffic_violations_p_hash_default
postgres-# partition of traffic_violations_p_hash default;
psql: ERROR:  a hash-partitioned table may not have a default partition

The final setup of our hash partitioned table is:

postgres=# \d+ traffic_violations_p_hash
                                   Partitioned table "public.traffic_violations_p_hash"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: HASH (councils)
Partitions: traffic_violations_p_hash_p1 FOR VALUES WITH (modulus 5, remainder 0),
            traffic_violations_p_hash_p2 FOR VALUES WITH (modulus 5, remainder 1),
            traffic_violations_p_hash_p3 FOR VALUES WITH (modulus 5, remainder 2),
            traffic_violations_p_hash_p4 FOR VALUES WITH (modulus 5, remainder 3),
            traffic_violations_p_hash_p5 FOR VALUES WITH (modulus 5, remainder 4)

Loading the data and checking the partitions:

postgres=# insert into traffic_violations_p_hash
postgres-#        select * from mv_traffic_violations;
INSERT 0 1528078
postgres=# select count(*) from traffic_violations_p_hash_p1;
 count  
--------
 988085
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p2;
 count  
--------
 539993
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p3;
 count 
-------
     0
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p4;
 count 
-------
     0
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p5;
 count 
-------
     0
(1 row)

Why do we have only data in the first and second partition? Looking more closely at the distribution of the councils in the materialized view:

postgres=# select distinct councils, count(*) from mv_traffic_violations group by councils;
 councils | count  
----------+--------
        1 | 231070
        2 | 211759
        3 | 328234
        4 | 334142
        5 | 308402
          | 114471
(6 rows)

First of all we have a lot of rows which do not have a council. Null values in hash partitioned tables always go to the partition where the remainder is 0:

postgres=# truncate table traffic_violations_p_hash;
TRUNCATE TABLE
postgres=# insert into traffic_violations_p_hash (councils) select null from generate_series(1,100);
INSERT 0 100
postgres=# select count(*) from traffic_violations_p_hash_p1;
 count 
-------
   100
(1 row)

Here is the reason for this behavior: “The calculated hash value for the null value will be zero, therefore, it will fall to the partition having remainder zero.”.

This does still not explain why nothing is in the partitions 3 to 5. Lets do a few tests with our councils:

postgres=# truncate traffic_violations_p_hash;
TRUNCATE TABLE
postgres=# insert into traffic_violations_p_hash (councils) values (1),(2),(3),(4),(5);
INSERT 0 5
postgres=# select councils from traffic_violations_p_hash_p1;
 councils 
----------
        1
        4
        5
(3 rows)

postgres=# select councils from traffic_violations_p_hash_p2;
 councils 
----------
        2
        3
(2 rows)

This confirms the behavior. Councils 1,4 and 5 go to the first partition while 2 and 3 go to the second one. The decision to hash partition the on the councils column clearly was not a good idea. Hash paritioning is great when you have many different values:

postgres=# truncate traffic_violations_p_hash;
TRUNCATE TABLE
postgres=# insert into traffic_violations_p_hash (councils) select * from generate_series(1,10000);
INSERT 0 10000
postgres=# select count(*) from traffic_violations_p_hash_p1;
 count 
-------
  1969
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p2;
 count 
-------
  2034
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p3;
 count 
-------
  2058
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p4;
 count 
-------
  1928
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p5;
 count 
-------
  2011
(1 row)

This gives much better distribution of the data. A good candidate would be the seqid column:

drop table traffic_violations_p_hash, traffic_violations_p_hash_p1, traffic_violations_p_hash_p2, traffic_violations_p_hash_p3, traffic_violations_p_hash_p4, traffic_violations_p_hash_p5;
create table traffic_violations_p_hash
( seqid text
, date_of_stop date  
, time_of_stop time
, agency text
, subagency text
, description text 
, location text
, latitude numeric
, longitude numeric
, accident text
, belts boolean
, personal_injury boolean 
, property_damage boolean
, fatal boolean
, commercial_license boolean
, hazmat boolean
, commercial_vehicle boolean
, alcohol boolean
, workzone boolean
, state text
, vehicletype text 
, year smallint
, make text
, model text
, color text
, violation_type text 
, charge text
, article text
, contributed_to_accident boolean
, race text
, gender text
, driver_city text
, driver_state text
, dl_state text
, arrest_type text
, geolocation point
, council_districts smallint 
, councils smallint
, communities smallint
, zip_codes smallint
, municipalities smallint
)
partition by hash (seqid);

create table traffic_violations_p_hash_p1
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 0);

create table traffic_violations_p_hash_p2
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 1);

create table traffic_violations_p_hash_p3
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 2);

create table traffic_violations_p_hash_p4
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 3);

create table traffic_violations_p_hash_p5
partition of traffic_violations_p_hash
for values with (modulus 5, remainder 4);

Loading the data again and checking the partitions:

postgres=# insert into traffic_violations_p_hash
postgres-#        select * from mv_traffic_violations;
INSERT 0 1528078
postgres=# select count(*) from traffic_violations_p_hash_p1;
 count  
--------
 305253
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p2;
 count  
--------
 304999
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p3;
 count  
--------
 305215
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p4;
 count  
--------
 305719
(1 row)

postgres=# select count(*) from traffic_violations_p_hash_p5;
 count  
--------
 306892
(1 row)

That was hash partitioning. In the next post we’ll look at partition pruning.

Cet article PostgreSQL partitioning (4): Hash partitioning est apparu en premier sur Blog dbi services.

Troubleshooting By Tracing GoldenGate Sessions

VitalSoftTech - Mon, 2019-06-03 09:56
Find out how troubleshoot by tracing GoldenGate DB sessions or the GoldenGate process from GGSCI and the process parameter file.
Categories: DBA Blogs

Partner Webcast – Oracle CASB - Enabling Security Monitoring for Oracle Cloud Infrastructure

As customers adopt cloud-based infrastructure as part of their digital journey, protecting this infrastructure becomes a critical security imperative to ensure that applications that are built on top...

We share our skills to maximize your revenue!
Categories: DBA Blogs

PostgreSQL partitioning (3): List partitioning

Yann Neuhaus - Mon, 2019-06-03 09:12

In the last posts of this series we prepared the data set and had a look at range partitioning. In this post we look at another partitioning strategy: List partitioning. Instead of partitioning by a range (typically based on day, year, month) list partitioning is used to partition on an explicit list with key values that define the partitions.

Coming back to the materialized view which holds our initial data set:

postgres=# \d mv_traffic_violations 
                 Materialized view "public.mv_traffic_violations"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 seqid                   | text                   |           |          | 
 date_of_stop            | date                   |           |          | 
 time_of_stop            | time without time zone |           |          | 
 agency                  | text                   |           |          | 
 subagency               | text                   |           |          | 
 description             | text                   |           |          | 
 location                | text                   |           |          | 
 latitude                | numeric                |           |          | 
 longitude               | numeric                |           |          | 
 accident                | text                   |           |          | 
 belts                   | boolean                |           |          | 
 personal_injury         | boolean                |           |          | 
 property_damage         | boolean                |           |          | 
 fatal                   | boolean                |           |          | 
 commercial_license      | boolean                |           |          | 
 hazmat                  | boolean                |           |          | 
 commercial_vehicle      | boolean                |           |          | 
 alcohol                 | boolean                |           |          | 
 workzone                | boolean                |           |          | 
 state                   | text                   |           |          | 
 vehicletype             | text                   |           |          | 
 year                    | smallint               |           |          | 
 make                    | text                   |           |          | 
 model                   | text                   |           |          | 
 color                   | text                   |           |          | 
 violation_type          | text                   |           |          | 
 charge                  | text                   |           |          | 
 article                 | text                   |           |          | 
 contributed_to_accident | boolean                |           |          | 
 race                    | text                   |           |          | 
 gender                  | text                   |           |          | 
 driver_city             | text                   |           |          | 
 driver_state            | text                   |           |          | 
 dl_state                | text                   |           |          | 
 arrest_type             | text                   |           |          | 
 geolocation             | point                  |           |          | 
 council_districts       | smallint               |           |          | 
 councils                | smallint               |           |          | 
 communities             | smallint               |           |          | 
 zip_codes               | smallint               |           |          | 
 municipalities          | smallint               |           |          | 

The application might want to ask for all traffic violations that match a specific violation type:

postgres=# select distinct violation_type from mv_traffic_violations;
 violation_type 
----------------
 Warning
 SERO
 Citation
 ESERO
(4 rows)

Partitioning by that would result in 4 partitions (plus one default partition for any other potential violation type in the future). The definition of the table and it’s partitions is pretty much the same as for range partitioning but this time we go for list partitioning:

create table traffic_violations_p_list
( seqid text
, date_of_stop date  
, time_of_stop time
, agency text
, subagency text
, description text 
, location text
, latitude numeric
, longitude numeric
, accident text
, belts boolean
, personal_injury boolean 
, property_damage boolean
, fatal boolean
, commercial_license boolean
, hazmat boolean
, commercial_vehicle boolean
, alcohol boolean
, workzone boolean
, state text
, vehicletype text 
, year smallint
, make text
, model text
, color text
, violation_type text 
, charge text
, article text
, contributed_to_accident boolean
, race text
, gender text
, driver_city text
, driver_state text
, dl_state text
, arrest_type text
, geolocation point
, council_districts smallint 
, councils smallint
, communities smallint
, zip_codes smallint
, municipalities smallint
)
partition by list (violation_type);

psql will show the list partitioning strategy as well:

postgres=# \d traffic_violations_p_list
               Partitioned table "public.traffic_violations_p_list"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 seqid                   | text                   |           |          | 
 date_of_stop            | date                   |           |          | 
 time_of_stop            | time without time zone |           |          | 
 agency                  | text                   |           |          | 
 subagency               | text                   |           |          | 
 description             | text                   |           |          | 
 location                | text                   |           |          | 
 latitude                | numeric                |           |          | 
 longitude               | numeric                |           |          | 
 accident                | text                   |           |          | 
 belts                   | boolean                |           |          | 
 personal_injury         | boolean                |           |          | 
 property_damage         | boolean                |           |          | 
 fatal                   | boolean                |           |          | 
 commercial_license      | boolean                |           |          | 
 hazmat                  | boolean                |           |          | 
 commercial_vehicle      | boolean                |           |          | 
 alcohol                 | boolean                |           |          | 
 workzone                | boolean                |           |          | 
 state                   | text                   |           |          | 
 vehicletype             | text                   |           |          | 
 year                    | smallint               |           |          | 
 make                    | text                   |           |          | 
 model                   | text                   |           |          | 
 color                   | text                   |           |          | 
 violation_type          | text                   |           |          | 
 charge                  | text                   |           |          | 
 article                 | text                   |           |          | 
 contributed_to_accident | boolean                |           |          | 
 race                    | text                   |           |          | 
 gender                  | text                   |           |          | 
 driver_city             | text                   |           |          | 
 driver_state            | text                   |           |          | 
 dl_state                | text                   |           |          | 
 arrest_type             | text                   |           |          | 
 geolocation             | point                  |           |          | 
 council_districts       | smallint               |           |          | 
 councils                | smallint               |           |          | 
 communities             | smallint               |           |          | 
 zip_codes               | smallint               |           |          | 
 municipalities          | smallint               |           |          | 
Partition key: LIST (violation_type)
Number of partitions: 0

Now we can create the partitions:

create table traffic_violations_p_list_warning
partition of traffic_violations_p_list
for values in ('Warning');

create table traffic_violations_p_list_sero
partition of traffic_violations_p_list
for values in ('SERO');

create table traffic_violations_p_list_Citation
partition of traffic_violations_p_list
for values in ('Citation');

create table traffic_violations_p_list_ESERO
partition of traffic_violations_p_list
for values in ('ESERO');

create table traffic_violations_p_list_default
    partition of traffic_violations_p_list DEFAULT;

Again, psql will display the partitions nicely:

postgres=# \d+ traffic_violations_p_list
                                   Partitioned table "public.traffic_violations_p_list"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: LIST (violation_type)
Partitions: traffic_violations_p_list_citation FOR VALUES IN ('Citation'),
            traffic_violations_p_list_esero FOR VALUES IN ('ESERO'),
            traffic_violations_p_list_sero FOR VALUES IN ('SERO'),
            traffic_violations_p_list_warning FOR VALUES IN ('Warning'),
            traffic_violations_p_list_default DEFAULT

Loading the data and checking the amount of data for each partition:

postgres=# insert into traffic_violations_p_list
postgres-#        select * from mv_traffic_violations;
INSERT 0 1528078
postgres=# select count(*) from traffic_violations_p_list_warning;
 count  
--------
 759747
(1 row)

postgres=# select count(*) from traffic_violations_p_list_sero;
 count 
-------
   899
(1 row)

postgres=# select count(*) from traffic_violations_p_list_Citation;
 count  
--------
 693226
(1 row)

postgres=# select count(*) from traffic_violations_p_list_ESERO;
 count 
-------
 74206
(1 row)

postgres=# select count(*) from traffic_violations_p_list_default;
 count 
-------
     0
(1 row)

Done. This is list partitioning. Instead of partitioning by a range, list partitioning is for partitioning by key values. In the next post we will have a look at hash partitioning.

Cet article PostgreSQL partitioning (3): List partitioning est apparu en premier sur Blog dbi services.

So long …

Jonathan Lewis - Mon, 2019-06-03 08:09

… and thanks for any Douglas Adams references.

I decided on Friday that after more than 31 years using the Oracle software and more than 37 years of being self-employed in the computer industry – not to mention a further 8 years being an enthusiastic amateur – the time had finally come for a change of pace.  As from 1st June 2019 I am retired.

I have a couple of long term commitments that I’ll still be doing a little work for over the next few months, and I wouldn’t be surprised if someone occasionally tempts me into doing a few days solving interesting problems.  I’ll still be answering questions on the Oracle Developer Community forum and (if the email gets through) on the Oracle-L listserver , and I’ll still be writing blog notes about Oracle and submitting abstracts to conferences for a few years. Who knows, I may even get round to writing another book – after all, I’ve already downloaded Oracle 19 so I’ll still be experimenting, investigating, and trying to break things Some people retire to do the Times crossword or Sudoku, my retirement pastime is going to be playing with Oracle.

But now I’ve got 3 musical instruments to re-learn, a garden to re-engineer, apple trees to train, and a lot of rowing [U.S. “crew”] time to catch up on so that I can get fit enough to get back to playing squash (which might be an interesting experience now that my eyesight has been fixed). And for relaxation I’ll be sitting in the garden watching the birds, drinking fine wine and reading a good book.

Update (6th June 2019)

Thanks for all the appreciative comments to know. It’s nice to have a record of the fact that I have made a difference for so many people.

 

Ignoring Hints

Jonathan Lewis - Mon, 2019-06-03 02:36

One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at the session level. The threat, of course, it that some of your code may use the hidden version of the parameter (perhaps in an SQL_Patch as an opt_param() option rather than in its hint form) which no longer works after the upgrade.

But there’s more. The parameter (whether the old hidden version or the new revealed version) doesn’t make the optimizer ignore parallel() hints. But 18.3 now has a related parameter optimizer_ignore_parallel_hints to address this limitation. Here’s a quick demo – we start by creating a table and then running a query where the full tablescan is clearly the default strategy that the optimizer would take if we didn’t hint an indexed access path:

rem
rem     Script:         ignore_parallel_hints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem 

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        mod(rownum,10)                  n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4 -- > comment to avoid WordPress format issue
;

create index t1_i1 on t1(id);

set serveroutput off

prompt  =============
prompt  Baseline test
prompt  =============

select
        /*+ index(t1) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);


SQL_ID  gudnnk7j7q5bz, child number 0
-------------------------------------
select  /*+ index(t1) */  n1, sum(id) from  t1 where  id > 0 group by
n1 order by  n1

Plan hash value: 356059923

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |   198 (100)|          |
|   1 |  SORT GROUP BY                       |       |    10 |    70 |   198   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    | 10000 | 70000 |   196   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 | 10000 |       |    22   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID">0)


Now we repeat the exercise with the version-specific “alter session” command below – and you should try each option with each version of Oracle if you want to do the complete test cycle – to see that the session will ignore hints and the plan will change (side note – using the underscore version  with 18.3 doesn’t raise an error, the statement is silently ignored):


alter session set "_optimizer_ignore_hints" = true;
alter session set "optimizer_ignore_hints" = true;

SQL_ID  gudnnk7j7q5bz, child number 1
-------------------------------------
select  /*+ index(t1) */  n1, sum(id) from  t1 where  id > 0 group by
n1 order by  n1

Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY     |      |    10 |    70 |    27  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |    25   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">0)

Then, of course, we have to test a query with a parallel() hint – or shared() hint, which is the internal equivalent you will notice occasionally in outlines or the “remote” statement for distributed execution plans – to show that we don’t yet ignore parallel queries – the plans following the code are from 18.3:


select
        /*+ parallel(t1 3) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);

alter session set "optimizer_ignore_parallel_hints" = true;

select
        /*+ parallel(t1 3) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);




SQL_ID  7jynurdtc48kv, child number 0
-------------------------------------
select  /*+ parallel(t1 3) */  n1, sum(id) from  t1 where  id > 0 group
by  n1 order by  n1

Plan hash value: 2919148568

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |       |       |    10 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |    10 |    70 |    10  (10)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 10000 | 70000 |     9   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| T1       | 10000 | 70000 |     9   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access(:Z>=:Z AND :Z<=:Z) -- > comment added to avoid wordpress format issue
       filter("ID">0)

Note
-----
   - Degree of Parallelism is 3 because of table property


Session altered.


SQL_ID  7jynurdtc48kv, child number 1
-------------------------------------
select  /*+ parallel(t1 3) */  n1, sum(id) from  t1 where  id > 0 group
by  n1 order by  n1

Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY     |      |    10 |    70 |    27  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |    25   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">0)


tl;dr

In 18.3 Oracle exposes the parameter optimizer_ignore_hints – any code using depending on the hidden version of this parameter will no longer behave as expected. 18.3 also introduces optimiser_ignore_parallel_hints to allow you to ignore parallel hints as well.

 

Pages

Subscribe to Oracle FAQ aggregator