Infrastructure at your Service

SQL Archives - Blog dbi services

Daniel Westermann

How to destroy your performance: PL/SQL vs SQL

By | Database Administration & Monitoring | No Comments

Disclaimer: This is in no way a recommendation to avoid PL/SQL. This post just describes a case I faced at a customer with a specific implementation in PL/SQL the customer (and me) believed is the most efficient way of doing it in PL/SQL. This was a very good example for myself to remind me to check the documentation and to verify if what I believed a feature does is really what the feature is actually…

 
Read More
Franck Pachot

Oracle 12cR2 SQL new feature: LISTAGG overflow

By | Database Administration & Monitoring | No Comments

LISTAGG was a great feature introduced in 11g: put rows into line with a simple aggregate function. 12cR2 adds an overflow clause to it. What happens when you have so many rows that the LISTAGG result is too long? SQL> select listagg(rownum,’,’)within group(order by rownum) from xmltable(‘1 to 10000′); select listagg(rownum,’,’)within group(order by rownum) from xmltable(‘1 to 10000′) * ERROR at line 1: ORA-01489: result of string concatenation is too long An error at runtime,…

 
Read More
Alain Lacour

APEX Connect 2016 – Day 1 – SQL and PL/SQL

By | Development & Performance | No Comments

This year the APEX connect conference spans over three days with the first day dedicated to SQL and PL/SQL which are the basement of APEX and its close link to the Database. After the Keynote about “Six months of ask Tom” by Chris Saxon who is filling in for Tom Kyte on the famous “Ask Tom” website I decided to attend presentations on following topics: – A Primer on Service Workers – Managing the changes…

 
Read More
Daniel Westermann

Parallel sequential scans are coming to PostgreSQL

By | Database Administration & Monitoring | No Comments

It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up: max_parallel_degree: The maximum degree of parallelism for an individual parallel operation. parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a parallel worker process to another process. The default is 0.1 parallel_setup_cost: Sets the planner’s estimate…

 
Read More
Daniel Westermann

Monitoring tools for PostgreSQL – POWA

By | Database Administration & Monitoring, Database management | No Comments

The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the file system layer pg_qualstats: gathers statistics of predicates found in where statements and join clauses btree_gist: provides GiST index operator…

 
Read More
Daniel Westermann

Monitoring tools for PostgreSQL – pgcluu

By | Database Administration & Monitoring | No Comments

The last posts introduced the logging system, pg_stat_statements and pg_activity. All of these can be used to monitor sql statements the PostgreSQL server is executing. In this post I’ll look into pgcluu: PostgreSQL Cluster utilization! This is a more complete monitoring solution as it is not only focused on sql statements but gives you information about the database cluster itself and other useful stuff. All you need to run pgcluu is a modern perl distribution…

 
Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (3) – pg_activity

By | Database Administration & Monitoring | No Comments

The last posts looked at how the logging system and the pg_stat_statements extension can be used to monitor sql statements in PostgreSQL. This post will introduce pg_activity which is very similar to htop. There are some dependencies which need to be installed before we can start installing pg_activity. The first one is python. As I am on a redhat based distribution this is quite easy: [root@oel7 ~] yum install -y python Then we need to…

 
Read More