← Back to the Blog

Processing SQL queries in Oracle and PostgreSQL

Intro:

 

Initial parsing phase of SQL query processing is pretty much similar for the both Oracle and PostgreSQL databases.

When an application issues a new SQL statement, it makes a parse call to the database to prepare the statement for execution.

When parsing a SQL query, both systems need to check syntax and semantics first, using metadata stored in SYSTEM dictionary for Oracle or SYSTEM Catalog for Postgresql.

Then prepare an exacution plan for a query, and optimize it using CBO (Cost Based Optimizer) CBO depends on a statistics to be pre-gathered on a database.

However, when paersing completed, there is a big difference,.Oracle stores compiled queries in a Global Library Cache, which is available for all sessions for a running Oracle Instance. PostgreSQL, stores pre-processed queries in a process local memory.

For our test cases, we will be using Oracle 11G and PostgreSQL 9.4 Both systems were configured and scaled relatively in the way, so a baseline query was taking about the same 10 seconds on both systems.

 



Oracle SQL processing

In Oracle parse call creates or opens a cursor first, which is a handler for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are written into the Program Global Area (PGA).

 

Stages of Oracle SQL Processing:

  • syntax check
  • semantic check
  • shared pool check
  • optimization
  • row source generation
  • execution

oracle sql processing stages

 

Then, pre-processed SQL query gets written into a Library Cache (which is a part of Shared Pool in SGA) Also, during the parse, Oracle checks shared pool to determine whether it can skip resource-intensive steps of statement processing. So every parsed SQL query in Oracle, eventually gets into Library Cache before actual execution.

 

Hard Parse

If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss. During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.

 

Soft Parse

If the submitted statement is the same as a reusable SQL statement in the shared pool. Then Oracle Database will reuse existing code. This reuse of code is also called a library cache hit. In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

Library Cache is a part of Shared Pool in SGA. In general, any item (shared SQL area or dictionary row) in the Oracle Shared Pool remains until it is flushed according to a modified Least Recently Used (LRU) algorithm.

Even small but frequent and consistent SQL queries, wich require a hard-parse for every execution will eventually displace other 'important' execution plans from a Library Cache slowing down overall database performance.

When attempting to write into Library Cache, process acquires a lock on a resource, it may also become a bottleneck.

 

Let's show it on an example:

Let's create a new table 't' in our Oracle Database, insert 100 000 rows into it and calculate statistics on it

create table t(
  id number primary key,
  n number not null
);
insert into t(id, n)
  select level, 1 from dual
  connect by rownum <= 100000;
exec dbms_stats.gather_table_stats(user,'T');
alter session set statistics_level=all;

Now let's update every row in a loop, using a generated 'update' statement, (Note, that we are not using bind variables deliberately in this example)

begin
  for i in (select id from t) loop
    execute immediate 'update t set n = n + 1 where id = '||i.id;
  end loop;
  commit;
end;
/

turning ON tracing on will show timings breakdown for all SQL queries executed in a block of code:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   100003    92.62      95.40          0       2837          0          0
Execute 100003    13.58      14.29          0     200002     102225     100000
Fetch     1002     0.87       0.75          0      10173          0     100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   201008   107.08     110.46          0     213012     102225     200000

Misses in library cache during parse: 100001

As we can see here, most of a time database is doing hard parsing
Running this SQL in a multiple terminal sessionds will cause contenctions: latch: row cache objects and latch: shared pool

 

using bind variables is Oracle

To avoid Hard Parsing from happening in Oracle we can use bind variables in loops as following:

begin
  for i in (select id from t) loop
    execute immediate 'update t set n = n + 1 where id = :A' using i.id;
  end loop;
  commit;
end;
/

or another one, just using PL/SQL variables every reference to a PLSQL variable is in fact a BIND VARIABLE:

begin
  for i in (select id from t) loop
    update t set n = n + 1 where id = i.id;
  end loop;
  commit;
end;
/

