Oakies Blog Aggregator

Jonathan Lewis's picture

Lost time

Here’s a little puzzle that came up in the ODC database forum yesterday – I’ve got a query that has been captured by SQL Monitor, and it’s taking much longer to run than it should but the monitoring report isn’t telling me what I need to know about the time.

Here’s a little model to demonstrate the problem – I’m going to join a table to itself (the self join isn’t a necessary feature of the demonstration, I’ve just been a bit lazy in preparing data). Here’s a (competely truthful) description of the table:

SQL> desc t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 MOD_1000                               NUMBER
 V1                                     VARCHAR2(40)
 V2                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> select num_rows, blocks from user_tables where table_name = 'T1';

  NUM_ROWS     BLOCKS
---------- ----------
    400000       7798

1 row selected.


And here’s the text version of the output I get from dbms_monitor.report_sql_monitor() for the query running on 18.3.0.0


Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  TEST_USER (261:36685)
 SQL ID              :  g6j671u7zc9mn
 SQL Execution ID    :  16777218
 Execution Started   :  03/21/2019 08:54:56
 First Refresh Time  :  03/21/2019 08:54:56
 Last Refresh Time   :  03/21/2019 08:55:17
 Duration            :  21s
 Module/Action       :  MyModule/MyAction
 Service             :  SYS$USERS
 Program             :  sqlplus@linux183.localdomain (TNS V1-V3)
 Fetch Calls         :  2

Global Stats
==========================================================================
| Elapsed |   Cpu   |    IO    | PL/SQL  | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Time(s) | Calls |  Gets  | Reqs | Bytes |
==========================================================================
|      21 |      21 |     0.04 |    0.07 |     2 |     3M |  215 | 180MB |
==========================================================================

SQL Plan Monitoring Details (Plan Hash Value=83896840)
==================================================================================================================================================
| Id |       Operation       | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity | Activity Detail |
|    |                       |      | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |   (# samples)   |
==================================================================================================================================================
|  0 | SELECT STATEMENT      |      |         |      |        21 |     +1 |     1 |        1 |      |       |     . |          |                 |
|  1 |   FILTER              |      |         |      |        21 |     +1 |     1 |        1 |      |       |     . |          |                 |
|  2 |    HASH JOIN OUTER    |      |     400 | 2014 |        21 |     +1 |     1 |      400 |      |       |   2MB |          |                 |
|  3 |     TABLE ACCESS FULL | T1   |     400 | 1005 |         1 |     +1 |     1 |      400 |   62 |  60MB |     . |          |                 |
|  4 |     TABLE ACCESS FULL | T1   |    400K | 1001 |        21 |     +1 |     1 |     400K |      |       |     . |          |                 |
==================================================================================================================================================

As you can see the total elapsed time 21 seconds of which the CPU time is the whole 21 seconds.

This seems a little large for a simple hash join so we should look at the “Activity Detail” section of the plan because that will report any ASH samples that can be found for this execution of this query. And that’s where the problem lies: despite using 21 CPU seconds in 21 seconds there are no ASH samples for the execution! Moreover – looking for other numerical oddities – the plan says we read 60MB in 62 read requests (that’s the first tablescan of of the 7,798 blocks of t1), but the summary says we read 180MB – where did we lose (or gain) 120MB ?

It might help to see the query (which is why I didn’t show it in the output above) and it might help to see the predicate section (which SQL Monitor doesn’t report). So here’s the query and its plan, with the resulting predicate section, pulled from memory:


SQL_ID  g6j671u7zc9mn, child number 0
-------------------------------------
select  /*+ monitor */  t1a.mod_1000, t1b.mod_1000 from  t1 t1a,  t1
t1b where  t1a.mod_1000 = 500 and t1b.id(+) = t1a.id and
nvl(t1b.mod_1000,0) + f2(t1a.id) + f3(t1a.id) > 0

Plan hash value: 83896840

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |  2014 (100)|          |
|*  1 |  FILTER             |      |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |      |   400 |  7200 |  2014   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |   400 |  3600 |  1005   (3)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |   400K|  3515K|  1001   (3)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NVL("T1B"."MOD_1000",0)+"F2"("T1A"."ID")+"F3"("T1A"."ID")>0)
   2 - access("T1B"."ID"="T1A"."ID")
   3 - filter("T1A"."MOD_1000"=500)


Notice the two function calls that are part of the FILTER operation. That’s where the time is going – but I don’t think you can infer that from the SQL Monitor report (which rather suggests that the problem might be with the second full tablescan of t1 at operation 4).

Functions f2() and f3() both do a table scan of another table that is as large as t1 – and it looks as if they’re both going to be called 400 times, which is a lot of work. The 120MB of “lost” reads is the first table scan of each of the two tables, the 3M buffer gets (in the summary, if you didn’t notice it) is from the repeated tablescans as each row comes out of the hash join and the filter is applied.

Why doesn’t the report show us the CPU (and disk) samples? Because it’s querying ASH (v$active_session_history) by the SQL_ID of the principle SQL statement – and the work is being done by two other statements with different SQL_IDs.

Since it’s PL/SQL functions doing all the work why isn’t the 21 CPU seconds showing up in the “PL/SQL time(s)” summary figure? Because the time isn’t being spent in PL/SQL, it’s being spent in the SQL being run by the PL/SQL.

Just to finish off, let’s try to find the SQL being run by the PL/SQL. I’ll post the script to recreate the example at the end of the posting but for the moment I’ll just show you the query against v$sql that I ran to extract the SQL that’s embedded in the functions:


select  sql_id, executions, disk_reads, buffer_gets, sql_text
from    V$sql 
where   sql_text like 'SELECT%T2%' 
or      sql_text like 'SELECT%T3%'
;

SQL_ID        EXECUTIONS DISK_READS BUFFER_GETS SQL_TEXT
------------- ---------- ---------- ----------- --------------------------------------------------
12ytf1rry45d9        400       7683     3072817 SELECT MAX(ID) FROM T2 WHERE MOD_1000 = :B1
85dmsgqg3bh4w          1       7680        7698 SELECT MAX(ID) FROM T3 WHERE MOD_1000 = :B1

As you can see, there are roughly 120MB of disk I/O and 3M buffer gets due to these two statement – and one of them has run the 400 times we expected. It looks as if Oracle has done a cute little optimisation with the other function, though. If you look at the predicate it says:

  • NVL(“T1B”.”MOD_1000″,0)+”F2″(“T1A”.”ID”)+”F3″(“T1A”.”ID”)>0

It looks as if Oracle has either cached the result of the f3() function call (in which case why didn’t it also cache the f2() result) or it’s stopped evaluating the predicate as soon as the running total exceeded zero (but that might be a problem since f3() could return a negative number !). I don’t know exactly why Oracle is doing what it’s doing – but if I reverse the order of the functions in the predicates the f3() query will run 400 times and the f2() query will run once.

Update

Following the comment below from “anonymous”, pointing out that the “Active” SQL Monitor (and, of course, the equivalent OEM screen) has a section showing the impact of everything the session has been doing while the query ran I re-ran my call to dbms_monitor.report_sql_monitor() with the “type” parameter set to ‘ACTIVE’ rather than ‘TEXT’. After spooling this to a text file (set linesize 255, set pagesize 0) with the suffix “html” and trimming the top and bottom off so that the file started and ended with opening and closing html tags, I opened it in Firefox.

You have to have Adobe Flash Player for this to work, and you have to be connected to the Internet as the file contains content that will call to Oracle’s home domain. After getting Adobe flash to work, here’s a snapshot of the resulting display:

https://jonathanlewis.files.wordpress.com/2019/03/active_monitor.jpg?w=1... 150w, https://jonathanlewis.files.wordpress.com/2019/03/active_monitor.jpg?w=3... 300w, https://jonathanlewis.files.wordpress.com/2019/03/active_monitor.jpg?w=7... 768w, https://jonathanlewis.files.wordpress.com/2019/03/active_monitor.jpg 1820w" sizes="(max-width: 1024px) 100vw, 1024px" />

