Search

Top 60 Oracle Blogs

Recent comments

April 2019

Writing Linux Scripts- Part I

Many see scripting as a science, but if you want to write not just functional scripts, but efficient and easy to work with scripts, it is also an art.

Most SQL DBAs are feeling the pressure to learn BASH as they enter Azure and I strongly recommend it. I’m learning PowerShell as part of my education coming from a Linux background to Azure. It’s all about “the more you know”….you know?

So let’s start with learning it right.

Scripts = Stories

A good script has the following parts to it:

  • An Introduction
  • A Body
  • A Conclusion

We’re going to focus on this as part of our education on Linux scripting before we get into a load of terminology or scripting language.

Raw partitions?

Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw partition key. (If you have one, please let me know in the comments). Anyway, here’s the demo. I’ll start with a table using the cool automatic list partition facility and load a single row into it.

PostgreSQL and Jupyter notebook

Here is a little test of Jupyter Notebook to access a PostgreSQL database with very simple installation, thanks to Anaconda. I did it on Windows 10 but the same simplicity is on Linux and Mac.

Anaconda

I’ll use Anaconda to install the required components

Avoid compound hints for better Hint Reporting in 19c

Even if the syntax accepts it, it is not a good idea to write a hint like:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-56DAA0EC-54BB-4E9D-9049-BCEA934F7A89

/*+ USE_NL(A B) */ with multiple aliases (‘tablespec’) even if it is documented.

One reason is that it is misleading. How many people think that this tells the optimizer to use a Nested Loop between A and B? That’s wrong. This hint just declares that Nested Loop should be used if possible when joining from any table to A, and for joining from any table to B.

Actually, this is a syntax shortcut for: /*+ USE_NL(A) USE_NL(B) */

Oracle: compare DB Time and CPU time to ASH

Screen Shot 2019-04-26 at 1.31.49 PM

Below I’m comparing AAS from ASH verses SYSMETRICs specificially

  • ASH CPU in AAS vs SYSMETRIC CPU in AAS
  • ASH total AAS vs SYSMETRIC  total AAS derived from DB TIME

ASH CPU is consistently lower that SYSMETRIC  CPU and that could be because waits burn CPU but show up as waits and not CPU in ASH.

On the other hand, not sure why total AAS from SYSMETRIC  is consistently smaller than total AAS from ASH.

Preparing A Device for Someone New To I.T.

In my previous post I covered what I felt were the main considerations on deciding how to get someone with no real experience of I.T. online, for an example an elderly relative like my mum. In this post I’ll cover setting up the device.  Set-up is actually quite complex and there is a lot of assumed knowledge, like how the keyboard works, what spyware you leave turned on (none!) etc. So I am setting it up for her.  First I’ll just recap the main points on why I decided to use an Apple iPad for my Mum:

Dynamic Values in Linux Scripting

I do a LOT of scripting. Given the choice to click in a GUI vs. typing at the command line, I’ll choose the command line. Given the choice to type commands in repeatedly vs. scripting out a task I perform more than twice, I’ll script. Scripting effectively is an art as much as it’s a science.

My idea of science

Where a GUI can change, both in content, as well as layout, a script is less impacted by this when it is designed to dynamically work with the catalog. You have the choice to either work with the values in an array or to just pull it into a temporary file to work with as part of the script. For the example, I’ll stick with the latter to make our example easier to reproduce.

Take care with automatic indexes

This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index.

Let’s take a look at a simple example. I’ll explicitly nominate the LARGETS tablespace for my index that will support the primary key.

Grab all the DDL

I posted a video a couple of days ago showing a trigger mechanism to customize the capture of DDL that is issued on your database. The aim here is to be more generous with letting developers execute DDL on their Development databases, whilst still having a thorough record of the changes that are occurring. Of course, it goes without saying, which is why I am saying it </p />
</p></div>

    	  	<div class=

Bloom Filter Efficiency And Cardinality Estimates

I've recently came across an interesting observation I've not seen documented yet, so I'm publishing a simple example here to demonstrate the issue.

In principle it looks like that the efficiency of Bloom Filter operations are dependent on the cardinality estimates. This means that in particular cardinality under-estimates of the optimizer can make a dramatic difference how efficient a corresponding Bloom Filter operation based on such a cardinality estimate will work at runtime. Since Bloom Filters are crucial for efficient processing in particular when using Exadata or In Memory column store this can have significant impact on the performance of affected operations.