Feed aggregator

RMAN Backup To FRA Repercussions

Michael Dinh - Sun, 2017-11-26 09:50

Common advice is to backup to FRA.
Before following advice, evaluate to determine fit and understand any repercussions.
Doesn’t this potentially create SPOF and may require restore from tape unnecessarily?

HINT:

Make sure the following commands are part of backup when backup to FRA.

CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
DEMO:
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Nov 26 16:02:17 2017

RMAN> show controlfile autobackup;

RMAN configuration parameters for database with db_unique_name HAWK are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name HAWK are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

RMAN> backup datafile 1;

Starting backup at 26-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/HAWK/DATAFILE/system.258.960967651
channel ORA_DISK_1: starting piece 1 at 26-NOV-17
channel ORA_DISK_1: finished piece 1 at 26-NOV-17
piece handle=+FRA/HAWK/BACKUPSET/2017_11_26/nnndf0_tag20171126t160327_0.274.961085007 tag=TAG20171126T160327 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-NOV-17

--- Control File and SPFILE Autobackup to FRA
Starting Control File and SPFILE Autobackup at 26-NOV-17
piece handle=+FRA/HAWK/AUTOBACKUP/2017_11_26/s_961085014.275.961085015 comment=NONE
Finished Control File and SPFILE Autobackup at 26-NOV-17

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
new RMAN configuration parameters are successfully stored

RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name HAWK are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
--- CONTROLFILE AUTOBACKUP FORMAT is same but ***NOT*** DEFAULT
RMAN> backup datafile 1;

Starting backup at 26-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/HAWK/DATAFILE/system.258.960967651
channel ORA_DISK_1: starting piece 1 at 26-NOV-17
channel ORA_DISK_1: finished piece 1 at 26-NOV-17
piece handle=+FRA/HAWK/BACKUPSET/2017_11_26/nnndf0_tag20171126t160655_0.276.961085215 tag=TAG20171126T160655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-NOV-17

--- Control File and SPFILE Autobackup to ***DISK***
Starting Control File and SPFILE Autobackup at 26-NOV-17
piece handle=/u01/app/oracle/12.1.0.2/db1/dbs/c-3219666184-20171126-01 comment=NONE
Finished Control File and SPFILE Autobackup at 26-NOV-17

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
RMAN configuration parameters are successfully reset to default value

RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name HAWK are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default <-- 

RMAN> backup datafile 1 FORMAT '%d_%I_%T_%U';

Starting backup at 26-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/HAWK/DATAFILE/system.258.960967651
channel ORA_DISK_1: starting piece 1 at 26-NOV-17
channel ORA_DISK_1: finished piece 1 at 26-NOV-17
piece handle=/u01/app/oracle/12.1.0.2/db1/dbs/HAWK_3219666184_20171126_0oski093_1_1 tag=TAG20171126T161531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-NOV-17

Starting Control File and SPFILE Autobackup at 26-NOV-17
piece handle=+FRA/HAWK/AUTOBACKUP/2017_11_26/s_961085738.277.961085739 comment=NONE
Finished Control File and SPFILE Autobackup at 26-NOV-17

RMAN>
REFERENCE:
How to KEEP a backup created in the Flash Recovery Area (FRA)? (Doc ID 401163.1)	
A backup needed to be KEPT, must be created outside the flash recovery area.

Why are backups going to $ORACLE_HOME/dbs rather than Flash recovery area via Rman or EM Grid control /FRA not considering Archivelog part of it (Doc ID 404854.1)
 1. Do not use a FORMAT clause on backup commands.
 
RMAN Uses Flash Recovery Area for Autobackup When Using Format '%F' (Doc ID 338483.1)	 

Docker-CE: Creating a small Linux as base OS for running a Docker cluster

Dietrich Schroff - Sat, 2017-11-25 15:13
After some first steps with docker i wanted to run more than one docker node. The problem is, that i have very limited resources on my laptop.
  • CoreOS (see here)
    consumes about 600MB diskspace
  • UbuntuCore (see here)
    consumes about 1.9GB diskspace
To setup CoreOS you have to learn ignition - otherwise every change is lost after a reboot. For UbuntuCore you have to use an UbuntuOne account to get it running.

I heard, that Alpine Linux is an OS with a very small footprint, so i downloaded the "virtual" image and

mounted it in my virtualbox:


After a short startup i got the following:

This is completely different to all other installation procedures i know. No configuration dialog just a
localhost login:You have to enter "root" and without a password you get a banner with one important information:
You can setup the system with the command: setup-alpineThis will guide you through a 11 step setup (read the manual). To get a installation with persistent homes, etc. you have to choose the "sys" mode.

And then after a reboot, i have a Linux VM with 170MB.
For my next steps (installing docker, cloning some nodes) this should be the right choice.

[Installing docker is quite easy: remove the # before "edge/community" in /etc/apk/repositories and the run "apk update" and "apk add docker".]

WebLogic – SSO/Atn/Atz – How to enable debug logs

Yann Neuhaus - Sat, 2017-11-25 13:04

Let’s say that you have an application deployed on a WebLogic Server and that the SSO/Atn/Atz (SSO/Authentication/Authorization) isn’t working or throwing a 4xx error code like ‘401 – Unauthorized’ or ‘403 – Forbidden’. How would you be able to know what is happening and how to solve it? Well the answer is always the same: enable debug logs, it is just a matter or choosing the right scope and choosing a way to do it because yes, it is possible to enable/disable them in several ways…

In this blog, I will be using a WebLogic Server configured with a SAML2 Single Sign-On (SSO) and hosting a Documentum D2 application. I will therefore enable and disable the debug logs for a Managed Server that is named ‘msD2-01′ and this MS is hosting D2, obviously (this is just for the background, it does not matter which application is running there!).

In the case of the SAML2 SSO, it’s pretty easy to recognize some pattern of errors, you just have to access the SSO URL and then watch closely the redirection from the WLS to the IdP Partner and then back to the WLS. Depending on when the issue appears, you can find out where the issue is. But in case you absolutely don’t know anything about all that or if you are completely new to this, you can and should always do the same thing: enable the debug logs.

 

I. Administration Console

WebLogic provides several ways to enable debug logs but the one that makes it really easy, even for beginners, is the WebLogic Administration Console. In this blog, I will only talk about SAML2 SSO, Authentication and Authorization. So enabling the debug logs for these three elements can be done using these steps:

  1. Login to the Admin Console using your weblogic account
  2. Navigate to the correct page: DOMAIN > Environment > Servers > ServerName (msD2-01 for me) > Debug
  3. Click on the ‘Lock & Edit’ button
  4. Expand the scope ‘weblogic’ and then ‘security’
  5. Check the correct checkboxes, depending on which element you want to enable:
    • atn
    • atz
    • ldap
    • saml
    • saml2
  6. Go back to the top and click on the ‘Enable’ button
  7. Click on the ‘Activate Changes’ button -> All changes have been activated. No restarts are necessary.

The correct page on the WebLogic Admin Console (step 2) is this one:

Debug

From this moment, you can check the Managed Server log file (its name is defined in the ‘Logging’ tab) which is $DOMAIN_HOME/servers/ServerName/logs/ServerName.log by default ($DOMAIN_HOME/servers/msD2-01/logs/msD2-01.log in my case) and it will start to log the messages for the elements that you enabled.

Do NOT enable too many elements! I usually only enable the SAML2 when I know it is linked to the SSO or atn+atz when it is more linked to how WebLogic manages the Authentication/Authorization. These are the three main scopes that you should work with when debugging an issue related to SSO/Authentication/Authorization.

Also, do not forget to disable the debug logs when you are done with your analysis. It is pretty obvious but it can store a lot of information in the log files so…

Please also note that if you expand the ‘atn’ scope for example, you will see that inside it, there is actually the ‘DebugSecurityAtn’, ‘DebugSecuritySAML2Atn’ and ‘DebugSecuritySAMLAtn’ -> these are attributes (not expandable). Therefore enabling the whole ‘atn’ will already activate some of the SAML2 debug logs. So there is a notion of group (=scope) on the Admin Console to simplify the selection that you can also find on other solutions below.

 

II. config.xml

If you already worked with WebLogic, you probably know what is the purpose of the config.xml file and you probably also know that it can be a little bit dangerous to mess with this file (not really if you know what you are doing). However it is still possible to do some configuration there, so at your own risks :).

By default, this file will NOT contain any information regarding the debug logging, unless you already enabled them at least once in the past using the Admin Console for example. So since there is, by default, no information regarding the debug logging, you need to add them manually if you want to configure the logging using the config.xml file. Be aware that the location where you put the configuration matters!

[weblogic@weblogic_server_01 ~]$ cd $DOMAIN_HOME/config
[weblogic@weblogic_server_01 config]$
[weblogic@weblogic_server_01 config]$ grep -C1 -E "debug|server>" config.xml
  </log>
  <server>
    <name>AdminServer</name>
--
    <custom-trust-key-store-pass-phrase-encrypted>{AES}QARbQAV1ul1u3tJcsGAhdATQrNJe1YlVnQmY9d1jWQFx4aM=</custom-trust-key-store-pass-phrase-encrypted>
  </server>
  <server>
    <name>msD2-01</name>
--
    </single-sign-on-services>
  </server>
  <production-mode-enabled>true</production-mode-enabled>
[weblogic@weblogic_server_01 config]$

 

With the example above, there are no debug information in the config.xml file so let’s add the SAML2, atn and atz logging. You can put these entries between the ‘<listen-port-enabled>’ and ‘<listen-address>’ parameters for example. That’s what it looks like afterwards:

[weblogic@weblogic_server_01 config]$ grep -C1 -E "debug|server>" config.xml
  </log>
  <server>
    <name>AdminServer</name>
--
    <custom-trust-key-store-pass-phrase-encrypted>{AES}QARbQAV1ul1u3tJcsGAhdATQrNJe1YlVnQmY9d1jWQFx4aM=</custom-trust-key-store-pass-phrase-encrypted>
  </server>
  <server>
    <name>msD2-01</name>
--
    <listen-port-enabled>false</listen-port-enabled>
    <server-debug>
      <debug-scope>
        <name>weblogic.security.atn</name>
        <enabled>true</enabled>
      </debug-scope>
      <debug-scope>
        <name>weblogic.security.atz</name>
        <enabled>true</enabled>
      </debug-scope>
      <debug-scope>
        <name>weblogic.security.saml2</name>
        <enabled>true</enabled>
      </debug-scope>
    </server-debug>
    <listen-address>10.10.10.10</listen-address>
--
    </single-sign-on-services>
  </server>
  <production-mode-enabled>true</production-mode-enabled>
[weblogic@weblogic_server_01 config]$

 

As you can see above, the debug-scope name that needs to be added in the config.xml file is simply the concatenation of the different elements you expanded in the WebLogic Administration Console separated by dots (E.g.: weblogic.security.saml2).

Above, I only talked about scopes again. If you want to set the attributes directly, it is also possible by adding this for example ‘<debug-security-saml2-service>true</debug-security-saml2-service>’ or ‘<debug-security-atz>true</debug-security-atz>’ but let’s try to keep it simple… I’m only showing the minimum to be able to debug issues but that is obviously not everything you can do.

The problem with configuring something in the config.xml: you need to restart the Managed Server for the change to be applied… Indeed, all changes are written to this file as soon as they are activated but this file is only loaded at the boot so a restart is needed, which makes this solution a little bit less effective when we are talking about debug logging because we usually want to enable/disable them on the fly!

 

III. WLST

You all know what the WLST is so let’s just get to it. This solution is probably the best one if you want to script/automate it because it’s the whole purpose of the WebLogic Scripting Tool, yeah yeah I know it is unbelievable ;)! Below I will not use the config and key files to connect to the AdminServer but keep in mind that these files contain the weblogic username and password encrypted so if you want to automate something, you will most probably need them to avoid clear text passwords! So first the connection to the WLST:

[weblogic@weblogic_server_01 config]$ source $MW_HOME/wlserver/server/bin/setWLSEnv.sh
CLASSPATH=$JAVA_HOME/lib/tools.jar:$MW_HOME/wlserver/modules/features/wlst.wls.classpath.jar:

PATH=$MW_HOME/wlserver/server/bin:$MW_HOME/wlserver/../oracle_common/modules/org.apache.ant_1.9.2/bin:$JAVA_HOME/jre/bin:$JAVA_HOME/bin:$JAVA_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/weblogic/bin:$MW_HOME/wlserver/../oracle_common/modules/org.apache.maven_3.2.5/bin

Your environment has been set.
[weblogic@weblogic_server_01 config]$
[weblogic@weblogic_server_01 config]$ java -Djava.security.egd=file:///dev/./urandom -Dwlst.offline.log=disable weblogic.WLST

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> connect('weblogic','P4sSw0rd','t3s://weblogic_server_01:8443')
Connecting to t3s://weblogic_server_01:8443 with userid weblogic ...
<Nov 11, 2017 2:50:12 PM UTC> <Info> <Security> <BEA-090908> <Using the default WebLogic SSL Hostname Verifier implementation.>
Successfully connected to Admin Server "AdminServer" that belongs to domain "DOMAIN".

wls:/DOMAIN/serverConfig/> edit()
Location changed to edit tree.
This is a writable tree with DomainMBean as the root.
To make changes you will need to start an edit session via startEdit().
For more help, use help('edit').

wls:/DOMAIN/edit/> cd('Servers/msD2-01/ServerDebug/msD2-01')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01> startEdit()
Starting an edit session ...
Started edit session, be sure to save and activate your changes once you are done.
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> set('DebugSecurityAtn','true')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> set('DebugSecurityAtz','true')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> set('DebugSecuritySAML2Atn','true')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> set('DebugSecuritySAML2Service','true')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> set('DebugSecuritySAMLCredMap','true')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> save()
Saving all your changes ...
Saved all your changes successfully.
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01 !> activate()
Activating all your changes, this may take a while ...
The edit lock associated with this edit session is released once the activation is completed.
Activation completed
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01>

 

The commands above will enable the attributes only. On the first paragraph above (related to the Admin Console), I talked about groups (=scope) and I actually only enabled the groups themselves on this first solution (scopes = ‘atn’, ‘atz’ and ‘saml2′). So if you enabled the scope ‘atn’ on the Admin Console for example, then you will probably see the scope ‘DebugSecurityAtn’ (on the WLST session) set to false (unless you already changed it using the above WLST commands). If you want to enable the scopes directly, it is not on the same location. Here is an example for the SAML2 scope:

wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01> cd('DebugScopes/weblogic.security.saml2')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2> ls()

-r--   DynamicallyCreated                           false
-rw-   Enabled                                      false
-r--   Id                                           0
-rw-   Name                                         weblogic.security.saml2
-rw-   Notes                                        null
-rw-   Tags                                         null
-r--   Type                                         DebugScope

-r-x   addTag                                       Boolean : String(tag)
-r-x   freezeCurrentValue                           Void : String(attributeName)
-r-x   getInheritedProperties                       String[] : String[](propertyNames)
-r-x   isInherited                                  Boolean : String(propertyName)
-r-x   isSet                                        Boolean : String(propertyName)
-r-x   removeTag                                    Boolean : String(tag)
-r-x   restoreDefaultValue                          Void : String(attributeName)
-r-x   unSet                                        Void : String(propertyName)

wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2> startEdit()
Starting an edit session ...
Started edit session, be sure to save and activate your changes once you are done.
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2 !> set('Enabled','true')
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2 !> ls()

-r--   DynamicallyCreated                           false
-rw-   Enabled                                      true
-r--   Id                                           0
-rw-   Name                                         weblogic.security.saml2
-rw-   Notes                                        null
-rw-   Tags                                         null
-r--   Type                                         DebugScope

-r-x   addTag                                       Boolean : String(tag)
-r-x   freezeCurrentValue                           Void : String(attributeName)
-r-x   getInheritedProperties                       String[] : String[](propertyNames)
-r-x   isInherited                                  Boolean : String(propertyName)
-r-x   isSet                                        Boolean : String(propertyName)
-r-x   removeTag                                    Boolean : String(tag)
-r-x   restoreDefaultValue                          Void : String(attributeName)
-r-x   unSet                                        Void : String(propertyName)

wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2 !> save()
Saving all your changes ...
Saved all your changes successfully.
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2 !> activate()
Activating all your changes, this may take a while ...
The edit lock associated with this edit session is released once the activation is completed.
Activation completed
wls:/DOMAIN/edit/Servers/msD2-01/ServerDebug/msD2-01/DebugScopes/weblogic.security.saml2> disconnect()
Disconnected from weblogic server: AdminServer
wls:/offline> exit()

Exiting WebLogic Scripting Tool.

[weblogic@weblogic_server_01 config]$

 

Pretty cool, right?

 

IV. Command line

The last way to do this is via the command line but just like the config.xml, this will require a restart of the concerned Managed Server. Here are some examples of command line arguments that can be added to the JAVA_OPTIONS to enable the debug logs on the Attributes level:

  • -Dweblogic.debug.DebugSecurityAtn=true
  • -Dweblogic.debug.DebugSecurityAtz=true
  • -Dweblogic.debug.DebugSecuritySAML2Atn=true
  • -Dweblogic.debug.DebugSecuritySAML2Service=true

With all that, you should be well armed to face any issue!

 

 

Cet article WebLogic – SSO/Atn/Atz – How to enable debug logs est apparu en premier sur Blog dbi services.

Documentum – DFC traces setup & investigation

Yann Neuhaus - Sat, 2017-11-25 12:11

When working with Documentum, you will most probably have to enable the DFC traces one day or another and then work with these traces to analyze them. The purpose of this blog is simply to show how the DFC traces can be enabled, which tools can be used to quickly process them and what are the limitations of such things.

Enabling the DFC traces can be done very easily by updating the dfc.properties file of the client. This client can be a DA, D2, JMS, Index Agent, aso… The change is applied directly (if enabled=true) and disabled by default (if commented or enable=false). If you have a dfc.properties that is inside a war file (for DA/D2 for example) and that you deployed your application as a war file (not exploded), then disabling the tracing might need a restart of your application. To avoid that, you can have a dfc.properties inside the war file that just point to another one outside of the war file and then enabling/disabling the traces from this second file will work properly. There are a lot of options to customize how the traces should be generated. A first example with only a few properties that you can use and reuse every time you need traces:

dfc.tracing.enable=true
dfc.tracing.verbose=true
dfc.tracing.max_stack_depth=0
dfc.tracing.mode=compact
dfc.tracing.dir=/tmp/dfc_tracing

 

Another example with more properties to really specify what you want to see:

dfc.tracing.enable=true
dfc.tracing.verbose=true
dfc.tracing.max_stack_depth=4
dfc.tracing.include_rpcs=true
dfc.tracing.mode=standard
dfc.tracing.include_session_id=true
dfc.tracing.user_name_filter[0]=dmadmin
dfc.tracing.user_name_filter[1]=myuser
dfc.tracing.thread_name_filter[0]=Thread-3
dfc.tracing.thread_name_filter[1]=Thread-25
dfc.tracing.timing_style=milliseconds_from_start
dfc.tracing.dir=/tmp/dfc_tracing
dfc.tracing.file_prefix=mydfc
dfc.tracing.max_backup_index=10
dfc.tracing.max_file_size=104857600
...

 

All these properties are quite easy to understand even without explanation but you can probably find more information and all the possible options in the official Documentum documentation. It’s not the main purpose of this blog so I’m just mentioning a few properties to get started. By default, the name of the generated files will be something like “dfctrace.timestamp.log”, you can change that by setting the “dfc.tracing.file_prefix” for example. Adding and customizing the properties will change the display format and style inside the files so if you want to have a way to analyze these DFC traces, it is better to use more or less always the same set of options. For the example below, OTX asked me to use these properties only:

dfc.tracing.enable=true
dfc.tracing.verbose=true
dfc.tracing.max_stack_depth=4
dfc.tracing.include_rpcs=true
dfc.tracing.mode=compact
dfc.tracing.include_session_id=true
dfc.tracing.dir=/tmp/dfc_tracing

 

When you have your DFC traces, you need a way to analyze them. They are pretty much readable but it will be complicated to get something out of it without spending a certain amount of time – unless you already know what you are looking for – simply because there are a lot of information inside… For that purpose, Ed Bueche developed more than 10 years ago some AWK scripts to parse the DFC traces files: traceD6.awk and trace_rpc_histD6.awk. You can find these scripts at the following locations (all EMC links… So might not be working at some point in the future):

As you can see above, it is not really maintained and the same scripts or a mix of several versions can be found at several locations so it can be a little bit confusing. All the old links are about the awk scripts but since 2013, there is now a python script too (also developed by Ed Bueche).

In this blog, I wanted to talk about the AWK scripts mainly. Earlier this month, I was working with OTX on some performance tuning tasks and for that, I gathered the DFC traces for several scenarios, in different log files, well separated, aso… Then, I provided them to OTX for the analysis. OTX came back to me a few minutes later saying that most of the traces were corrupted and asking me to regenerate them. I wasn’t quite OK with that simply because it takes time and because there were some testing in progress on this environment so gathering clean DFC traces for several scenarios would have forced the tests to be stopped, aso… (Ok ok you got me, I’m just lazy ;))

The content of the DFC traces looked correct to me and after a quick verification, I saw that OTX was using the AWK scripts (traceD6.awk and trace_rpc_histD6.awk) to analyze the logs but they were apparently getting an error. The files didn’t look corrupted to me so I mentioned to OTX that the issue might very well be with the AWK scripts they were using. They didn’t really listen to what I said and stayed focus on getting a new set of DFC traces. I already used these scripts but never really looked inside so it was the perfect reason to take some time for that:

[dmadmin@content_server_01 ~]$ cd /tmp/dfc_tracing/
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ ls -l trace* dfctrace.*.log
-rw-r-----. 1 dmadmin dmadmin 92661060 Nov 3 09:24 dfctrace.1510220481672.log
-rw-r-----. 1 dmadmin dmadmin 3240 Nov 4 14:10 traceD6.awk
-rw-r-----. 1 dmadmin dmadmin 7379 Nov 4 14:10 traceD6.py
-rw-r-----. 1 dmadmin dmadmin 5191 Nov 4 14:10 trace_rpc_histD6.awk
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ awk -f traceD6.awk < dfctrace.1510220481672.log > output_awk_1.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ wc -l output_awk_1.log
2 output_awk_1.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ awk -f trace_rpc_histD6.awk < dfctrace.1510220481672.log > output_awk_2.log
awk: trace_rpc_histD6.awk:203: (FILENAME=- FNR=428309) fatal: division by zero attempted
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ wc -l output_awk_2.log
4 output_awk_2.log
[dmadmin@content_server_01 dfc_tracing]$

 

As you can see above, the first script generated a log file that contains only 2 lines, so this is already suspicious even if there are no errors. The second script generated an error and its log file contains only 4 lines… The input DFC trace file has a size of 90Mb so it’s clear that there is something wrong and that’s why OTX said that the DFC traces were corrupted. The error message shows the line (203) as the origin of the issue as well as a “division by zero attempted” message. This obviously means that somewhere on this line, there is a division and that the divisor is equal to 0 or at least not set at all. Since I love all kind of UNIX scripting, I would rather fix the bug in the script than having to generate a new set of DFC traces (and the new set would still be impacted by the issue anyway…)! So checking inside the trace_rpc_histD6.awk file, the line 203 is the following one:

[dmadmin@content_server_01 dfc_tracing]$ grep -n -C1 "TIME SPENT" trace_rpc_histD6.awk
202-    printf ("DURATION (secs):\t%17.3f\n", ((curr_tms - st_tms)) );
203:    printf ("TIME SPENT EXECUTING RPCs (secs):%8.3f (which is %3.2f percent of total time)\n", total_rpc_time, 100*total_rpc_time/(curr_tms - st_tms));
204-    printf ("Threads :\t%25d\n", thread_cnt);
[dmadmin@content_server_01 dfc_tracing]$

 

The only division on this line is the total time taken to execute the RPCs divided by the duration of the log file (timestamp of last message – first message). So the value of “curr_tms – st_tms” is 0. Potentially, it could be that both variables have the exact same value but since the first and last messages on the DFC traces don’t have the same timestamp, this isn’t possible and therefore both variables are actually 0 or not set. To check where these variables are defined, how and in which function:

[dmadmin@content_server_01 dfc_tracing]$ grep -n -C15 -E "curr_tms|st_tms" trace_rpc_histD6.awk | grep -E "curr_tms|st_tms|^[0-9]*[:-][^[:space:]]"
144-/ .RPC:/ {
159:                    st_tms = $1;
162:            curr_tms = $1;
175-}
177-/obtained from pool/ {
--
187-}
188-/.INFO: Session/ {
193-}
197-END {
202:    printf ("DURATION (secs):\t%17.3f\n", ((curr_tms - st_tms)) );
203:    printf ("TIME SPENT EXECUTING RPCs (secs):%8.3f (which is %3.2f percent of total time)\n", total_rpc_time, 100*total_rpc_time/(curr_tms - st_tms));
[dmadmin@content_server_01 dfc_tracing]$

 

This shows that the only location where these two variables are set is inside the matching pattern “/ .RPC:/” (st_tms is set to $1 only on the first execution). So it means that this portion of code is never executed so in other words: this pattern is never found in the DFC trace file. Why is that? Well that’s pretty simple: the DFC traces file contains a lot of RPC calls but these lines never contain ” .RPC:”, there are always at least two dots (so something like that: ” ..RPC:” or ” …RPC:” or ” ….RPC:”). The reason why there are several dots is simply because the RPC are placed where they are called… In this case, OTX asked us to use “dfc.tracing.max_stack_depth=4″ so this is what I did and it is the reason why the AWK scripts cannot work by default because they need “dfc.tracing.max_stack_depth=0″, that’s written at the beginning of the scripts in the comment sections.

So a simple way to fix the AWK scripts is to remove the space at the beginning of the pattern for both the traceD6.awk and trace_rpc_histD6.awk scripts and after doing that, it will work for all max_stack_depth values:

[dmadmin@content_server_01 dfc_tracing]$ grep -n ".RPC:/" *.awk
traceD6.awk:145:/ .RPC:/ {
trace_rpc_histD6.awk:144:/ .RPC:/ {
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ sed -i 's,/ .RPC:/,/.RPC:/,' *.awk
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ grep -n ".RPC:/" *.awk
traceD6.awk:145:/.RPC:/ {
trace_rpc_histD6.awk:144:/.RPC:/ {
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ awk -f traceD6.awk < dfctrace.1510220481672.log > output_awk_1.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ wc -lc output_awk_1.log
 1961 163788 output_awk_1.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ awk -f trace_rpc_histD6.awk < dfctrace.1510220481672.log > output_awk_2.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ wc -l output_awk_2.log
 367 49050 output_awk_2.log
[dmadmin@content_server_01 dfc_tracing]$

 

That looks much much better… Basically, the first script list all RPCs with their thread, name and times while the second script creates a sorted list of queries that took the most time to execute as well as a list of calls and occurrences per types/names.

The AWK and Python scripts, even if they are globally working, might have some issues with commas, parenthesis and stuff like that (again it depends which dfc.tracing options you selected). This is why I mentioned above that there is actually both a AWK and Python version of these scripts. Sometimes, the AWK scripts will contain the right information, sometimes it is the Python version that will but in all cases, the later will run much faster. So if you want to work with these scripts, you will have to juggle a little bit:

[dmadmin@content_server_01 dfc_tracing]$ python traceD6.py dfctrace.1510220481672.log > output_py_1.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ wc -l output_py_1.log
 1959 194011 output_py_1.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ python traceD6.py dfctrace.1510220481672.log -profile > output_py_2.log
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ wc -l output_py_2.log
 342 65917 output_py_2.log
[dmadmin@content_server_01 dfc_tracing]$

 

As you can see, there are fewer lines in the python output files but that’s because some unnecessary headers have been removed in the python version so it’s actually normal. However there are much more characters so it shows that, in this case, the extracted DQL queries contain more characters but it does not mean that these characters are actually part of the DQL queries: you will see below that there are references to “, FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T]],50,true,true)” => This is NOT part of the DQL but it is present on the output of the Python script while it is not for the AWK one:

[dmadmin@content_server_01 dfc_tracing]$ head -15 output_awk_1.log
analysis program version 2 based on DFC build 6.0.0.76
68354.130 & 0.005 & [http--0.0.0.0-9082-3] & EXEC_QUERY  select r_object_id from dm_sysobject where folder ('/Home') and object_name = 'Morgan Patou'
68354.135 & 0.000 & [http--0.0.0.0-9082-3] & multiNext
68354.136 & 0.005 & [http--0.0.0.0-9082-3] & SysObjFullFetch  0b0f12345004f0de
68354.165 & 0.002 & [http--0.0.0.0-9082-4] & EXEC_QUERY  select r_object_id from dm_user where user_name = 'Morgan Patou'
68354.167 & 0.000 & [http--0.0.0.0-9082-4] & multiNext
68354.167 & 0.002 & [http--0.0.0.0-9082-4] & IsCurrent
68354.170 & 0.003 & [http--0.0.0.0-9082-4] & EXEC_QUERY  SELECT COUNT(*) AS items FROM dm_group WHERE group_name = 'report_user' AND ANY i_all_users_names = 'Morgan Patou'
68354.173 & 0.001 & [http--0.0.0.0-9082-4] & multiNext
68354.175 & 0.003 & [http--0.0.0.0-9082-4] & EXEC_QUERY  select r_object_id from dm_sysobject where folder ('/myInsight') and object_name = 'myInsight.license'
68354.178 & 0.001 & [http--0.0.0.0-9082-4] & multiNext
68354.179 & 0.001 & [http--0.0.0.0-9082-4] & IsCurrent
68354.165 & 0.010 & [http--0.0.0.0-9082-3] & SysObjGetPermit
68354.175 & 0.006 & [http--0.0.0.0-9082-3] & SysObjGetXPermit
68354.181 & 0.006 & [http--0.0.0.0-9082-4] & MAKE_PULLER
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ head -15 output_py_1.log
68354.130 & 0.005 & [http--0.0.0.0-9082-3] & EXEC_QUERY & select r_object_id from dm_sysobject where folder ('/Home') and object_name = 'Morgan Patou', FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T]],50,true,true)
68354.135 & 0.000 & [http--0.0.0.0-9082-3] & multiNext &
68354.136 & 0.005 & [http--0.0.0.0-9082-3] & SysObjFullFetch & 0b0f12345004f0de
68354.165 & 0.002 & [http--0.0.0.0-9082-4] & EXEC_QUERY & select r_object_id from dm_user where user_name = 'Morgan Patou', FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T]],50,true,true)
68354.167 & 0.000 & [http--0.0.0.0-9082-4] & multiNext &
68354.167 & 0.002 & [http--0.0.0.0-9082-4] & IsCurrent & 110f123450001d07
68354.170 & 0.003 & [http--0.0.0.0-9082-4] & EXEC_QUERY & SELECT COUNT(*) AS items FROM dm_group WHERE group_name = 'report_user' AND ANY i_all_users_names = 'Morgan Patou', FOR_UPDATE=T, BATCH_HINT=50, BOF_DQL=T, FLUSH_BATCH=-1]],50,true,true)
68354.173 & 0.001 & [http--0.0.0.0-9082-4] & multiNext &
68354.175 & 0.003 & [http--0.0.0.0-9082-4] & EXEC_QUERY & select r_object_id from dm_sysobject where folder ('/myInsight') and object_name = 'myInsight.license', FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T]],50,true,true)
68354.178 & 0.001 & [http--0.0.0.0-9082-4] & multiNext &
68354.179 & 0.001 & [http--0.0.0.0-9082-4] & IsCurrent & 090f123450023f63
68354.165 & 0.010 & [http--0.0.0.0-9082-3] & SysObjGetPermit & 0b0f12345004f0de
68354.175 & 0.006 & [http--0.0.0.0-9082-3] & SysObjGetXPermit & 0b0f12345004f0de
68354.181 & 0.006 & [http--0.0.0.0-9082-4] & MAKE_PULLER & null
68354.187 & 0.000 & [http--0.0.0.0-9082-4] & getBlock &
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$
[dmadmin@content_server_01 dfc_tracing]$ head -35 output_py_2.log

****** PROFILE OF rpc CALLS *****
     3.273           0.080              41      AUTHENTICATE_USER
     0.032           0.002              17      BEGIN_TRANS
     0.001           0.000              14      END_PUSH_V2
     0.202           0.012              17      END_TRANS
    21.898           0.071             310      EXEC_QUERY
     0.028           0.005               6      FETCH_CONTENT
     0.011           0.000              55      GET_ERRORS
     0.117           0.004              27      GET_LOGIN
     0.290           0.002             163      IsCurrent
     0.013           0.000              82      KILL_PULLER
     0.003           0.000              14      KILL_PUSHER
     0.991           0.012              82      MAKE_PULLER
     0.005           0.000              14      MAKE_PUSHER
     0.002           0.000               5      NEXT_ID_LIST
     0.083           0.002              38      NORPC
     0.015           0.005               3      RelationCopy
     0.446           0.032              14      SAVE
     0.274           0.014              20      SAVE_CONT_ATTRS
     0.140           0.010              14      START_PUSH
     0.134           0.045               3      SysObjCheckin
     0.048           0.016               3      SysObjCheckout
     2.199           0.009             240      SysObjFullFetch
     0.913           0.006             141      SysObjGetPermit
     0.764           0.005             141      SysObjGetXPermit
     0.642           0.046              14      SysObjSave
     0.033           0.000              82      getBlock
     1.454           0.004             399      multiNext

**** QUERY RESPONSE SORTED IN DESCENDING ORDER ****

10.317  select distinct wf.object_name as workflow_name, pr.object_name as process_name, i.name as Performer_Name, i.task_name as Task_Name, i.date_sent as Date_Task_Sent, i.actual_start_date as Date_Task_Acquired, wf.r_creator_name as Workflow_Initiator, cd.primary_group as "group", cd.subgroup as subgroup, cd.artifact_name as Artifact_Name, cd.object_name as document_name, cd.r_version_label as version_label, cd.title as Document_Title, cd.r_object_id as object_id from cd_common_ref_model(all) cd, dmi_package p, dmi_queue_item i, dm_workflow wf, dm_process pr
0.607   select r_object_id from dm_sysobject where folder ('/myInsight/Presentations/Standard Presentations/Graphical Reports') and object_name = 'FusionInterface.xsl', FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T]],50,true,true)
0.505   select r_object_id from dm_sysobject where folder ('/myInsight/Presentations/Life Sciences') and object_name = 'Unique Templates.xsl', FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T]],50,true,true)
[dmadmin@content_server_01 dfc_tracing]$

 

To conclude this blog on a more philosophical note: always question what other people ask you to do and think twice before doing the same thing over and over again. ;)

 

 

Cet article Documentum – DFC traces setup & investigation est apparu en premier sur Blog dbi services.

email in PL/SQL

Tom Kyte - Fri, 2017-11-24 19:46
Tom: 1. If you want to write something in PL/SQL application server that send email automatically to an administrator requesting approval after a user create a record, which oracle package would be using. Would you also use an after insert tri...
Categories: DBA Blogs

How to define a composite primary key

Tom Kyte - Fri, 2017-11-24 19:46
I have a table with two columns that need to be defined as primary keys, order_number and item_number. A table can have only one primary key, but I can define composite primary keys in the table_constraint syntax. How do I define two columns ...
Categories: DBA Blogs

#DOAG2017

Yann Neuhaus - Fri, 2017-11-24 16:02

CaptureGPTW

The discussions about the technologies we love. With Bryn about my tests on the MLE and the fact that I compared very different things, running a recursive function on different datatype (integer vs. number). With Mike about the way RUs will be recommended and RURs only for very special cases. With Nigel about the ODC Database Ideas, with Stefan about what is documented or not, with… Discussions about community also, and user groups.

The trip, where meeting fellow speakers start in the plane,…

The dinners with ACEs, with Speakers, with friends…

The beers, thanks to the Pieter & Philippe for sharing Belgian beers & cheese & mustard & celery salt & your good mood

The sessions of course. Kamil’s tool to show tablespace fragmentation visually, Jan’s comparison between Oracle and EDB, Philippe & Pieter technical view on GDPR, Adam’s research on NFS for his appliance,…

The party for sure,…

DSC00332My session, and the very interesting questions I got… I was lucky to speak on the first day. And proud to speak on the Oak Table stream for the first time. I was happy to see many people already with a CDB and even in production. It is a slow adoption but people come to it and finally notice that it is not a big change for daily job.

IMG_4712And colleagues of course. This is the conference where dbi services has a booth and several speakers. We are passionate and like to share. At the booth, we did some demos of Dbvisit Standby 8, Orachrome Lighty, and also the OpenDB Appliance. We meet customers, or candidatees, talk about the technologies we love, explain how we do our training workshops. It is also a great place to discuss among us. Even if we have internal projects, and two ‘dbi xChange’ events every year, we are mainly at customers and have so much to share.

DOAG is an amazing conference. Intense time compressed into 3 days. This incredibly friendly ambiance is hard to quit at the end of the conference. Fortunately, persistence and durability are guaranteed thanks to Kamil’s snapshots:

Some of the speakers at #DOAG2017 party – @MDWidlake @BrynLite @ChandlerDBA @FranckPachot @RoelH @pioro @boliniak @chrisrsaxon @phurley @kmensah @lleturgez @DBAKevlar @oraesque @MikeDietrichDE @OracleSK – it was fun :) pic.twitter.com/Oe2l26QxSp

— Kamil Stawiarski (@ora600pl) November 23, 2017

#DOAG2017 speakers dinner was awesome! pic.twitter.com/cSsUaf6VPB

— Kamil Stawiarski (@ora600pl) November 22, 2017

When you see how Kamil highlights each personality with a simple camera, can you imagine what he can do when organizing a conference? Keep an eye on POUG website.

 

Cet article #DOAG2017 est apparu en premier sur Blog dbi services.

DOAG 2017: avg_row_len with virtual columns

Yann Neuhaus - Fri, 2017-11-24 11:47

At the DOAG I attended a session “Top-level DB design for Big Data in ATLAS Experiment at CERN” provided by Gancho Dimitrov. The presentation was actually very interesting. As part of Gancho’s improvement activities to reduce space in a table he stored data in a 16 Bytes raw format (instead of a string representing hex values which requires 36 Bytes) and use virtual columns to actually calculate the real hex-string.

So the original value is e.g. 21EC2020-3AEA-4069-A2DD-08002B30309D, which is reduced to 16 Bytes by removing the ‘-‘ and converting the resulting hex-string to raw:

HEXTORAW(REPLACE(’21EC2020-3AEA-4069-A2DD-08002B30309D’, ‘-‘, ”))

The problem was that the longer virtual columns added to the average row length statistic in Oracle. I.e. here the simple testcase:


create table cern_test
(
GUID0 RAW(16)
,GUID1 RAW(16)
,GUID2 RAW(16)
,GUID0_CHAR as (SUBSTR(RAWTOHEX(GUID0),1,8)||'-'||
SUBSTR(RAWTOHEX(GUID0),9,4)||'-'||
SUBSTR(RAWTOHEX(GUID0),13,4)||'-'||
SUBSTR(RAWTOHEX(GUID0),17,4)||'-'||
SUBSTR(RAWTOHEX(GUID0),21,12))
,GUID1_CHAR as (SUBSTR(RAWTOHEX(GUID1),1,8)||'-'||
SUBSTR(RAWTOHEX(GUID1),9,4)||'-'||
SUBSTR(RAWTOHEX(GUID1),13,4)||'-'||
SUBSTR(RAWTOHEX(GUID1),17,4)||'-'||
SUBSTR(RAWTOHEX(GUID1),21,12))
,GUID2_CHAR as (SUBSTR(RAWTOHEX(GUID2),1,8)||'-'||
SUBSTR(RAWTOHEX(GUID2),9,4)||'-'||
SUBSTR(RAWTOHEX(GUID2),13,4)||'-'||
SUBSTR(RAWTOHEX(GUID2),17,4)||'-'||
SUBSTR(RAWTOHEX(GUID2),21,12))
);
 
insert into cern_test (guid0,guid1,guid2)
select HEXTORAW('21EC20203AEA4069A2DD08002B30309D'),
HEXTORAW('31DC20203AEA4069A2DD08002B30309D'),
HEXTORAW('41CC20203AEA4069A2DD08002B30309D')
from xmltable('1 to 10000');
commit;
 
exec dbms_stats.gather_table_stats(user,'CERN_TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1');
 
select avg_row_len from tabs where table_name='CERN_TEST';
 
AVG_ROW_LEN
-----------
162
 
select sum(avg_col_len) from user_tab_columns
where table_name='CERN_TEST' and column_name in ('GUID0','GUID1','GUID2');
 
SUM(AVG_COL_LEN)
----------------
51
 
select sum(avg_col_len) from user_tab_columns
where table_name='CERN_TEST' and column_name in ('GUID0_CHAR','GUID1_CHAR','GUID2_CHAR');
 
SUM(AVG_COL_LEN)
----------------
111

The question is if the computation of the average row length by Oracle is correct. I.e. should the physically non-existent virtual columns be considered?
I.e. physically they do not take space. So physically the average row length in the example above is 51, but logically it is 162. What is correct?

To answer that question it has to be checked what the average row length is used for. That information is not documented, but my assumption is that it’s actually only used for the calculation of Bytes required when doing a “select * “, i.e. all columns. That number however, may become important later on when calculating the memory required for e.g. a hash join.

Anyway, the basic question is how Oracle treats virtual columns in execution plans? I.e. does it compute the value of the virtual column when the table is accessed or does it compute the virtual column when it needs it (e.g. when fetching the row or when needing it as a column to join with). According the number “Bytes” in the execution plan the value is computed when the table is accessed:


SQL> explain plan for
2 select a.*, b.guid0 b_guid0 from cern_test a, cern_test b
3 where a.guid0_char=b.guid0_char;
 
Explained.
 
SQL> select * from table(dbms_xplan.display(format=>'+PROJECTION'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3506643611
 
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100M| 20G| 267 (83)| 00:00:01 |
|* 1 | HASH JOIN | | 100M| 20G| 267 (83)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CERN_TEST | 10000 | 527K| 23 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CERN_TEST | 10000 | 1582K| 23 (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - access("A"."GUID0_CHAR"="B"."GUID0_CHAR")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
1 - (#keys=1) "A"."GUID0_CHAR"[VARCHAR2,132], "GUID0"[RAW,16],
"GUID0"[RAW,16], "GUID1"[RAW,16], "GUID2"[RAW,16] 2 - "GUID0"[RAW,16] 3 - "GUID0"[RAW,16], "GUID1"[RAW,16], "GUID2"[RAW,16]  
23 rows selected.
 
SQL> select column_name, avg_col_len from user_tab_columns
2 where table_name='CERN_TEST' and column_name in ('GUID0','GUID0_CHAR');
 
COLUMN_NAME AVG_COL_LEN
----------------------------------- -----------
GUID0 17
GUID0_CHAR 37
 
SQL> select (10000*17)/1024 from dual;
 
(10000*17)/1024
---------------
166.015625
 
SQL> select ((10000*17)+(10000*37))/1024 from dual;
 
((10000*17)+(10000*37))/1024
----------------------------
527.34375

So according the projection at step 2 of the plan we use B.GUID0 only, but the Bytes value of 527K considers GUID0 and the virtual column GUID0_CHAR. So the calculation of Bytes is done when the table is accessed and not when the virtual column is actually needed (during the hash).

In that regard the calculation of the avg_row_len by dbms_stats with the virtual columns considered is correct.

The only issue I see are old scripts people wrote long ago, which try to compute the amount of data in a table based on its avg_row_len statistic using something like


SELECT table_name, num_rows * avg_row_len actual_size_of_data
FROM user_tables order by 2;

If there are virtual columns in the table, such a select may return too high values for “actual_size_of_data”.

REMARK: Using the old ANALYZE command to gather statistics results in a value for the avg_row_len, which considers only “real” columns. However, ANALYZE must not be used anymore of course.

 

Cet article DOAG 2017: avg_row_len with virtual columns est apparu en premier sur Blog dbi services.

nVision Performance Tuning: 9. Using Compression without the Advanced Compression Licence

David Kurtz - Fri, 2017-11-24 10:42
This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Table compression can significantly decrease the size of tables and reduce the volume of I/O required to retrieve data.  Compression of the ledger, ledger budget, and summary ledger tables can significantly improve the performance of scans in nVision.
The Advanced Compression licence enables compression features to be used where segments are still updated by the application.  Without this licence, only simple table compression can be used, although Hybrid Column Compression (HCC) can only be used on an engineered system.  Neither forms of compression can be used in on-line line transaction processing mode.  A table that is marked for compression, but that is populated in conventional path mode will not be compressed.  Any compressed blocks will no longer be compressed after being updated.
However, in most systems, ledger rows cease to be updated within a month or two of the period closing.  The ledger tables will usually be partitioned by fiscal year and accounting period, so a month after the period has closed the corresponding partition will cease to be updated.  Therefore, it could then be compressed.
I usually compress historical partitions when I introduce partitioning.  This following example comes from an engineered system, so Hybrid Columnar Compression has been used.
  • There is one range partition each for the whole of fiscal years 2014 and 2015.  This data is historical and rarely queried, and then not by a single period.  A more aggressive compression QUERY HIGH has been used.  
  • Monthly partitioning is used for the previous fiscal year, 2016.  These partitions are compressed, but using QUERY LOW which should deliver better performance with lower CPU overhead than QUERY HIGH (although usually there isn't much in it).
  • Partition LEDGER_2017_05 is not partitioned because it is current (at the time of building this script) and could still be updated by the application.
CREATE TABLE sysadm.ps_ledger
(...)
TABLESPACE GLLARGE
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(FISCAL_YEAR,ACCOUNTING_PERIOD)
SUBPARTITION BY LIST (LEDGER)
(PARTITION ledger_2014 VALUES LESS THAN (2015,0) PCTFREE 0 COMPRESS FOR QUERY HIGH
(SUBPARTITION ledger_2014_actuals VALUES ('ACTUALS')

,SUBPARTITION ledger_2014_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015 VALUES LESS THAN (2016,0) PCTFREE 0 COMPRESS FOR QUERY HIGH
(SUBPARTITION ledger_2015_actuals VALUES ('ACTUALS')

,SUBPARTITION ledger_2015_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2016_bf VALUES LESS THAN (2016,1) PCTFREE 0 COMPRESS FOR QUERY LOW
(SUBPARTITION ledger_2016_bf_actuals VALUES ('ACTUALS')

,SUBPARTITION ledger_2016_bf_z_others VALUES (DEFAULT)
)

,PARTITION ledger_2017_05 VALUES LESS THAN (2017,6)
(SUBPARTITION ledger_2017_05_actuals VALUES ('ACTUALS')

,SUBPARTITION ledger_2017_05_z_others VALUES (DEFAULT)
)

)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/
As periods close there are more partitions that can be compressed.  However, it may not be practical to take an outage to rebuild ledger partitions each month, and it certainly isn't practical to rebuild the entire ledger table every month.  Instead, from 11g, partitions can be rebuilt in place in an online operation.  The compression attribute can only be specified on the partition, and then the sub-partitions can be rebuilt.  The data in the partition can still be read while it is being moved.
ALTER TABLE ps_ledger MODIFY PARTITON ledger_2017_05 PCTFREE COMPRESS FOR QUERY LOW;
ALTER TABLE ps_ledger MODIFY SUBPARTITION ledger_2017_05_actuals ONLINE PARALLEL 32;

ALTER TABLE ps_ledger MODIFY SUBPARTITION ledger_2017_05_z_others ONLINE PARALLEL 32;
NB: Moving a partition online can fail in 12.1.0.1 or later of the database due to bug 2070300.  This is documented in MOS Doc ID 2040742.1.
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
The bug is fixed in 12.2 and can be addressed in 12.1 by patch 2070300.

Docker-CE: Get Started Tutorial - processes / files / HTTP headers

Dietrich Schroff - Thu, 2017-11-23 14:37
Inside my docker installation on ubuntu server i performed the official docker tutorial part 2: "containers"

Ok. Not really interesting, because the tutorial describes all steps very well (except the point, that you have to install python3-pip and you have to run pip3 and not pip).

Here the interesting part:

After i started the container ("docker run -p 4000:80 friendlyhello") i got the following:
root@ubuntuserver:/var/lib/docker/image# docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS                  NAMES
6f187950452f        friendlyhello       "python app.py"     2 hours ago         Up 2 hours          0.0.0.0:4000->80/tcp   keen_bartik
and the OS displayed

root@ubuntuserver:/var/lib/docker/image# ps x |grep docker
 5852 ?        Ssl    0:18 /usr/bin/dockerd -H fd://
 5867 ?        Ssl    0:10 docker-containerd --config /var/run/docker/containerd/containerd.toml
17100 ?        Sl     0:00 /usr/bin/docker-proxy -proto tcp -host-ip 0.0.0.0 -host-port 4000 -container-ip 172.17.0.2 -container-port 80
17105 ?        Sl     0:00 docker-containerd-shim --namespace moby --workdir /var/lib/docker/containerd/daemon/io.containerd.runtime.v1.linux/moby/6f187950452f501cf18d7efd673e8305d6c9752c58acfb14918ae37341952a11 --address /var/run/docker/containerd/docker-containerd.sock --runtime-root /var/run/docker/runtime-runc
So the containerid is part of the directory which is loaded with "--workdir"

The HTTP headers showup the following:

So the process "docker-proxy-proto" does only a portforwarding from port 4000 to port 80 and the webserver is provided by python:
Content-Type: text/html; charset=utf-8
Content-Length: 118
Server: Werkzeug/0.12.2 Python/2.7.14
Date: Sun, 19 Nov 2017 12:20:42 GMT Here the process tree:
# pstree -al
systemd
  ├─dockerd -H fd://
  │   ├─docker-containe --config /var/run/docker/containerd/containerd.toml
  │   │   ├─docker-containe --namespace moby --workdir /var/lib/docker/containerd/daemon/io.containerd.runtime.v1.linux/moby/6f187950452f501cf18d7efd673e8305d6c9752c58acfb14918ae37341952a11 --address /var/run/docker/containerd/docker-containerd.sock --runtime-root /var/run/docker/runtime-runc
  │   │   │   ├─python app.py
  │   │   │   └─9*[{docker-containe}]
  │   │   └─7*[{docker-containe}]
  │   ├─docker-proxy -proto tcp -host-ip 0.0.0.0 -host-port 4000 -container-ip 172.17.0.2 -container-port 80
  │   │   └─4*[{docker-proxy}]
  │   └─9*[{dockerd}]
Note that this container has the ip 172.17.0.2. This is part of a private network and the ubuntu server has the ip
# ip address show docker0
3: docker0: mtu 1500 qdisc noqueue state UP group default
    link/ether 02:42:f4:04:06:3d brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 scope global docker0
       valid_lft forever preferred_lft forever
If you start the container twice
root@ubuntuserver:~/dockerapp# docker run -d -p 4000:80 friendlyhello
aebe1a25dd2dda1c36c5cdd46bbdc138099711c885a7fb270b9981301a66fbd9
root@ubuntuserver:~/dockerapp# docker run -d -p 4001:80 friendlyhello
59276d1e15d7a636dbcf4eb599f7a948365eed656d93d47097999bee9b25db6b
there are two docker-proxies running:
# ps aux|grep docker-proxy
root      1286  0.0  0.3 304928  3084 ?        Sl   16:16   0:00 /usr/bin/docker-proxy -proto tcp -host-ip 0.0.0.0 -host-port 4000 -container-ip 172.17.0.2 -container-port 80
root      1385  0.0  0.2 304928  2880 ?        Sl   16:16   0:00 /usr/bin/docker-proxy -proto tcp -host-ip 0.0.0.0 -host-port 4001 -container-ip 172.17.0.3 -container-port 80


rman checksyntax not reliable

Michael Dinh - Thu, 2017-11-23 10:42

There is no syntax error reported but backup will fail.
Can you spot where the error is at?

[oracle@racnode-dc1-1 backup]$ rman checksyntax @ bkupinc0.rman

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Nov 23 17:14:31 2017

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

RMAN> connect target;
2> set echo on
3> set command id to "LEVEL0";
4> show all;
5> run {
6> set controlfile autobackup format for device type disk to '/sf_working/backup/%d_%I_%F.ctl';
7> allocate channel d1 device type disk format '/sf_working/backup/%d_%I_%T_%U.LEVEL0' MAXPIECESIZE 512 M MAXOPEMFILES 1;
8> backup as compressed backupset
9> incremental level 0 check logical database
10> filesperset 1 tag="LEVEL0"
11> tag="LEVEL0"
12> plus archivelog
13> filesperset 8 tag="LEVEL0"
14> tag="LEVEL0"
15> ;
16> alter database backup controlfile to trace as '/sf_working/backup/cf_@.sql' REUSE RESETLOGS;
17> create pfile='/sf_working/backup/init@.ora' from spfile;
18> }
19> report schema;
20> list backup summary;
21> exit
The cmdfile has no syntax errors

Recovery Manager complete.
[oracle@racnode-dc1-1 backup]$

TensorFlow - Getting Started with Docker Container and Jupyter Notebook

Andrejus Baranovski - Thu, 2017-11-23 10:38
I'm studying Machine Learning and would like to share some intro experience working with TensorFlow. To get started with TensorFlow you need to install it, easiest way (at least for me) was to run TensorFlow using Docker. Read installation instructions - Installing TensorFlow.

Once TensorFlow Docker image is installed. I suggest to create container in detach mode (--detach=true) and provide port for Jupyter UI. Make sure to provide meaningful name for Docker container:

docker run --detach=true --name RedSamuraiTensorFlowUI -it -p 8888:8888 gcr.io/tensorflow/tensorflow

Make sure to start and stop container using Docker start/stop commands, don't run and create container each time (this way you will loose your work, since new container will be created each time):

docker start RedSamuraiTensorFlowUI (docker stop RedSamuraiTensorFlowUI)

Once container is running in detached mode, you can access logs by executing docker logs command and specifying container name:

docker logs -f RedSamuraiTensorFlowUI

At this point you should see output in Docker container log, copy URL to Jupyter UI with token and paste it to the browser (for example: http://localhost:8888/?token=d0f617a4c719c40ea39a3732447d67fd40ff2028bb335823):


This will give you access to Jupyter UI. Is possible to run TensorFlow Python scripts directly through command line in Docker environment, but is more convenient to do the same through UI:


UI gives option to create new Terminal session:


Terminal allows to run Python code using command line:


Instead of using command line, more convenient is to create new notebook:


Notebook environment allows to type in Python code and execute math calculations. In the example below I multiply two arrays (1x5, 2x6, 3x7, 4x8) in Python code through TensorFlow library. Result is printed through TensorFlow session object right below and prompt for the next command is displayed - very convenient:


Jupyter UI allows to track running notebooks and terminals:


Whatever action you do in Jupyter UI, it can be tracked using log printed in Docker container log. Jupyter UI is client side JS application:


To double check Docker config, I have TensorFlow Docker image:


And Docker container, which can be started/stopped by name (see command listed above), without running new Docker container every time during restart:

[K21Academy Weekly Newsletter] 171123 Happy Thanksgiving & Happy Holidays if you are in USA or Canada

Online Apps DBA - Thu, 2017-11-23 08:04

[K21Academy Weekly Newsletter] 171123 Happy Thanksgiving & Happy Holidays if you are in USA or Canada. Thanks for registering my Weekly Newsletter where you get latest Updates, Tips & How To Get related to Oracle. In this Week, you will find: 1. Oracle EBS (R12) on Cloud for Beginners: 15 Must-Know Things 2. Oracle Fusion […]

The post [K21Academy Weekly Newsletter] 171123 Happy Thanksgiving & Happy Holidays if you are in USA or Canada appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

DOAG 2017

Yann Neuhaus - Thu, 2017-11-23 08:00

Als Consultant bei dbi services war ich die letzten 2 Jahre hauptsächlich in Konsolidierungsprojekten basierend auf den Oracle Engineered Systems unterwegs. Deshalb waren für mich natürlich die Vorträge über die neue Generation der Oracle Database Appliance X7-2 interessant.

Aus meiner Sicht hat Oracle den richtigen Schritt getan und die Vielfalt der ODA Systeme wie es sie noch in der X6-2 Generation gab (Small/Medium/Large und HA) reduziert.

Künftig wird es die ODA X7-2 nur noch in 3 Modellen (S, M und HA) geben, wobei klar zu sagen ist, dass die kleineren Systeme leistungstechnisch aufgewertet wurden und die HA endlich wieder in einer Ausstattung zur Verfügung steht, die eine Konsolidierung auch von größeren Datenbank- und Applikationssystemen ermöglicht:

  • die ODA X7-2 S als Einsteigersystem mit einer 10 Core CPU, bis zu 384 GB RAM und 12,8 TB NVMe Storage
  • die ODA X7-2 M entspricht nun eher den X6 L Systemen mit 2×18 Core, bis zu 768 GB RAM und bis 51,2 TB NVMe Storage
  • die ODA X7-2 HA ist natürlich das Flaggschiff der ODA Klasse. 2 Server mit je 2×18 Cores, bis zu 768 GB RAM pro Server und diversen Storage Erweiterungen bis 150 TB geben einem das alte X5 Gefühl (oder vielleicht schon mit einer Exadata) zu arbeiten

Die für mich interessantesten Neuerungen sind weniger im Hardwarebereich zu finden, sondern viel mehr in den möglichen Deployments der Systeme:

  • alle Systeme unterstützen SE/SE1/SE2/EE 11.2.0.4, 12.1.0.2 und 12.2.01
  • alle Systeme unterstützen ein virtualisiertes Setup, die kleinen Systeme mit KVM, die X7-2 HA mit KVM und OVM, wobei bis jetzt noch kein Hardpartitioning mit KVM möglich, aber in Planung ist
  • auf der X7-2 HA kann bei den Storage Erweiterungen zwischen High Performance (SSD) und High Capacity (HDD) gewählt werden, sogar Mischformen sind mit Einschränkungen möglich

Eingespart wurde allerdings bei den Netzwerkschnittstellen, hier gibt es nur noch 2 Interfaces statt bisher 4 wie an der X5-2 (neben den privaten Interfaces für den Interconnect). Es gibt zwar die Möglichkeit ein weiteres Interface nach dem Deployment zu konfigurieren, allerdings nur mit 1GB. Geplant ist zwar, dass künftig auch im Bare Metal Setup VLAN Konfigurationen auf dem Public Interface möglich sind, trotzdem hätte man (insbesondere der HA) noch zwei zusätzliche Interfaces spendieren können, zudem Steckplätze vorhanden sind.

Hoch interessant sind die Leistungsdaten des NVMe bzw. SSD Storage. Hier sind bis zu 100 000 IOPS möglich, bei der HA sehe ich als begrenzenden Faktor eher den SAS Bus als die SDDs. Was wirklich schön ist, dass der Storage für die Redo Logs auf 4x800GB SSD erweitert wurde, hier musste man in den früheren Systemen immer etwas sparsam sein…

Alles in allem freue ich mich darauf mit der X7-2 zu arbeiten, denn Oracle stellt hier ein gutes Stück Hardware bereit, das auch preislich im Rahmen bleibt.

 

 

 

 

 

 

 

 

Cet article DOAG 2017 est apparu en premier sur Blog dbi services.

Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ?

Tom Kyte - Thu, 2017-11-23 07:06
Hello, teams:-) Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ? There has an example that I have given in Oracle 11.2.0.4.0. <code> SYS@orcl28> select round(sum(user_bytes)/(1024*1024*1024),2) fro...
Categories: DBA Blogs

Sending HTML using UTL_SMTP

Tom Kyte - Thu, 2017-11-23 07:06
Hi Tom I hope I'm phrasing this correctly... I'd like to be able to send an HTML formatted email from the database using the UTL_SMTP package. I don't see any way of setting the MIME type. Is this beyond the scope of UTL_SMTP? thanks in ...
Categories: DBA Blogs

CRS-2674: Start of dbfs_mount failed

Michael Dinh - Wed, 2017-11-22 19:04

$ crsctl start resource dbfs_mount
CRS-2672: Attempting to start ‘dbfs_mount’ on ‘node2’
CRS-2672: Attempting to start ‘dbfs_mount’ on ‘node1’
CRS-2674: Start of ‘dbfs_mount’ on ‘node1’ failed
CRS-2679: Attempting to clean ‘dbfs_mount’ on ‘node1’
CRS-2674: Start of ‘dbfs_mount’ on ‘node2’ failed
CRS-2679: Attempting to clean ‘dbfs_mount’ on ‘node2’
CRS-2681: Clean of ‘dbfs_mount’ on ‘node1’ succeeded
CRS-2681: Clean of ‘dbfs_mount’ on ‘node2’ succeeded
CRS-4000: Command Start failed, or completed with errors.

Check to make sure DBFS_USER password is not expired.


Conditional index

Tom Kyte - Wed, 2017-11-22 12:46
Tom, Thanks for taking my question. I am trying to conditionally index rows in a table. In SQL Server 2008 there is a feature called filtered indexes that allows you to create an index with a where clause. So I have a table abc: <code>create...
Categories: DBA Blogs

Transfer data from one db to another db over db link using trigger

Tom Kyte - Wed, 2017-11-22 12:46
Hi, I am working on a project in which data marts are involved. We are creating triggers to transfer data from OLTP DB to data mart (Online extraction). Following is the code of a trigger for a table involving clob column. I have seen different solut...
Categories: DBA Blogs

Create index CONCURRENTLY in PostgreSQL

Yann Neuhaus - Wed, 2017-11-22 12:10

In PostgreSQL when you create an index on a table, sessions that want to write to the table must wait until the index build completed by default. There is a way around that, though, and in this post we’ll look at how you can avoid that.

As usual we’ll start with a little table:

postgres=# \! cat a.sql
drop table if exists t1;
create table t1 ( a int, b varchar(50));
insert into t1
select a.*, md5(a::varchar) from generate_series(1,5000000) a;
postgres=# \i a.sql
DROP TABLE
CREATE TABLE
INSERT 0 5000000

When you now create an index on that table and try to write the table at the same time from a different session that session will wait until the index is there (the screenshot shows the first session creating the index on the left and the second session doing the update on the right, which is waiting for the left one):
Selection_007

For production environments this not something you want to happen as this can block a lot of other sessions especially when the table in question is heavily used. You can avoid that by using “create index concurrently”.

Selection_008

Using that syntax writes to the table from other sessions will succeed while the index is being build. But, as clearly written in the documentation: The downside is that the table needs to be scanned twice, so more work needs to be done which means more resource usage on your server. Other points need to be considered as well. When, for whatever reason, you index build fails (e.g. by canceling the create index statement):

postgres=# create index concurrently i1 on t1(a);
^CCancel request sent
ERROR:  canceling statement due to user request

… you maybe would expect the index not to be there at all but this is not the case. When you try to create the index right after the canceled statement again you’ll hit this:

postgres=# create index concurrently i1 on t1(a);
ERROR:  relation "i1" already exists

This does not happen when you do not create the index concurrently:

postgres=# create index i1 on t1(a);
^CCancel request sent
ERROR:  canceling statement due to user request
postgres=# create index i1 on t1(a);
CREATE INDEX
postgres=# 

The questions is why this happens in the concurrent case but not in the “normal” case? The reason is simple: When you create an index the “normal” way the whole build is done in one transaction. Because of this the index does not exist when the transaction is aborted (the create index statement is canceled). When you build the index concurrently there are multiple transactions involved: “In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions”. So in this case:

postgres=# create index concurrently i1 on t1(a);
ERROR:  relation "i1" already exists

… the index is already stored in the catalog:

postgres=# create index concurrently i1 on t1(a);
^CCancel request sent
ERROR:  canceling statement due to user request
postgres=# select relname,relkind,relfilenode from pg_class where relname = 'i1';
 relname | relkind | relfilenode 
---------+---------+-------------
 i1      | i       |       32926
(1 row)

If you don’t take care of that you will have invalid indexes in your database:

postgres=# \d t1
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 a      | integer               |           |          | 
 b      | character varying(50) |           |          | 
Indexes:
    "i1" btree (a) INVALID

You might think that this does not harm, but then consider this case:

-- in session one build a unique index
postgres=# create unique index concurrently i1 on t1(a);
-- then in session two violate the uniqueness after some seconds
postgres=# update t1 set a = 5 where a = 4000000;
UPDATE 1
-- the create index statement will fail in the first session
postgres=# create unique index concurrently i1 on t1(a);
ERROR:  duplicate key value violates unique constraint "i1"
DETAIL:  Key (a)=(5) already exists.

This is even worse as the index now really consumes space on disk:

postgres=# select relpages from pg_class where relname = 'i1';
 relpages 
----------
    13713
(1 row)

The index is invalid, of course and will not be used by the planner:

postgres=# \d t1
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 a      | integer               |           |          | 
 b      | character varying(50) |           |          | 
Indexes:
    "i1" UNIQUE, btree (a) INVALID

postgres=# explain select * from t1 where a = 12345;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Gather  (cost=1000.00..82251.41 rows=1 width=37)
   Workers Planned: 2
   ->  Parallel Seq Scan on t1  (cost=0.00..81251.31 rows=1 width=37)
         Filter: (a = 12345)
(4 rows)

But the index is still maintained:

postgres=# select relpages from pg_class where relname = 'i1';
 relpages 
----------
    13713
(1 row)
postgres=# insert into t1 select a.*, md5(a::varchar) from generate_series(5000001,6000000) a;
INSERT 0 1000000

postgres=# select relpages from pg_class where relname = 'i1';
 relpages 
----------
    16454
(1 row)

So now you have an index which can not be used to speed up queries (which is bad) but the index is still maintained when you write to the table (which is even worse because you consume resources for nothing). The only way out of this is to drop and re-create the index:

postgres=# drop index i1;
DROP INDEX
-- potentially clean up any rows that violate the constraint and then
postgres=# create unique index concurrently i1 on t1(a);
CREATE INDEX
postgres=# \d t1
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 a      | integer               |           |          | 
 b      | character varying(50) |           |          | 
Indexes:
    "i1" UNIQUE, btree (a)

postgres=# explain select * from t1 where a = 12345;
                          QUERY PLAN                           
---------------------------------------------------------------
 Index Scan using i1 on t1  (cost=0.43..8.45 rows=1 width=122)
   Index Cond: (a = 12345)
(2 rows)

Remember: When a create index operations fails in concurrent mode make sure that you drop the index immediately.

One more thing to keep in mind: When you create an index concurrently and there is another session already modifying the data the create index command waits until that other operation completes:

-- first session inserts data without completing the transaction
postgres=# begin;
BEGIN
Time: 0.579 ms
postgres=# insert into t1 select a.*, md5(a::varchar) from generate_series(6000001,7000000) a;
INSERT 0 1000000
-- second sessions tries to build the index
postgres=# create unique index concurrently i1 on t1(a);

The create index operation will wait until that completes:

postgres=# select query,state,wait_event,wait_event_type from pg_stat_activity where state ='active';
                                query                                 | state  | wait_event | wait_event_t
----------------------------------------------------------------------+--------+------------+-------------
 create unique index concurrently i1 on t1(a);                        | active | virtualxid | Lock
 select query,state,wait_event,wait_event_type from pg_stat_activity; | active |            | 

… meaning when someone forgets to end the transaction the create index command will wait forever. There is the parameter idle_in_transaction_session_timeout which gives you more control on that but still you need to be aware what is happening here.

Happy index creation :)

 

Cet article Create index CONCURRENTLY in PostgreSQL est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator