Last week in Stream Processing & Analytics 11/7/2016

This is the 39th installment of my blog series around Stream Processing and Analytics.

As usual, find below the new blog articles, presentations, videos and software releases from last week:

News and Blog Posts

General

Comparison

Apache Kafka

Signing up for scary stuff

So I subscribe to the idea that the only way to improve is to dive in on the deep end. Sink or swim.

With that in mind I sent in an abstract to RMOUG and actually got it accepted. Now, this is a conference I used to attend every year when I lived in Denver. I know the quality they have in most presentations nad I know that lots of people with “important” names in the (more...)

12c: OPTIMIZER_DYNAMIC_SAMPLING=11

Oracle 12c has a new feature related to optimizer dynamic sampling. The new level 11 is supposed to put the dynamic sampling on auto pilot and make the statistics available to other users. However, as soon as this was set, I started noticing processes spending significant time waiting for row cache latch. This was strange, especially having in mind that ASMM was configured. I checked the V$ROWCACHE  by using the following, fairly standard, query:

select (more...)

OTN tour 2016 APAC

The OTN tour came to APAC this year, so it’s been a pleasure and privilege to be able to participate in some of the legs.  Being Perth born and bred, I know all too well that any travel to Australia from … well… anywhere except Australia, is a long haul, so I’m very grateful to the array of overseas speakers who gave up their time and comfort to make the journey.

My first stop (more...)

Running SQL*Plus from a Python script

I needed to write a new script that was running on a Red Hat Linux 6 virtual machine and that would connect to databases using SQL*Plus. I was going to write a bash shell script but decided to use Python instead to see if I could do it using the Python that came with this version of Linux. I wont paste the entire script here but the key was to run SQL*Plus from Python instead of a shell (more...)

Code enhancements without changing code

An interesting suggestion came through on AskTom this week, which prompted the following exploration.

Let us assume you are populating a table with


INSERT INTO EMP SELECT * FROM SOURCE_EMPS

and it falls over with


ORA-12899: value too large for column

To capture the data that caused that error is not trivial. Of course, we could edit the code to perform DML error logging but of course, that means changing the source code, which means (more...)

So Long ACED

… and thanks for all the fish.

Today I removed myself from the OTN ACED program. This isn’t a reflection on the anything to do with the ACE program – quite the reverse, in fact – it’s because they’re introducing steps to ensure that the ACE Directors can justify their titles. Unfortunately, as anyone who has gone through (e.g.) ISO 9001 certification can tell you, quality assurance tends to translate into paperwork and (more...)

Bash Critical Mistakes

lately I wrote a couple of posts about bash scripting for DBAs (part 1 and part 2). I promised to post an example script as well, and I will. But before that I thought it’s important to give several examples of how scripts can be destructive. The examples in this post are real, and involved … Continue reading Bash Critical Mistakes

I put my SQL scripts on GitHub

I created a new GitHub public repository with my SQL scripts. Here is the URL:

https://github.com/bobbydurrett/OracleDatabaseTuningSQL

I’ve experimented with GitHub for my Python graphing scripts but wasn’t sure about putting the SQL out there. I don’t really have any comments in the SQL scripts. But, I have mentioned many of the scripts in blog posts so those posts form a type of documentation. Anyway, it is there so people can see it. Also, I get (more...)

ASH script to show query run times

I ran into a situation last week where a developer complained that a query sometimes ran for 3 or more seconds but normally runs much less than 1 second. I had just been to a local AZORA user group meeting where Tim Gorman talked about using ASH to diagnose issues so Tim’s talk motivated me to find some clever way to use ASH. I had these three pairs of start and stop dates and times to (more...)

I’m speaking at #DOAG2016 and #ukoug_tech16

This year I get to speak about advanced SQL twice at two different conferences. My first presentation is about row pattern matching with MATCH_RECOGNIZE and my second deals with ranges - including but not limited to Temporal Validity ranges.

BULK COLLECT into nested table

I had an observation come to me last week about PL/SQL and populating nested tables.

“The BULK COLLECT into <nested table> statement cannot be used repeatedly to append results into a table. 
Instead, it silently truncates the target table each time. “

This is true.  However, if you need to use BULK COLLECT to append results into a single nested table data structure, it is trivial to come up with a workaround using (more...)

HugePages speeds up Oracle login process on Linux

We bumped a Linux 11.2.0.4 database up to a 12 gigabyte SGA and the login time went up to about 2.5 seconds. Then a Linux admin configured 12 gigabytes of HugePages to fit the SGA and login time went down to .13 seconds. Here is how I tested the login time. E.sql just has the exit command in it so this logs in as SYSDBA and immediately exits:

$ time sqlplus /  (more...)

Quickly built new Python graph SQL execution by plan

sql_id-c6m8w0rxsa92v-on-mydb-database-with-plans

I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.

Since I just split up my Python graphs into multiple smaller scripts (more...)

Tim Gorman at AZORA meeting tomorrow in Scottsdale

Thinking about using Python scripts like SQL scripts

I’ve used Python to make graphs of Oracle database performance information. I put the scripts out on GitHub at https://github.com/bobbydurrett/PythonDBAGraphs. As a result I’m keeping my Python skills a little fresher and learning about git for version control and GitHub as a forum for sharing Open Source. Really, these Python scripts were an experiment. I don’t claim that I have done any great programming or that I will.

But, as I review what I have done (more...)

October Webinar Series: Digital Transformation, Forms and Mobile

This year’s Oracle Open World was an exciting experience for AuraPlayer, with many flocking to our booth for live demos and attending Mia’s sessions with clients like Rogers Group Inc. We were sad that many were unable to attend the conference, so we are bringing Oracle Open World to you in our October webinar series!

In the first installment of this series, “Digital Transformation: From Legacy to Enterprise Mobility,” we will demonstrate step-by-step how best (more...)

Need classes directory to run ENCRYPT_PASSWORD on PeopleTools 8.53

I had worked on creating a Delphix virtual copy of our production PeopleTools 8.53 database and wanted to use ENCRYPT_PASSWORD in Datamover to change a user’s password. But I got this ugly error:

Error: Process aborted. Possibly due to JVM is not available or missing java class or empty password.

What the heck! I have used Datamover to change passwords this way for 20 years and never seen this error. Evidently in PeopleTools 8.53 (more...)

OTN Appreciation Day: Tom Kyte #ThanksOTN

To answer Tim Hall’s call to appreciate OTN, I could have written about my go-to feature, the MATCH_RECOGNIZE clause, or my go-to development tool, Oracle SQL Developer. Instead, I’d like to salute my go-to “Oracle Technology” guy for over 10 years, Tom Kyte. It was 2005. After almost 25 years in IT, I knew something about […]

OTN Appreciation Day : Prebuilt Developer VMs

I learnt a lot from the entire Oracle Technology Network and I still do. One of the best features of OTN these days, IMHO, is the ability to download prebuilt Virtual Machines. Since I am a developer and not an administrator, I don’t like to be bothered with stuff like how much disk space do I need, how many cores should I use etc. I can just download a Virtual Box image, import it and (more...)