trace:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        3     0.02       0.03          0        297          0          0
Execute 100002     9.08       9.28          0     201694     102315     100000
Fetch     1001     0.77       0.68          0      10173          0     100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   101006     9.87      10.00          0     212164     102315     200000

 

Parsing time is down to minimum,
now all the update cursors in the loop will look exactly the same to the database.

Soft Parsing is going to happen for all cursors with the same sql_id and child_number, where SQL_ID is a hash identifier of the parent cursor in the library cache CHILD_NUMBER is a number of a child cursor.

Most efficient sql statement to update all rows in the table is going to be:

update t set n = n + 1;

In cases where the distribution of data is such that the selectivity is almost the same for all values, the execution plan would remain the same.

 

uneven data distribution

Now let's add flag column into our table, setting flag equal to 'Y' for 0.1% of rows and flag = 'N' for the rest 99.9%
And then let's create a new index for it.

alter table t add (
  flag char(1) check (flag in ('Y','N'))
);
update t
  set flag = case when mod(id,1000)=0 then 'Y' else 'N' end;
create index t_flag on t(flag);

Now we have highly skewed data as an outcome.
let's create histogram as following:

exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns flag size 2');

It's interresting, that dbms_xplan.display still show us prediction, splitting table by half (number of rows for a full table scan):

explain plan for select * from t where flag = :f;
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |   488K|    76   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 50000 |   488K|    76   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG"=:F)

Thus as you can see, bind variables, even though they were good in most cases, actually fail in cases where the selectivity of the values radically affected the plans

NOTE: running explain plan command before SQL statement in Oracle

  • is not taking into consideration values and types for the bind variables,
  • plan, generated by this command is not getting into the cache for further reuse.

 

bind peeking and adaptive cursors in Oracle 11G

On execution phase, Oracle performs bind peeking to lookup types and values for associated bind variables,

Let's use dbms_xplan.display_cursor procedure to get actual explain plan for a given cursor:

var f char(1)
exec :f := 'Y'
select * from t where flag = :f;
...
100 rows selected.

select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID 6pncxxhknwgqc, child number 0

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |   135 |  1350 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_FLAG |   135 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :F (CHAR(30), CSID=873): 'Y'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:F)

As we can see on above example, now optimizer is taking binded variables into consideration and used Index range scan instead of full table scan.

With Oracle Database 11g, cursors suddenly have a new kind of intelligence. Instead of blindly using the cached execution plan whenever the query is executed, they actually decide if a plan has to be recalculated when the bind variable value changes. If a cursor has a bind variable in it, the database observes it for a while to see what type of values are passed to the variable and if the plan needs recalculation. If the plan does need to be recalculated, the cursor is marked as "Bind-Sensitive". (see IS_BIND_SENSITIVE and IS_BIND_AWARE comlumns in V$SQL dictionary view)

Bind-Sensitive cursors are potential candidates for changed plans Bind-Aware ones are where the plans actually change. This feature is called Adaptive Cursors

Let's see how does it look in v$sql for our sql_id:

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
           0                 Y             N          1         128

Now our SQL has been marked as a bind sensitive, And buffer gets is a number of read data blocks.

Let's run our SQL with a flag set to 'N'

exec :f := 'N'
select * from t where flag = :f;
...
99900 rows selected.

Now let's make sure, that query has been exeucted with a non-optimal plan taken from cache Setting statistics_level paramater to allstats to comapre expected number of rows (E-Rows) and Actual number of rows (A-Rows) processed:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

SQL_ID 6pncxxhknwgqc, child number 0

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |  99900 |   41368 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |    135 |  99900 |   41368 |
|*  2 |   INDEX RANGE SCAN          | T_FLAG |      1 |    135 |  99900 |    6842 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FLAG"=:F)
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
           0                 Y             N          2       41496

Let's run it one more time:

select * from t where flag = :f;
...
99900 rows selected.

New execution plan for a query, note changed chiled number and section with peeked binds:

select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID 6pncxxhknwgqc, child number 1

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    77 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 99856 |   975K|    77   (3)| 00:00:01 |
--------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :F (CHAR(30), CSID=873): 'N'

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG"=:F)

