Oracle

Jonathan Lewis's picture

NULL predicate

People ask me from time to time if I’m going to write another book on the Cost Based Optimizer – and I think the answer has to be no because the product keeps growing so fast it’s not possible to keep up and because there are always more and more little details that might have been around for years and finally show up when someone asks me a question about some little oddity I’ve never noticed before.

The difficult with the “little oddities” is the amount of time you could spend trying to work out whether or not they matter and if it’s worth writing about them. Here’s a little example to show what I mean – first the data set:

Franck Pachot's picture

Oracle VPD as a safeguard for DML

A new blog post on the Databases at CERN blog about using VPD Row-Level Security (DBMS_RLS) as a safeguard for the privileged users who need to bypass the application and run SQL directly: https://db-blog.web.cern.ch/blog/franck-pachot/2018-12-oracle-vpd-safeguard-dml

Franck Pachot's picture

CPU Capacity planning from OEM metrics

The CPU used by your Oracle Database is expensive because it is the metric used by licensing. The more you can control and know what you need, the more freedom you will have to optimize the costs. With instance caging, available in all editions, you can put a soft limit. This means that:

  • you run on a limited number of threads and after a while, this gives a good idea of what you really need. You can forecast the capacity for a future consolidation.
  • you monitor ‘resmgr: cpu quantum’ and if activity is high you can decide to scale-up immediately, throttle some services, or do some query/design tuning.

In order to set instance caging, you need to define a value for CPU_COUNT according to the past activity. This post is the detail behind the following tweet:

Jonathan Lewis's picture

Extreme Nulls

This note is a variant of a note that I wrote a few months ago about the impact of nulls on column groups. The effect showed up recently on a client site with a little camouflage that confused the issue for a little while, so I thought it would be worth a repeat.  We’ll start with a script to generate some test data:

Franck Pachot's picture

Minimal Oracle installation (and Docker image)

A new blog post on the Databases at CERN blog about some research on the minimal Oracle Database installation: https://db-blog.web.cern.ch/blog/franck-pachot/2018-12-minimal-oracle-1

And a bonus here: the Dockerfile which builds this minimal image. You need to build the Oracle XE image (oracle/database:18.4.0-xe) with the buildfiles provided by Oracle (https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/18.4.0) and this Dockerfile will copy only the necessary:

Jonathan Lewis's picture

Case Study

A recent thread on the ODC database forum highlighted a case where the optimizer was estimating 83,000 for a particular index full scan when the SQL Monitor output for the operation showed that it was returning 11,000,000 rows.

Apart from the minor detail that the OP didn’t specifically ask a question, the information supplied was pretty good. The OP had given us a list of bind variables, with values, and the SQL statement, followed by the text output of the Monitor’ed SQL and, to get the predicate section of the plan, the output from a call to dbms_xplan. This was followed by the DDL for the critical index and a list of the stats for all the columns in the index.

Franck Pachot's picture

See you in OBUG Tech Days Belgium

Antwerp, February 7, 2019 — February 8, 2019

I’ll demo join methods in slow motion, but look at the full Agenda: https://www.techdaysbelgium.be/?page_id=507

And it’s not only about sessions: all speakers are well known in the community for their will to discuss and share knowledge, opinions… and beers.

Registration opened

Tickets! " Techdays Belgium

fritshoogland's picture

OBUG Tech Days Belgium 2019 – Antwerp – 7/8-FEB-2019

Agenda: https://www.techdaysbelgium.be/?page_id=507

Dates: February 7 and 8, 2019

Location: http://cinemacartoons.be in Antwerp, Belgium

More information soon.

For people from the netherlands: this is easy reachable by car or by train! This is a chance to attend a conference and meet up with a lot of well-known speakers in the Oracle database area without too extensive travelling.

Jonathan Lewis's picture

Row Migration

There’s a little detail of row migration that’s been bugging me for a long time – and I’ve finally found a comment on MoS explaining why it happens. Before saying anything, though, else I’m going to give you a little script (that I’ve run on 12.2.0.1 with an 8KB block size in a tablespace using [corrected ASSM]  manual (freelist) space management and system allocated extents) to demonstrate the anomaly.

Franck Pachot's picture

Oracle Index compression for range scan on file names

A new blog post on the Databases at CERN blog about tables storing long file names in a table, with full path, and index range scan on a prefixed pattern: https://db-blog.web.cern.ch/blog/franck-pachot/2018-11-oracle-index-compression-range-scan-file-names

COMPRESS ADVANCED LOW

The 12cR1 advanced index compression does not help here as all values are unique. Only partial prefix is redundant.

COMPRESS ADVANCED HIGH

The advanced algorithm ‘high’ introduced in 12cR2 can reduce better. But there’s no magic. Redundancy should be addressed at design. Full test:

To prevent automated spam submissions leave this field empty.
Syndicate content