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
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...)
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:
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...)
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...)
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...)
… 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...)
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 created a new GitHub public repository with my SQL scripts. Here is the URL:
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...)
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...)
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.
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...)
We bumped a Linux 184.108.40.206 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...)
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...)
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...)
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...)
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...)
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 […]
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...)