Now both plans are in library cache:

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
           0                 Y             N          2       41496
           1                 Y             Y          1        6922

V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor sharing. This information is used to decide whether to enable extended cursor sharing for a query. It is stored in a histogram, whose bucket's contents are exposed by this view.

 

Oracle 11g: cardinality feedback

Cardinality feedback feature was introduced in Oracle 11gR2. The purpose of cardinality feedback is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly.

DBA can tell that cardinality feedback is in use, if it appears in the note section of the plan. we can also determine this by selecting the USE_FEEDBACK_STATS column from V$SQL_SHARED_CURSOR view.

Cardinality feedback monitoring may be enabled in the following cases:

  • tables with no statistics,
  • multiple conjunctive or disjunctive filter predicates on a table, and predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for. I

Cardinality feedback is useful for queries where the data volume being processed is stable over time.
For a query on volatile tables, the first execution statistics are not necessarily reliable.

 

Adaptive plans in Oracle 12c

The concept of an adaptive plan is that execution of a statement can start with one plan, and (during execution) switch to another. No need to run the statement to completion and try again, correct it on-a-fly instead.

The mechanism relies on embedding statistic collectors at critical points in the execution plan, that will allow Oracle to compare the actual row counts with the estimates. If these counters cross certain thresholds (means CBO was wrong in its estimates of cardinality) Oracle will switch to an alternative plan, during execution.

Adaptive plan mechanism in a 12c instance is getting enabled during the hard parse stage. During parse, many plans are computed. CBO will store alternative plans with the cursor, and calculate exactly what cardinalities would make an alternative plan as a better choice. Then, as the statement executes, if the embedded counters for actual cardinality cross those limits, it will switch plan. Doing this requires buffering of rows at the critical points, so that the switch can be made without any need for running the statement to completion or re-starting it. Two last points: the plans must have the same starting point, otherwise a switch is not possible. Also, in the current release only join method and parallel query distribution method can be adjusted.



 

SQL processing in PostgreSQL

 

Stages of SQL processing in PostgreSQL:

  • parsing and syntax check
  • semantic analysis
  • transformation process (query rewrite based on system or user-defined rules)
  • query optimization
  • execution

 

about parsing and transformation process

The parser stage creates a parse tree using only fixed rules about the syntactic structure of SQL. It does not make any lookups in the system catalogs, so there is no possibility to understand the detailed semantics of the requested operations. After the parser completes, the transformation process takes the tree handed back by the parser as input and does the semantic interpretation needed to understand which tables, functions, and operators are referenced by the query. The data structure that is built to represent this information is called the query tree.

There is no Global Library Cache for a parsed and prepared SQL queries in PorstreSQL. And by default, Postgres will not keep parsed queries for a process in local memory of a process.

So for example, if we will run same query over and over again, it will get parsed every time:

create table t(
  id serial primary key,
  n numeric not null
);
insert into t(n)
  select 1 from generate_series(1,100000);
analyze t;

let's run following PL/PGSQL code:

\timing on
do $$
declare
  i record;
begin
  for i in (select id from t) loop
  execute 'update t set n = n + 1 where id = '||i.id;
  end loop;
end;
$$ language plpgsql;
DO
Time: 36164,377 ms

 

prepare statement in PostgreSQL

A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

In order to reuse parsing results, we'll need to prepare query first and then execute it as following:

prepare u(integer) as update t set n = n + 1 where id = $1;
execute u(1);
execute u(2);
...
execute u(100000);

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE command.

Let's run first PL/PGSQL block again, but without execute command now:

do $$
declare
  i record;
begin
  for i in (select id from t) loop
  update t set n = n + 1 where id = i.id;
  end loop;
end;
$$ language plpgsql;
DO
Time: 10000,000 ms

note, that query runs 3.5 times faster this time.

And again, most efficient sql statement to update all rows in the table is going to be:

update t set n = n + 1;
Time: 3142.670 ms

A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

DBA can look-up all prepared statements available in the session by querying the pg_prepared_statements system view.

 

