Accelerate your #BI Performance with #Exasol

Your BI users complain about slow performance of their analytical queries? Is this your Status Quo?

tableau was taken as a popular example for AdHoc analytics but it might be any of the others like MicroStrategy, Looker, you name it. The good news is that this problem can be solved quite easily and without having to spend a fortune trying to speed up your legacy DWH to keep up with the BI demands:

Exasol High Performance Sidecar

Using Exasol as a High Performance Sidecar to take away the pain from your BI users is the easy and fast cure for your problem! This is actually the most common way how Exasol arrives at companies. More often than not this may lead to a complete replacement of the legacy DWH by Exasol:

Exasol replaces legacy DWH

That’s what adidas, Otto and Zalando did, to name a few of our customers.

Don’t take our word for it, we are more than happy to do a PoC!

Leave a comment

Account locking in an Active Data Guard environment

During the Data Guard round table of the excellent UKOUG Tech18 conference I got aware of this topic that I’d like to share with the Oracle community:

What is the locking behavior for user accounts in an environment where users may connect to the primary as well as to the standby database?

User gets locked on the primary

SQL> alter profile default limit failed_login_attempts 2;

Profile altered.

SQL> create user adam identified by adam;

User created.
SQL> grant create session to adam;

Grant succeeded.

SQL> connect adam/wrong@prima
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@prima
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect adam/wrong@prima
ERROR:
ORA-28000: The account is locked.

I changed the default profile so the account lock happens faster. The change of the default profile reaches the standby via redo apply. The same goes for account locks that happened on the primary like above.

Standby inherits the locked accounts from primary

SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.

This inherited lock cannot be unlocked on the standby:

SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> alter user adam account unlock;
alter user adam account unlock
*
ERROR at line 1:
ORA-28015: account unlock attempted on read-only database but a conflicting
account lockout on the primary exists

The account can only be unlocked on the primary and that implictly unlocks it on the standby too:

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> alter user adam account unlock;

User altered.
SQL> connect adam/adam@prima
Connected.
SQL> connect adam/adam@physt
Connected.

Account gets locked on the standby only

SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.

This lock at the standby is kept there in memory only and doesn’t impact the primary:

SQL> connect adam/adam@prima
Connected.

It can be unlocked on the standby:

SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> alter user adam account unlock;

User altered.

SQL> connect adam/adam@physt
Connected.

Standby locks are kept in memory

After a restart, the lock is gone:

SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.


SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  629143384 bytes
Fixed Size		    8660824 bytes
Variable Size		  180355072 bytes
Database Buffers	  436207616 bytes
Redo Buffers		    3919872 bytes
Database mounted.
Database opened.
SQL> connect adam/adam@physt
Connected.

Role change involving a restart of the standby

That will also lose the previous locks that have been done at the standby only:

SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.


