Search

Top 60 Oracle Blogs

Recent comments

October 2019

New scripts: xb.sql and xbi.sql - Explain Oracle Execution Plans Better! (Part 1)

As I promised in my previous post, I'm going to blog more frequently for a change. So here's a blog entry about some “new” Oracle execution plan displaying scripts that I've had since 2011 or so - I just tidied them up recently and added some improvements too. My aim in this blog post is not to go deep into SQL tuning topics, but just show what these scripts can do.

New scripts: xb.sql and xbi.sql - Explain Oracle Execution Plans Better! (Part 1)

As I promised in my previous post, I'm going to blog more frequently for a change. So here's a blog entry about some “new” Oracle execution plan displaying scripts that I've had since 2011 or so - I just tidied them up recently and added some improvements too. My aim in this blog post is not to go deep into SQL tuning topics, but just show what these scripts can do.

opt_estimate catalogue

This is just a list of the notes I’ve written about the opt_estimate() hint.

My Oracle Support (MOS) : Where do we go from here?

Well, it happened again. I lost the plot on Twitter … again. I deleted them a lot quicker this time, but a few people saw them … again…

Today’s “incident” was because I was juggling multiple SRs, where I don’t think I’m getting straight answers, and what I believe is a reasonable level of service.

Having deleted the tweets I put out this one.

I am venting because I have no filter these days, and I am quickly deleting them because I know they will cause problems for some of my friends inside Oracle.

I feel like I want to go to war over this, but I know the best thing to do is to go home and play with tech…

What’s new with Oracle database 12.1.0.2.190416 versus 12.1.0.2.190716

There are a couple of undocumented spare parameters changed to named undocumented parameters, this is quite normal to see.

With the Oracle database version 12.1.0.2.190416 patched to 12.1.0.2.190716 on linux, the following things have changed:

Trace Files

A recent blog note by Martin Berger about reading trace files in 12.2 poped up in my twitter timeline yesterday and reminded me of a script I wrote a while ago to create a simple view I could query to read the tracefile generated by the current session while the session was still connected. You either have to create the view and a public synonym through the SYS schema, or you have to use the SYS schema to grant select privileges on several dynamic performance views to the user to allow the user to create the view in the user’s schema. For my scratch database I tend to create the view in the SYS schema.

Script to be run by SYS:

Midlands Microsoft 365 and Azure User Group – October 2019

https://oracle-base.com/blog/wp-content/uploads/2019/09/m365-and-azure-3... 300w" sizes="(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px" />

On Tuesday evening I went to the second event of the Midlands Microsoft 365 and Azure User Group. It was co-organised by Urfaan Azhar and Lee Thatcher from Pure Technology Group, and Adrian Newton from my company.

The definition of proof

One of the pieces of advice that I often see on the ‘net is that undo space is somehow this incredibly precious thing, and as a consequence, one should always keep the amount of uncommitted changes in the database to a small size.

Personally I think that is baloney (Ed-in reality, as an Australian I have a slightly more powerful choice of term, but lets keep things PG-rated </p />
</em></p></div>

    	  	<div class=

_cursor_obsolete_threshold

At the recent Trivadis Performance Days in Zurich, Chris Antognini answered a question that had been bugging me for some time. Why would Oracle want to set the default value of _cursor_obsolete_threshold to a value like 8192 in 12.2 ?

In 11.2.0.3 the parameter was introduced with the default value 100; then in 11.2.0.4, continuing into 12.1, the default value increased to 1,024 – what possible reason could anyone have for thinking that 8192 was a good idea ?

The answer is PDBs – specifically the much larger number of PDBs a single CBD can (theoretically) support in 12.2.

In fact a few comments, and the following specific explanation, are available on MoS in Doc ID 2431353.1 “High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance”:

A refreshing look at PIVOT

We had an AskTOM question come in recently where our customer was very excited about the PIVOT operator in SQL which lets you transpose rows to columns. This is a very common requirement in applications that want to take data that has been modelled in “pure” relational form, and present in a more “human-digestible” form. There are plenty of posts and examples out there about PIVOT, but if you haven’t seen one, here’s a trivial example just so that you are up to speed for the rest of this post.