3 Replies Latest reply on Dec 3, 2014 10:02 PM by Arturo Gutierrez

    Optimize Database Links between PDBs on the same CDB

    Arturo Gutierrez

      Hello,

      Is very common to use database links connect several non-cdb databases.

      If we consolidate this databases on the same CDB , I think that database links work fine without change nothing, but now that are on the same CDB, (same instance).

      Some question appears,.

       

      Is there any option to optimize  database Links comunication?

      An obvious option would create a network alias that uses the IPC protocol to eliminate the TCP layer.

      Do you have any specific option or treatment in MultiTenant arquitecture?


      Thanks

      Arturo

       

        • 1. Re: Optimize Database Links between PDBs on the same CDB

          Is very common to use database links connect several non-cdb databases.

          If we consolidate this databases on the same CDB , I think that database links work fine without change nothing, but now that are on the same CDB, (same instance).

          Some question appears,.

           

          Is there any option to optimize  database Links comunication?

          An obvious option would create a network alias that uses the IPC protocol to eliminate the TCP layer.

          Do you have any specific option or treatment in MultiTenant arquitecture?

          You have the same options as always; there are no 'new' options that I am aware of. For dedicated server you could use the BEQ (bequeath) protocol.

           

          Generally you shouldn't try to second-guess Oracle. There are several different types of links that use different authentication mechanisms.

           

          Oracle doesn't always use the network for certain operation. This White Paper has a lot if previously undocumented info about the multitenant architecuture, why it works the way it does and the reasons for some of the decisions that were made for that design.

           

          There are several places where it explicitly states that a design decision was to make use of multitenant as 'transparent' as possible so it would work the same as if separate DBs were being used.

           

          http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf

           

          The section about PDB cloning says this:

          Code_4 shows the clone PDB SQL statement for remote cloning.

          -- Code_4

          create pluggable database PDB_2 from PDB_1@CDB_02_link

          The semantics are “create a clone of PDB_1, to be found in the CDB whose root is denoted by CDB_02_link, and call the resulting new PDB PDB_2”. (Alternatively, the database link may denote the to-be-cloned PDB.)

          The database link encapsulates the specification of the location of the source CDB (it specifies listener name, listener port, and service name, which leads to the database and its files). It also specifies the authorization to start a session whose current container is the root of the source CDB. Notice that, once location and authorization are established, the transport of the files uses a suitable low-level protocol and is parallelized, using Oracle parallel server processes as described. The files are not transported over the database link.

          So for that use case a DB link is specified but the network isn't used for the file copy.

           

          Conduct some tests with a network packet sniffer (e.g. Wireshark) and see what results you get when you use a db link to send data between PDBs.

          1 person found this helpful
          • 2. Re: Optimize Database Links between PDBs on the same CDB
            Richard Harrison .

            Hi,

            12.1.0.2 introduces the containers clause which allowed you to query other pdb's directly - see exaple here

             

            Oracle DBA Blog 2.0: 12cR2 new feature - containers clause in SQL

             

            It will need code changes to do that though and it may not be worth the effort.

             

            Cheers,

            Rich

            1 person found this helpful
            • 3. Re: Optimize Database Links between PDBs on the same CDB
              Arturo Gutierrez

              With you feedback, we can do this simple test to show the events wait for a sesión accessing inter-PDB tables using DB LINKS.

               

              The idea is to run this basic script:

               

              Set autotrace TRACEONLY

              Set arraysize 100

              Set timing on

              select * from arturo.x@pdb1_link;


              From the PDB (PDB2) having the db link Pdb1_link.

               

              The link definition is:

               

              create database link pdb1_link connect to arturo identified by xxxxxxxx  using 'pdb1_T';

               

              The TNS entry  used:

               

              PDB1_T =

                 (DESCRIPTION =

                   (ADDRESS_LIST =

                   (ADDRESS = (PROTOCOL = TCP) (HOST = ag-acer) (PORT = 1521))

                   )

                   (CONNECT_DATA =

                     (SERVICE_NAME = pDB1)

                   )

                 )

               

               

              The trace generated, after apply tkprof.:

               

              select *
              from
              arturo.x@pdb1_link


              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.03       0.07          0          0          1           0
              Execute      1      0.00       0.01          0          0          0           0
              Fetch     3643      4.60       5.73          0          0          0      364168
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total     3645      4.64       5.81          0          0          1      364168

              Misses in library cache during parse: 0
              Optimizer mode: ALL_ROWS
              Parsing user id: 103 

              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                single-task message                             1        0.02          0.02
                SQL*Net message from dblink                  3652        0.02          1.38
                SQL*Net message to dblink                    3651        0.00          0.00
                SQL*Net message to client                    3643        0.00          0.02
                SQL*Net message from client                  3643        0.00          5.03
                SQL*Net more data from dblink                3642        0.02          0.12
                SQL*Net more data to client                  3642        0.00          0.05
              ********************************************************************************

               

              Show, that SQL*NET layer was used for this communication.

              I've al also try to use IPC protocol using this TNSNAMES alias.


              PDB1 =
                (DESCRIPTION =
                  (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                  )
                  (CONNECT_DATA =
                    (SERVICE_NAME = pdb1)
                  )
                )

              And the event results are the same:

              select *
              from
              arturo.x@link_pdb1


              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.03       0.07          0          0          1           0
              Execute      1      0.00       0.00          0          0          0           0
              Fetch     3643      4.39       4.97          0          0          0      364168
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total     3645      4.42       5.04          0          0          1      364168

              Misses in library cache during parse: 0
              Optimizer mode: ALL_ROWS
              Parsing user id: 103 

              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                single-task message                             1        0.02          0.02
                SQL*Net message from dblink                  3652        0.01          0.81
                SQL*Net message to dblink                    3651        0.00          0.00
                SQL*Net message to client                    3643        0.00          0.02
                SQL*Net message from client                  3643        0.00          5.04
                SQL*Net more data from dblink                3642        0.00          0.05
                SQL*Net more data to client                  3642        0.00          0.05
              ********************************************************************************

              In both test the elapsed time was the same.

               

              However, IF I execute the query in a non-cdb database:

               

              select *
              from
              y


              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.00          0          0          0           0
              Execute      1      0.00       0.00          0          0          0           0
              Fetch     3686      0.85       0.88          0       9824          0      368488
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total     3688      0.85       0.88          0       9824          0      368488

              Misses in library cache during parse: 0
              Optimizer mode: ALL_ROWS
              Parsing user id: 110 
              Number of plan statistics captured: 1

              Rows (1st) Rows (avg) Rows (max)  Row Source Operation
              ---------- ---------- ----------  ---------------------------------------------------
                  368488     368488     368488  TABLE ACCESS FULL Y (cr=9824 pr=0 pw=0 time=391335 us cost=431 size=10594030 card=92122)


              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                SQL*Net message to client                    3686        0.00          0.01
                SQL*Net message from client                  3686        0.00          4.78
                SQL*Net more data to client                     1        0.00          0.00
              ********************************************************************************

               

              Well, with this evidence, it is very difficult to draw concrete conclusions.

              But it is clear that mass access through a DB LInk has significant overhead. Since the time when the SQL elapsed time was connected locally was 6 seconds and 10 seconds with db link.

              .....

              Although the Multi-Tenant, architecture is transparent to applications using DB LINKS, it would be interesting to apply some optimization with DB access via links between different databases CDB. Since it is quite common.

              ...

              In Oracle University courses dealing with these issues, say:

               

              With a multitenant architecture that holds several PDBs, these once separated non-CDBs now may work with a single instance, sharing memory, disk and CPU resources, but maintaining application separation.

              These databases shared data using database links. The database link still works, but now because the “link” communication does not leave the instance, the link is very fast.


              For this, statement is the reason of this post.


              Your comments will be welcome.

              Thanks

              Arturo