The print may be a little small, but in the lower half of the screen (which usually shows the plan with execution statistics) I’ve selected the “Activity” tab, and this has produced a graphic display of all the samples captured for the session while the query was running. I took this report after re-running the test case, but I had swapped the order of the two functions in the interim, so this output is showing that (from Oracle’s perspective) all the work done during the execution of the query was done by a statement with SQL_ID 85dmsgqg3bh4w.

Appendix

If you want to re-run some tests here’s the code I used to create the demo:


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

-- alter table t1 add constraint t1_pk primary key(id);

create table t2 nologging as select * from t1;
create table t3 nologging as select * from t1;

create function f2 (i_in number) return number
as
        m_ret number;
begin
        select max(id)
        into    m_ret
        from    t2
        where   mod_1000 = i_in
        ;

        return m_ret;
end;
/

create function f3 (i_in number) return number
as
        m_ret number;
begin
        select  max(id)
        into    m_ret
        from    t3
        where   mod_1000 = i_in
        ;

        return m_ret;
end;
/


begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1'
        );
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T2',
                method_opt  => 'for all columns size 1'
        );
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T3',
                method_opt  => 'for all columns size 1'
        );
end;
/

alter system flush buffer_cache;

variable b1 number
variable b2 number

set timing on

select
        /*+ monitor */
        t1a.mod_1000, t1b.mod_1000
from
        t1      t1a,
        t1      t1b
where
        t1a.mod_1000 = 500
and     t1b.id(+) = t1a.id
and     nvl(t1b.mod_1000,0) + f3(t1a.id) + f2(t1a.id) > 0
;


One interesting little detail, if you try the tests, is that the join has to be an outer join for the FILTER operation to appear.

Richard Foote's picture

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 9-12 July 2019 !!

I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Webinar will run between 9-12 July 2019 (6pm-10pm AEST): Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST):  This is a must attend seminar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  […]

dbakevlar's picture

Oracle vs. SQL Server Architecture

There are a lot of DBAs that are expected to manage both Oracle and MSSQL environments. This is only going to become more common as database platforms variations with the introduction of the cloud continue. A database is a database in our management’s world and we’re expected to understand it all.

Its not an easy topic, but I’m going to post on it, taking it step by step and hopefully the diagrams will help. Its also not an apple to apple comparison, so hopefully, but starting at the base and working my way into it with as similar as comparisons as I’m able to with features, it will make sense for those out there that need to understand it.

We have a number of customers that are migrating Oracle to Azure and many love Oracle and want to keep their Oracle database as is, just bringing their licenses over to the cloud. The importance of this is they may have Azure/SQL DBAs managing them, so I’m here to help.

To begin, let’s start with a diagram that I *believe* best compares the basic, (and pretty high level) comparison between the two database platforms:

https://dbakevlar.com/wp-content/uploads/2019/03/oracle_sql-300x164.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/03/oracle_sql-768x420.jpg 768w" sizes="(max-width: 1024px) 100vw, 1024px" />
Oracle and SQL Server

Instance vs. Database

The first thing you’ll notice is what Oracle refers to as an INSTANCE is different to what SQL Server calls one.

Oracle’s instance is most closely related to what SQL Server calls their database, (although it includes the files that Oracle includes in their description, too) and the Oracle home is *relatively* SQL Server’s version of an instance.

Please also note that the Oracle architecture in the diagram isn’t 12c+ multi-tenant, a RAC, (Real Application Cluster) or has an ASM, (Automatic Storage Management) secondary instance managing the database files. All of this we’ll take on in further posts, but for today, we’ll stick to the generic, traditional architecture and simplest installation.

Background Processes

The second major difference is in the way of background processing. Where Oracle has an Oracle executable and SQL Server has one for it’s database engine, too, Oracle also has numerous background processes running for each database. These processes each perform a job or back up another process as part of a secondary responsibility. SQL Server is multi-threaded architecture and although I can quickly assess what threads are performing what responsibilities inside the database, I’ve been unable to assess this from the server level tools, where in Oracle, each process is clearly named at the OS level of its description.

Two of the major background processes are the PMON, (Process Monitor) and SMON, (System Monitor, but you will hear some people refer to it as the Session Monitor). If either of these processes die or are killed, the database instance, (i.e. the running processes and accessibility) will discontinue.

Other important background processes, like RECO, (Recovery) DBWR, (Database Writer) LGWR, (Log Writer) QMNC, (Queueing Coordinator) and CKPT, (Checkpoint) address important and obvious responsibilities in a relational database. Most of the time, you can kill these individual processes and the database will restart the process and recover without the instance failing.

Performance Data Collection

There are also two memory processes, MMON, (Manageability manager) and MMNL, (manageability light) that are memory allocations and processing isolated to the Automatic Workload Repository, (AWR) and Active Session History, (ASH). For those in the MSSQL world, these are like the Query Store and Dynamic Management Views, (DMV) performance and session data. One of the important design features of the memory buffer isolated for this work is that it writes one way while the users read the other, eliminating much, if any locking. There is both the V$ view data and then a set of aggregated snapshots, (AWR) and samples, (ASH, also written to the snapshots) written to the AWR repository into the SYSAUX Tablespace.

Tablespace = Filegroups

There’s that funny word, too- “Tablespace”. A tablespace isn’t much different than “Filegroups” in MSSQL. Its just a way of grouping logical objects into a logical space, inside a datafile.

With the introduction of multi-tenant, more Oracle emphasis is shifting from schema centric data to having separate tenants, i.e. pluggable databases. This means that the architecture is becoming more similar to SQL/Azure DB, along with other multi-tenant databases and with that, simpler datafile and tablespace design. Having all the data in one tablespace isn’t viewed as negatively as it once was, along with simplifying the management of data, databases and development.

Control Files

Control Files are the God controls for the database. They are binary files that contain everything from transactional status in the database, undo and redo sequence to physical status of data files. Without a control file, a DBA is in a world of hurt to recover an Oracle database. Having mirrored copies of the control file is one of the first things a DBA learns as part of DBA 101.

Redo Logs

These are another important aspect of Oracle database architecture. Each database contains an UNDO tablespace that tracks all undo if something is rolled back. All of this undo, along with any redo, is written to the REDO logs. These are created in sets, just like transaction logs to handle ongoing workloads, while a second is archiving work and another is available. The busier the database, the larger and more numerous the redo logs are. These are also mirrored in case of corruption or loss, as they are necessary for recovery to undo and redo a database back to an assessible state.

The redo logs are written to archive logs on a regular interval to ensure the database can be recovered for PIT, (Point in Time) recovery situations from backup.

SP and Password Files

The SP, (Parameter) file is binary, but was once a text file called simply the pfile, so you may hear this term as well for those still using it. Similar to the integrated SP_CONFIGURE, it is used to set up the parameters for the database configuration, including version, naming, etc. The file is included in backups and can be copied to the text, pfile version easily.

These parameters can be updated from the database management console, (UI) or the command line with the “in memory” option, which means a database cycle will be required to solidify the change or for many, scope=both, which would make the change immediate and written to the file.

The Password file is configuration of password management for the database, including remote connectivity access for management. A shared option means that it can be shared among databases, eliminating extra management and setting the SYSDBA management of the database.

PGA- What is it?

PGA, (Process Global Area) is an allocation of memory used for sorting, hashing and PL/SQL tables, among a few things. As Oracle doesn’t have a TEMP database to perform these tasks and 99% of indexes are heap, not clustered, having this memory is essential for increased performance. The memory is outside of the SGA, (System Global Area) and its also outside of the configuration for SGA memory. This is important when a DBA is sizing out a machine and knowing that there are distinct limits per process, process type and workload for PGA allocation, no matter how much PGA is set in the SP File.

Why is the sizing of the PGA important? If you don’t have enough PGA allocated or if the SQL is written poorly, a process won’t run inside of memory and will swap to TEMP tablespace. Unlike a TEMP database, a tablespace is disk allocation and disk, unless SSD, if very slow.

I’m hoping this was a good introduction into how Oracle is similar and different from SQL Server. I’ll try to continue with this topic and dig in deeper as we go along and hopefully I didn’t melt anyone’s brain.



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBAKevlar [Oracle vs. SQL Server Architecture], All Right Reserved. 2019.

Kamil Stawiarski's picture

Golang chatting with Oracle

Python is awesome. I like Python very much, but there are some cases where Python’s performance is just not enough. What other options do we have?
Well, there’s for example GoLang

Problem with GoLang is that there is not a lot on the Internet about using it with Oracle Databases. Let me show you some basic steps for configuring the environment.

The first step is to install GoLang – instructions can be found in here: https://golang.org/doc/install

To connect to Oracle, you need Oracle Client (it can be Oracle Instant Client).
I will configure my environment on my MAC and use Oracle Instant Client for MAC

piclerick:~ inter$ mkdir oracle
piclerick:~ inter$ cd oracle
piclerick:oracle inter$ ls
piclerick:oracle inter$ unzip ~/Downloads/instantclient-basic-macos.x64-18.1.0.0.0.zip
Archive:  /Users/inter/Downloads/instantclient-basic-macos.x64-18.1.0.0.0.zip
  inflating: instantclient_18_1/BASIC_README
  inflating: instantclient_18_1/adrci
  inflating: instantclient_18_1/genezi
    linking: instantclient_18_1/libclntsh.dylib  -> libclntsh.dylib.18.1
  inflating: instantclient_18_1/libclntsh.dylib.18.1
  inflating: instantclient_18_1/libclntshcore.dylib.18.1
  inflating: instantclient_18_1/libnnz18.dylib
    linking: instantclient_18_1/libocci.dylib  -> libocci.dylib.18.1
  inflating: instantclient_18_1/libocci.dylib.18.1
  inflating: instantclient_18_1/libociei.dylib
  inflating: instantclient_18_1/libocijdbc18.dylib
  inflating: instantclient_18_1/libons.dylib
  inflating: instantclient_18_1/liboramysql18.dylib
   creating: instantclient_18_1/network/
  inflating: instantclient_18_1/ojdbc8.jar
  inflating: instantclient_18_1/ucp.jar
  inflating: instantclient_18_1/uidrvci
  inflating: instantclient_18_1/xstreams.jar
   creating: instantclient_18_1/network/admin/
  inflating: instantclient_18_1/network/admin/README
finishing deferred symbolic links:
  instantclient_18_1/libclntsh.dylib -> libclntsh.dylib.18.1
  instantclient_18_1/libocci.dylib -> libocci.dylib.18.1
piclerick:oracle inter$ unzip ~/Downloads/instantclient-sqlplus-macos.x64-18.1.0.0.0.zip
Archive:  /Users/inter/Downloads/instantclient-sqlplus-macos.x64-18.1.0.0.0.zip
  inflating: instantclient_18_1/SQLPLUS_README
  inflating: instantclient_18_1/glogin.sql
  inflating: instantclient_18_1/libsqlplus.dylib
  inflating: instantclient_18_1/libsqlplusic.dylib
  inflating: instantclient_18_1/sqlplus
piclerick:oracle inter$ unzip ~/Downloads/instantclient-tools-macos.x64-18.1.0.0.0.zip
Archive:  /Users/inter/Downloads/instantclient-tools-macos.x64-18.1.0.0.0.zip
  inflating: instantclient_18_1/TOOLS_README
  inflating: instantclient_18_1/exp
  inflating: instantclient_18_1/expdp
  inflating: instantclient_18_1/imp
  inflating: instantclient_18_1/impdp
  inflating: instantclient_18_1/libnfsodm18.dylib
  inflating: instantclient_18_1/sqlldr
  inflating: instantclient_18_1/wrc
piclerick:oracle inter$ unzip ~/Downloads/instantclient-sdk-macos.x64-18.1.0.0.0-2.zip
Archive:  /Users/inter/Downloads/instantclient-sdk-macos.x64-18.1.0.0.0-2.zip
   creating: instantclient_18_1/sdk/
   creating: instantclient_18_1/sdk/admin/
  inflating: instantclient_18_1/sdk/admin/oraaccess.xsd
   creating: instantclient_18_1/sdk/demo/
  inflating: instantclient_18_1/sdk/demo/cdemo81.c
  inflating: instantclient_18_1/sdk/demo/demo.mk
  inflating: instantclient_18_1/sdk/demo/occidemo.sql
  inflating: instantclient_18_1/sdk/demo/occidemod.sql
  inflating: instantclient_18_1/sdk/demo/occidml.cpp
  inflating: instantclient_18_1/sdk/demo/occiobj.cpp
  inflating: instantclient_18_1/sdk/demo/occiobj.typ
  inflating: instantclient_18_1/sdk/demo/oraaccess.xml
  inflating: instantclient_18_1/sdk/demo/setuporamysql.sh
   creating: instantclient_18_1/sdk/include/
  inflating: instantclient_18_1/sdk/include/ldap.h
  inflating: instantclient_18_1/sdk/include/nzerror.h
  inflating: instantclient_18_1/sdk/include/nzt.h
  inflating: instantclient_18_1/sdk/include/occi.h
  inflating: instantclient_18_1/sdk/include/occiAQ.h
  inflating: instantclient_18_1/sdk/include/occiCommon.h
  inflating: instantclient_18_1/sdk/include/occiControl.h
  inflating: instantclient_18_1/sdk/include/occiData.h
  inflating: instantclient_18_1/sdk/include/occiObjects.h
  inflating: instantclient_18_1/sdk/include/oci.h
  inflating: instantclient_18_1/sdk/include/oci1.h
  inflating: instantclient_18_1/sdk/include/oci8dp.h
  inflating: instantclient_18_1/sdk/include/ociap.h
  inflating: instantclient_18_1/sdk/include/ociapr.h
  inflating: instantclient_18_1/sdk/include/ocidef.h
  inflating: instantclient_18_1/sdk/include/ocidem.h
  inflating: instantclient_18_1/sdk/include/ocidfn.h
  inflating: instantclient_18_1/sdk/include/ociextp.h
  inflating: instantclient_18_1/sdk/include/ocikpr.h
  inflating: instantclient_18_1/sdk/include/ociver.h
  inflating: instantclient_18_1/sdk/include/ocixmldb.h
  inflating: instantclient_18_1/sdk/include/ocixstream.h
  inflating: instantclient_18_1/sdk/include/odci.h
  inflating: instantclient_18_1/sdk/include/orastruc.h
  inflating: instantclient_18_1/sdk/include/oratypes.h
  inflating: instantclient_18_1/sdk/include/oraxml.h
  inflating: instantclient_18_1/sdk/include/oraxml.hpp
  inflating: instantclient_18_1/sdk/include/oraxmlcg.h
  inflating: instantclient_18_1/sdk/include/oraxsd.h
  inflating: instantclient_18_1/sdk/include/oraxsd.hpp
  inflating: instantclient_18_1/sdk/include/ori.h
  inflating: instantclient_18_1/sdk/include/orid.h
  inflating: instantclient_18_1/sdk/include/orl.h
  inflating: instantclient_18_1/sdk/include/oro.h
  inflating: instantclient_18_1/sdk/include/ort.h
  inflating: instantclient_18_1/sdk/include/xa.h
  inflating: instantclient_18_1/sdk/include/xml.h
  inflating: instantclient_18_1/sdk/include/xml.hpp
  inflating: instantclient_18_1/sdk/include/xmlctx.hpp
  inflating: instantclient_18_1/sdk/include/xmldav.h
  inflating: instantclient_18_1/sdk/include/xmldf.h
  inflating: instantclient_18_1/sdk/include/xmlerr.h
  inflating: instantclient_18_1/sdk/include/xmlev.h
  inflating: instantclient_18_1/sdk/include/xmlotn.h
  inflating: instantclient_18_1/sdk/include/xmlotn.hpp
  inflating: instantclient_18_1/sdk/include/xmlproc.h
  inflating: instantclient_18_1/sdk/include/xmlsch.h
  inflating: instantclient_18_1/sdk/include/xmlsoap.h
  inflating: instantclient_18_1/sdk/include/xmlsoap.hpp
  inflating: instantclient_18_1/sdk/include/xmlsoapc.hpp
  inflating: instantclient_18_1/sdk/include/xmlurl.h
  inflating: instantclient_18_1/sdk/include/xmlxptr.h
  inflating: instantclient_18_1/sdk/include/xmlxsl.h
  inflating: instantclient_18_1/sdk/include/xmlxvm.h
  inflating: instantclient_18_1/sdk/ott
 extracting: instantclient_18_1/sdk/ottclasses.zip
  inflating: instantclient_18_1/sdk/SDK_README

To connect to Oracle, we will use goracle – it is a package which is a database/sql/driver.Driver for connecting to Oracle DB, using Anthony Tuininga’s excellent OCI wrapper, ODPI-C.

Let’s get the package:

piclerick:~ inter$ go get gopkg.in/goracle.v2

Now we can set the environment variables and create our source code:

piclerick:~ inter$ export  ORACLE_HOME=/Users/inter/oracle/instantclient_18_1
piclerick:~ inter$ export DYLD_LIBRARY_PATH=/Users/inter/oracle/instantclient_18_1
piclerick:~ inter$ vim chatt_with_oracle.go

The base for our script will look like this:

package main

import (
        "fmt"
        "database/sql"
        "log"
        _ "gopkg.in/goracle.v2"
)

func main() {

}

Please note that we have to import goracle a bit differently than other packages – with "_" prefix. That’s because we will connect to the database like this: sql.Open("goracle", conn). So the "goracle" won’t be used directly in a code but as a string literal to SQL driver.

Let’s create a connection to the database and execute a simple query

package main

import (
        "fmt"
        "database/sql"
        "log"
        _ "gopkg.in/goracle.v2"
)

func main() {
  //connect to database
  db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")

  //close database connection at the end of program execution
  defer db.Close()

  //check if there were some errors during connection
  if err != nil {
        log.Panic(err)
  }

  //execute a query and get pointer to the rows
  rows, err := db.Query("select count(1) from employees")

  //check if there were some errors during execution
  if err != nil {
        log.Panic(err)
  }

  //define variable for the output
  var emps_cnt int

  //if there are some rows - put the output to the variable address by reference
  if rows.Next() {
        rows.Scan(&emps_cnt)
  }

  fmt.Println(emps_cnt)

}

Now we can compile our program and execute it:

piclerick:~ inter$ go build chatt_with_oracle.go
piclerick:~ inter$ ./chatt_with_oracle
107

Great! Let’s add more rows to the output and additional bind variable:

package main

import (
	"fmt"
	"database/sql"
	"log"
	_ "gopkg.in/goracle.v2"
)

func main() {
  //connect to database
  db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")

  //close database connection at the end of program execution
  defer db.Close()

  //check if there were some errors during connection
  if err != nil {
	log.Panic(err)
  }

  //execute a query and get pointer to the rows
  deptId := 20
  rows, err := db.Query("select first_name, last_name, salary from employees where department_id=:1", deptId)

  //check if there were some errors during execution
  if err != nil {
	log.Panic(err)
  }

  //define variable for the output
  var salary int
  var fname, lname string

  //if there are some rows - put the output to the variable address by reference
  for rows.Next() {
  	rows.Scan(&fname, &lname, &salary)
        fmt.Println(fname, lname, salary)
  }

}

And here’s the result:

piclerick:~ inter$ ./chatt_with_oracle
Michael Hartstein 26000
Pat Fay 12000

If you want to execute PL/SQL function or procedure and get the results, you can use sql.Out interface. For example, let’s create a sample PL/SQL function:

SQL> ed
Zapisano file afiedt.buf

  1  create or replace function f_get_dept_avg_sal(p_dept_id number) return number is
  2    v_avg_sal number;
  3  begin
  4    select avg(salary) into v_avg_sal
  5    from employees e
  6    where e.department_id=p_dept_id;
  7    return v_avg_sal;
  8* end;
  9  /

Utworzono funkcje.

Now we can add a simple PL/SQL block to our GO function and use Exec method and sql.Out interface:

package main

import (
	"fmt"
	"database/sql"
	"log"
	_ "gopkg.in/goracle.v2"
)

func main() {
  //connect to database
  db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")

  //close database connection at the end of program execution
  defer db.Close()

  //check if there were some errors during connection
  if err != nil {
	log.Panic(err)
  }

  //execute a query and get pointer to the rows
  deptId := 20
  rows, err := db.Query("select first_name, last_name, salary from employees where department_id=:1", deptId)

  //check if there were some errors during execution
  if err != nil {
	log.Panic(err)
  }

  //define variable for the output
  var salary int
  var fname, lname string

  //if there are some rows - put the output to the variable address by reference
  for rows.Next() {
	rows.Scan(&fname, &lname, &salary)
        fmt.Println(fname, lname, salary)
  }

  //define variable for average salary
  var avgSal float64

  //execute anonymous block and use sql.Out interface for extracting bind variable value
  db.Exec("begin :1 := f_get_dept_avg_sal(:2); end;", sql.Out{Dest: &avgSal}, deptId)

  fmt.Printf("AVG salary in department %d is %f\n", deptId, avgSal)

}

And here’s the result:

piclerick:~ inter$ go build chatt_with_oracle.go
piclerick:~ inter$ ./chatt_with_oracle
Michael Hartstein 26000
Pat Fay 12000
AVG salary in department 20 is 19000.000000

And it’s simple as that </p />
</p></div>
    <div class=»

martin.bach's picture

Using dbca to create a physical standby database

While investigating new options I discovered with dbca for the previous article I noticed that it’s now possible to use Oracle’s Database Creation Assistant (dbca) to create a physical standby database using the -createDuplicateDB flag.

I wanted to know how easily this can be done on my Oracle 18.5.0 system. I have 2 VMs, server3 and server4 running Oracle Linux 7.6. I created the primary database on server3. The database name is NCDB with the db_unique_name set to SITEA. I also use SITEA as the database SID to prevent confusion. My physical standby database with db_unique_name SITEB will reside on server4.

The setup is kept as simple as possible to avoid any unintended complications. In other words, this is a lab setup, not something you’d entertain using for any other purpose. As always, this is a tech post and not a piece about licencing. It is up to you to ensure you aren’t performing any tasks that you aren’t licensed for.

Creating the primary database

The primary database can be created using dbca as always. Unlike the last post, this one uses ASM, not a filesystem to show how easy it is to do both.

The primary database is a non-Container Database (NCDB). I intend to create the primary instance as SITEA (Site A) with the standby named SITEB. This way no one gets confused when the database runs in primary role in the designated disaster recovery location. I haven’t seen gdbName <> sid/db_unique_name in many single instance deployments and appreciate that might be unfamiliar but bear with me. I’ll try to make this as transparent as possible.

Invoking dbca

Enough talking, let’s create the database:

[oracle@server3 ~]$ dbca -silent -createDatabase \
> -gdbName NCDB \
> -templateName lab_template.dbc \
> -sid SITEA \
> -enableArchive true -archiveLogMode auto \
> -memoryMgmtType auto_sga \
> -createAsContainerDatabase false \
> -recoveryAreaDestination +RECO \
> -totalMemory 2048 \
> -datafileDestination +DATA \
> -initParams db_unique_name=SITEA,dg_broker_start=true \
> -customScripts add_srls_dbca.sql

[...]

Prepare for db operation
10% complete
Registering database with Oracle Restart
14% complete
Copying database files
43% complete
Creating and starting Oracle instance
45% complete
49% complete
53% complete
56% complete
62% complete
Completing Database Creation
68% complete
70% complete
71% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/SITEA.
Database Information:
Global Database Name:SITEA
System Identifier(SID):SITEA
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SITEA/SITEA1.log" for further details.

A few words of explanation: As I said I use SITEA as db_unique_name and the database’s SID. In Oracle Managed Files (OMF) deployments a file name references the db_unique name, and I wanted my data files, temp files and log files all to be in +DATA/SITEA/. db_unique_name defaults to db_name, and not changing it would have resulted in the database being created in +DATA/NCDB and that’s not what I wanted.