uneven data distribution

Let's add Boolean type variable 'flag' into our table to have an uneven data distribution case example:

alter table t add column
  flag boolean;
update t
  set flag = mod(id,1000)=0;
create index on t(flag);

Note that histogram will get created automatically upon running analyze:

analyze t;

now let's prepare SQL query

prepare s1(boolean) as select * from t where flag = $1;

Now we will run explain to get actual execution plan. As in PostgreSQL execute command is aware about types and values for associated variables and will show a true query plan for a given SQL statement.

explain execute s1(true);
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using t_flag_idx on t  (cost=0.29..14.31 rows=110 width=10)
   Index Cond: (flag = true)
   Filter: flag

Data accessed by Index, and 110 rows will get scanned. Explain command is also useful when aside of building a query plan, DBA want to execute given SQL statement immediately, and it also shows actual and predicted cardinality values.

Let's take a look at another example with a value for flag set to "False"

explain analyze execute s1(false);
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on t (cost=0.00..2958.00 rows=99890 width=10) (actual time=0.043..265.272 rows=99900 loops=1)
   Filter: (NOT flag)
   Rows Removed by Filter: 100
 Execution time: 385.455 ms

Optimizer expected to read 99890 rows, and a full table scan has been selected automatically.

 

generic and custom plans in PostgreSQL

In Postgres we'll have a situation, which is quite different from Oracle Optimizer behaviour

If a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. This will occur immediately if the prepared statement has no parameters. Otherwise it occurs only if the generic plan appears to be not much more expensive than a custom plan (depends on specific parameter values). Typically, a generic plan will be selected only if the query's performance is estimated to be fairly insensitive to the specific parameter values supplied.

To examine the query plan PostgreSQL is using for a prepared statement, we can use EXPLAIN. If a generic plan is in use, it will contain parameter symbols $n, while a custom plan will have the current actual parameter values substituted into it.

 

even data distribution example

Let's go back to an even data distribution example:

prepare s2(integer) as select * from t where id = $1;
explain execute s2(1);
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
   Index Cond: (id = 1)

As we can see here, a custom plan is being used by optimizer, because of an Index Condition (id = 1) shows an actual value for parameter id.

However, optimizer is going to switch to the generic plan, if we'll re-run prepared SQL statement 4 more times with any random values for id variable:

execute s2(2);
...
execute s2(3);
...
execute s2(4);
...
execute s2(5);
...
explain execute s2(6);
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
   Index Cond: (id = $1)

Here we can see, bind variable instead of an actual value (Index Cond: (id = $1) and it means that custom plan is being selected by optimizer. Note that calculated cost for both generic and custom plans in above examples is the same.



 

Conclusion, Summary:

Oracle: from generic plan (stored in Library Cache) -> goes down to custom plan (only if required)
Postgres: from custom plan -> to generic (locally cached for a process/session)

Oracle: Global Cache is always ON, Develpers just need not to forget to use bind variables in the code.

PosgreSQL: No Global Cache in Postgres, and it's up to Developer, if they want to prepare and reuse code, and when to use bind variables.

Oracle: Due to cache size limitations, risks of dropping existing 'good' plans from it, and involved concurrency/locking mechanisms, Library Cache becomes an expensive system-wide shared resource, and it would be reasonable to minimize unnecessary writing into it.

PosgreSQL: Each process has to parse all it's querires on it's own,
however Hard Parse is a lot more common and not a critical situation for Prosgres Smaller one-off SQL parses produce no overhead (as there is no attemp to look-up and write plan into Global cache).

Two different approaches in processing and caching SQL plans and SQL statements in Oracle and PostgreSQL RDBMS have their Pros and Cons.

Software Developers, Solution Architects and Database Administrators should be aware of these differences in the technology and take them into consideration, when developing new or optimizing existing applications systems.



references:

based on:


Original article (in russian language)

SQL processing in Oracle documentation

Oracle cardinality feedback feature explanation

SQL query parsing in PostgreSQL


Be the first to reply