Top 60 Oracle Blogs

Recent comments

January 2020

Data Warehouse Design: Snowflake Dimensions and Lost Skew Trap

This post is part of a series that discusses some common issues in data warehouses. Originally written in 2018, but I never got round to publishing it.
While I was experimenting with the previous query I noticed that the cost of the execution plans didn't change as I changed the COUNTRY_ISO_CODE, yet the data volumes for different countries are very different.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">select c.country_name
, u.cust_state_province
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
from sales s
, customers u

PL/SQL Package with no DEFINER or INVOKER rights - Part 2

I posted about a discovery I made whilst testing for an issue in our PL/SQL code analyser checks in PFCLScan last week as I discovered that the AUTHID column in DBA_PROCEDURES or ALL_PROCEDURES or USER_PROCEDURES can be NULL; this caused....[Read More]

Posted by Pete On 28/01/20 At 03:11 PM

ANSI flashback

I am seeing “traditional” Oracle SQL syntax being replaced by “ANSI”-style far more frequently than I used to – so I thought I’d just flag up another reminder that you shouldn’t be too surprised if you see odd little glitches showing up in ANSI style that don’t show up when you translate to traditional; so if your SQL throws an unexpected error (and if it’s only a minor effort to modify the code for testing purposes) it might be a good idea to see if the problem goes away when you switch styles. Today’s little glitch is one that showed up on the Oracle-l listserver 7 years ago running but the anomaly still exists in 19c.

Video : Decoupling to Improve Performance

In today’s video we demonstrate how to cheat your way to looking like you’ve improved performance using decoupling.

This was based on the following article.

This came up in conversation a few days ago, so I thought it was worth resurrecting this demo. It doesn’t really matter what tech stack you use, the idea is still the same.

Oracle and postgres disk IO performance

This post is about one of the fundamentally important properties of a database: how IO is done. The test case I studied is doing a simple full table scan of a single large table. In both Oracle and postgres the table doesn’t have any indexes or constraints, which is not a realistic example, but this doesn’t change the principal topic of the study: doing a table scan.

I used a publicly available dataset from the US bureau of transportation statistics called
The zipped file is 347MB, unzipped size 1.7GB.

PL/SQL That is not DEFINER or INVOKER rights - BUG?

Note: Part 2 - PL/SQL Package with no DEFINER or INVOKER rights - Part 2 is available that takes this investigation further I always understood that PL/SQL objects in the database that are not explicitly changed to INVOKER rights....[Read More]

Posted by Pete On 24/01/20 At 03:19 PM

Friday Philosophy – Presenting Sex

These slides are from the first ever presentation I did. And yes, the presentation was at work.

Online Statistics Collection during Bulk Loads on Partitioned Tables


One of the enhancements to statistics collection and management in Oracle 12c was the ability of the database will automatically collect statistics during either a create-table-as-select operation or during the initial insert into a freshly created or freshly truncated table, provide that insert is done in direct-path mode (i.e. using the APPEND hint).
When that occurs, there is an additional operation in the execution plan; OPTIMIZER STATISTICS GATHERING.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

Structuring Content : Think Pyramid! 300w" sizes="(max-width: 308px) 85vw, 308px" />

This post is about making sure you get your message across by structuring your content correctly, and giving people convenient jump-off points when they’ve hit the level that is appropriate for them.

SQLcmd on my Mac: Seg fault

trying to connect to SQL Server from my Mac so install sqlcmd:

brew tap microsoft/mssql-release
brew update
brew install mssql-tools

then get error

$ sqlcmd -S,1433 -U kylelf  
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed].
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
Segmentation fault: 11

Did’t find much info google, so blogging here.

ln -s /usr/local/opt/openssl@1.1 /usr/local/opt/openssl

and it worked