Feed aggregator

Postgres vs. Oracle access paths – intro

Yann Neuhaus - Tue, 2017-08-01 00:00

This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have a long experience of reading Oracle execution plans and no experience at all on Postgres. This is my way to learn and share what I learn. You will probably be interested if you are in the same situation: an Oracle DBA wanting to learn about Postgres. But you may also be an experienced Postgres DBA who wants to see a different point of view from a different ‘culture’.

I’ll probably use the Oracle terms more often as I’m more familiar with them: blocks for pages, optimizer for query planner, rows for tuples, tables for relations…

Please, don’t hesitate to comment on the blog posts or through twitter (@FranckPachot) if you find some mistakes in my Postgres interpretation. I tend to verify any assumption in the same way I do it with Oracle: the documented behavior and the test result should match. My test should be fully reproducible (using Postgres 9.6.2 here with all defaults). But as I said above, I’ve not the same experience as I have on Oracle when interpreting execution statistics.

Postgres

I’m using the latest versions here. Postgres 9.2.6 (as the one I installed here)
I’ve installed pg_hint_plan to be able to control the execution plan with hints. This is mandatory when doing some research. In order to understand an optimizer (query planner) choice, we need to see the estimated cost for different possibilities. Most of my tests will be done with: EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS)

fpa=# explain (analyze,verbose,costs,buffers) select 1;
 
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
Output: 1
Planning time: 0.060 ms
Execution time: 0.036 ms
(4 rows)

I my go further with unix tools (like strace to see the system calls)

Oracle