I decided to include starting the Data Guard broker as part of the initialisation parameters. Archiving is enabled right from the start as well.

The custom script you see referenced creates the necessary number of standby redo logs. This will save me a bit of work, because RMAN is smart. When duplicating the database for use as a standby it creates standby redo logs (SRL) for me if if finds SRLs in the target database. Yet another thing less for me to worry about.

Implications of using gdbname and sid (+ db_unique_name) in dbca

If you haven’t used a SID <> gDBName combination before, you might feel about unsure about how Oracle creates things. First of all, it created the database with the db_name you passed to dbca as gdbname. In this post that’s NCDB. Normally, if you grep’d the operating system’s process table for NCDB you would find all the instance’s background processes. With my dbca command however (where an Oracle SID <> gdbname is provided) you won’t find anything though. Grep for the ORACLE_SID “SITEA” instead. Likewise, if you want to create a bequeath connection to the database, set your ORACLE_SID to SITEA.

Since this is Oracle Restart the database is registered with Grid Infrastructure under its db_unique_name/ORACLE_SID combination in the RDBMS home:

[oracle@server3 ~]$ srvctl config database
SITEA
[oracle@server3 ~]$ srvctl config database -db SITEA
Database unique name: SITEA
Database name: NCDB
Oracle home: /u01/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SITEA/PARAMETERFILE/spfile.273.1003226559
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group:
OSOPER group:
Database instance: SITEA
[oracle@server3 ~]$

Interacting with the database via srvctl requires you to use the db_unique_name SITEA as shown.

This is my preferred way to set things up these days. I’d like to invite you to critically reflect about the approach I took. I prefer it over other designations such as “PROD” and “STDBY”.

Creating the standby database

With the primary database created, archivelog mode enabled plus a working Fast Recovery Area I can move on to creating the physical standby database.

Before I do this a few small changes have to be made to my network configuration. I always add TNS entries for both primary and standby database in the “primary” host’s $ORACLE_HOME/network/admin/tnsnames.ora file and ship this across to the “standby” host. They are needed later. With the preparation done, I proceed to calling dbca:

[oracle@server4 ~]$ dbca -silent -createDuplicateDB \
> -gdbName NCDB \
> -sid SITEB \
> -initParams db_create_file_dest=+DATA,db_recovery_file_dest=+RECO,dg_broker_start=true \
> -primaryDBConnectionString server3:1521/SITEA \
> -createAsStandby \
> -dbUniqueName SITEB

[ ... ]

Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SITEB/SITEB1.log" for further details.

So far so good. What did dbca do? And more importantly, how did it create the standby database? I didn’t have a backup of NCDB/SITEA at the time I called dbca on server4 so it must have performed a duplicate … from active database. Looking at the dbca trace in $ORACLE_BASE/cfgtoollogs/dbca/SITEB I can confirm this. After wading through a lot of messages, here’s the relevant bit:

INFO: Mar 11, 2019 12:14:04 PM oracle.assistants.dbca.driver.backend.steps.RmanDuplicateStep executeImpl
INFO: Running rman duplicate command:run {
set newname for datafile 1 to new;
set newname for datafile 3 to new;
set newname for datafile 4 to new;
set newname for datafile 7 to new;
duplicate target database
for standby
from active database
dorecover
nofilenamecheck;
}

This might be an issue for some users because it requires a direct connection to the source database. I don’t know if RMAN might use the “from service … using backupset …” clause if you provided an offline backup.

The files I care about are all in the right place:

SQL> select name from v$datafile union all
2 select name from v$tempfile union all
3 select member from v$logfile union all
4 select value from v$parameter2 where name = 'control_files';

NAME
-------------------------------------------------------
+DATA/SITEB/DATAFILE/system.268.1003228125
+DATA/SITEB/DATAFILE/sysaux.273.1003228127
+DATA/SITEB/DATAFILE/undotbs1.272.1003228129
+DATA/SITEB/DATAFILE/users.271.1003228129
+DATA/SITEB/TEMPFILE/temp.262.1003228149
+DATA/SITEB/ONLINELOG/group_3.266.1003228137
+RECO/SITEB/ONLINELOG/group_3.310.1003228137
+DATA/SITEB/ONLINELOG/group_2.267.1003228137
+RECO/SITEB/ONLINELOG/group_2.258.1003228137
+DATA/SITEB/ONLINELOG/group_1.270.1003228135
+RECO/SITEB/ONLINELOG/group_1.259.1003228135
+DATA/SITEB/ONLINELOG/group_4.265.1003228137
+RECO/SITEB/ONLINELOG/group_4.311.1003228137
+DATA/SITEB/ONLINELOG/group_5.264.1003228139
+RECO/SITEB/ONLINELOG/group_5.312.1003228139
+DATA/SITEB/ONLINELOG/group_6.274.1003228139
+RECO/SITEB/ONLINELOG/group_6.313.1003228141
+DATA/SITEB/ONLINELOG/group_7.263.1003228147
+RECO/SITEB/ONLINELOG/group_7.314.1003228147
+DATA/SITEB/CONTROLFILE/current.260.1003228117
+RECO/SITEB/CONTROLFILE/current.260.1003228117

21 rows selected.

As reported by other bloggers, dbca creates a “temporary” listener that it doesn’t remove after the database creation process is completed. I specifically wanted to see if this is the case with Oracle Restart as well and yes, it is:

[oracle@server4 SITEB]$ ps -ef | grep tnslsnr
oracle 1125 1 0 10:27 ? 00:00:00 /u01/app/oracle/product/18.0.0/dbhome_1/bin/tnslsnr LISTENER20190318102721 -inherit
grid 5837 1 0 09:01 ? 00:00:00 /u01/app/grid/product/18.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 7588 31809 0 10:44 pts/1 00:00:00 grep --color=auto tnslsnr

The extra listener didn’t survive a reboot though and the processes should disappear eventually. dbca persists the configuration into $ORACLE_HOME/network/admin/listener.ora though, and those entries should probably be removed. This is especially true with Oracle Restart (and RAC for that matter) because I want all listeners to be controlled from the GRID home, not the RDBMS home.

There are other leftover files in $ORACLE_HOME/dbs as well for which I’d hope dbca removes them one day.

On the plus side dbca registers the database in Oracle Restart for me as part of the work it did:

[oracle@server4 ~]$ srvctl config database -db SITEB
Database unique name: SITEB
Database name: NCDB
Oracle home: /u01/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SITEB/PARAMETERFILE/spfile.275.1003228201
Password file: +DATA/SITEB/orapwsiteb
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group:
OSOPER group:
Database instance: SITEB

It’s good to see that I don’t need to worry about storing password file and spfile in ASM myself, so that job is already taken care off. A few other things need to be changed though. The database is still considered a primary (of which I know it isn’t) and the start option therefore is wrong, too. There are licensing implications opening a standby read only rather than just mounting it when turning on redo apply. Refer to the documentation for more details.

Out of habit I changed the role to PHYSICAL_STANDBY and the start option to MOUNT. This is what I ended up with in my lab, YMMV and depends on how you are licensed.

[oracle@server4 ~]$ srvctl config database -db SITEB
Database unique name: SITEB
Database name: NCDB
Oracle home: /u01/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SITEB/PARAMETERFILE/spfile.275.1003228201
Password file: +DATA/SITEB/orapwsiteb
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group:
OSOPER group:
Database instance: SITEB

Creating the Broker configuration

The standby redo logs have been created by RMAN and with dg_broker_start set to true by dbca for both databases I can now create a Data Guard Broker configuration. With Oracle Restart I don’t have to statically register the databases with the listener anymore. Clusterware takes care of starting/stopping them during role changes.

[oracle@server3 ~]$ dgmgrl
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Mar 18 11:11:55 2019 Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@SITEA
Password:
Connected to "SITEA"
Connected as SYSDBA.
DGMGRL> create configuration autoconf
> primary database is 'SITEA'
> connect identifier is 'SITEA';
Configuration "autoconf" created with primary database "SITEA"
DGMGRL> add database 'SITEB' as connect identifier is 'SITEB';
Database "SITEB" added
DGMGRL> edit database 'SITEA' set property standbyfilemanagement=auto;
Property "standbyfilemanagement" updated
DGMGRL> edit database 'SITEB' set property standbyfilemanagement=auto;
Property "standbyfilemanagement" updated
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - autoconf

Protection Mode: MaxPerformance
Members:
SITEA - Primary database
SITEB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 17 seconds ago)
DGMGRL>

You might want to increase the protection level from MaxPerformance to something that better suits your requirements. Three might be additional changes you want to do within the broker as well. The code output you just saw is the bare minimum I put into place.

Summary

All in all this has been pretty straight forward without too many hiccups along the way. If you want to you can put a lot of those steps into your favourite automation toolkit and you’ll end up with a standby database reasonably quickly provided your environment supports this kind of setup.

jeremy.schneider's picture

PostgresConf 2019 Training Days

https://ardentperf.files.wordpress.com/2019/03/hands-on-lab.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/hands-on-lab.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />It feels like PostgresConf in New York is in full swing, even though the main tracks haven’t even started yet!

(Oh, and by the way, as of this morning I heard there are still day-passes available for those who haven’t yet registered for the conference… and then you can come hear a great session about Wait Events in PostgreSQL this Thursday at 4:20pm!)

The first two days of PostgresConf are summits, tutorials and training sessions. A good chunk of my day today was helping out with Scott Mead’s intensive 3 hour hands-on lab Setting up PostgreSQL for Production in AWS – but outside of that I’ve managed to drop in to a number of other sessions that sounded interesting. I did my best to take down some notes so I could share a few highlights.

Monday March 18

Personally, my favorite session on Monday was Brent Bigonger’s session.  He’s a database engineer at Amazon who was involved in migrating their Inventory Management System to Aurora PostgreSQL (slides available here). I always love hearing good stories (part of why I’ve always been a fan of user groups) – this presentation gave a nice high level overview of the business, a review of the planning and execution process for the migration, and lots of practical lessons learned.

  • Some of the tips were things people are generally familiar with – like NULLs behaving differently and the importance of performance management with a tool like Performance Insights.
  • My favorite tip is getting better telemetry by instrumenting SQL with comments (SELECT /* my-service-call-1234 */ …) which reminded me of something I also read in Baron Sc​hwartz’s recently updated e-book on observable systems: “including implicit data in SQL.”
  • A great new tip (to me) was the idea of creating a heartbeat table as one more safety check in a replication process.  You can get a sense for lag by querying the table and you can also use it during a cutover to get an extra degree of assurance that no data was missed.
  • Another general point I really resonated with: Brent gave a nice reminder that a simple solution which meets the business requirements is better than a sophisticated or complex solution that goes beyond what the business really needs.  I feel tempted on occasion to leverage architectures because they are interesting – and I always appreciate hearing this reiterated!

On the AWS track, aside from Brent’s session, I caught a few others: Jim Mlodgenski giving a deep dive on Aurora PostgreSQL architecture and Jim Finnerty giving a great talk on Aurora PostgreSQL performance tuning and query plan management.  It’s funny, but I think my favorite slide from Finnerty’s talk was actually one of the simplest and most basic; he had a slide that just had high-level list of steps for performance tuning.  I don’t remember the exact list on that slide at the moment, but the essential process: (1) identify to top SQL (2) EXPLAIN to get the plan (3) make improvements to the SQL and (4) test and verify whether the improvements actually had the intended effect.

Other sessions I dropped into:

  • Alvaro Hernandez giving an Oracle to PostgreSQL Migration Tutorial.  I love live demos (goes along with loving hands on labs) and so this session was a hit with me – I wasn’t able to catch the whole thing but I did catch a walk-through of ora2pg.
  • Avinash Vallarapu giving an Introduction to PostgreSQL for Oracle and MySQL DBAs. When I slipped in, he was just wrapping up a section on hot physical backups in PostgreSQL with the pg_basebackup utility.  After that, Avi launched into a section on MVCC in PostgreSQL – digging into transaction IDs and vacuum, illustrated with block dumps and the pageinspect extension.  The part of this session I found most interesting was actually a few of the participant discussions – I heard lively discussions about what extensions are and about comparisons with RMAN and older versions of Oracle.

Tuesday March 19

As I said before, a good chunk of my morning was in Scott’s hands-on lab. If you ever do a hands-on lab with Scott then you’d better look out… he did something clever there: somewhere toward the beginning, if you followed the instructions correctly, then you would be unable to connect to your database!  Turns out this was on purpose (and the instructions actually tell you this) – since people often have this particular problem connecting when they first start on out RDS, Scott figured he’d just teach everyone how to fix it.  I won’t tell you what the problem actually is though – you’ll have to sign up for a lab sometime and learn for yourself.  :)

As always, we had a lot of really interesting discussions with participants in the hands-on lab.  We talked about the DBA role and the shared responsibility model, about new tools used to administer RDS databases in lieu of shell access (like Performance Insights and Enhanced Monitoring), and about how RDS helps implement industry best practices like standardization and automation. On a more technical level, people were interested to learn about the “pgbench” tool provided with postgresql.

In addition to the lab, I also managed to catch part of Simon Riggs’ session Essential PostgreSQL11 Database Administration – in particular, the part about PostgreSQL 11 new features.  One interesting new thing I learned was about some work done specifically around the performance of indexes on monotonically increasing keys.

Interesting Conversations

https://ardentperf.files.wordpress.com/2019/03/img_2736.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/img_2736.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />Of course I learned just as much outside of the sessions as I learned in the sessions.  I ended up eating lunch with Alexander Kukushkin who helped facilitate a 3 hour hands-on session today about Understanding and implementing PostgreSQL High Availability with Patroni and enjoyed hearing a bit more about PostgreSQL at Zalando. Talked with a few people from a government organization who were a long-time PostgreSQL shop and interested to hear more about Aurora PostgreSQL. Talked with a guy from a large financial and media company about flashback query, bloat and vacuum, pg_repack, parallel query and partitioning in PostgreSQL.

And of course lots of discussions about the professional community. Met PostgresConf conference volunteers from California to South Africa and talked about how they got involved in the community.  Saw Lloyd and chatted about the Seattle PostgreSQL User Group.

The training and summit days are wrapping up and now it’s time to get ready for the next three days: keynotes, breakout sessions, exposition, a career fair and more!  I can’t wait.  :)

jeremy.schneider's picture

PostgresConf 2019 Training Days

https://ardentperf.files.wordpress.com/2019/03/hands-on-lab.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/hands-on-lab.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />It feels like PostgresConf in New York is in full swing, even though the main tracks haven’t even started yet!

(Oh, and by the way, as of this morning I heard there are still day-passes available for those who haven’t yet registered for the conference… and then you can come hear a great session about Wait Events in PostgreSQL this Thursday at 4:20pm!)

The first two days of PostgresConf are summits, tutorials and training sessions. A good chunk of my day today was helping out with Scott Mead’s intensive 3 hour hands-on lab Setting up PostgreSQL for Production in AWS – but outside of that I’ve managed to drop in to a number of other sessions that sounded interesting. I did my best to take down some notes so I could share a few highlights.

Monday March 18

Personally, my favorite session on Monday was Brent Bigonger’s session.  He’s a database engineer at Amazon who was involved in migrating their Inventory Management System to Aurora PostgreSQL (slides available here). I always love hearing good stories (part of why I’ve always been a fan of user groups) – this presentation gave a nice high level overview of the business, a review of the planning and execution process for the migration, and lots of practical lessons learned.

  • Some of the tips were things people are generally familiar with – like NULLs behaving differently and the importance of performance management with a tool like Performance Insights.
  • My favorite tip is getting better telemetry by instrumenting SQL with comments (SELECT /* my-service-call-1234 */ …) which reminded me of something I also read in Baron Sc​hwartz’s recently updated e-book on observable systems: “including implicit data in SQL.”
  • A great new tip (to me) was the idea of creating a heartbeat table as one more safety check in a replication process.  You can get a sense for lag by querying the table and you can also use it during a cutover to get an extra degree of assurance that no data was missed.
  • Another general point I really resonated with: Brent gave a nice reminder that a simple solution which meets the business requirements is better than a sophisticated or complex solution that goes beyond what the business really needs.  I feel tempted on occasion to leverage architectures because they are interesting – and I always appreciate hearing this reiterated!

On the AWS track, aside from Brent’s session, I caught a few others: Jim Mlodgenski giving a deep dive on Aurora PostgreSQL architecture and Jim Finnerty giving a great talk on Aurora PostgreSQL performance tuning and query plan management.  It’s funny, but I think my favorite slide from Finnerty’s talk was actually one of the simplest and most basic; he had a slide that just had high-level list of steps for performance tuning.  I don’t remember the exact list on that slide at the moment, but the essential process: (1) identify to top SQL (2) EXPLAIN to get the plan (3) make improvements to the SQL and (4) test and verify whether the improvements actually had the intended effect.

Other sessions I dropped into:

  • Alvaro Hernandez giving an Oracle to PostgreSQL Migration Tutorial.  I love live demos (goes along with loving hands on labs) and so this session was a hit with me – I wasn’t able to catch the whole thing but I did catch a walk-through of ora2pg.
  • Avinash Vallarapu giving an Introduction to PostgreSQL for Oracle and MySQL DBAs. When I slipped in, he was just wrapping up a section on hot physical backups in PostgreSQL with the pg_basebackup utility.  After that, Avi launched into a section on MVCC in PostgreSQL – digging into transaction IDs and vacuum, illustrated with block dumps and the pageinspect extension.  The part of this session I found most interesting was actually a few of the participant discussions – I heard lively discussions about what extensions are and about comparisons with RMAN and older versions of Oracle.

Tuesday March 19

As I said before, a good chunk of my morning was in Scott’s hands-on lab. If you ever do a hands-on lab with Scott then you’d better look out… he did something clever there: somewhere toward the beginning, if you followed the instructions correctly, then you would be unable to connect to your database!  Turns out this was on purpose (and the instructions actually tell you this) – since people often have this particular problem connecting when they first start on out RDS, Scott figured he’d just teach everyone how to fix it.  I won’t tell you what the problem actually is though – you’ll have to sign up for a lab sometime and learn for yourself.  :)

As always, we had a lot of really interesting discussions with participants in the hands-on lab.  We talked about the DBA role and the shared responsibility model, about new tools used to administer RDS databases in lieu of shell access (like Performance Insights and Enhanced Monitoring), and about how RDS helps implement industry best practices like standardization and automation. On a more technical level, people were interested to learn about the “pgbench” tool provided with postgresql.

In addition to the lab, I also managed to catch part of Simon Riggs’ session Essential PostgreSQL11 Database Administration – in particular, the part about PostgreSQL 11 new features.  One interesting new thing I learned was about some work done specifically around the performance of indexes on monotonically increasing keys.

Interesting Conversations

https://ardentperf.files.wordpress.com/2019/03/img_2736.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/img_2736.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />Of course I learned just as much outside of the sessions as I learned in the sessions.  I ended up eating lunch with Alexander Kukushkin who helped facilitate a 3 hour hands-on session today about Understanding and implementing PostgreSQL High Availability with Patroni and enjoyed hearing a bit more about PostgreSQL at Zalando. Talked with a few people from a government organization who were a long-time PostgreSQL shop and interested to hear more about Aurora PostgreSQL. Talked with a guy from a large financial and media company about flashback query, bloat and vacuum, pg_repack, parallel query and partitioning in PostgreSQL.

And of course lots of discussions about the professional community. Met PostgresConf conference volunteers from California to South Africa and talked about how they got involved in the community.  Saw Lloyd and chatted about the Seattle PostgreSQL User Group.

The training and summit days are wrapping up and now it’s time to get ready for the next three days: keynotes, breakout sessions, exposition, a career fair and more!  I can’t wait.  :)

jeremy.schneider's picture

PostgresConf 2019 Training Days

https://ardentperf.files.wordpress.com/2019/03/hands-on-lab.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/hands-on-lab.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />It feels like PostgresConf in New York is in full swing, even though the main tracks haven’t even started yet!

(Oh, and by the way, as of this morning I heard there are still day-passes available for those who haven’t yet registered for the conference… and then you can come hear a great session about Wait Events in PostgreSQL this Thursday at 4:20pm!)

The first two days of PostgresConf are summits, tutorials and training sessions. A good chunk of my day today was helping out with Scott Mead’s intensive 3 hour hands-on lab Setting up PostgreSQL for Production in AWS – but outside of that I’ve managed to drop in to a number of other sessions that sounded interesting. I did my best to take down some notes so I could share a few highlights.

Monday March 18

Personally, my favorite session on Monday was Brent Bigonger’s session.  He’s a database engineer at Amazon who was involved in migrating their Inventory Management System to Aurora PostgreSQL (slides available here). I always love hearing good stories (part of why I’ve always been a fan of user groups) – this presentation gave a nice high level overview of the business, a review of the planning and execution process for the migration, and lots of practical lessons learned.

  • Some of the tips were things people are generally familiar with – like NULLs behaving differently and the importance of performance management with a tool like Performance Insights.
  • My favorite tip is getting better telemetry by instrumenting SQL with comments (SELECT /* my-service-call-1234 */ …) which reminded me of something I also read in Baron Sc​hwartz’s recently updated e-book on observable systems: “including implicit data in SQL.”
  • A great new tip (to me) was the idea of creating a heartbeat table as one more safety check in a replication process.  You can get a sense for lag by querying the table and you can also use it during a cutover to get an extra degree of assurance that no data was missed.
  • Another general point I really resonated with: Brent gave a nice reminder that a simple solution which meets the business requirements is better than a sophisticated or complex solution that goes beyond what the business really needs.  I feel tempted on occasion to leverage architectures because they are interesting – and I always appreciate hearing this reiterated!

On the AWS track, aside from Brent’s session, I caught a few others: Jim Mlodgenski giving a deep dive on Aurora PostgreSQL architecture and Jim Finnerty giving a great talk on Aurora PostgreSQL performance tuning and query plan management.  It’s funny, but I think my favorite slide from Finnerty’s talk was actually one of the simplest and most basic; he had a slide that just had high-level list of steps for performance tuning.  I don’t remember the exact list on that slide at the moment, but the essential process: (1) identify to top SQL (2) EXPLAIN to get the plan (3) make improvements to the SQL and (4) test and verify whether the improvements actually had the intended effect.

Other sessions I dropped into:

  • Alvaro Hernandez giving an Oracle to PostgreSQL Migration Tutorial.  I love live demos (goes along with loving hands on labs) and so this session was a hit with me – I wasn’t able to catch the whole thing but I did catch a walk-through of ora2pg.
  • Avinash Vallarapu giving an Introduction to PostgreSQL for Oracle and MySQL DBAs. When I slipped in, he was just wrapping up a section on hot physical backups in PostgreSQL with the pg_basebackup utility.  After that, Avi launched into a section on MVCC in PostgreSQL – digging into transaction IDs and vacuum, illustrated with block dumps and the pageinspect extension.  The part of this session I found most interesting was actually a few of the participant discussions – I heard lively discussions about what extensions are and about comparisons with RMAN and older versions of Oracle.

Tuesday March 19

As I said before, a good chunk of my morning was in Scott’s hands-on lab. If you ever do a hands-on lab with Scott then you’d better look out… he did something clever there: somewhere toward the beginning, if you followed the instructions correctly, then you would be unable to connect to your database!  Turns out this was on purpose (and the instructions actually tell you this) – since people often have this particular problem connecting when they first start on out RDS, Scott figured he’d just teach everyone how to fix it.  I won’t tell you what the problem actually is though – you’ll have to sign up for a lab sometime and learn for yourself.  :)

As always, we had a lot of really interesting discussions with participants in the hands-on lab.  We talked about the DBA role and the shared responsibility model, about new tools used to administer RDS databases in lieu of shell access (like Performance Insights and Enhanced Monitoring), and about how RDS helps implement industry best practices like standardization and automation. On a more technical level, people were interested to learn about the “pgbench” tool provided with postgresql.

In addition to the lab, I also managed to catch part of Simon Riggs’ session Essential PostgreSQL11 Database Administration – in particular, the part about PostgreSQL 11 new features.  One interesting new thing I learned was about some work done specifically around the performance of indexes on monotonically increasing keys.

Interesting Conversations

https://ardentperf.files.wordpress.com/2019/03/img_2736.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/img_2736.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />Of course I learned just as much outside of the sessions as I learned in the sessions.  I ended up eating lunch with Alexander Kukushkin who helped facilitate a 3 hour hands-on session today about Understanding and implementing PostgreSQL High Availability with Patroni and enjoyed hearing a bit more about PostgreSQL at Zalando. Talked with a few people from a government organization who were a long-time PostgreSQL shop and interested to hear more about Aurora PostgreSQL. Talked with a guy from a large financial and media company about flashback query, bloat and vacuum, pg_repack, parallel query and partitioning in PostgreSQL.

And of course lots of discussions about the professional community. Met PostgresConf conference volunteers from California to South Africa and talked about how they got involved in the community.  Saw Lloyd and chatted about the Seattle PostgreSQL User Group.

The training and summit days are wrapping up and now it’s time to get ready for the next three days: keynotes, breakout sessions, exposition, a career fair and more!  I can’t wait.  :)

Jonathan Lewis's picture

IM_DOMAIN$

A few months ago Franck Pachot wrote about a recursive SQL statement that kept appearing in the library cache. I discovered the note today because I had just found a client site where the following statement suddenly appeared near the top of the “SQL ordered by Executions” section of their AWR reports after they had upgraded to 18c.


select domain# from sys.im_domain$ where objn = :1 and col# = :2

I found Franck’s article by the simple expedient of typing the entire query into a Google search – his note was the first hit on the list, and he had a convenient example (based on the SCOTT schema) to demonstrate the effect, so I built the tables from the schema and ran a simple test with extended SQL tracing (event 10046) enabled.

Here’s an extract (with no deletions) from the resulting trace file:

PARSING IN CURSOR #139819795591784 len=110 dep=0 uid=104 oct=3 lid=104 tim=31306461773 hv=3572295767 ad='6bf8b8a0' sqlid='8n2bcc3aftu2r'
select /*+ leading(EMP DEPT) USE_HASH(DEPT) USE_HASH(BONUS) */ * from DEPT natural join EMP natural join BONUS
END OF STMT
PARSE #139819795591784:c=59,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4262704031,tim=31306461772

PARSING IN CURSOR #139819795585328 len=64 dep=1 uid=0 oct=3 lid=0 tim=31306461966 hv=1240924087 ad='69a8b760' sqlid='0b639nx4zdzxr'
select domain# from sys.im_domain$ where objn = :1 and col# = :2
END OF STMT
PARSE #139819795585328:c=37,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2321277860,tim=31306461965
EXEC #139819795585328:c=32,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2321277860,tim=31306462058
FETCH #139819795585328:c=17,e=17,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=2321277860,tim=31306462098
STAT #139819795585328 id=1 cnt=0 pid=0 pos=1 obj=10422 op='TABLE ACCESS BY INDEX ROWID IM_DOMAIN$ (cr=1 pr=0 pw=0 str=1 time=21 us cost=0 size=39 card=1)'
STAT #139819795585328 id=2 cnt=0 pid=1 pos=1 obj=10423 op='INDEX UNIQUE SCAN IM_DOMAIN_UK (cr=1 pr=0 pw=0 str=1 time=18 us cost=0 size=0 card=1)'
CLOSE #139819795585328:c=5,e=5,dep=1,type=1,tim=31306462287

EXEC #139819795591784:c=484,e=484,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=4262704031,tim=31306462316
FETCH #139819795591784:c=0,e=804,p=0,cr=44,cu=0,mis=0,r=0,dep=0,og=1,plh=4262704031,tim=31306463191
STAT #139819795591784 id=1 cnt=0 pid=0 pos=1 obj=0 op='HASH JOIN  (cr=45 pr=0 pw=0 str=1 time=1222 us cost=72 size=97 card=1)'
STAT #139819795591784 id=2 cnt=4 pid=1 pos=1 obj=0 op='HASH JOIN  (cr=45 pr=0 pw=0 str=1 time=1001 us cost=70 size=232 card=4)'
STAT #139819795591784 id=3 cnt=4 pid=2 pos=1 obj=117764 op='TABLE ACCESS FULL EMP (cr=22 pr=0 pw=0 str=1 time=259 us cost=35 size=152 card=4)'
STAT #139819795591784 id=4 cnt=4 pid=2 pos=2 obj=117765 op='TABLE ACCESS FULL DEPT (cr=22 pr=0 pw=0 str=1 time=81 us cost=35 size=80 card=4)'
STAT #139819795591784 id=5 cnt=0 pid=1 pos=2 obj=117766 op='TABLE ACCESS FULL BONUS (cr=0 pr=0 pw=0 str=1 time=4 us cost=2 size=39 card=1)'
CLOSE #139819795591784:c=24,e=24,dep=0,type=1,tim=31306508552

PARSE #139819795591784:c=41,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4262704031,tim=31306508798
PARSE #139819795585328:c=21,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2321277860,tim=31306509010
EXEC #139819795585328:c=132,e=132,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2321277860,tim=31306509220
FETCH #139819795585328:c=20,e=19,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=2321277860,tim=31306509415
CLOSE #139819795585328:c=8,e=8,dep=1,type=3,tim=31306509494
EXEC #139819795591784:c=682,e=704,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=4262704031,tim=31306509558
FETCH #139819795591784:c=588,e=1246,p=0,cr=44,cu=0,mis=0,r=0,dep=0,og=1,plh=4262704031,tim=31306511014
CLOSE #139819795591784:c=23,e=22,dep=0,type=3,ti

As you can see, every time I do a parse call for the query against the SCOTT schema (PARSE #139819795591784), Oracle does a parse/exec/fetch/close for the query against im_domain$ (PARSE #139819795585328) – and this happens even when the SCOTT query is in the session cursor cache!

As Franck points out, this looks like something to do with the In Memory option even though the option wasn’t enabled in his database, and wasn’t enabled in my client’s database. Once you’ve got a reproducible example of a problem, though, you can start fiddling to see if you can bypass it. In this case I decided to check all the parameters to do with the in-memory option – which is a bit like hard work because there are 208 parameters that include the word “inmemory”. After checking the descriptions of the first twenty or so I decided there was an easier option – if Oracle is asking about “domains” for columns possibly it’s something to do with the relatively new “join group” feature for in-memory columnar compression, so I ran a query to produce the list of parameter names and description for parameter with the words “join” and “group” in their names – there are two:


_sqlexec_join_group_aware_hj_enabled              enable/disable join group aware hash join
_sqlexec_join_group_aware_hj_unencoded_rowset     minimum number of unencoded rowsets processed before adaptation 

The first one looks rather promising – and it has a default value to TRUE, and it can be changed by “alter session” or “alter system”. So I executed:


alter session set "_sqlexec_join_group_aware_hj_enabled" = false;
alter system flush shared_pool;

Then I ran my test again and voila! there it wasn’t. No more queries against in_domain$.

Problem solved (until the client decides they want to use the in-memory option, perhaps).

There may be other reasons why this recursive query appears which aren’t to do with hash joins, of course, but join groups are specifically to allow efficient hash joins with the in-memory option, (it’s a trick to do with common encoding for compression to allow Bloom filtering to eliminate CUs without decoding) so I’m hoping I won’t have to track down and eliminate another sources for the query.

 

Richard Foote's picture

Oracle Database 19c Automatic Indexing: Predicted Back In 2008 (A Better Future)

I’ve recently received a number of correspondences regarding one of my most popular blog posts, dating back to February 2008: Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones). In the comments section, there’s an interesting discussion where I mention: “If Oracle19 does everything for you and all the various indexes structures get […]

To prevent automated spam submissions leave this field empty.