Search

Top 60 Oracle Blogs

Recent comments

January 2020

Thank You

Regular readers will have noticed that for the last few weeks I’ve been adding a footnote (now deleted) to any new blog posts asking for donations to the Nepal Youth Foundation (UK), with an offer to match (up to a limit) any donations made by my readers.  The page I had set up on JustGiving to collect donations and describe the work of the foundation reached my limit last week, so I sent off my matching cheque on 6th Jan. I got a very prompt email telling me the cheque had arrived the following day, and thanking me for the effort; then yesterday a follow-up thank you letter arrived in the morning post.

I thought I’d share this with you so that you could see that your generosity has been appreciated by an organisation that really cares about the change it can make to the lives of the people who most need their help:

(this is an answer to Jeff Potter —  “3 Reasons I Hate Booleans In Databases”)

(this is an answer to Jeff Potter — “3 Reasons I Hate Booleans In Databases”)

I’ll start with the “benchmark” because I like facts.

Testcase

Here is your test which I ran on less number of rows (because it is not needed and easier to run and share from db<>fiddle) and I’ve run the queries once before in order to warm-up the cache. And I displayed the execution plan to get better understanding about the response time:

(this is an answer to Jeff Potter —  “3 Reasons I Hate Booleans In Databases”)

(this is an answer to Jeff Potter — “3 Reasons I Hate Booleans In Databases”)

I’ll start with the “benchmark” because I like facts.

Testcase

Here is your test which I ran on less number of rows (because it is not needed and easier to run and share from db<>fiddle) and I’ve run the queries once before in order to warm-up the cache. And I displayed the execution plan to get better understanding about the response time:

Collection limitation

The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer’s handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert into another table any installments that were not yet recorded there.

The mechanism to turn a single row of contract data into a set of installments was a (optionally pipelined) table function that involved some business logic that (presumably) dealt with the timing and size of the installments. The final SQL to create the data that needed to be inserted was reported as follows (though it had clearly been somewhat modified):

Automating SQL and PL/SQL Deployments using Liquibase

https://oracle-base.com/blog/wp-content/uploads/2019/12/sqlcl-295x300.png 295w, https://oracle-base.com/blog/wp-content/uploads/2019/12/sqlcl-57x57.png 57w" sizes="(max-width: 189px) 85vw, 189px" />

You’ll have heard me barking on about automation, but one subject that’s been conspicuous by its absence is the automation of SQL and PL/SQL deployments…

The time has come …

… the walrus said, to speak of many things!

2019 was for me, in many ways, my “Annis Horribilis”. My mother passed away in early January, I suffered through a fair chunk of the year through chronic pain, and in mid December, my father also passed away.

Given all these things, I have made the relatively easy decision that I would retire, and effective January 6th, I left the workforce. I want to thank publicly my manager, Peter Underwood, who had held my position open for me to return to since I went on medical leave on December 5th, 2018.

So this is the last post that I will make on PeteWhoDidNotTweet. In the next week or so, I plan on removing the social media accounts I’ve been using. The website will remain up and running till the next time I’m supposed to renew it. I’ve set it to not renew, so at some stage, GoDaddy will no doubt expire it. If you need anything from the site, I suggest you copy it sooner rather than later.

The time has come …

… the walrus said, to speak of many things!

2019 was for me, in many ways, my “Annis Horribilis”. My mother passed away in early January, I suffered through a fair chunk of the year through chronic pain, and in mid December, my father also passed away.

Given all these things, I have made the relatively easy decision that I would retire, and effective January 6th, I left the workforce. I want to thank publicly my manager, Peter Underwood, who had held my position open for me to return to since I went on medical leave on December 5th, 2018.

So this is the last post that I will make on PeteWhoDidNotTweet. In the next week or so, I plan on removing the social media accounts I’ve been using. The website will remain up and running till the next time I’m supposed to renew it. I’ve set it to not renew, so at some stage, GoDaddy will no doubt expire it. If you need anything from the site, I suggest you copy it sooner rather than later.

Single Value Column Frequency Histogram Oracle 12c and later

It is hopefully in the meantime well known that Oracle has introduced in version 11g a new algorithm to gather statistics on a table that no longer requires sorting for determining the critical Number Of Distinct Values (NDV) figure - it instead uses a clever "approximate NDV" algorithm which always reads 100% of the table data and therefore in principle generates very accurate statistics. This new algorithm gets used only when the ESTIMATE_PERCENT parameter to the DBMS_STATS.GATHER*STATS calls is left at default or explicitly passed as "DBMS_STATS.AUTO_SAMPLE_SIZE". This new algorithm is also required in case other new features like "Incremental Statistics" should be used.

In 12c Oracle improved this algorithm allowing the generation of Frequency and the new Top Frequency histogram types in a single pass. The new Hybrid histogram type still requires a separate pass.

The curious case of enq: TX – row lock contention

So we had a doubtful pleasure of migrating a few databases from Oracle Exadata X3-2 to Oracle Exadata X7-2 Cloud at Customer.
Why doubtful? Well, this a material for a whole different story with a lot of beer – let me just say, that CC gen 1 was a bit rough around the edges </p />
</p></div>

    	  	<div class=

Oracle State Objects and Reading System State Dumps Hacking Session Video

I have uploaded the 3 hour hacking session video about Oracle instance state objects and how to read Oracle systemstate dumps to my YouTube channel:
Note that as I just uploaded the video, you may want to wait for a few hours until YouTube publishes HD version (currently it’s available only at 360p, but that should change soon).
The slides are in Slideshare:
https://www.slideshare.net/tanelp/oracle-state-objects-and-system-state-... Enjoy! :-)