I’m using Oracle 12.2 here and the tests are done by running the statement after setting ALTER SESSION SET STATISTICS_LEVEL=ALL and displaying the execution plan with DBMS_XPLAN:
select * from dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection');
Note that if you are in lower Oracle versions, you need to call dbms_xplan through the table() function:
select * from table(dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection'));
Example:

SQL> set arraysize 5000 linesize 150 trimspool on pagesize 1000 feedback off termout off
SQL> alter session set statistics_level=all;
SQL> select 1 from dual;
SQL> set termout on
SQL> select * from dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 520mkxqpf15q8, child number 0
-------------------------------------
select 1 from dual
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 |
| 1 | FAST DUAL | | 1 | 1 | 2 (0)| 1 |00:00:00.01 |
--------------------------------------------------------------------------------------

I’ll probably never compare the execution time, as this depends on the system and makes no sense on artificial small examples. But I’ll try to compare all other statistics: estimated cost, the actual number of pages/blocks read, etc.

Table of content

I’ll update (or rather insert /*+ append */) the links to the series posts as soon as they are published.

  1. Postgres vs. Oracle access paths I – Seq Scan
  2. Postgres vs. Oracle access paths II – Index Only Scan
 

Cet article Postgres vs. Oracle access paths – intro est apparu en premier sur Blog dbi services.

PostgreSQL on Cygwin

Yann Neuhaus - Mon, 2017-07-31 23:00

I run my laptop with Windows 10 for office programs, and VirtualBox machines with Linux for the big stuff (Oracle databases). I have also Cygwin installed on Windows for GNU programs. I wanted to quickly install PosgreSQL and rather than installing it in a Linux VM, or as a Windows program, I installed the Cygwin version of it. Here is how.

Cygwin

Cygwin is easy to install: just run the setup-x86_64.exe from https://www.cygwin.com/ and choose the packages you want to install. Here is what is related to PostgreSQL:
CapturePGCY0001

Note that if you want to install postgres extensions you may need pg_config and you need to install the libpd-devel in addition to postgresql-devel. And gcc and make. Those are not displayed in the screenshot above but you may get something like the following, if you don’t have them, when installing an extension:
pg_config: Command not found

Of course, PostgreSQL is Open Source and you can also compile it yourself.

Cygserver

Cygwin can run daemons through a Windows service (Cygserver) and you need to set it up if not already done. For this step, you will need to run the Cygwin Terminal as Administrator.
fpa@dell-fpa ~
$ /usr/bin/cygserver-config
Overwrite existing /etc/cygserver.conf file? (yes/no) yes
Generating /etc/cygserver.conf file
 
Warning: The following function requires administrator privileges!
 
Do you want to install cygserver as service?
(Say "no" if it's already installed as service) (yes/no) yes
 
The service has been installed under LocalSystem account.
To start it, call `net start cygserver' or `cygrunsrv -S cygserver'.
 
Further configuration options are available by editing the configuration
file /etc/cygserver.conf. Please read the inline information in that
file carefully. The best option for the start is to just leave it alone.
 
Basic Cygserver configuration finished. Have fun!

You start this service as any Windows service:

fpa@dell-fpa ~
$ net start cygserver
The CYGWIN cygserver service is starting.
The CYGWIN cygserver service was started successfully.

You can check from that the service is running:

fpa@dell-fpa ~
$ cygstart services.msc

CapturePGCY0002

PostgreSQL database cluster

Here is the creation of the PostgreSQL database cluster.
fpa@dell-fpa ~
$ /usr/sbin/initdb -D /usr/share/postgresql/data
The files belonging to this database system will be owned by user "fpa".
This user must also own the server process.
 
The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
 
Data page checksums are disabled.
 
creating directory /usr/share/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 30
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
 
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
 
Success. You can now start the database server using:
 
/usr/sbin/pg_ctl -D /usr/share/postgresql/data -l log.txt start

Start PostgreSQL database server

I add my network onto the /usr/share/postgresql/data/postgresql.conf

# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.78.0/24 trust

I define the interface and port where the server listen in /usr/share/postgresql/data/postgresql.conf

listen_addresses = 'localhost,192.168.78.1' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 30 # (change requires restart)

Now ready to start the PostgreSQL server:
fpa@dell-fpa ~
$ /usr/sbin/pg_ctl -D /usr/share/postgresql/data -l log.txt start
server starting

Username

My Windows username is ‘FPA’ and so is the Cygwin user which started the database server and I check that I can connect to the maintenance database with this user:

fpa@dell-fpa ~
$ psql -U fpa postgres
psql (9.6.2)
Type "help" for help.
 
postgres=# \du
 
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
fpa | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 
postgres=# quit

PgAdmin

As I am on Windows, I install the graphical console PgAdmin and setup the connection to this database:
CapturePGCY0003

SQL Developer

As an Oracle fan, I prefer to connect with SQL Developer. Just download the JDBC driver for PostgreSQL: https://jdbc.postgresql.org/download.html

In SQL Developer you can declare this .jar from Tools -> Preferences -> Third Party JDBC Drivers

CapturePGCY0004

And create the connection with the new ‘PostgreSQL’ tab:

CapturePGCY0005
Then with ‘Choose Database’ you can fill the dropbox and choose the database you want to connect to.

As I have no database with the same name as the username, I have to mention the database name at the end of the hostname, suffixed with ‘?’ to get the proper JDBC url. And what you put in the dropbox will be ignored. I don’t really know the reason, but this is how I got the correct url.

CapturePGCY0006

Extensions

You can install extensions. For example, I’ve installed pg_hint_plan to be able to hint the access path and join methods.

wget https://osdn.net/dl/pghintplan/pg_hint_plan96-1.2.1.tar.gz
tar -zxvf pg_hint_plan96-1.2.1.tar.gz
cd pg_hint_plan96-1.2.1
make
make install

And I’m now able to load it:

$ psql
psql (9.6.2)
Type "help" for help.
 
fpa=# load 'pg_hint_plan';
LOAD

But Why?

You may wonder why I don’t install it directly on Linux. My laptop is on Windows and, of course, I have a lot of VirtualBox VMs. But this doesn’t require to start a VM.
You may wonder why I don’t install the Windows version? I want to investigate the linux behaviour. And I may want to trace the postgres processes. For example, cygwin has a strace.exe which shows similar output as strace on Linux. Here is the I/O calls from a full table scan (Seq Scan):
CaptureStraceCygwinPostgres
I can see that postgres sequential reads are done through one lseek() and sequential 8k read().

This was simple. Just get the pid of the session process:

fpa=# select pg_backend_pid();
pg_backend_pid
----------------
11960

and strace it:

$ strace -p 11960

I’ve done that in about one hour: download, install, setup and write this blog post. Without any virtual machine, you can have a Linux Postgres database server running on Windows.

 

Cet article PostgreSQL on Cygwin est apparu en premier sur Blog dbi services.

Error ora-01640 making tablespace read only without active transaction

Tom Kyte - Mon, 2017-07-31 15:46
Hi Tom, We are trying put a tablespace into readonly mode and getting error ora-01640. There is no transaction as the database was stopped and started in normal mode.. Only one session was established from which table space readonly was executed....
Categories: DBA Blogs

How to create FIFO report based on evaluation system

Tom Kyte - Mon, 2017-07-31 15:46
I want to implement FIFO Stock model in my inventory. So I face some problems but before staring query i describe my physical structure of the table. <code>create table purchase_mas ( stock_id number pur_date date, product_code number...
Categories: DBA Blogs

PLS-00302 using XMLTABLE

Tom Kyte - Mon, 2017-07-31 15:46
I'm getting PLS-00302 when trying to select from a chunk of XML using XMLTYPE and XMLTABLLE. This works, and I get the desired output... <code> SELECT xt.* FROM (SELECT xmltype ( '<TABLE> <TR><TD>Organization</TD><TD>Organization Title</TD>...
Categories: DBA Blogs

How to do a EXP full excluding some schemas?

Tom Kyte - Mon, 2017-07-31 15:46
Hello Oracle Masters, Thanks a lot for taking time to answer questions. I know that it is possible to do an export full datapump and use the exclude clause. Since I have space limitations, I'm using original export with exp_pipe. It's possi...
Categories: DBA Blogs

loading jar files into Oracle

Tom Kyte - Mon, 2017-07-31 15:46
Could you please provide an example that 1) Loads a jar file into oracle [any permissions and path variables that needs to be set-up using DBAs help] 2) Load a class that uses the above jar file 3) Create a PL/SQL procedure or function that uses ...
Categories: DBA Blogs

How to Compile Code on all Connections

Tom Kyte - Mon, 2017-07-31 15:46
Hi, Scenario: We are using 6 different schemas and we are compiling codes on all of them. So first, I connect to those schemas, then when I need to compile my codes, what i do is: Select Schema, Compile, Repeat. I was wondering if there's a tec...
Categories: DBA Blogs

Oracle DB audit

Tom Kyte - Mon, 2017-07-31 15:46
We have set Oracle DB audit to DB, Extended. From the 2 hour span AWR report, there are 824,993 physical reads to AUD$ table. Is this physical read, Oracle internal process? or external query to the AUD$ table? Will it affect the database perform...
Categories: DBA Blogs

Performance of the PL/SQL function

Tom Kyte - Mon, 2017-07-31 15:46
Hi Team , I need some help here for performance improvement I have an XML as below ,this XML would be input to my PL/SQL function <?xml version="1.0" encoding="UTF-8"?> <n1:Request xmlns:n1="urn:global:cs:common"> <n1:Conditions> ...
Categories: DBA Blogs

New OA Framework 12.2.5 Update 14 Now Available

Steven Chan - Mon, 2017-07-31 11:07

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure. Since the initial release of Oracle E-Business Suite Release 12.2 in 2013, we have released a number of cumulative updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.5 is now available:

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.5 users should apply this patch.  Future OAF patches for EBS Release 12.2.5 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes 44 fixes in total, including all fixes released in previous EBS Release 12.2.5 bundle patches.

This latest bundle patch includes fixes for following bugs/issues:

  • Exporting table data fails when the View Object has view links associated with it and has a different number of bind parameters and bind values.
  • Long notification title is not wrapping.
  • Code changes in OA Framework to support Thai, English Hijrah and Arabic Hijrah calendars in WebADI.

Related Articles

Categories: APPS Blogs

Video: The Rise of ChatBots in Enterprise Mobile Application Development

OTN TechBlog - Mon, 2017-07-31 08:16

Conversations between human beings and computers have been a mainstay of science fictions movies for decades. Remember the HAL 9000 in director Stanley Kubrick's groundbreaking 1968 epic "2001 - A Space Odyssey?" Remember Joshua, the computer in the 1983 hit "War Games?" Remember Kirk and Spock issuing verbal commands to the Starship Enterprise's onboard computer? In the second decade of the 21st century, conversational interfaces have transcended fiction to become a reality, and the next big thing in consumer and enterprise mobile application development. Paired with artificial intelligence, chatbots help to simplify mobile communication between users and services. As in human conversation, well-chosen wordings allow users to book a table in a restaurant, arrange a taxi to the airport, buy an airline ticket, and more. This on-demand session video from the Oracle Code Online event in June 2017 explores a developer perspective on chatbots.

Additional Resources

What Employers Want : A Series of Posts

Tim Hall - Mon, 2017-07-31 04:37

A couple of years ago I was asked to give a talk about employability, with respect to graduates. I’m not an expert in that field, so I reached out to a few friends and companies to get some feedback about what they expect from new staff. I get questions about jobs all the time, so I figured I would write a short series of posts on the subject, just so I can refer people to them.

The posts in the series are as follows.

After doing the presentation I discussed it with the careers staff present, who confirmed this matched the feedback they were getting from their industrial contacts, so it seems the feedback I got from my contacts was on the money!

As I publish each post I will turn these bullet points into links.

Hope this helps.

Cheers

Tim…

What Employers Want : A Series of Posts was first posted on July 31, 2017 at 10:37 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Re-assigning all objects from on role to another in PostgreSQL

Yann Neuhaus - Mon, 2017-07-31 04:07

From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go.

Obviously we need two users when we want to move objects from one user to another (actually it is not really moving the objects but more about changing the ownership):

postgres=# create role a login password 'a';
CREATE ROLE
postgres=# create role b login password 'b';
CREATE ROLE
postgres=# create schema a authorization a;
CREATE SCHEMA
postgres=# create schema b authorization b;
CREATE SCHEMA
postgres=# alter role a set search_path=a;
ALTER ROLE
postgres=# alter role b set search_path=b;
ALTER ROLE
postgres=# 

Lets create some objects in schema “a” owned by user “a”:

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> \! cat a.sql
create table a ( a int );
create table b ( a int );
create table c ( a int );
create table d ( a int );
create index i1 on a (a);
create index i2 on b (a);
create index i3 on c (a);
create index i4 on d (a);

postgres=> \i a.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

By joining pg_class and pg_roles we can verify who is actually the owner of the objects:

postgres=> select t.relname, d.rolname 
             from pg_class t, pg_roles d 
            where t.relowner = d.oid and d.rolname = 'a';
 relname | rolname 
---------+---------
 a       | a
 b       | a
 c       | a
 d       | a
 i1      | a
 i2      | a
 i3      | a
 i4      | a
(8 rows)

The easiest way to make these objects owned by another user (call it “c”) would be:

postgres=# alter user a rename to c;
NOTICE:  MD5 password cleared because of role rename
ALTER ROLE
postgres=# select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'c';
 relname | rolname 
---------+---------
 a       | c
 b       | c
 c       | c
 d       | c
 i1      | c
 i2      | c
 i3      | c
 i4      | c
(8 rows)

Not a good idea though as the schema still is named “a” and this at least will create some confusion with the naming. Of course we could rename the schema as well:

postgres=# alter schema a rename to c;
ALTER SCHEMA
postgres=# \c postgres c
You are now connected to database "postgres" as user "c".
postgres=> select count(*) from a;
2017-07-28 15:51:25.499 CEST [3415] ERROR:  relation "a" does not exist at character 22
2017-07-28 15:51:25.499 CEST [3415] STATEMENT:  select count(*) from a;
ERROR:  relation "a" does not exist
LINE 1: select count(*) from a;

… but now we have another mess. Because the search_path is still set to “a” we can not see the objects by default but we will need to use the fully qualified name:

postgres=> select count(*) from c.a;
 count 
-------
     0
(1 row)

Finally we would need to adjust the search_path to get back the previous behavior:

postgres=> set search_path=c;
SET
postgres=> select count(*) from a;
 count 
-------
     0
(1 row)

A lot of steps to follow. Easier is:

postgres=# reassign owned by c to b;
REASSIGN OWNED
postgres=# alter user b set search_path=c,b;
ALTER ROLE
postgres=# \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a;
 count 
-------
     0
(1 row)
postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b';
 relname | rolname 
---------+---------
 d       | b
 i4      | b
 c       | b
 i3      | b
 b       | b
 i2      | b
 a       | b
 i1      | b
(8 rows)

Cool :) There is also a command to drop all objects of a user:

postgres=> drop owned by b;
DROP OWNED
postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b';
 relname | rolname 
---------+---------
(0 rows)

Nice …

 

Cet article Re-assigning all objects from on role to another in PostgreSQL est apparu en premier sur Blog dbi services.

String extraction

Tom Kyte - Sun, 2017-07-30 21:26
Hi I have a query regarding strings. If I give a string like 'Mrs xxxxxx yyyyy', I should get the output like Title FirstName LastName Mrs xxxxxx yyyyy How can I do this using string functions. Many...
Categories: DBA Blogs

How is undo data retrieved for the purpose of a Flashback Query?

Tom Kyte - Sun, 2017-07-30 21:26
Hello Tom, Looking at a flashback version query, it is obvious that all changes to a row are kept in the undo, not just the latest one. My understanding is that the address of the undo corresponding to a specific row is stored in the header of...
Categories: DBA Blogs

Frequency of archive log switches

Tom Kyte - Sun, 2017-07-30 21:26
Hi, I've been a DBA for a very long time (since 7.3.4) and in the old days I remember 'rules-of-thumb' that stated that you should never switch logs more than 'x' times an hour. This was of course due to the fact that checkpoints take up a lot of ...
Categories: DBA Blogs

INSERT SELECT with APPEND NOLOGGING PARALLEL(t,8) - Whether this can able to process upto 15 crores records ?

Tom Kyte - Sun, 2017-07-30 21:26
As part of data migration project , i need to transfer data from staging table to target table (with out filter criteria) for about 41 tables. Its a simple insert select and below query i am using for 41 tables. INSERT /*+ APPEND NOLOGGING */...
Categories: DBA Blogs

Best practice for ETL update

Tom Kyte - Sun, 2017-07-30 21:26
We have an ETL process on a table with 38 updatable columns. The source data is a CSV file with a single column unique record identifier and we set this file up as an external table. The target table has a corresponding primary key column. The sou...
Categories: DBA Blogs

Dynamic file names with sql Loader

Tom Kyte - Sun, 2017-07-30 21:26
Hi Can I load a file using sqlloader within a batch file where the file name changes dynamically. Eg: The file name is FUNC_DDMMYY.csv Can I load this file by passing it to SQLLoader as FUNC_*.csv? Would wild cards work in this case. How...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator