Skip navigation.

Yann Neuhaus

Syndicate content
All blog entries from http://www.dbi-services.com/
Updated: 4 weeks 2 days ago

SharePoint 2013 - Office 365 & Power BI

Wed, 2015-06-24 03:00

 alt

Quick reminder of what is SharePoint 2013 and Office 365

SharePoint 2013

SharePoint 2013 is a collaborative platform that allows organizations to increase the efficiency of their business processes.

link: https://technet.microsoft.com/en-us/library/cc303422.aspx

Office 365 with SharePoint 2013

This is the Online version of SharePoint 2013.

When you sign in to Office 365, or your organization’s corporate intranet site, you’ll see links to Newsfeed, OneDrive, and Sites in the global navigation bar.
These are your entry points into SharePoint.

Organizations use SharePoint to create websites. You can use it as a secure place to store, organize, share, and access information from almost any device.
All you need is a web browser, such as Internet Explorer, Google Chrome, or Mozilla Firefox.

link: https://support.office.com/en-za/article/Get-started-with-SharePoint-2013-909ec2f0-05c8-4e92-8ad3-3f8b0b6cf261?ui=en-US&rs=en-ZA&ad=ZA

 

What is Power BI?

Power BI is a Microsoft Tool which gives you the "Visual Power", it means it allows you to get the best rich visuals to organize and collect data you care the most to focus on. This will keep you in the knowledge of your business activity. 

BI
 

WHAT FOR? what

Depending on the concern, Power BI:

  • MARKETING: 
    • Market Smarter: easily monitor and analyze your marketing campaigns and efficiently allocate your resources to the right channels, all in one place.
    • Monitor your campaign: will give you a view on your campaign efficacy and your tactics performances.

    • Talk to the right customers: demographic filters, customer lifetime values, etc... will help you to get specifics views on your customers activity.


  • SALES:
    • Crush your quotas: Used with Microsoft Dynamics CRM or Salesforce.com, Power BI extends and enhances these services with instant insight into your pipeline.
    • Sales management: Dashboard creation giving more visibility on results to learn from past deal, and get better goals then. 
    • Sales representative: Understand how your previous deals performed so you can execute on future deals more efficiently.



  • CUSTOMER SUPPORT: With Power BI, you will be able to track and have a better view and understanding of Customer Support Activities, drive the team to success.

    CSBI

  • DECISION MAKER: By getting all data "in one", in one dashboard shared with your team, it will help you to take the right decision on time.
  • HUMAN RESSOURCES: All information related to employees on the same dashboard. It will make your HR Meeting and Employees reviews so easiest.

    HR_BI

 

CONNECTING DATA

Dashboards, reports, and datasets are at the middle of Power BI Preview. Connect to or import datasets from a variety of sources:

  • Excel
  • GitHub
  • Google Analytics
  • Marketo
  • Microsoft Dynamics CRM
  • Microsoft Dynamics Marketing
  • Power BI Designer file
  • Salesforce
  • SendGrid
  • SQL Server Analysis Services
  • Zendesk

Data

 

POWER BI DESIGNER

Power Bi Designer is a tool with which you can create robust data models and amazing reports in order to get the best way for your Business Intelligence activities.

PowerBiDesigner

 

POWER BI MOBILE 

phone2

Stay connected to your data from anywhere, anytime with the Power BI app for Windows and iOS.

VERSIONS

There is 2 versions:

  • Power BI: FREE
  • Power BI Pro: with LICENCE ($9.99 user/month)

 

Microsoft Power BI is a user-friendly, intuitive and cloud based self-service BI solution for all your data needs in your own Excel.
including different tools for data extraction, analysis and visualization. 

 

 

Multithreaded 12c and 'connect / as sysdba'

Wed, 2015-06-24 01:21

In Oracle 12c you can run Oracle processes as operating system threads, lowering the number of OS processes. But you can't use OS authentification: you need to provide a password. Here is a way to set an environment so that you can still 'connect / as sysdba' to a multithreaded instance.

Windows

I start with Windows because Oracle has always been multithreaded on windows. Are you able to use operating system authentication then? You  think so because you can 'connect / as sysdba'. But look at your sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

You need NTS to connect locally without a password, the same authentication as when you connect remotely. If you don't set NTS then both local and remote connections need a password.

Threaded execution

Back to Linux, I've set my instance with multithreading:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
threaded_execution boolean TRUE

If I try to connect witout a password I got an error:

SQL> connect / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

it's exacly the same as if I set 

SQLNET.AUTHENTICATION_SERVICES = (NONE)

by default on Unix/Linux the AUTHENTICATION_SERVICES is not set, which allows operating system suthentication for Bequeath connections.

When multithreaded, I can only connect with a password:

SQL> connect sys as sysdba
Enter password:
Connected.

But I don't want that. I want to keep she same scripts and procedures as I had before going to multithread instance. I can put the password in an external password file (wallet) and then connect without typing the password. But then I have to use a network service name. I can use TWO_TASK environment variable to add that network service name to connections transparently, but - for waterver reason - I don't want to connect through the listener. So let's see how to set it up.

TNS_ADMIN

I'll setup my own SQL*Net files in a custom directory and use TNS_ADMIN to use them.

$ mkdir /home/franck/tns
$ export TNS_ADMIN=/home/franck/tns

Here are my ORACLE_HOME and ORACLE_SID:

$ env | grep ORACLE
ORACLE_SID=DEMO11
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12102EE
External password file

It has been described before on our blog by Nicolas Jardot.

$ mkstore -wrl $TNS_ADMIN -create
$ mkstore -wrl $TNS_ADMIN -createCredential BEQ_DEMO11_SYS SYS

this as created the wallet containing my user (SYS) and password for the network service name BEQ_DEMO111_SYS

$ ls -l
-rwxrwx---. 1 root vboxsf 589 Jun 23 23:29 cwallet.sso
-rwxrwx---. 1 root vboxsf 0 Jun 23 23:29 cwallet.sso.lck
-rwxrwx---. 1 root vboxsf 544 Jun 23 23:29 ewallet.p12
-rwxrwx---. 1 root vboxsf 0 Jun 23 23:29 ewallet.p12.lck

I have to declare the wallet in my sqlnet.ora

$ cat sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/franck/tns)))
SQLNET.WALLET_OVERRIDE=TRUE
Beqeath connection string

Now time to define that BEQ_DEMO11_SYS network service name. I want to connect locally (not through the listener) so I define a BEQ connection string:

$ cat tnsnames.ora
BEQ_DEMO11_SYS=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/u01/app/oracle/product/12102EE/bin/oracle)(ARGV0=oracleDEMO11)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_HOME=/u01/app/oracle/product/12102EE,ORACLE_SID=DEMO11'))

Here is how a beaqueath (PROTOCOL=BEQ) connection is defined. You need to define the PROGRAM to be run (the oracle binary) and the ARGS. You need to pass the environement variables - at least ORACLE_HOME and ORACLE_SID

The ARGV0 is the name that will be displayed by the ps 'CMD' command, but you can put whatever you want in it (just saying... have fun but not in prod please). The convention is to add the ORACLE_SID to the binary name 'oracle'.

Then I can connect:

SQL> connect /@BEQ_DEMO11_SYS as sysdba
Connected.
TWO_TASK

Finally, I don't want to add the network service name in my scripts, then I can set the TWO_TASK environment variable to it.  I definitely don't want to set it for all my environment because it can be misleading (you think you use the ORACLE_SID but you are not, you change environement with oraenv but TWO_TASK remains,...). So i set it locally when I run sqlplus.

Here is an example where I set TNS_ADMIN and TWO_TASK only when calling sqlplus:

$ TNS_ADMIN=$PWD TWO_TASK=BEQ_DEMO11_SYS sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 24 10:54:58 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

If the scripts does a 'connect / as sysdba' it still work:

SQL> connect / as sysdba
Connected.

but you should now that if the script is connecting with another user, TWO_TASK is still used:

SQL> connect scott/tiger
Connected.

Note that those sessions are multithreaded even if you don't set DEDICATED_THROUGH_BROKER for the listener, because you're not connecting through the listener here. More information about it in Martin Bach's post.

Here is how to check it - process and thread id from v$process:

SQL> select spid, stid, program, execution_type from v$process where addr=(select paddr from v$session where sid=sys_context('userenv','sid'));

SPID                     STID                     PROGRAM              EXECUTION_TYPE
------------------------ ------------------------ -------------------- ------------------
21107                    21107                    oracle@VM111         PROCESS

and the info about it from Linux:

SQL> host ps -Lfp &pid
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle   21107     1 21107  0    1 11:04 ?        00:00:00 oracleDEMO11 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))

 

TWO_TASK is coming from very old version but will be useful to run old scripts in 12c. Here is an example with threaded instance. You can use it also to connect directly to a PDB (but through listener then - you need a service).

But...

There is one thing that doesn't work as I want with external password files. DGMGRL keeps the password provided and uses it to connect to the remote instance - which is why you need same password for sys on standby. But let's see if it works with external password file:

$ TNS_ADMIN=$PWD TWO_TASK=BEQ_DEMO11_SYS dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> switchover to demo11
Performing switchover NOW, please wait...
New primary database "demo11" is opening...
Operation requires start up of instance "DEMO12" on database "demo12"
Starting instance "DEMO12"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up instance "DEMO12" of database "demo12"

I have to finish the switchover manually because the password retreived from the wallet is not used here. Same behaviour than OS authentication here. Tip: if you connect to the primary to do the switchover, then the connection to remote is detected at the begining.

Final note

This is not best practice. Using external password file is a good practice of course because we should never put passwords in our scripts or in command line. Passwords are something to be only typed by human fingers. TWO_TASK and BEQ connection string are not a good practice, but only a workaround to keep old scripts compatible with new features.

2 ways to move archivelogs - both need RMAN

Tue, 2015-06-23 14:03

The database must know where are the files. If you move them from the OS, the database will not be aware of it. But there is a database tool to copy or move files and update the database repository (controlfile). It's RMAN. With RMAN you can either:

  • update the repository after you've moved the file from the OS
  • or do the both: move and update the repository
The syntax is a bit weird, so let's have an example.

RMAN> CATALOG

I have the following archived logs in the /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23 directory:

[oracle@VM111 2015_06_23]$ pwd
/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23
[oracle@VM111 2015_06_23]$ ls -alrt
total 188
drwxr-x---. 5 oracle oinstall   4096 Jun 23 21:40 ..
-rw-r-----. 1 oracle oinstall 120320 Jun 23 21:44 o1_mf_1_61_brmfrl7v_.arc
-rw-r-----. 1 oracle oinstall   3584 Jun 23 21:44 o1_mf_1_62_brmfrq6c_.arc
-rw-r-----. 1 oracle oinstall   2560 Jun 23 21:44 o1_mf_1_63_brmfrsts_.arc
-rw-r-----. 1 oracle oinstall   1536 Jun 23 21:44 o1_mf_1_64_brmfrtjs_.arc
-rw-r-----. 1 oracle oinstall  30208 Jun 23 21:45 o1_mf_1_65_brmft8n5_.arc
-rw-r-----. 1 oracle oinstall   6144 Jun 23 21:45 o1_mf_1_66_brmftlpg_.arc
-rw-r-----. 1 oracle oinstall   4096 Jun 23 21:45 o1_mf_1_67_brmftr8w_.arc
-rw-r-----. 1 oracle oinstall   2560 Jun 23 21:45 o1_mf_1_68_brmftvwn_.arc
drwxr-x---. 2 oracle oinstall   4096 Jun 23 21:45 .
and I move them to /u01/DEMO/temp/:
[oracle@VM111 2015_06_23]$ mv * /u01/DEMO/temp/
my current directory is empty:
[oracle@VM111 2015_06_23]$ ls -alrt
total 8
drwxr-x---. 5 oracle oinstall 4096 Jun 23 21:40 ..
drwxr-x---. 2 oracle oinstall 4096 Jun 23 21:50 .
and the /u01 one has my archived logs:
[oracle@VM111 2015_06_23]$ ls -alrt /u01/DEMO/temp
total 188
-rw-r-----.  1 oracle oinstall 120320 Jun 23 21:44 o1_mf_1_61_brmfrl7v_.arc
-rw-r-----.  1 oracle oinstall   3584 Jun 23 21:44 o1_mf_1_62_brmfrq6c_.arc
-rw-r-----.  1 oracle oinstall   2560 Jun 23 21:44 o1_mf_1_63_brmfrsts_.arc
-rw-r-----.  1 oracle oinstall   1536 Jun 23 21:44 o1_mf_1_64_brmfrtjs_.arc
-rw-r-----.  1 oracle oinstall  30208 Jun 23 21:45 o1_mf_1_65_brmft8n5_.arc
-rw-r-----.  1 oracle oinstall   6144 Jun 23 21:45 o1_mf_1_66_brmftlpg_.arc
-rw-r-----.  1 oracle oinstall   4096 Jun 23 21:45 o1_mf_1_67_brmftr8w_.arc
-rw-r-----.  1 oracle oinstall   2560 Jun 23 21:45 o1_mf_1_68_brmftvwn_.arc
drwxr-x---. 10 oracle oinstall   4096 Jun 23 21:49 ..
drwxr-xr-x.  2 oracle oinstall   4096 Jun 23 21:50 .
[oracle@VM111 2015_06_23]$

But let's list the archived logs from RMAN:

[oracle@VM111 2015_06_23]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 23 21:50:48 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEMO11 (DBID=684456715)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
82      1    61      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc

84      1    62      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc

86      1    63      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc

88      1    64      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc

90      1    65      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc

92      1    66      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc

94      1    67      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc

96      1    68      A 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc
the repository (in the controlfile) still have the old location. If I need the redologs to recover the database, then it will fail.

The CROSSCHECK command can be used so that RMAN verifies if the files are still there:

RMAN> crosscheck archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc RECID=82 STAMP=883172658
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc RECID=84 STAMP=883172663
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc RECID=86 STAMP=883172665
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc RECID=88 STAMP=883172666
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc RECID=90 STAMP=883172712
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc RECID=92 STAMP=883172722
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc RECID=94 STAMP=883172728
validation failed for archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc RECID=96 STAMP=883172731
Crosschecked 8 objects
validation failed for all of them. They are marked as EXPIRED:
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
82      1    61      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc

84      1    62      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc

86      1    63      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc

88      1    64      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc

90      1    65      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc

92      1    66      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc

94      1    67      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc

96      1    68      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc
which means that the database do not know any archived logs now.

I did only one part of the job. Now I need to register the new location with the CATALOG command:

RMAN> catalog start with '/u01/DEMO/temp';

searching for all files that match the pattern /u01/DEMO/temp

List of Files Unknown to the Database
=====================================
File Name: /u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc
File Name: /u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc
File Name: /u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc
File Name: /u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc
File Name: /u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc
File Name: /u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc
File Name: /u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc
File Name: /u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc
File Name: /u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc
File Name: /u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc
File Name: /u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc
File Name: /u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc
File Name: /u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc
File Name: /u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc
File Name: /u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc
the file types and the header is read to see if the file belongs to this database. Then they are registered. Before listing them, I remove the expired entries:
RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
82      1    61      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc

84      1    62      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc

86      1    63      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc

88      1    64      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc

90      1    65      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc

92      1    66      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc

94      1    67      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc

96      1    68      X 23-JUN-15
        Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc RECID=82 STAMP=883172658
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc RECID=84 STAMP=883172663
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc RECID=86 STAMP=883172665
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc RECID=88 STAMP=883172666
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc RECID=90 STAMP=883172712
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc RECID=92 STAMP=883172722
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc RECID=94 STAMP=883172728
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc RECID=96 STAMP=883172731
Deleted 8 EXPIRED objects
and I can verify that a crosscheck validates all my files from the new location:
RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc RECID=104 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc RECID=102 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc RECID=103 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc RECID=97 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc RECID=101 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc RECID=99 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc RECID=100 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc RECID=98 STAMP=883173169
Crosschecked 8 objects

RMAN> BACKUP AS COPY

Let's do the same in one command. RMAN is there to do backups. Backups can go to backupsets or they can be a simple copy with BACKUP AS COPY. The destination is defined with the backup FORMAT string. And if we want to move instead of copy, we just add the DELETE INPUT.

RMAN> backup as copy archivelog all format '/u03/DEMO/temp/%U' delete input;

Starting backup at 23-JUN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=69 RECID=106 STAMP=883173353
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-69_T-1_A-880845261_0bqa8a0b RECID=109 STAMP=883173387
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_69_brmgg9on_.arc RECID=106 STAMP=8
83173353
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=61 RECID=104 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-61_T-1_A-880845261_0cqa8a0d RECID=110 STAMP=883173389
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc RECID=104 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=65 RECID=101 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-65_T-1_A-880845261_0dqa8a0e RECID=111 STAMP=883173390
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc RECID=101 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=70 RECID=108 STAMP=883173387
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-70_T-1_A-880845261_0eqa8a0f RECID=112 STAMP=883173391
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_70_brmghct5_.arc RECID=108 STAMP=8
83173387
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=66 RECID=99 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-66_T-1_A-880845261_0fqa8a0g RECID=113 STAMP=883173392
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc RECID=99 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=67 RECID=100 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-67_T-1_A-880845261_0gqa8a0h RECID=114 STAMP=883173393
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc RECID=100 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=62 RECID=102 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-62_T-1_A-880845261_0hqa8a0i RECID=115 STAMP=883173394
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc RECID=102 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=63 RECID=103 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-63_T-1_A-880845261_0iqa8a0j RECID=116 STAMP=883173395
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc RECID=103 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=68 RECID=98 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-68_T-1_A-880845261_0jqa8a0k RECID=117 STAMP=883173396
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc RECID=98 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=64 RECID=97 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-64_T-1_A-880845261_0kqa8a0l RECID=118 STAMP=883173397
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc RECID=97 STAMP=883173169
Finished backup at 23-JUN-15
The syntax is very different from a move command but it's the same. The file names may have changed (because of the %U format) but who cares? Only RMAN should know what is inside the files. You have a repository (controlfile or rman catalog) which knows all the attributes about the files (DBID, thread#, sequence#, SCN, etc) so better rely on that rather than on a file name and timestamp.

Look at the files, they are now in my third destination:

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
110     1    61      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-61_T-1_A-880845261_0cqa8a0d

115     1    62      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-62_T-1_A-880845261_0hqa8a0i

116     1    63      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-63_T-1_A-880845261_0iqa8a0j

118     1    64      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-64_T-1_A-880845261_0kqa8a0l

111     1    65      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-65_T-1_A-880845261_0dqa8a0e

113     1    66      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-66_T-1_A-880845261_0fqa8a0g

114     1    67      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-67_T-1_A-880845261_0gqa8a0h

117     1    68      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-68_T-1_A-880845261_0jqa8a0k

109     1    69      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-69_T-1_A-880845261_0bqa8a0b

112     1    70      A 23-JUN-15
        Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-70_T-1_A-880845261_0eqa8a0f
and a crosscheck validates that they are accesible there:
RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-61_T-1_A-880845261_0cqa8a0d RECID=110 STAMP=883173389
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-62_T-1_A-880845261_0hqa8a0i RECID=115 STAMP=883173394
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-63_T-1_A-880845261_0iqa8a0j RECID=116 STAMP=883173395
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-64_T-1_A-880845261_0kqa8a0l RECID=118 STAMP=883173397
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-65_T-1_A-880845261_0dqa8a0e RECID=111 STAMP=883173390
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-66_T-1_A-880845261_0fqa8a0g RECID=113 STAMP=883173392
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-67_T-1_A-880845261_0gqa8a0h RECID=114 STAMP=883173393
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-68_T-1_A-880845261_0jqa8a0k RECID=117 STAMP=883173396
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-69_T-1_A-880845261_0bqa8a0b RECID=109 STAMP=883173387
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-70_T-1_A-880845261_0eqa8a0f RECID=112 STAMP=883173391
Crosschecked 10 objects

Conclusion

Which one do you prefer? Managing database files from the OS is old-style. You are doing your backups with RMAN, so why not use RMAN to do any operations on the files. Then you are sure that the repository is up to date. When you will need your archivelogs to recover, you don't want to waste time finding where an archived logs has been moved one day by a collegue that forgot to re-catalog them because of the emergency situation.

SQL Server 2016 : availability groups and the new SSISDB support

Tue, 2015-06-23 12:25

This blog post is focused on the new supportability of SSIDB catalog on AlwaysOn architecture.

Others studies are available here:

 

A couple of weeks ago, I was involved in an SSIS infrastructure project with SQL Server 2014. As you know, the SSIS architecture has fundamentally changed since SQL Server 2012 and has lead to a new way of administrating it from the DBA perspective. This is also particularly true when we have to take into account an AlwaysOn architecture with the new SSISDB catalog since SQL Server 2014..

Indeed, when you want to include the SSISDB catalog to an SQL Server 2014 availability group, you have to perform some extra steps that are required according to the Microsoft SSIS blog post here. The task consists in creating manually some SQL Server jobs to leverage a failover event that requires the re-encryption of the database master key by the service master key on the new primary replica. Likewise, you will have to deal with the SSIS Server Maintenance job that is not AlwaysOn aware by default. Thus, deploying the SSISDB catalog in an availability group’s environment is not an easy task with SQL Server 2014 but let’s take a look at the new support AlwaysOn support with SQL Server 2016.

Fortunately, Microsoft has built on the experience gained from the previous version. Unlike SQL Server 2014, the next version will provide an easier way to deploy and manage the SSISDB catalog in an AlwaysOn infrastructure. By referring to the BOL here, you will notice that all the configuration stuff is done directly from the availability groups wizard without scripting any additional object. So, my goal in this blog post will consist in understanding the internal changes made by Microsoft in this area.

First of all, let’s take a look at some changes by trying to add an SSISDB catalog. At this point, you will be asked to provide the password of your database master key before to continue as follows:

 

blog_54_-_1-_aag_ssidb_database_master_key

 

Yes, the SSIDB catalog uses intensively the encryption to protect sensitive data from projects, packages, parameters and so on. At this point,  you may notice a warning icon point. In fact, the wizard warns us about configuring the AlwaysOn support for SSISDB in a final step because it is required in order to leverage availability group failover events.

 

blog_54_-_2-_aag_ssidb_validation_step

 

To enable AlwaysOn support we need to go the Integration Services Catalog node and we must include the concerned replica(s) as shown below:

 

blog_54_-_3-_aag_ssidb_services_integration_services_node

 

...

 

blog_54_-_4-_aag_ssidb_services_integration_services_alwayson_support

 

My configuration is now finished. In a second step, we will have a look at the SQL Server agent jobs. Indeed, during my test I suspected that a lot of stuff was done by SQL Server behind the scene and I was right. It added two additional jobs as shown below:

 

blog_54_-_5-_aag_ssidb_catalog_jobs

 

First of all, the SSIS Failover Monitor Job is designed to run on regular basis in order to detect failover events by refreshing the state of the concerned replica(s) and finally by starting accordingly the SSISDB catalog with the SSISDB.catalog.startup stored procedure. I remember in the past having implemented this kind of failover detection mechanism with the first AlwaysOn release in order to monitor availability group failover events (see my blog post here).

 

DECLARE @role int DECLARE @status tinyint   SET @role =(SELECT [role]                     FROM [sys].[dm_hadr_availability_replica_states] hars                     INNER JOIN [sys].[availability_databases_cluster] adc                            ON hars.[group_id] = adc.[group_id]                     WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')   IF @role = 1 BEGIN        EXEC [SSISDB].[internal].[refresh_replica_status]              @server_name = N'SQL161',              @status =              @status OUTPUT          IF @status = 1              EXEC [SSISDB].[catalog].[startup] END

 

Moreover, we may also notice some changes about the second job SSIS Server Maintenance Job. Indeed, this job is directly designed to support the AlwaysOn feature regardless the current SSISDB configuration (included or not in an availability group)

 

DECLARE @role int   SET @role =(SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars              INNER JOIN [sys].[availability_databases_cluster] adc                     ON hars.[group_id] = adc.[group_id]                     WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')                     IF DB_ID('SSISDB') IS NOT NULL AND(@role IS NULL OR @role = 1)        EXEC [SSISDB].[internal].[cleanup_server_retention_window]


Finally, just a quick search in the SSISDB database gives us a good overview of the new objects related to the AlwaysOn feature:

 

select        name as [object_name],        type_desc from sys.objects where name like '%replica%'

 

blog_54_-_6-_aag_ssidb_objects

 

  • The procedure add_replica_info seems to be used for adding a new SQL Server instance as replica in the SSISDB catalog.
  • The update_replica_info procedure seems to be used for updating the replica state in the alwayson_support_state internal table.
  • The refresh_replica_status seems to be used by the SSIS Failover Monitor Job on regular basis for updating the replica configuration state as well as detecting failover events.
  • However, according to my tests, I didn’t found neither any explicit call of the delete_replica_info stored procedure or guideline that explains how to exclude a AlwaysOn replica from the SSIDB catalog. I will update this blog post when I get the response.

 

My feeling is that the new SSISDB support for AlwaysOn will be a pretty cool feature of the next version. This is not the most important improvement in this area for sure but it will help each DBA that wants to implement the SSISDB catalog in an AlwaysOn infrastructure by avoiding an additional burden required with SQL Server 2014. I’m looking forward the definitive package soon!

Buffer pool advisory in AWR

Mon, 2015-06-22 07:45

In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption.

  I'm running the following query to compare the 'physical reads cache' from DBA_HIST_SYSSTATS and the value from the advisor in DBA_HIST_DB_CACHE:

SQL> column sysstat_value format 999G999G999G999
SQL> column advisor_value format 999G999G999G999
SQL> select snap_id,sysstat_value,advisor_value from
-- physical reads cache
(select snap_id,dbid,instance_number,stat_name,value sysstat_value from dba_hist_sysstat where stat_name like 'physical reads cache')
natural join
--  ACTUAL_PHYSICAL_READS
(select snap_id,dbid,instance_number,'ADVISOR',actual_physical_reads advisor_value from DBA_HIST_DB_CACHE_ADVICE where size_factor=1 and name='DEFAULT')
order by 1 desc,2,3;

Here is the result where I can see that they match for all snapshots I have in history:

   SNAP_ID    SYSSTAT_VALUE    ADVISOR_VALUE
---------- ---------------- ----------------
      3025      708,373,759      731,359,811
      3024      708,364,027      731,350,072
      3023      708,284,582      731,270,631
      3022      708,281,965      731,268,020
      3021      708,280,406      731,266,424
      3020      708,252,249      731,238,240
...
      2133       45,538,775       46,930,580
      2132       45,533,062       46,924,865
      2131       30,030,094       31,423,247
      2130          138,897          138,406
      2129          125,126          124,637
      2128          114,556          114,052
      2127          113,455          112,959
      2126          112,378          111,890
      2125          111,179          110,682
      2124          106,701          106,197
      2123          104,782          104,287
      2122           59,071           58,578
      2121           57,972           57,476
...

I'm not sure about the three columns available in that view: PHYSICAL_READS BASE_PHYSICAL_READS ACTUAL_PHYSICAL_READS so let's check that the one I used is the one that is displayed in an AWR report. Here is the latest report for snapshots 3024 to 3025:  

CaptureBPA.PNG

Here is how I verified my assumtions, on an instance that is running for a long time. When you read at the advisor recommendations, you should know whether the activity since instance startup is relevant or not. And I don't know how to reset the counters (except with an instance restart).

SQL Server 2016 : availability groups and the new clustered columnstore index support

Sat, 2015-06-20 13:25

This blog post is focused on the new supportability of clustered columnstore index on the high- availability read-only replicas.

Others studies are available here:

After reading some improvements from the columnstore index feature side, I was very surprised to see the following section:

 

blog_53_-_1-_cci_new_features

 

Clustered columnstore indexes are now supported on AlwaysOn readable secondary replicas! Wonderful! And I guess that the new support of both SI and RCSI transaction isolation level have something to do with this improvement.

So let’s create a clustered columnstore index on my lab environment. I will use for my tests the AdventureWorksDW2012 database and the FactProductInventory fact table.

A first look at this fact table tells us that we’ll probably face an error message because this table contains some foreign keys and one primary key that are not supported on SQL Server 2014. Fortunately, SQL Server 2016 has no such limitations and we’ll able to create the clustered columnstore index.

 

sp_helpconstraint 'dbo.FactProductInventory'; go

 

blog_53_-_2-_cci_fk_pk

 

We can notice two foreign keys and one primary key. The latter is clustered so before creating the clustered columnstore, I will have to change the primary key to a unique constraint.

 

-- drop primary key alter table dbo.FactProductInventory drop constraint PK_FactProductInventory; go   -- create CCI create clustered columnstore index idx_cci_FactProductInventory on FactProductInventory   -- create unique constraint on ProductKey, DateKey columns alter table dbo.FactProductInventory add constraint PK_FactProductInventory unique (ProductKey, DateKey); go

 

Let’s add 2 rows in the dbo.FactProductInventory

 

insert [AdventureWorksDW2012].[dbo].[FactProductInventory] values (167, '20090101', '2009-01-01', 0.19, 0, 0, 875)   insert [AdventureWorksDW2012].[dbo].[FactProductInventory] values (167, '20091002', '2009-01-02', 0.19, 0, 0, 875)

 

… and let’s take a look at this columstore index configuration:

 

select        partition_number,        state_description,        count(*) as nb_row_groups,        sum(total_rows) as total_rows,        sum(size_in_bytes) / 1024 / 1024 as size_mb from sys.column_store_row_groups where object_id = object_id('dbo.FactProductInventory') group by partition_number, state_description order by partition_number, state_description; go

 

blog_53_-_3-_cci_rowgroups

 

Finally let’s add this AdventureWorks2012DW database to my availability group named 2016Grp:

 

blog_53_-_4-_cci_aag

 

Now, let’s try to query my columnstore index on the SQL162 replica configured as secondary read-only replica:

 

select @@servername as server_name; go   select ProductKey, max(DateKey) from [AdventureWorksDW2012].[dbo].[FactProductInventory] where ProductKey = 167 group by ProductKey; go

 

blog_53_-_5-_cci_query

 

 

Do you remember this error message with SQL Server 2014?

Msg 35371, Level 16, State 1, Line 120 SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.

 

With SQL Server 2016 it seems to be another story and it appears to be working properly and this is because snapshot is now supported for clustered columnstore indexes. Let’s have a look at the result of the following query:

 

select        st.session_id,        st.is_user_transaction,        tat.transaction_id,        tat.transaction_sequence_num,        tat.is_snapshot from sys.dm_tran_active_snapshot_database_transactions as tat join sys.dm_tran_session_transactions as st        on tat.transaction_id = st.transaction_id

 

blog_53_-_6-_cci_tran

 

The session with id = 65 concerns my query here. We may notice that it is using snapshot without specifying anything transaction isolation level parameter from my side. As a reminder all read-only queries on a secondary replica are automatically overridden to snapshot isolation and row version mechanism to avoid blocking contention.

But I’m also curious to know if we will face the same blocked redo thread issue in this case? As you know, snapshot isolation prevents read-only queries on the secondary replicas from taking locks and preventing other DML statements against the database from executing, but it doesn’t prevent the read-only queries from taking schema stability locks and blocking DDL statements.

So I wondered if operations issued by the tuple mover in order to switch data from delta store to a row group could stuck the redo thread from executing correctly. To create such situation we may use a long running query on the columnstore index from the secondary replica and in the same time we may insert sufficient data to close a delta store from the primary replica.

Here my horrible and inefficient query that I executed from the secondary. The idea is to hold a schema stability lock during my test.

 

-- from the secondary replica select c.ProductKey, max(c.DateKey) from [AdventureWorksDW2012].[dbo].[FactProductInventory] as c cross join [AdventureWorksDW2012].[dbo].[FactProductInventory] as c2 cross join [AdventureWorksDW2012].[dbo].[FactProductInventory] as c3 group by c.ProductKey; go

 

And the locks hold by my query:

 

blog_53_-_7-_ro_query_locks

 

Then I inserted sufficient data to close my delta store:

 

blog_53_-_8-_cci_delta_store_closed

 

After calling manually the tuple mover by using the ALTER INDEX REORGANIZE command, I didn’t face a blocking redo thread situation.

 

-- from secondary replica use AdventureWorksDW2012 go   select        r.replica_server_name,        g.name as aag_name,        drs.synchronization_state_desc as replica_state,        drs.synchronization_health_desc as synchro_state,        drs.redo_queue_size from sys.dm_hadr_database_replica_states as drs join sys.availability_replicas as r        on r.replica_id = drs.replica_id join sys.availability_groups as g        on g.group_id = drs.group_id where g.name = '2016Grp'        and drs.database_id = DB_ID();

 

blog_53_-_9-_cci_redo_size_queue

 

In fact, we can expect to this result because according to the Microsoft documentation, ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during this operation.

So the new situation is the following on both replicas:

 

blog_53_-_10-_cci_reorganize

 

Note the new delta store state TOMBSTONE here. I got some information from the Niko Neugebauer (@NikoNeugebauer) blog post here. In fact row group in TOMBSTONE state are delta stores that got compressed (row_group_id = 16 to row_group_id = 18 in my context). This unused row group will be dropped asynchronously by the garbage collector by the tuple mover.

Finally, let’s try to rebuild my columnstore index. In this case we may expect to be in a situation where the redo thread will be blocked by this DDL operation.

 

-- from the primary replica alter index [idx_cci_FactProductInventory] on [dbo].[FactProductInventory]

 

Let’s take a look at the redo queue from my secondary replica:

 

-- from secondary replica use AdventureWorks2012DW go   select        r.replica_server_name,        g.name as aag_name,        drs.synchronization_state_desc as replica_state,        drs.synchronization_health_desc as synchro_state,        drs.redo_queue_size from sys.dm_hadr_database_replica_states as drs join sys.availability_replicas as r        on r.replica_id = drs.replica_id join sys.availability_groups as g        on g.group_id = drs.group_id where g.name = '2016Grp'        and drs.database_id = DB_ID();

 

blog_53_-_11-_ro_blocked_redo_thread

 

This time, the ALTER INDEX REBUILD operation has a negative effect on the redo thread which is now stuck while the reporting query execution.

 

-- from the secondary replica select session_id,        command,        blocking_session_id,        wait_time,        wait_type,        wait_resource from sys.dm_exec_requests where command = 'DB STARTUP'

 

blog_53_-_12-_ro_blocked_redo_thread_2

 

The blocking session concerns my reporting query here:

 

The bottom line

Introducing the new clustered columnstore index in AlwaysOn availability groups will be definitively a good improvement in several aspects. Indeed, even if clustered columnstore indexes are designed to save a lot of resources, it is always interesting to benefit this feature to offload reporting activities in some scenarios. Moreover, it will be also a good answer to the existing lack of both SI and RCSI transaction isolation levels and the reporting data consistency without locking. I’m looking forward to see these both features in action in production environments soon!

 

When a function returns a collection: can you directly reference the index of the returned collection?

Sat, 2015-06-20 13:00

As I am still interested in PLSQL I browsed the documentation some days ago and learned something new which is about functions returning collections. Here is a simple test case:

Does the block size matter?

Sat, 2015-06-20 04:57

The default block size is 8k in Oracle. This is the most common. Sometimes, people create the database with 16k block size for datawarehouses. You can also find some 32k block size, but less common which means more bugs. In our tuning workshop we recommend the default, 8k, for most of the cases. I'll show here 3 tests to show what different block size change for full table scan and index access.

Test case

I have defined a cache size for the non default block size I want to use:

SQL> show parameter db%_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_cache_size big integer 0
db_2k_cache_size                     big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_16k_cache_size big integer 112M
db_32k_cache_size big integer 112M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

I'm creating 3 tablespaces with 8k, 16k and 32k block size. I create them as uniform with a 1M extent size because we are supposed to use large block size for large tables and I don't want the side effects of smaller first extents in auto extent size.

SQL> create tablespace DEMO08K datafile '/oracle/u01/oradata/DEMO08K.dbf' size 1024M extent management local uniform size 1M blocksize 8k;
Tablespace created.

SQL> create tablespace DEMO16K datafile '/oracle/u01/oradata/DEMO16K.dbf' size 1024M extent management local uniform size 1M blocksize 16k;
Tablespace created.

SQL> create tablespace DEMO32K datafile '/oracle/u01/oradata/DEMO32K.dbf' size 1024M extent management local uniform size 1M blocksize 32k;
Tablespace created.

and then create 3 identical tables in each tablespace:

SQL> create table TAB08K (id constraint PK_08K primary key,n,x) tablespace DEMO08K as
     select rownum id , ora_hash(rownum,10) n , lpad(ora_hash(rownum,100),20,'0') x from xmltable('1 to 10000000');
Table created.

SQL> create table TAB16K (id constraint PK_16K primary key,n,x) tablespace DEMO16K as
     select rownum id , ora_hash(rownum,10) n , lpad(ora_hash(rownum,100),20,'0') x from xmltable('1 to 10000000');
Table created.

SQL> create table TAB32K (id constraint PK_32K primary key,n,x) tablespace DEMO32K as
     select rownum id , ora_hash(rownum,10) n , lpad(ora_hash(rownum,100),20,'0') x from xmltable('1 to 10000000');
Table created.

My tables have 10 million rows, two number column and one larger varchar2:

SQL> select table_name,num_rows,avg_row_len,blocks from user_tables where table_name like 'TAB__K';

TABLE_NAME   NUM_ROWS AVG_ROW_LEN     BLOCKS
---------- ---------- ----------- ----------
TAB08K       10000000          30      48459
TAB16K       10000000          30      23997
TAB32K       10000000          30      11933

Of course, larger block size need smaller number of blocks, but the total size is roughly the same. Here I have small rows so this is where the fixed size of block header can make the most difference.

 

Full Table Scan

So, the common idea is that larger block size helps to do larger i/o calls when doing full table scan...

SQL> set timing on arraysize 5000 autotrace trace

SQL> select * from TAB08K;
10000000 rows selected.

Elapsed: 00:00:32.53

Execution Plan
----------------------------------------------------------
Plan hash value: 1209268626

-------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |    10M|   286M|  8462   (1)|
|   1 |  TABLE ACCESS STORAGE FULL| TAB08K |    10M|   286M|  8462   (1)|
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      50174  consistent gets
      48175  physical reads
          0  redo size
  348174002  bytes sent via SQL*Net to client
      22489  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         403
physical read total multi block requests                                379
physical read total bytes                                         394821632
physical reads                                                        48196
physical reads cache                                                     23
physical reads direct                                                 48173
physical read IO requests                                               403
physical read bytes                                               394821632
physical reads direct temporary tablespace                                1

I've read 48175 8k blocks with 403 i/o calls.

 

Now doing the same from the table stored in the 16k blocksize tablespace:

SQL> select * from TAB16K;

10000000 rows selected.

Elapsed: 00:00:31.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2288178481

-------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |    10M|   286M|  4378   (2)|
|   1 |  TABLE ACCESS STORAGE FULL| TAB16K |    10M|   286M|  4378   (2)|
-------------------------------------------------------------------------

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         397
physical read total multi block requests                                375
physical read total bytes                                         391012352
physical reads                                                        23876
physical reads cache                                                     21
physical reads direct                                                 23855
physical read IO requests                                               397
physical read bytes                                               391012352
physical reads direct temporary tablespace                                1

I've read 23855 16k blocks with 397 i/o calls. It's not a lot better.

SQL> select * from TAB32K;

10000000 rows selected.

Elapsed: 00:00:29.61

Execution Plan
----------------------------------------------------------
Plan hash value: 1240330363

-------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |    10M|   286M|  2364   (3)|
|   1 |  TABLE ACCESS STORAGE FULL| TAB32K |    10M|   286M|  2364   (3)|
-------------------------------------------------------------------------

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         398
physical read total multi block requests                                373
physical read total bytes                                         388890624
physical reads                                                        11886
physical reads cache                                                     24
physical reads direct                                                 11862
physical read IO requests                                               398
physical read bytes                                               388890624
physical reads direct temporary tablespace                                1

I've read 11892 32k blocks with 398 i/o calls.

 

Conclusion: we do roughly the same amount of i/o when doing a full table scan. This is because Oracle is reading in multiblock. Note that the db_file_multiblock_read_count is defined as the number of blocks of default block size, but the i/o size is adapted for tablespace with non default block size. For example, db_file_multiblock_read_count=128 when default block size is 8k will always read 1MB.

Indexes

I already have an index on the primary key. Let's add some more indexes:

SQL> create index ID_08K on TAB08K(x) tablespace DEMO08K ;
Index created.
SQL> create index ID_16K on TAB16K(x) tablespace DEMO16K ;
Index created.
SQL> create index ID_32K on TAB32K(x) tablespace DEMO32K ;
Index created.
SQL> create bitmap index BI_08K on TAB08K(n) tablespace DEMO08K ;
Index created.
SQL> create bitmap index BI_16K on TAB16K(n) tablespace DEMO16K ;
Index created.
SQL> create bitmap index BI_32K on TAB32K(n) tablespace DEMO32K ;
Index created.

and check their size:

SQL> select index_name,num_rows,blevel,leaf_blocks from user_indexes where table_name like 'TAB__K' order by 1;

INDEX_NAME   NUM_ROWS     BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -----------
BI_08K           3211          2        1606
BI_16K           1562          1         781
BI_32K            759          1         380
ID_08K       10000000          2       44643
ID_16K       10000000          2       22027
ID_32K       10000000          2       10929
PK_08K       10000000          2       22132
PK_16K       10000000          2       10921
PK_32K       10000000          2        5425

Of course the number of blocks is lower when the block size is bigger. And because branches are larger, then you may have a smaller depth. But look: on my 10000000 rows table the depth is the same for the regular indexes: 2 branch levels. Only for the bitmap indexes, because they are very small, we need one less branch level here.

 

But think about it. Index depth mostly matter for OLTP where you get rows by their primary key. But people say that smaller blocks are better for OLTP... Datawarehouses often have bitmap indexes, but do you care to have smaller bitmap indexes?

Index access (lot of rows, good clustering factor)

Anyway, let's test a large range scan:

SQL> select * from TAB08K where id between 1 and 100000;

100000 rows selected.

Elapsed: 00:00:00.44

Execution Plan
----------------------------------------------------------
Plan hash value: 2790916815

-----------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|  2929K|   707   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB08K |   100K|  2929K|   707   (1)|
|*  2 |   INDEX RANGE SCAN                  | PK_08K |   100K|       |   225   (1)|
-----------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID" v=100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        878  consistent gets
        679  physical reads
          0  redo size
    3389860  bytes sent via SQL*Net to client
       1589  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         705
physical read total bytes                                           5775360
physical reads                                                          705
physical reads cache                                                    705
physical read IO requests                                               705
physical read bytes                                                 5775360

We have read 100000 rows through index. The index is very well clustered. I've done 705 i/o calls to get those rows from 8k blocks.

 

Now with 16k blocks:

SQL> select * from TAB16K where id between 1 and 100000;

100000 rows selected.

Elapsed: 00:00:00.37

Execution Plan
----------------------------------------------------------
Plan hash value: 1432239150

-----------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|  2929K|   352   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB16K |   100K|  2929K|   352   (1)|
|*  2 |   INDEX RANGE SCAN                  | PK_16K |   100K|       |   113   (1)|
-----------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"v=100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        537  consistent gets
        337  physical reads
          0  redo size
    3389860  bytes sent via SQL*Net to client
       1589  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         363
physical read total bytes                                           5734400
physical reads                                                          363
physical reads cache                                                    363
physical read IO requests                                               363
physical read bytes                                                 5734400

the number of i/o calls have been divided by two.

 

 

SQL> select * from TAB32K where id between 1 and 100000;

100000 rows selected.

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 3074346038

-----------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|  2929K|   177   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB32K |   100K|  2929K|   177   (1)|
|*  2 |   INDEX RANGE SCAN                  | PK_32K |   100K|       |    58   (2)|
-----------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"v=100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        369  consistent gets
        169  physical reads
          0  redo size
    3389860  bytes sent via SQL*Net to client
       1589  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         195
physical read total bytes                                           5750784
physical reads                                                          195
physical reads cache                                                    195
physical read IO requests                                               195
physical read bytes                                                 5750784

with 32k blocks, it's once again divided by two.

 

Conclusion: when doing single block reads, coming from a well clustered index, we do less i/o calls with larger blocks. The fact is that because we need contiguous rows (because we are using a well clustered index) having large blocks makes more rows physically contiguous.

Index access (few rows, bad clustering factor)

Here is a query WHERE X='00000000000000000000'. The index on N - that I've populated with a hash value on rownum - has a bad clustering factor. I fetch only 30 rows.

----------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    30 |   900 |    16   (0)|
|   1 |  COUNT STOPKEY               |        |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB08K |    30 |   900 |    16   (0)|
|*  3 |    INDEX RANGE SCAN          | ID_08K | 99010 |       |     3   (0)|
----------------------------------------------------------------------------

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

   3 - access("X"='00000000000000000000')

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                          16
physical read total bytes                                            131072
physical reads                                                           16
physical reads cache                                                     16
physical read IO requests                                                16
physical read bytes                                                  131072

The query returned about 30 rows. Because of the bad clustering factor we had to read a block every two rows on average. Let's see the same with 16k blocks.

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    30 |   900 |     7   (0)| 00:00:01 |
|   1 |  COUNT STOPKEY               |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB32K |    30 |   900 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ID_32K | 99010 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("X"='00000000000000000000')

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                          33
physical read total bytes                                            442368
physical reads                                                           33
physical reads cache                                                     33
physical read IO requests                                                33
physical read bytes                                                  442368

More i/o calls here and higher block size.
Conclusion: larger block size is bad when we need only few rows, especially from a badly clustered index. More i/o calls, larger i/o size, and the large blocks takes more space in the buffer cache.

 

So, which block size suits your workload?

Question is: do you get better performance in datawarhouse with larger block size? For full table scans, no it's the same and has always been the same. For index range scans retrieving large number of rows, then yes, the access by index may be faster. But think about it. Retrieving lot of rows by index is always bad, - whatever the block size is. If you want do to something about it, look at the design: define better indexes (covering all selected columns), partition the table, compress the table, use Parallel Query,... Lot of tuning to do before thinking about block size.

With smaller block size the optimizer will favor a full table scan for those cases, and today the gap between full table scan and index access is not so large. There is Exadata SmartScan, In-Memory. Even without options, multiblock read is faster since 11g when doing serial direct read. All that makes full table scan faster. And index access has also been improved: you have the batched access by rowid to compensate bad clustring factor.

And if you think about having a non default block size for some tablespaces, are you sure that the gain you expect will not be ruined by a bad sizing of buffer cache? When having non default blocksize tablespace you have to manage their buffer cache manually.

Other considerations

Larger block size may have been recommended for very large tablespaces in order to avoid to have too many datafiles (their maximum size is in number of block) but today you can create bigfile tablespaces for them, so it is not a reason anymore.

There is one reason to have larger block size. When you have very large rows, you can avoid row chaining. But once again, are you sure it is a problem (i.e do you select often the columns at the end)? And maybe you should review the design first.

There was another reason to have a larger block size for tablespace containing large LOB (I know that the 'L' is already for 'Large' but I mean LOBs larger than the default block size). Today you should use SecureFiles and we get better performance with them. But that's for another blog post.

Conclusion

When people come with those kinds of rules of thumbs, I usually try to see if it is something they thought about, or just a blind idea. For example, when they want to rebuild indexes, I ask them which PCTFREE they set for that. Because rebuilding without knowing the PCTFREE we want to achieve is pointless.
And when they want to create a tablespace with non default block size, I ask them how they have calculated the buffer cache to allocate for that blocksize. The whole size of the table? Then why do you want to do less i/o calls if everything is in cache. A small size? Then are you sure that the lower number of i/o calls will compensate the cache you can have in the default buffer pool?

In most of the cases, just keep the default block size, and you have probably lot of other things to tune. I'll now tell you a short story. I was a junior not-yet-DBA in 1998, implementing a banking software (COBOL, tuxedo, HP-UX, Oracle 7). Application design had a lot to review. Things like comments stored in CHAR(2000) for example. Then I've seen a senior consultant recommending 'The' solution: increase the block size. The customer accepted that, we did it and everything was immediately better. Of course, in order to do that you have to export/import the database, everything was reorganized, lot of design problems were hidden for a few days. After a while, the performance issues came back, and we had to continue the optimization tasks. Being on a larger block size did not change anything about that. This is where I learned exactly which kind of consultant I don't want to be.

SQL Server 2016 : availability groups and load balancing features

Thu, 2015-06-18 14:53

Let’s continue with this third post about SQL Server AlwaysOn and availability groups.

Others studies are available here:

 

This time I’ll talk about read-only secondaries and the new load-balancing support that will be introduced by SQL Server 2016.

First of all, SQL Server 2014 improved the read-only secondary availability by solving the issue related to secondary accessibility when the primary is offline. However, the redirection to a readable secondary was still basic because it concerned only the first secondary replica defined in the configured priority list. So, unless using a third-party tool it was not possible to use very efficiently all of the resources available from secondaries. Fortunately, the next SQL Server version will change the game by introducing native load-balancing capabilities.

In order to be able to use this new feature, you must define:

  • The list of possible secondary replicas
  • A read-only route for each concerned replica
  • A routing list that include read-only replicas and load-balancing rules

At this point I admit to expect a GUI for configuring both read-only routes and the routing list rules in a user friendly fashion even if I prefer using T-SQL to be honest. But anyway, let’s try to configure secondary replicas in round-robin fashion as follows:

 

/* enable read-only secondary replicas */ ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL161' WITH (        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL161' WITH (        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL161.dbi-services.test:1433') ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL162' WITH (        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL162' WITH (        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL162.dbi-services.test:1433') ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL163' WITH (        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL163' WITH (        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL163.dbi-services.test:1433') ); GO   /* configure replicas priority list */ ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL161' WITH (        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL162','SQL163'))) );   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL162' WITH (        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL161','SQL163'))) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL163' WITH (        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL162','SQL161'))) ); GO

 

My test lab includes 3 replicas (SQL161, SQL162 and SQL163). The secondaries will be used as read-only replicas with the new load-balancing feature.

Note the double brackets around the replicas list that defines the load-balancing mechanism for the concerned replicas. In my context, I have only two read-only replicas but rules are defined as follows:

  • (replica1, replica2, replica3): no load-balancing capabilities in this case. The first replica will be used, then the second and finally the third.
  • ((replica1, replica2), replica3): replica1 and replica will be used in a round-robin fashion. The replica3 will be used only if both replica1 and replica2 are not available.

Now let’s play with this new infrastructure by using sqlcmd command as follows:

 

blog_52_-_1-_sqlcmd_readonly

 

As reminder, you have to meet some others requirements in order to use correctly the transparent redirection to a secondary replica as using TCP protocol, referencing directly the availability group listener and the concerned database as well and setting the application intent attribute as readonly. So in my case, I reference directly the LST-2016 listener and the killerdb. I use also the –K parameter with READONLY attribute. Finally, I run the query SELECT @@SERVERNAME in order to know which replica I am after login.

I ran this command several times and I can state that the load-balancing feature plays its full role.

 

blog_52_-_2-_sqlcmd_tests

 

However, let’s play now with the following PowerShell script:

 

Clear-Host;   $dataSource = “LST-2016"; $database = "killerdb"; $connectionString = "Server=tcp:$dataSource;Integrated Security=SSPI;Database=$database;ApplicationIntent=ReadOnly”;    $i = 0;   while ($i -le 3) {        Write-Host "Test connexion initial server nb : $i - $dataSource " -NoNewline;    Write-Host "";    Write-Host "";      Try    {        $connection = New-Object System.Data.SqlClient.SqlConnection;        $connection.ConnectionString = $connectionString;          $connection.Open();          $sqlCommandText="SELECT 'Current server : ' + @@SERVERNAME as server_name";        $sqlCommand = New-Object system.Data.sqlclient.SqlCommand($sqlCommandText,$connection);        $sqlCommand.ExecuteScalar();          $connection.Close();          $sqlCommand.Dispose();        $connection.Dispose();      }    Catch [Exception]    {        Write-Host "KO" -ForegroundColor Red;        Write-Host $_.Exception.Message;    }      Write-Host "";      Start-Sleep 3;      $i++; }

 

The result is not the same. The redirection to a read-only replica works perfectly but there was not load-balancing mechanism in action this time as shown below:

 

blog_52_-_3-_powershell_tests

 

 

What’s going on in the case? In fact and to be honest, I didn’t remember that PowerShell uses connection pooling by default (thanks to Brent Ozar - @BrentO to put me on the right track).

Let’s take a look at the output of an extended event session that includes the following events:

  • sqlserver.login
  • sqlserver.logout
  • sqlserver.read_only_route_complete
  • sqlserver.rpc_completed

 

blog_52_-_4-_xe_sqlcmd

 

You can notice that sqlcmd tool doesn’t use connection pooling (is_cached column = false). In this case for each run, SQL Server will calculate the read-only route.

However for my PowerShell script the story is not the same as shown below:

 

blog_52_-_5-_xe_pw

 

The first connection is not pooled and we can noticed only one read-only route calculation from SQL Server. All of the next connections are pooled and technically they are still alive on the SQL Server instance. This is why the load balancing mechanism is not performed in this case. So this is an important point to keep in mind if you want to plan to benefit to this new feature.

I also had a dream: Having a real load-balancing feature based on resource scheduling algorithm… maybe the next step? :)

See you

 

 

 

SQL Server 2016 CTP2: Stretch database feature - Part 2

Thu, 2015-06-18 02:55

In my previous blog SQL Server 2016 CTP2: Stretch database feature - Part 1, I explained how to prepare your instance and your database to enable the Stretch feature for your table.

Now I present you how to enable the feature for your tables!

Prerequisites

Enabling Stretch Database at the table level requires ALTER permissions on this table.

 

Limitations

For the moment in the SQL Server 2016 CTP2, there are several and important limitations, which drastically reduces the scope of use of Stretch Database feature.

These limitations include Table Properties, Data Types, Column Properties, Column Types and Constraints and Indexes.

You can obtain the detailed list with the following Microsoft article: Requirements and limitations for Stretch Database.

 

Enable Stretch for a Table

First, I create a new table to avoid all limitations I explained above. Here is the SQL script:

Use AdventureWorks2014;
CREATE TABLE Stretch_Table
(
    Column1 nvarchar(50),
    Column2 int,
    Column3 nchar(10)
)

If you have followed all steps in my previous blog, you must be ready for enabling Stretch feature for your table!

As all have been pre-configured, you just need to enable the feature for the targeted table.

enable_table_for_stretch.png

 

If we take a look at the SQL Database server in Azure, we must be able to visualize the "Stretch_Table" table:

SQL_azure_20150618-090340_1.png

 

You can notice that a new column named "batchID" has been included in the original table. Indeed, a non-null bigint is incremented each time you insert a new row in your table: it is the PRIMARY KEY of your table.

Moreover, your table still remains visible on your on-premise instance and you can perform your normal work.

 

Next step

Performing backup of the database with Stretch feature enabled will not include the data stored in Azure. It means you also needs to perform a backup in Azure.

I will detail this part in my next blog.

ODA workshop at Arrow ECS

Wed, 2015-06-17 02:30
On the 16th and 17th of June David Hueber, Georges Grey and myself had the chance to attend the ODA hands on workshop at Arrow ECS. Lead Trainer Ruggero Citton (Oracle ODA Product Development) did the first day with plenty of theory and examples. On the second day we had the opportunity to play on a brand new ODA X5-2:

SQL Server 2014: Analysis, Migrate and Report (AMR) - a powerful In-Memory migration tool

Wed, 2015-06-17 02:28

An important new functionality of Microsoft SQL Server 2014 is the In-Memory OLTP engine, which enable you to load Tables and also Stored Procedures In-Memory for a very fast response time.
The goal is not to load all the database In-Memory but just Tables with critical performances and Stored Procedures with complex logical calculations.

To identify which Tables or Stored Procedures will give you the best performance gain after migration; Microsoft SQL Server 2014 has introduced a new tool: Analysis, Migrate and Report (AMR).

This tool will collect statistics about Tables and Stored Procedures in order to analyze the current workload. It will give you advice on the migration benefits of the different Tables or Stored Procedures. It will also give you an overview of the time/work needed to push Tables or Stored Procedures In-Memory.

In the following article I will show you how to setup and use this Tool.

Configuration of the Management Data Warehouse

The AMR Tool is built into SQL Server Management Studio.
It consists of:

  • Reports which come from a Management Data Warehouse and give recommendations about tables and Stored procedures which could be migrated to In-Memory OLTP
  • Memory Optimization Advisor which will help you during the migration process of a disk table to a Memory Optimized table
  • Native Compilation Advisor which will help you migrated a Stored Procedure to a Natively Compiled Stored Procedure

AMR Tool leverages the Management Data Warehouse and the Data Collector, with his new Transaction Performance Collection Sets, for gathering information about workloads.

AMR will analyze the collected data and provide recommendations via reports.
First, we have to configure the Management Data Warehouse.

To start the configuration, open Management Studio, go to Object Explorer, then Management folder, and right-click on Data Collection. Then select Tasks and click on Configure Management Data Warehouse as shown below:

AMR_picture1.png

On the Configure Management Data Warehouse Storage screen, enter the server name and the database name where you Management Data Warehouse will be host. AMR tool will collect, via its collection sets, data from three Dynamic Management Views every fifteen minutes and will save those data in the MDW database. Uploading data will have minimal performance impact.

If you already have a database, enter her name. If not, click the New button to create a new one.
On the Map logins and Users page, if needed, you can map a user to administer, read, or write the Data Management Warehouse database.
Verify the Management Data Warehouse configuration and proceed to the configuration.
When the configuration of the Management Data Warehouse has been successfully finalized, you should see the following screen:

 AMR_picture2.png

The Management Data Warehouse setup is finished.

Configuration of the Data collection

Take care, SQL Server agent has to be started on the instance that will collect the data.
To collect data, we will enable the new Transaction Performance Collection set which is composed of two new collection sets:

  • Stored Procedure Usage Analysis: used to capture statistics about Stored Procedures which could be migrate to Natively Compiled Stored Procedures
  • Table Usage Analysis: takes information about disk based tables for a future migration to Memory Optimized tables.

 In order to configure the Data Collection, go to Object Explorer, then Management folder, right-click on Data Collection, select Tasks, and click on Configure Data Collection, as shown below:

AMR_picture3.png

After having skipped the Welcome page, you have to select a server and a database name that will host the Management Data Warehouse.

Now, you need to select the data collector sets. In the wizard, check “Transaction Performance Collection Set” in the list of collection sets. This will collect statistics for transaction performance issues.

If the Management Data Warehouse is located on a different SQL Server instance from the data collector and SQL Server agent is not running under a domain account which has dc_admin permissions on the remote instance you have to use a SQL Server Agent proxy.

AMR_picture4.png

After having performed the Data Collection configuration with success, you will have an enabled Data Collection which will collect information about all user databases.

Via SQL Server Agent Folder Jobs, you are now able to see new collection jobs used to collect data from your workloads with names like collection_set_N_collection and jobs used to populate our new Management Data Warehouse database with names like collection_set_N_upload.

It is also good to know that upload jobs will be run every thirty minutes for Stored Procedure Usage Analysis (job: collection_set_5_upload) and every fifteen minutes for Table Usage Analysis (job: collection_set_6_upload). So if you want to speed your upload, you can execute these jobs manually.
 

Reports

To access recommendations based on collected information about all user databases on the workload server, you have to right-click on your Management Data Warehouse database, select Reports, then Management Data Warehouse, and finally Transaction Performance Analysis.

In the Transaction Performance Analysis Overview report, you can choose among three reports, depending on what you want or need:

  • Usage analysis for tables
  • Contention analysis for tables
  • Usage analysis for Stored procedures

AMR_picture5.png

Usage analysis for tables

This report, based on table's usage, shows you best candidate tables that could be pushed In-Memory.
On the left side of the report you have the possibility to select the database and the number of tables you would like to see.
The central part is a chart with two axis:

  • Horizontal axis represents significant to minimal works needed to migrate a table to In-Memory OLTP
  • Vertical axis represents increasing gains you will benefit after having moved the table to In-Memory OLTP

The best part of this graph is the top right corner, which shows tables that could be migrated easily In-Memory, but will give you the best performance gain.

AMR_picture6.png

When you click a table point on the graph, you will access a more detailed statistics report.

This report shows access characteristics (lookup statistics, range scan statistics, etc.) and also contention statistics (latches statistics, lock statistics, etc.) of the concerning table and for the monitoring time period of your instance’s workload with the Transaction Performance Collection Set.

AMR_picture7.png

Contention analysis for table

This report is based on table's contention instead of usage. It shows you best candidate tables that could be migrated In-Memory.
As before, on the left side of the report you have the possibility to select the database and the number of tables you would like to see.
The central part is a chart with two axis:

  • Horizontal axis represents significant to minimal works needed to migrate a table to In-Memory OLTP
  • Vertical axis represents increasing gains you will benefit after having moved the table to In-Memory OLTP

The best part of this graph is the top right corner, showing tables that can be easily migrated In-Memory, but will give you best performance gain.

AMR_picture8.png

As for the usage analysis report, you can also click a table name on the graph to see the statistics details of the table.

Usage analysis for Stored Procedures

This report contains the top candidate stored procedures for an In-Memory OLTP migration with regards to their usage. This report is based on the Total CPU Time.

You also have the possibility to select the database and the number of stored procedure you would like to see.

AMR_picture9.png

If you want to see the usage statistics for a specific stored procedure, you can click on the blue bar. You will then have a more detailed report.

AMR_picture10.png

Now, you know which Tables and Stored Procedures will give you best performance gain after migration to In-Memory OLTP.
AMR provide us two Advisors which will help you to manage the transformation of your disk tables to Memory Optimized Tables as well as your Stored Procedures to Natively Compiled Stored Procedures. To know more about those advisors, please have a look to my blog.

DOAG Database 2015

Wed, 2015-06-17 00:17

It was my first time at DOAG Datenbank in Dusseldorf. 

What is more efficient: arrays or single columns values? - oracle

Mon, 2015-06-15 06:38
In the last post on this topic it turned out that using an array as a column type needs more space than using a column per value in PostgreSQL. Now I'll do the same test case in Oracle.

What is more efficient: arrays or single column values?

Mon, 2015-06-15 05:59
In PostgreSQL ( as well as in other rdbms ) you can define columns as arrays. What I wondered is: What is more efficient when it comes to space: Creating several columns or just creating once column as array? The result, at least for me, is rather surprising.

SQL Interpolation with psql

Mon, 2015-06-15 04:45

The PostgreSQL psql utility provides some really nice features. One of these features is SQL interpolation which allows us to do interesting things, e.g. reading files and analyze the results directly in the database. This post will show how to use this by reading and analyzing sar files of a linux server.

A free PostgreSQL cloud database?

Sun, 2015-06-14 04:10
Recently I was looking for a free PostgreSQL cloud database service for testing. Why? Because I'd like to use such a cloud instance for testing no matter on which workstation or OS I am currently on. Another reason is, that I could could prepare some demos at home and use the same demos at work without needing to worry about taking the database with me each time.

PostgreSQL portable?

Fri, 2015-06-12 05:01
Initially I was looking for a free PostgreSQL cloud database which I can use from any workstation and any OS in case I'll need to do some tests or demos and do not have a PostgreSQL database available. In fact I found a free cloud service for PostgreSQL (there are several. More on that in another post) but then I needed a client as I did not want to work with the tools the service provider listed on the website. So I did a search on "portable psql" as I was on a Windows machine:

Show the Top 10 events from latest AWR snapshot

Fri, 2015-06-12 02:17

Want to display a specific section from the latest AWR report? I'll share the script I use to get something like that:

SQL> @ LASTAWRSEC.sql 'Top 10 Foreground Events by Total Wait Time' ''

AWR_REPORT_TEXT
--------------------------------------------------------------------------------
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class
------------------------------ ----------- ---------- ---------- ------ --------
direct path write temp               7,146        8.1       1.14   67.3 User I/O
DB CPU                                            4.6              38.0
direct path read                     1,102         .3       0.32    2.9 User I/O
enq: KO - fast object checkpoi           1         .2     228.12    1.9 Applicat
db file sequential read                140          0       0.15     .2 User I/O
Disk file operations I/O                 4          0       0.66     .0 User I/O
log file sync                            2          0       0.68     .0 Commit
reliable message                         1          0       0.94     .0 Other
control file sequential read           122          0       0.01     .0 System I
SQL*Net break/reset to client            2          0       0.25     .0 Applicat

The script

The LASTAWRSEC.sql used here script get the latest snapshot from DBA_HIST_SNAPSHOT for the current instance and calls DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT to get the report. Then it adds a column with the section name (I've listed the sections available in 12.1.0.2 - you may have to modify that list) and the script accepts two parameters:

  • &1 is a regexp to apply to the section name
  • &2 is a regexp applied to the report line (can be the empty string)
and they are combined with a 'or'. I use that to get the Top event section and a few instance statistics in addition to that.

So here is the script:

with snap as (
  select * from (
    select dbid,lead(snap_id)over(partition by instance_number order by end_interval_time desc) bid,snap_id eid,row_number() over(order by end_interval_time desc) n
    from dba_hist_snapshot where dbid=(select dbid from v$database)
  ) where n=1
),
awr as (
        select rownum line,output
        from table(
                dbms_workload_repository.awr_report_text(l_dbid=>(select dbid from snap),l_inst_num=>(select instance_number from v$instance),l_bid=>(select bid from snap),l_eid=>(select eid from snap),l_options=>1+4+8)
        )
),
awr_sections as (
        select
         last_value(case when regexp_replace(output,' *DB/Inst.*$') in (''
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top Event P1/P2/P3 Values'
        ,'Top SQL with Top Events'
        ,'Top SQL with Top Row Sources'
        ,'Top Sessions'
        ,'Top Blocking Sessions'
        ,'Top PL/SQL Procedures'
        ,'Top Events'
        ,'Top DB Objects'
        ,'Activity Over Time'
        ,'Wait Event Histogram Detail (64 msec to 2 sec)'
        ,'Wait Event Histogram Detail (4 sec to 2 min)'
        ,'Wait Event Histogram Detail (4 min to 1 hr)'
        ,'SQL ordered by Elapsed Time'
        ,'SQL ordered by CPU Time'
        ,'SQL ordered by User I/O Wait Time'
        ,'SQL ordered by Gets'
        ,'SQL ordered by Reads'
        ,'SQL ordered by Physical Reads (UnOptimized)'
        ,'SQL ordered by Optimized Reads'
        ,'SQL ordered by Executions'
        ,'SQL ordered by Parse Calls'
        ,'SQL ordered by Sharable Memory'
        ,'SQL ordered by Version Count'
        ,'SQL ordered by Cluster Wait Time'
        ,'Key Instance Activity Stats'
        ,'Instance Activity Stats'
        ,'IOStat by Function summary'
        ,'IOStat by Filetype summary'
        ,'IOStat by Function/Filetype summary'
        ,'Tablespace IO Stats'
        ,'File IO Stats'
        ,'Checkpoint Activity'
        ,'MTTR Advisory'
        ,'Segments by Logical Reads'
        ,'Segments by Physical Reads'
        ,'Segments by Direct Physical Reads'
        ,'Segments by Physical Read Requests'
        ,'Segments by UnOptimized Reads'
        ,'Segments by Optimized Reads'
        ,'Segments by Physical Write Requests'
        ,'Segments by Physical Writes'
        ,'Segments by Direct Physical Writes'
        ,'Segments by DB Blocks Changes'
       ,'Segments by Table Scans'
        ,'Segments by Row Lock Waits'
        ,'Segments by ITL Waits'
        ,'Segments by Buffer Busy Waits'
        ,'Segments by Global Cache Buffer Busy'
        ,'Segments by CR Blocks Received'
        ,'Segments by Current Blocks Received'
        ,'In-Memory Segments by Scans'
        ,'In-Memory Segments by DB Block Changes'
        ,'In-Memory Segments by Populate CUs'
        ,'In-Memory Segments by Repopulate CUs'
        ,'Interconnect Device Statistics'
        ,'Dynamic Remastering Stats'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Reader'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Buffered Queue Subscribers'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'Persistent Queue Subscribers'
        ,'Rule Set'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Cluster Interconnect'
        ,'Wait Classes by Total Wait Time'
        ,'Top 10 Foreground Events by Total Wait Time'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'Host Configuration Comparison'
        ,'Top Timed Events'
        ,'Top SQL Comparison by Elapsed Time'
        ,'Top SQL Comparison by I/O Time'
        ,'Top SQL Comparison by CPU Time'
        ,'Top SQL Comparison by Buffer Gets'
        ,'Top SQL Comparison by Physical Reads'
        ,'Top SQL Comparison by UnOptimized Read Requests'
        ,'Top SQL Comparison by Optimized Reads'
        ,'Top SQL Comparison by Executions'
        ,'Top SQL Comparison by Parse Calls'
        ,'Top SQL Comparison by Cluster Wait Time'
        ,'Top SQL Comparison by Sharable Memory'
        ,'Top SQL Comparison by Version Count'
        ,'Top Segments Comparison by Logical Reads'
        ,'Top Segments Comparison by Physical Reads'
        ,'Top Segments Comparison by Direct Physical Reads'
        ,'Top Segments Comparison by Physical Read Requests'
        ,'Top Segments Comparison by Optimized Read Requests'
        ,'Top Segments Comparison by Physical Write Requests'
        ,'Top Segments Comparison by Physical Writes'
        ,'Top Segments Comparison by Table Scans'
        ,'Top Segments Comparison by DB Block Changes'
        ,'Top Segments by Buffer Busy Waits'
        ,'Top Segments by Row Lock Waits'
        ,'Top Segments by ITL Waits'
        ,'Top Segments by CR Blocks Received'
        ,'Top Segments by Current Blocks Received'
        ,'Top Segments by GC Buffer Busy Waits'
        ,'Top In-Memory Segments Comparison by Scans'
        ,'Top In-Memory Segments Comparison by DB Block Changes'
        ,'Top In-Memory Segments Comparison by Populate CUs'
        ,'Top In-Memory Segments Comparison by Repopulate CUs'
        ,'Service Statistics'
        ,'Service Statistics (RAC)'
        ,'Global Messaging Statistics'
        ,'Global CR Served Stats'
        ,'Global CURRENT Served Stats'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'Streams by CPU Time'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Reader'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams by IO Time'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Rule Set by Evaluations'
        ,'Rule Set by Elapsed Time'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'IOStat by Function - Data Rate per Second'
        ,'IOStat by Function - Requests per Second'
        ,'IOStat by File Type - Data Rate per Second'
        ,'IOStat by File Type - Requests per Second'
        ,'Tablespace IO Stats'
        ,'Top File Comparison by IO'
        ,'Top File Comparison by Read Time'
        ,'Top File Comparison by Buffer Waits'
        ,'Key Instance Activity Stats'
        ,'Other Instance Activity Stats'
        ,'Enqueue Activity'
        ,'Buffer Wait Statistics'
        ,'Dynamic Remastering Stats'
        ,'Library Cache Activity'
        ,'Library Cache Activity (RAC)'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Buffered Subscribers'
        ,'Persistent Queue Subscribers'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'OS Statistics By Instance'
        ,'Foreground Wait Classes -  % of Total DB time'
        ,'Foreground Wait Classes'
        ,'Foreground Wait Classes -  % of DB time '
        ,'Time Model'
        ,'Time Model - % of DB time'
        ,'System Statistics'
        ,'System Statistics - Per Second'
        ,'System Statistics - Per Transaction'
        ,'Global Cache Efficiency Percentages'
        ,'Global Cache and Enqueue Workload Characteristics'
        ,'Global Cache and Enqueue Messaging Statistics'
        ,'SysStat and Global Messaging  - RAC'
        ,'SysStat and  Global Messaging (per Sec)- RAC'
        ,'SysStat and Global Messaging (per Tx)- RAC'
        ,'CR Blocks Served Statistics'
        ,'Current Blocks Served Statistics'
        ,'Global Cache Transfer Stats'
        ,'Global Cache Transfer (Immediate)'
        ,'Cluster Interconnect'
        ,'Interconnect Client Statistics'
        ,'Interconnect Client Statistics (per Second)'
        ,'Interconnect Device Statistics'
        ,'Interconnect Device Statistics (per Second)'
        ,'Ping Statistics'
        ,'Top Timed Events'
        ,'Top Timed Foreground Events'
        ,'Top Timed Background Events'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'SQL ordered by Elapsed Time (Global)'
        ,'SQL ordered by CPU Time (Global)'
        ,'SQL ordered by User I/O Time (Global)'
        ,'SQL ordered by Gets (Global)'
        ,'SQL ordered by Reads (Global)'
        ,'SQL ordered by UnOptimized Read Requests (Global)'
        ,'SQL ordered by Optimized Reads (Global)'
        ,'SQL ordered by Cluster Wait Time (Global)'
        ,'SQL ordered by Executions (Global)'
        ,'IOStat by Function (per Second)'
        ,'IOStat by File Type (per Second)'
        ,'Segment Statistics (Global)'
        ,'Library Cache Activity'
        ,'System Statistics (Global)'
        ,'Global Messaging Statistics (Global)'
        ,'System Statistics (Absolute Values)'
        ,'PGA Aggregate Target Statistics'
        ,'Process Memory Summary'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Time Model Statistics'
        ,'Operating System Statistics'
        ,'Host Utilization Percentages'
        ,'Global Cache Load Profile'
        ,'Wait Classes'
        ,'Wait Events'
        ,'Cache Sizes'
        ,'PGA Aggr Target Stats'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Global Cache Transfer Stats'
        ,' Exadata Storage Server Model'
        ,' Exadata Storage Server Version'
        ,' Exadata Storage Information'
        ,' Exadata Griddisks'
        ,' Exadata Celldisks'
        ,' ASM Diskgroups'
        ,' Exadata Non-Online Disks'
        ,' Exadata Alerts Summary'
        ,' Exadata Alerts Detail'
        ,'Exadata Statistics'
) then output end ) ignore nulls over(order by line) section
        ,output
        from awr
)
select output AWR_REPORT_TEXT from awr_sections where regexp_like(section,'&1') or regexp_like(output,'&2')
/

Please is you have any improvement or modification, don't hesitate to comment.

Parallel DML in 12c

Thu, 2015-06-11 12:48

Following a question from Randolf Geist (who can imagine that there is something about parallel query that Randolf didn't know?), I get back to some notes I've taken when 12c was out and I've tested them again on 12.1.0.2 - it's about the ability to enable parallel DML at query level.

Test case

In 12.1.0.2 I create two tables. DEMO1 has 100000 rows and is about 80MB. DEMO2 is empty. DEMO1 is parallel.

SQL> create table DEMO1 parallel 2 as
  2  select rownum id , ora_hash(rownum,10) a from xmltable('1 to 1000000');

Table created.

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

TABLE_NAME   NUM_ROWS     BLOCKS
---------- ---------- ----------
DEMO           100000      10143

SQL>
SQL> create table DEMO2 as select * from DEMO1 where null is not null;

Table created.

SQL>
SQL> alter session set statistics_level=all;

Session altered.

insert ... select ...

Here is a simple insert as select:

SQL> insert into DEMO2 select * from DEMO1;

1000000 rows created.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  bx27xdnkr7dvw, child number 0
-------------------------------------
insert into DEMO2 select * from DEMO1

Plan hash value: 4271246053

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |      1 |        |      0 |00:00:17.40 |   24311 |
|   1 |  LOAD TABLE CONVENTIONAL | DEMO2    |      1 |        |      0 |00:00:17.40 |   24311 |
|   2 |   PX COORDINATOR         |          |      1 |        |   1000K|00:00:04.49 |       5 |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |      0 |   1000K|      0 |00:00:00.01 |       0 |
|   4 |     PX BLOCK ITERATOR    |          |      0 |   1000K|      0 |00:00:00.01 |       0 |
|*  5 |      TABLE ACCESS FULL   | DEMO1    |      0 |   1000K|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------

Note                                                                                                                                
-----
   - Degree of Parallelism is 2 because of table property
   - PDML is disabled in current session

The select part is done in parallel (it's below the coordinator) but the insert part (LOAD TABLE) is above the coordinator, which means that it is done in serial by the coordinator. In 12.1.0.2 you have no doubt: dbms_xplan has a note to tell you that PDML was not used and it gives the reason: it's not enabled in the session.

When you have tuning pack the parallel queries are monitored by default, so we can get the SQL Monitor Plan. You can get it as text, html or flash but I'll use Lighty here as I find it awesome for that as well:

PDML1.png

Look at the bottom right which details the highlighted plan line: 100% of the load has been done by my session process.

Enable parallel dml

So we need to enable parallel DML for our session. Do you know why? Because inserting in parallel requires to lock the table (or partition) it is inserted into, so the optimizer cannot decide that without our permission. So let's enable parallel DML:

SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction

I cannot do that here because I have a transaction in progress. But in 12c you can also enable parallel DML at query level, with the ENABLE_PARALLEL_DML hint. I've seen it when 12c came out, but it was undocumented. But I discover today that it is documented in the Enable Parallel DML Mode of the Database VLDB and Partitioning Guide.

With the DISABLE_PARALLEL_DML hint you can disable PDML at query level when you have enabled it in the session. And with the ENABLE_PARALLEL_DML hint you can enable PDML for one query even when it's not enabled in the session. And you can do that even if you have a transaction in progress:

SQL> insert /*+ enable_parallel_dml */ into DEMO2 select * from DEMO1;

1000000 rows created.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  707bk8y125hp4, child number 0
-------------------------------------
insert /*+ enable_parallel_dml */ into DEMO2 select * from DEMO1

Plan hash value: 4271246053

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |      1 |        |      0 |00:00:18.76 |   22343 |
|   1 |  LOAD TABLE CONVENTIONAL | DEMO2    |      1 |        |      0 |00:00:18.76 |   22343 |
|   2 |   PX COORDINATOR         |          |      1 |        |   1000K|00:00:04.22 |       5 |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |      0 |   1000K|      0 |00:00:00.01 |       0 |
|   4 |     PX BLOCK ITERATOR    |          |      0 |   1000K|      0 |00:00:00.01 |       0 |
|*  5 |      TABLE ACCESS FULL   | DEMO1    |      0 |   1000K|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 2 because of table property
   - PDML disabled because object is not decorated with parallel clause
   - Direct Load disabled because no append hint given and not executing in parallel

Ok. I've enabled PDML but PDML occurs only when in parallel. Here the table has no parallel degree and there is no PARALLEL hint. Once again dbms_xplan gives us the reason. And because it's not PDML and there is no append hint, then it's not loaded in direct-path.

Here is the SQL Monitoring plan. Note that is the same as the previous one except that it's not the same cost. I don't know why yet. If you have any idea, please comment.

PDML2.png

Enable parallel DML while in a transaction

I disable PDML and start a transaction:

SQL> commit;

Commit complete.

SQL> alter session disable parallel dml;

Session altered.

SQL> delete from DEMO1 where rownum
SQL> select status,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=sys_context('userenv','sid'));

STATUS            USED_UREC
---------------- ----------
ACTIVE                 1000

And while I'm within that transaction, Let's do the parallel insert enabled by hint:

SQL> insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1;

999000 rows created.

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  2b8q4k902pbdx, child number 1
-------------------------------------
insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1

Plan hash value: 86785878

-------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Starts | A-Rows | Buffers | OMem |1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |      |      1 |      4 |     136 |      |     |          |
|   1 |  PX COORDINATOR                    |      |      1 |      4 |     136 |      |     |          |
|   2 |   PX SEND QC (RANDOM)              | :TQ10|      0 |      0 |       0 |      |     |          |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|      |      0 |      0 |       0 |   33M|  33M| 2068K (0)|
|   4 |     OPTIMIZER STATISTICS GATHERING |      |      0 |      0 |       0 |      |     |          |
|   5 |      PX BLOCK ITERATOR             |      |      0 |      0 |       0 |      |     |          |
|*  6 |       TABLE ACCESS FULL            | DEMO1|      0 |      0 |       0 |      |     |          |
-------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

Here PDML occurred. We know that because of the load operator under the coordinator (Note to self: HYBRID TSM/HWMB is something to investigate - once again comments welcome). I've displayed the plan with 'allstats last' which show only the coordinator activity. SQL Monitor can show all:

PDML5.png

Conclusion

Yes you can enable PDML at query level in 12c and it is documented. And you can do it even when within a transaction which is a restriction only for 'alter session enable parallel dml' but not for that hint.