Oracle

Jonathan Lewis's picture

Min/Max costing

A question about the min/max index scan appeared on the Oracle Developer Community forum recently. The query supplied in the thread was a little odd – you might ask why anyone would run it as it stands – and I’ve modified it to make it even stranger to demonstrate a range of details.

I’ll start with a simple data set, not bothering to collect stats because that will be done automatically on create for my versions:

oraclebase's picture

Video : Resource Manager : Runaway Query Management

In today’s video we give a quick demonstration of using Resource Manager to cancel a long running SQL statement.

This video is based on a series of articles from Oracle 8i to the present day. There is an example here.

Although there are aspects of it throughout most of the Resource Manager articles on the website.

Jonathan Lewis's picture

Massive Deletes

One of the recurrent questions on the Oracle Developer Commuity forum is:

What’s the best way to delete millions of rows from a table?

There are an enormous number of relevant details that you need to know before you can give the “right” answer to this question, e.g.

Jonathan Lewis's picture

Recursive WITH upgrade

There’s a notable change in the way the optimizer does cost and cardinality calculations for recursive subquery factoring that may make some of your execution plans change – with a massive impact on performance – as you upgrade to any version of Oracle from 12.2.0.1 onwards. The problem appeared in a question on the Oracle Developer Community forum a little while ago, with a demonstration script to model the issue.

I’ve copied the script – with a little editing – and reproduced the change in execution plan described by the OP. Here’s my copy of the script, with the insert statements that generate the data (all 1,580 of them) removed.

Jonathan Lewis's picture

Execution Plans

This is an example from the Oracle Developer Community of using the output of SQL Monitor to detect a problem with object statistics that resulted in an extremely poor choice of execution plan.

A short time after posting the original statement of the problem the OP identified where he thought the problem was and the general principle of why he thought he had a problem – so I didn’t have to read the entire execution plan to work out a strategy that would be (at least) a step in the right direction of solving the performance problem.

This note, then, is just a summary of the five minute that I spent confirming the OP’s hypothesis and explaining how to work around the problem he had identified. It does, however, give a little lead-in to the comments I made to the OP in order to give a more rounded picture of what his execution plan wass telling us.

oraclebase's picture

When Implicit Date Conversions Attack

https://oracle-base.com/blog/wp-content/uploads/2020/07/shark-5135934_64... 300w" sizes="(max-width: 232px) 85vw, 232px" />

Yesterday, one of the developers was having a problem and emailed to ask what was going on. They sent me a section of code from an old trigger that included some date handling that looked “interesting”.

TO_DATE(SYSDATE,'DD/MON/RRRR')

Some bright spark had decided this was the best way to trim the time component off a date, and unfortunately for us it worked for a very, very long time. Many years in fact.

oraclebase's picture

Video : SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

In today’s video we’ll give a quick demonstration of deploying an APEX application using the SQLcl implementation of Liquibase.

I Know what you’re thinking. Didn’t I do this video two weeks ago? The answer is yes and no. This video is very similar to the Liquibase video I did two weeks ago, but that was using the Liquibase Pro client. This video uses the SQLcl implementation of Liquibase, and more specifically the runOracleScript tag to achieve the same thing.

The video is based on this article, which has an example of deploying an APEX workspace and an APEX application.

Franck Pachot's picture

Oracle ACFS: “du” vs. “df” and “acfsutil info”

By Franck Pachot

.
This is a demo about Oracle ACFS snapshots, and how to understand the used and free space, as displayed by “df”, when there are modifications in the base parent or the snapshot children. The important concept to understand is that, when you take a snapshot, any modification to the child or parent will

oraclebase's picture

20 Years of ORACLE-BASE.com

It was twenty years ago today that the first incarnation of my website was born.

It started life as a few scripts and notes put on the internet so I didn’t have to carry them around on floppy disks or CDs when I was moving around between contracts. I had been working with Oracle technologies for five years before the website was born, but most of the early content was Oracle 8i stuff.

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