Search

Navigation

Top 60 Oracle Blogs

September 2018

Case Study

Submitted by Jonathan Lewis on Sun, 2018-09-30 19:59

A question about reading execution plans and optimising queries arrived on the ODC database forum a little while ago; the owner says the following statement is taking 14 minutes to return 30,000 rows and wants some help understanding why.

If you look at the original posting you’ll see that we’ve been given the text of the query and the execution plan including rowsource execution stats. There’s an inconsistency between the supplied information and the question asked, and I’ll get back to that shortly, but to keep this note fairly short I’ve excluded the 2nd half of the query (which is a UNION ALL) because the plan says the first part of the query took 13 minutes and 20 second and the user is worried about a total of 14 minutes.

My APEX 18.2 upgrade in a nutshell

Submitted by connor_mc_d on Sun, 2018-09-30 06:24

As always, you should read the Installation/Upgrade manual from top to bottom before upgrading any piece of software, and be aware of all of the pre-requisites and processes. But for me, my Application Express 18.2 upgrade was as simple as:

Download Application Express
Unzip to my target location
sqlplus / as sysdba
- SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
Go to my ORDS installation
- java -jar ords.war validate
sqlplus / as sysdba
- SQL> @apex_rest_config.sql

And voila! A freshly upgraded Application Express in a matter of minutes…

Efficient query on DBA_EXTENTS for access by FILE_ID / BLOCK_ID

Original article

Submitted by Franck Pachot on Fri, 2018-09-28 16:38

A new blog post on the Databases at CERN blog: a query on DBA_EXTENTS optimized for very large databases.

https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-efficiently-query-dbaextents-fileid-blockid

Efficiently query DBA_EXTENTS for FILE_ID / BLOCK_ID

Hacking for Skew

Submitted by Jonathan Lewis on Fri, 2018-09-28 13:23

In my presentation to the UKOUG SIG yesterday “Struggling with Statistics – part 2” I described a problem that I wrote about a few months ago: when you join a fact table with a massively skewed distribution on one of the surrogate key columns to a dimension holding the unique list of keys and descriptions a query against a description “loses” the skew. Here’s an demo of the problem that’s a little simpler than the one in the previous article.

OpenWorld Monday…I’m so screwed

Submitted by connor_mc_d on Fri, 2018-09-28 04:34

As I mentioned in a previous blog post, I whipped up a small Application Express application to let me plan out my Openworld activities. But there’s a small problem with being able to quickly and easily find thing based on the schedule…. There is too much to see!!!

I had a quick glance through the schedule just for Monday, and already I can see myself being very frustrated with all of the content that I am going to have miss out on, simply because it clashes with other content I’d like to see.

Oracle write consistency, bug, and scalable multi-thread de-queuing

Original article

Submitted by Franck Pachot on Thu, 2018-09-27 20:16

A new blog post on the Databases at CERN blog:

A write consistency bug, how to see it with flashback query, and a scalable workaround.

https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-oracle-write-consistency-bug-and-multi-thread-de-queuing

Oracle write consistency bug and multi-thread de-queuing

Oracle

Column Group Catalog

Submitted by Jonathan Lewis on Thu, 2018-09-27 17:16

I seem to have written a number of aricles about column groups – the rather special, and most useful, variant on extended stats. To make it as easy as possible to find the right article I’ve decided to produce a little catalogue (catalog) of all the relevant articles, with a little note about the topic each article covers. Some of the articles will link to others in the list, and there are a few items in the list from other blogs. There are also a few items which are the titles of drafts which have been hanging around for the last few years.

Correcting datatypes with minimal downtime

Submitted by connor_mc_d on Thu, 2018-09-27 01:02

Just a quick post here by request of an attendee of the September Office Hours. I had a demo converting the data type of a primary key without losing data and with minimal impact to the availability of the database using DBMS_REDEFINITION. You can see that video here