SQL> exit
[oracle@uhesse ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Fri Dec 7 08:28:59 2018
Version 18.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "prima"
Connected as SYSDBA.
DGMGRL> switchover to physt;
Performing switchover NOW, please wait...
Operation requires a connection to database "physt"
Connecting ...
Connected to "physt"
Connected as SYSDBA.
New primary database "physt" is opening...
Operation requires start up of instance "prima" on database "prima"
Starting instance "prima"...
Connected to an idle instance.
ORACLE instance started.
Database mounted.
Database opened.
Connected to "prima"
Switchover succeeded, new primary is "physt"
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Members:
  physt - Primary database
    prima - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

DGMGRL> exit
[oracle@uhesse ~]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Dec 7 08:31:16 2018
Version 18.3.0.0.0

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

SQL> connect adam/adam@prima
Connected.
SQL> connect adam/adam@physt
Connected.

I did the tests on 18c but I don’t think this is a new feature. I just didn’t have that topic on the radar before. Which is one reason why we go to conferences, right? 🙂

,

1 Comment

#DOAG2018 Impressions

As every year in November, the database circus moved to Nuremberg on behalf of the annual DOAG conference. As you may know, this conference has very high standards in terms of turnout and top-notch speakers and it reached them once again: It was a great event!

It started with the welcome keynote where Stefan Kinnen told us that the conference attracted over 2000 attendees and more than 400 speakers from all over the world. That should make it the largest database conference in Europe, I suppose!

DOAG welcome keynote (Stefan Kinnen speaking)

I went on to attend Tirthankar Lahiri who presented about the In-Memory column store:

Tirthankar Lahiri preparing to speak

To me, the In-Memory expressions and In-Memory external tables sounded particular useful here.

Next talk was done by Martin Widlake about Performance Tuning:

Martin Widlake walking on stage

I liked his statement that in order to solve performance problems, the key skill you need is not technical in the first place, but more of a social nature: You need to thoroughly understand the problem and listen carefully. Secondly, some technical expertise is helpful, of course 🙂

Afterwards I did my own talk about Flashback in the same room to approximately 150 attendees – some less than Martin had, though:

The talk was well received, I finished my live demos in time while being able to answer some questions also – all good 🙂

Then came The Basics of Machine Learning by Heli Helskyaho (the pic I took is too bad to put it here, unfortunately) whose talk was so successful that she had to repeat it on the next day. Great job!

I went on attending Markus Michalewicz presenting about The Smartest HA Features in Oracle’s Autonomous Database:

Markus Michalewicz presenting

Here I liked the new concept of “Recovery Buddies” in RAC that leads to a shorter freeze time of the Global Resource Directory if one instance fails in particular.

Then came a talk How to run a user group delivered by three (!) presidents:

Three User Group presidents presenting

Martin emphasized that you need to have (at least) one guy who is passionate about the topic and one (often another guy) who gets things organized. Apart from that, size doesn’t matter that much: You can start a user group with three persons. Kamil and Luiza told us their story about how they started the POUG user group in Poland while Stefan gave us some insights into the quite extensive DOAG internal organization.

Continued with RAC Performance Internals by Anil Nair who attracted a substantial audience in the largest room:

Anil Nair in room Tokio

One takeaway here was for me that on Exadata, Oracle RAC uses ExaFusion to achieve 3 times faster block transfers over the interconnect compared to non-Exadata platforms – which is one more reason why Oracle uses Exadata as the platform for their autonomous database offers.

Then I was really shocked by the outcome of the DOAG support survey: Two thirds of the 270 customers interviewed said they are not satisfied with Oracle Support! That was even worse than two years ago when that survey reported over 50 % discontent. The room Singapur was full – but unfortunately filled with frustrated people who gave the Oracle officials present a hard time. I felt bad for them, it was a bitter moment, being for so long with Oracle myself in the past.

Went on to attend The battle between Oracle vs. PostgreSQL:

Jan Karremans (Postgres) vs. Daniel Westermannn (Oracle)

Frankly, my impression was that Daniel came up with the better arguments but Jan scored many sympathy points – nonetheless because many people wanted Postgres to win over Oracle. I think Oracle has to watch out here in order not to alienate from their customer base further. Otherwise, Postgres might continue to take away market share.

Next talk was from Paolo Kreth about Open Source Databases:

Paolo Kreth on stage

His message was that DBAs cannot ignore the usage of open source tools but have to be approachable and try to assist the internal customers with these tools. Otherwise they may just become less relevant.

Another interesting presentation was The changing role of the DBA by Valentin Leonard Tabacaru:

Valentin Leonard Tabacaru presenting

Especially in the light of Oracle’s new autonomous databases, his prediction is that maintenance tasks like patching will disappear while areas like data modelling, security or application tuning should become more important for DBAs.

Another very good talk was about the TimesTen In-Memory database by Doug Hood:

Doug Hood

He showed some convincing facts to back his claim that TimesTen is the fastest In-Memory database regarding OLTP workloads while also providing fault-tolerance and scalability.

All in all, the DOAG annual conference rocked again, kudos to the DOAG members who helped organizing and running it again so smoothly!

 

Leave a comment

%d bloggers like this: