Skip navigation.

Yann Neuhaus

Syndicate content
All blog entries from http://www.dbi-services.com/
Updated: 14 hours 51 min ago

BEEZY: Social Network for SharePoint 2013

Wed, 2015-07-01 13:32


Beezy-logo-M-25255B20-25255D


Social Networking.. Everybody is actually "connected": professional network, private social network... There is so many solutions around as of today. Which one should I use? What are the differences?
Regarding the use of  a social work, we have already seen YAMMER, what about BEEZY?

What is Beezy? what

Beezy is a social network built inside SharePoint.
Beezy comes in two flavors: on premises behind the firewall on SharePoint Server 2010 and in the cloud on Office365.


Beezy Features

 

  • Collaboration tools: by a click, sharing Files, events, tasks, images, video, links, is possible! Yes it is!
  • Groups: Beezy allows to create groups to structure corporate information, the setting up is user friendly and even if a group is shut down, information’ are kept.
  • Microblogging: this is a good way for collaboration, team spirit, you share ideas and get feedbacks in real-time. As with Twitter, you can use Tag like hashtags (#) and replies (@) and Embed videos from YouTube!
  • Follows: Knowledge management is also about effectively filtering information. Following, replying… users are notified when a change is made to anything they are following whether conversations or documents.
  • Profiles: A unique employee profile regrouping professional data, latest activity is available. You can also link your past activities with LinkedIn, and synchronize employee data with Active Directory.

Here is video link about Beezy: Beezy or Yammer? Beezy-logo-M-25255B20-25255D   and yammer

 

The biggest difference between both tools is the integration.
Beezy is SharePoint integrated whereas Yammer get only a link in a top menu and a web part that doesn’t accept uploading files in the microblog.

Beezy works within the SharePoint framework, all of your permissions, storage, compliance policies and procedures remain the same, unlike in a hybrid solution using Yammer/Office365 where the level of access is limited by comparison, requiring additional management overhead.


Only good User Experience drives real adoption
As we already seen in others articles, user experience only is capable to drive to a real adoption. More simple, fast and intuitive tools you will put in place, more your employees will jump in.

Collaboration
Conclusion


Beezy offers a complete integrated Collaboration tool in SharePoint 2013 / OFFICE 365, easily deploy in SharePoint Servers 2013 and easy to use.
In order to make the right choice, take time to analyze your business needs, try solutions with small groups, get feedbacks from users and then take a decision.


Source: www.beezy.net



Multitenant vs. schema based consolidation

Tue, 2015-06-30 11:12

If you want install multiple instances of a software, for example you host the ERP for several companies or subsidiaries, you have 3 solutions:

  • have one database and multiple schema
  • have multiple databases
  • have one database and multiple pluggable databases

Of course, this is exactly the reason for pluggable databases: multitenant. You have good isolation but still share resources. A lot of reasons have been given why multiple schema - or schema based consolidation - is not a good solution. I don't agree with most of them. But there is one very good reason that I'll show later and it's about cursor sharing.

schema based consolidation

Let's take the Oracle white paper presenting multitenancy.

Name collision might prevent schema-based consolidation

Yes some applications have a fixed schema name. If your ERP must be installed in SYSERP schema, then you cannot install several ones in the same database.

However, you should challenge your application provider for that before changing all your infrastructure and buying expensive options. Maybe I'm too optimistic here, but I  think it's something from the past. I remember a telco billing software I've installed 15 years ago. The schema was 'PB'. It had nothing to do with the software name or the vendor name. But when I asked if I can change, answer was No. That schema name was hard-coded everywhere. It got it when the main developer came to visit us... his name was Pierre B.

About public synonyms, and public database links... please just avoid them.


Schema-based consolidation brings weak security

Same idea. If your application requires a 'SELECT ANY PRIVILEGE' then don't do it. In 12c you have privilege analysis that can help to identify the minimal rights you need to grant.

 

Per application backend point-in-time recovery is prohibitively difficult

I don't see the point. Currently multitenant do not give us more options because pluggable database point in time recovery, nor flashback pluggable database, is currently possible in-place. But I know it's planned for the future. You can already read about it at http://www.google.com/patents/US20140095452

Of course, when using schema-based consolidation you should used different tablespaces and you have TSPITR.

 

Resource management between application backends is difficult

Well you don't need pluggable database to use services. Multitenant is just an easy way to force the application to use specific services.

 

Patching the Oracle version for a single application backend is not possible

Yes, plugging a PDB into a different version CDB can be faster for those applications that have lot of objects. But it is not as easy as the doc says. The PDB dictionary must be patched. It's still a good think when the system metadata is a lot smaller than the application metadata

 

Cloning a single application backend is difficult

Cloning a PDB is easy. Right. 

Finally, multitenant is nice because of pluggable databases. Do you know that all occurrence of 'multitenant' in 12c code or documentation was 'pluggable database' one month before the release?

But wait a minute, I'm not talking about test environments here. I'm talking about consolidating the similar production databases. And all the plug/unplug has the same problem as transportable tablespaces: source must be made read-only.

 

Cursor sharing in schema based consolidation

Time to show you what is the big advantage of multitenant.

10 years ago I worked on a database that had 3000 schemas. Well we had 5 databases like that. You can think of them as specialized datamarts: same code, same data model, but different data, used by application services provided to different customers. A total of 45TB was quite nice at that time.

That was growing very fast and we had 3 issues.

Issue one was capacity planning. The growth was difficult to predict. We had to move those schemas from one database to another, from one Storage system to another... It was 10g - no online datafile move at that time. Transportable tablespaces was there, but see next point.

The second issue was the number of files. At first, each datamart had its set of tablespaces. But >5000 datafiles on a database was too much for several reasons. One of the reason was RMAN. I remember a duplicate with skip tablespace took 2 days to initialize... 

Then we have consolidated several datamarts into same tablespaces. When I think about it, the multitenant database we can have today (12c) would not have been an easy solution. Lot of pluggable databases mean lot of datafiles. I hope those RMAN issues have been fixed. But there are other ones. Did you ever try to query DBA_EXTENTS on a >5000 datafiles database? I had to when we had some block corruption on the SAN (you know, because of issue 1 we did lot of online reorg of the filesystems, and SAN software had a bug) This is where I made my alternative to DBA_EXTENTS.

Then the third issue was cursor sharing.

Let me give you an example

I create the same table in two schemas (DEMO1 and DEMO2) of same database.

SQL> connect demo1/demo@//192.168.78.113/PDB1
Connected.
SQL> create table DEMO as select * from dual;

Table created.

SQL> select * from DEMO;

D
-
X

SQL> select prev_sql_id from v$session where sid=sys_context('userenv','sid');

PREV_SQL_ID
-------------
0m8kbvzchkytt

SQL>
SQL> connect demo2/demo@//192.168.78.113/PDB1
Connected.
SQL> create table DEMO as select * from dual;

Table created.

SQL> select * from DEMO;

D
-
X

I'm in multitenant here because of the second test I'll do, but it's the same pluggable database PDB1.

 You see that I've executed exactly the same statement - SELECT * FROM DEMO - in both connections. Same statement but on different tables. Let's look at the cursors:

b2ap3_thumbnail_CaptureShared1.JPG

The optimization tried to share the same cursor. The parent cursor is the same because the sql text is the same. Then it follows the child list in order to see if a child can be shared. But semantic verification sees that it's not the same 'DEMO' table and it had to hard parse.

The problem is not hard parse. It's not the same table, then it's another cursor. Only the name is the same.

Imagine what happened on my database where I had 3000 identical queries on different schemas. We didn't have 'perf flame graphs' at that time, or we would have seen a large flame over kkscsSearchChildList.

Looking at thousand of child cursors in the hope to find one that can be shared is very expensive. And because it's the same parent cursor, there is a high contention on the latch protecting the parent.

The solution at that time was to add a comment into the sql statements with the name of the datamart, so that each one is a different sql text - different parent cursor. But that was a big change of code with dynamic SQL.

Cursor sharing in multitenant consolidation

So, in 12c if I run the same query on different pluggable databases. After the previous test where I had two child cursors in the PDB1 (CON_ID=5) I have run the same in PDB2 (CON_ID=4) and here is the view of parent and child cursors from the CDB:

b2ap3_thumbnail_CaptureShared2.JPG

We have the two child cursors from the previous test and we have a new child for CON_ID=4

The child number may be misleading but the search for shareable cursor is done only for the current container, so the same query when run from another pluggable database did not try to share a previous cursor. We can see that because there is not an additional 'reason' in V$SQL_SHARED_CURSOR.

SQL> select con_id,sql_id,version_count from v$sqlarea where sql_id='0m8kbvzchkytt';

    CON_ID SQL_ID        VERSION_COUNT
---------- ------------- -------------
         5 0m8kbvzchkytt             3
         4 0m8kbvzchkytt             3

The V$SQLAREA is also misleading because VERSION_COUNT aggregates the versions across containers.

But the real behavior is visible in V$SQL_SHARED_CURSOR above and if you run that with a lot of child cursor you will see the difference in CPU time, latching activity, etc.

Conclusion

I'm not talking about pluggable databases here. Pluggable database do not need the multitenant option as you can plug/unplug database in single-tenant. Pluggable database is a nice evolution of transportable database.

When it comes to multitenant - having several pluggable database in the same container, in order to have several 'instances' of your software without demultiplicating the instances of your RDBMS - then here is the big point: consolidation scalability.

You can add new pluggable databases, and run same application code on them, without increasing contention, because most of the instance resources are isolated to one container. 

Indexing for like/similarity operations

Mon, 2015-06-29 04:05

Indexing queries for like/similarity conditions is not that easy with the usual index types. The only option you have with btree indexes (especially if the wild-card is at the beginning of the filter) is to create a partial index on that columns for a very specific query.

Oracle Log Writer and Write-Ahead-Logging

Sun, 2015-06-28 11:29

I posted a tweet with a link to a very old document - 20 years old - about 'internals of recovery'. It's a gem. All the complexity of the ACID mecanisms of Oracle are explained in a very simple way. It was written for Oracle 7.2 but it's incredible to see how much the basic things are still relevant today. Of course, there is  a reason for that: the mecanisms of recovery are critical and must be stable. There is one more reason in my opinion: the Oracle RDBMS software was very well designed, then the basic structures designed 20 years ago are still able to cope with new features, and to scale with very large databases, through the versions and the years. 

It's 20 years old but it's still the best written document I've read about how Oracle works http://t.co/4CAI4Q5MIm http://t.co/mmgA50JzMQ

— Franck Pachot (@FranckPachot) June 26, 2015

If you check the conversation that followed, a doubt has been raised about the following sentence:

According to write-ahead log protocol, before DBWR can write out a cache buffer containing a modified datablock, LGWR must write out the redo log buffer containing redo records describing changes to that datablock.

There are 2 ways to clear out that kind of doubt: read and test. And we need both of them because:

  • documentation may have bug
  • software may have bug

so you can be sure about a behaviour only when both documentation and test validates your assumption.

Documentation

The first documentation I find about it is another gem describing how Oracle works: Jonathan Lewis 'Oracle Core (Apress)'. And it's clearly stated that:

One of the most important features of the Oracle code is that the database writer will not write a changed block to disk before the log writer has written the redo that describes how the block was changed. This write-ahead logging strategy is critical to the whole recovery mechanism.

Then there is of course the Oracle Documentation:

Before DBW can write a dirty buffer, the database must write to disk the redo records associated with changes to the buffer (the write-ahead protocol). If DBW discovers that some redo records have not been written, it signals LGWR to write the records to disk, and waits for LGWR to complete before writing the data buffers to disk.

Test case

Ok, that should be enough. But I want to do a simple testcase in order to see if anything has changed in the latest version (12.1.0.2). My idea is to check two things:

  • whether a checkpoint is requesting so work to be done by logwriter
  • whether a change is written to redo log after a checkpoint, without waiting the usual 

I create a table:

19:07:21 SQL> create table DEMO as select '--VAL--1--'||to_char(current_timestamp,'hh24missffff') val from dual;

Table created.

19:07:21 SQL> select * from DEMO;

VAL
----------------------------------
--VAL--1--190721367902367902

 

I start with a new logfile:

19:07:21 SQL> alter system switch logfile;
System altered.

And I retrieve the log writer process id for future use:

19:07:21 SQL> column spid new_value pid
19:07:21 SQL> select spid,pname from v$process where pname='LGWR';

SPID PNAME
------------------------ -----
12402 LGWR

19:07:21 SQL> host ps -fp &pid
UID PID PPID C STIME TTY TIME CMD
oracle 12402 1 0 Jun25 ? 00:00:46 ora_lgwr_DEMO14
update and commit

Here is a scenario where I update and commit:

19:07:21 SQL> update DEMO set val='--VAL--2--'||to_char(current_timestamp,'hh24missffff');

1 row updated.

19:07:21 SQL> select * from DEMO;

VAL
----------------------------------
--VAL--2--190721443102443102

19:07:21 SQL> commit;

Commit complete.

I want to see if a checkpoint has something to wait from the log writer, so I freeze the log writer:

19:07:21 SQL> host kill -sigstop &pid

and I checkpoint:

19:07:21 SQL> alter system checkpoint;

System altered.

No problem. The checkpoint did not require anything from log writer in that case. Note that the dirty buffers related redo has already been written to disk at commit (and log writer was running at that time).

I un-freeze it for the next test:

19:07:21 SQL> host kill -sigcont &pid

update without commit

Now I'm doing the same but without commit. My goal is to see if uncommited dirty blocks need their redo to be written to disk.

19:07:51 SQL> select * from DEMO;

VAL
----------------------------------
--VAL--2--190721443102443102

19:07:51 SQL> host kill -sigstop &pid

19:07:51 SQL> update DEMO set val='--VAL--3--'||to_char(current_timestamp,'hh24missffff');

1 row updated.

19:07:51 SQL> alter system checkpoint;

Here it hangs. Look at the wait events:

b2ap3_thumbnail_CaptureLGWR.JPG

My checkpoint is waiting on 'rdbms ipc reply' until the log writer is woken up. 


$ kill -sigcont 12402

System altered.

19:09:37 SQL> select * from DEMO;

VAL
----------------------------------
--VAL--3--190751477395477395

The checkpoint is done.

 

Note that if I run the same but wait 3 seconds after the update (because I know that log writer writes redo at least every 3 seconds even not asked to do it):

21:33:35 SQL> update DEMO set val='--VAL--3--'||to_char(current_timestamp,'hh24missffff');

1 row updated.

21:33:35 SQL> host sleep 3

21:33:38 SQL> host kill -sigstop &pid

21:33:38 SQL> alter system checkpoint;

System altered.

21:33:38 SQL>

checkpoint is not waiting because all the redo that covers the dirty buffers are alerady written.

I've also checked that immediately after the checkpoint (without stopping the log writer here) the uncommited change is written to the redo log files:

21:56:38 SQL> select group#,v$log.status,member from v$log join v$logfile using(group#) where v$log.status='CURRENT';

GROUP# STATUS MEMBER
---------- ---------------- ----------------------------------------
2 CURRENT /u01/DEMO/oradata/DEMO14/redo02.log


21:56:38 SQL> update DEMO set val='--VAL--2--'||to_char(current_timestamp,'hh24missffff');

1 row updated.

21:56:38 SQL> select * from DEMO;

VAL
----------------------------------
--VAL--2--215638557183557183

21:56:38 SQL> alter system checkpoint;

System altered.

21:56:38 SQL> host strings &redo | grep "VAL--"
--VAL--1--215638376899376899
--VAL--2--2156385571

A simple grep reveals that redo has been written (I've no other activity in the database - so no concurrent commits here).

Conclusion

Even if some mecanisms have been improved (see Jonathan lewis book for them) for performance, the fundamentals have not changed.

I've said that there are two ways to validated an assumption: documention and test.
But there is a third one: understanding.

When you think about it, if you write uncommited changes to the files, then you must be able to rollback them in case of recovery. Where is the rollback information? In the undo blocks. Are the undo blocks written on disk when the database is written on disk? You don't know. Then where do you find the undo information in case of recovery? The redo genereated by the transaction contains change vectors for data blocks and for undo blocks. Then if you are sure that all redo is written before the block containing uncomitted changes, then you are sure to be able to rollback those uncommited changes.

Note that this occurs only for modifications through buffer cache. Direct-path insert do not need to be covered by redo to be undone. It's the change of high water mark that will be undone and this one is done in buffer cache, protected by redo.

Oracle Database Cloud Service - My first trial

Sat, 2015-06-27 12:31

The cloud has been annouced, I want to try.

From the cloud.oracle.com/database website, there is Trial only for the 'Database Schema Service' so I asked fot it, received an e-mail with connection info and it works:

b2ap3_thumbnail_CaptureCloud001.JPG

 

Good. The password was temporary, so I have to change it, and set answers to 3 within 4 questions in case I forgot my password.

Java error: my user does not exist:

b2ap3_thumbnail_CaptureCloud002.JPG

Ok, I was too quick after receiving the e-mail... Let's wait 5 minutes and come back:

 

b2ap3_thumbnail_CaptureCloud003.JPG

 

What ? Same as old password ? 

let's try another one:

 

b2ap3_thumbnail_CaptureCloud004.JPG

 

Ok. Now I understand. When my user did not exist, the passward has been changed anway... Good I remember it.

I put the first password I tried as old password:

 

b2ap3_thumbnail_CaptureCloud005.JPG

Ok. But I know my previous passord, let's keep it.

I come back to the url and connect:

 

b2ap3_thumbnail_CaptureCloud006.JPG

 

It seems that I don't remember it... let's go through the 'Forgot Passord' screen. I entered my mother's maiden name, may pet name, my city of birth... well all thost top secret information that everybody has on his Facebook first page ;)

And enter a new password:

 

b2ap3_thumbnail_CaptureCloud007.JPG

Bad luck again...

Going back to the e-mail I see something strange: there is two spaces in front of my username:

 

b2ap3_thumbnail_CaptureCloud007b.JPG

Ok, I try everything: my username with and without space in front, the 3 passwords I tried to change previously... same error.

Let's start from the begining. Click on the first url in the mail.

Bingo. I don't know how but I'm logged. Here is the first screen of by first database in the cloud:

b2ap3_thumbnail_CaptureCloud008.JPG

 

Wait a minute... is this new ? An online APEX workspace that is available for years is now 'The' Oracle Cloud Database Schema Service available as a 30-days trial?

Without any exitation, I'll do my first Hello world in a database in the Cloud:

b2ap3_thumbnail_CaptureCloud009.JPG

I hope we will be able to get a trial account for the 'real' Database as a Service in the Cloud. I always loved to be able to download and try Oracle products for free. I don't think we are ready to pay to test it. Prices here.

Quickly create a hundred databases and users

Fri, 2015-06-26 13:00
Do you need a hundred databases and users for training etc. in PostgreSQL?

Swiss Postgres Conference 2015

Fri, 2015-06-26 11:00

At the 26th of June I had the chance to attend the second Swiss Postgres Conference at the HSR Rapperswil. It was packed with interesting sessions.

RAC buffer states: XCUR, SCUR, PI, CI

Thu, 2015-06-25 13:43

In RAC, blocks are copied across instances by the Global Cache Service. In single instance, we have only two status: CR for consistent read clones where undo is applied, and CUR for the current version that can be modified (then being a dirty block). I'ts a bit more complex in RAC. Here is a brief example to show the buffer status in Global Cache.

SCUR: shared current

I connect to one instance (I have a few singleton services. service ONE is on instance 3 and service TWO is on instance 1)

SQL> connect demo/demo@//192.168.78.252/ONE.racattack
Connected.
and I query a row by ROWID in order to read only one block
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1';
old   1: select rowid,DEMO.* from DEMO where rowid='&rowid1'
new   1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD'

ROWID                      ID          N
------------------ ---------- ----------
AAAXqxAALAAACUkAAD         10         10
Here is the status of the buffer in the buffer cache:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id;

   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         3          1 scur       00000000B9FEA060 N N N N N N
The block has been read from disk by my instance. Without modification it is in SCUR status: it's the current version of the block and can be shared.

SCUR copies

Now connecting to another instance

SQL> connect demo/demo@//192.168.78.252/TWO.racattack
Connected.
and reading the same block
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1';
old   1: select rowid,DEMO.* from DEMO where rowid='&rowid1'
new   1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD'

ROWID                      ID          N
------------------ ---------- ----------
AAAXqxAALAAACUkAAD         10         10
let's see what I have in my Global Cache:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;

   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 scur       00000000B0FAADC0 N N N N N N
         3          1 scur       00000000B9FEA060 N N N N N N
non modified blocks can be shared: I have a copy on each instance.

XCUR: exclusive current

I'll start a new case, I flush the buffer cache

connecting to the first instance

SQL> connect demo/demo@//192.168.78.252/ONE.racattack
Connected.
I'm now doing a modification with a select for update (which writes the lock in the block, so it's a modification)
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1' for update;
old   1: select rowid,DEMO.* from DEMO where rowid='&rowid1' for update
new   1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' for update

ROWID                      ID          N
------------------ ---------- ----------
AAAXqxAALAAACUkAAD         10         10
now the status in buffer cache is different:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;

   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         3          1 cr         00               N N N N N N
         3          1 xcur       00000000B9FEA060 Y N N N N N
So I have two buffers for the same block. The buffer that has been read and will not be current anymore because it has the rows before the modifications. It stays in consistent read (CR) status. The modified one is then the current one but cannot be shared: its the XCUR buffer where modifications will be done.

CR consistent read

Now I'll read it from the second instance

SQL> connect demo/demo@//192.168.78.252/TWO.racattack
Connected.
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1';
old   1: select rowid,DEMO.* from DEMO where rowid='&rowid1'
new   1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD'

ROWID                      ID          N
------------------ ---------- ----------
AAAXqxAALAAACUkAAD         10         10
the block is read and I've another CR buffer:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;

   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 cr         00               N N N N N N
         3          1 cr         00               N N N N N N
         3          1 xcur       00000000B9FEA060 Y N N N N N
the CR buffer is at another SCN. A block can have several CR blocks (by default up to 6 per instance)

PI: past image

Let's do a modification from the other instance

SQL> connect demo/demo@//192.168.78.252/TWO.racattack
Connected.
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1' for update;
old   1: select rowid,DEMO.* from DEMO where rowid='&rowid1' for update
new   1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' for update

ROWID                      ID          N
------------------ ---------- ----------
AAAXqxAALAAACUkAAD         10         10
My modification must be done on the current version, which must be shipped to my instance
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;

   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         1          1 xcur       00000000B0FAADC0 Y N N N N N
         3          1 cr         00               N N N N N N
         3          1 pi         00000000B9FEA060 Y N N N N N
and the previous current version remains as a PI - past image. It cannot be used for consistent reads but it is kept for recovery: if current block is lost, redo can be applied to the past image to recover it. See Jonathan Lewis explanation.

Checkpoint

As the past images are there in case of recovery, they are not needed once an instance has checkpointed the current block.

SQL> connect sys/oracle@//192.168.78.252/ONE.racattack as sysdba
Connected.
SQL> alter system checkpoint;
System altered.
afer the checkpoint on the instance that has the XCUR, there is no dirty buffer in any instance:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr;

   INST_ID     CLASS# STATUS     LOCK_ELEMENT_ADD D T P S D N
---------- ---------- ---------- ---------------- - - - - - -
         1          1 cr         00               N N N N N N
         1          1 cr         00               N N N N N N
         1          1 xcur       00000000B0FAADC0 N N N N N N
         3          1 cr         00               N N N N N N
         3          1 cr         00               N N N N N N
the PI became a consistent read.

Summary

Here are the states we have seen here:

XCUR: current version of the block - holding an exclusive lock for it

SCUR: current version of the block that can be share because no modification were done

CR: only valid for consistent read, after applying the necessary undo to get it back to requried SCN

PI: past image of a modified current block, kept until the latest version is checkpointed

and the other possible states:

FREE: The buffer is not currently in use.

READ: when the block is being read from disk

MREC: when the block is being recovered for media recovery

IREC: when the block is being recovered for crash recovery

another way to list invalid objects

Thu, 2015-06-25 01:00

How often did I type a query like this to list the invalid objects in a database?

select count(*)
  from dba_objects
 where status  'VALID';
    -- and user in/not in

Today I learned another way to do the same.

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: