Search

Top 60 Oracle Blogs

Recent comments

January 2019

Oracle — Table lock modes

Oracle — Table lock modes

Here is a post with a few links to previous blog/article/video about Oracle table lock modes. And remember that in 12cR2 the event 10704 has been replaced by UTS tracing:

Hint Reports

Nigel Bayliss has posted a note about a frequently requested feature that has now appeared in Oracle 19c – a mechanism to help people understand what has happened to their hints.  It’s very easy to use, it’s just another format option to the “display_xxx()” calls in dbms_xplan; so I thought I’d run up a little demonstration (using an example I first generated 18 years and 11 versions ago) to make three points: first, to show the sort of report you get, second to show you that the report may tell you what has happened, but that doesn’t necessarily tell you why it has happened, and third to remind you that you should have stopped using the /*+ ordered */ hint 18 years ago.

I’ve run the following code on livesql:

SYS.STATS_TARGET$

Here is a little note about the SYS.STATS_TARGET$ table used by the automatic statistics gathering job run at maintenance window, or when running it manually with:

exec dbms_auto_task_immediate.gather_optimizer_stats

This table is not documented and has no view on it, so those are only my guesses about what I observed, and comments are welcome. Basically, this table is used by the Auto Stats job to list the tables to process, from one execution to the other.

Note that in 12c the same information is updated into DBA_OPTSTAT_OPERATION_TASKS and visible through DBMS_STATS.REPORT_STATS_OPERATIONS. But I still use STATS_TARGET$ so see in real-time what is currently processed.

Columns description

STATUS

When the Auto Stats job lists the objects to process, they are in state PENDING (STATUS=0).

EXPORT not GATHER with DBMS_STATS

Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited to.

The question was:

“When I gather statistics using DBMS_STATS, can I just create a statistic table and pass that as a parameter to get the results of the gather”

And the answer simply is “No” Smile but let me clear up the confusion.

Announcement: New “Oracle Diagnostics and Performance Tuning” Seminar Now Available !!

It’s been a work in progress for quite some time, with many of my customers asking when will it be completed. Well, I’m very excited to announce that I have finally completed my new 2 day “Oracle Diagnostics and Performance Tuning” seminar and that it’s ready to be presented. I already have a number of […]

My APEX was fine and then it wasn’t

I got a nasty shock this morning when I fired up my local Application Expression installation.

image

It had been working fine and all of a sudden…just dead. I sounded like all of those family members that as I.T practitioners we have to deal with (and that we’re so sceptical of) when they say: “I didn’t change anything…it just stopped!” Smile

In keeping with the treatment of family members, I then adopted the advice that I normally give them first.

You don’t need the PLAN_TABLE table

This post is about the following error you may get when looking at an execution plan after setting the current_schema:

Error: cannot fetch last explain plan from PLAN_TABLE

It is related with old versions and relics from even older versions.

In the old times, PLAN_TABLE was a permanent shared regular table created by utlxplan.sql. Since Oracle 8i which introduced Global Temporary Tables, the PLAN_TABLE public synonym refers to SYS.PLAN_TABLE$ which is a GTT, not shared and emptied at the end of your session.

When I want to tune a query, I usually connect with my DBA user and change my session schema to the application one, so that I can explain or run the user query without having to prefix all tables. But when there is a PLAN_TABLE in the current schema, DBMS_XPLAN.DISPLAY may fail:

Making some more sense of direct path reads during primary key lookups

After having published my first article of 2019 I have received feedback I felt like including. With a nod to @fritshoogland, @ChrisAntognini and @FranckPachot.

In the previous post I showed you output of Tanel Poder’s ashtop.sql as proof that direct path reads can occur even if all you do is look up data by primary key. This script touches v$active_session_history, and I’m not getting tired of mentioning that you need to license the system in scope for Enterprise Edition and the Diagnostics Pack to do so.

Oracle global vs. partition level statistics CBO usage

Global statistics are complex to gather. Gathering on the whole table can be very long and doesn’t ‘scale’ because the duration will increase with the volume. Incremental gathering can be a solution but has its side effects (such as the size of the synopsis). But having stale global statistics can be dangerous. Do you know when the optimizer bases its estimation on global or on partition level statistics? The partition level statistics are used only:

  • when partition pruning iterates on only one single partition
  • when this partition is known at optimization time during the parse phase

This is clearly explained in Jonathan Lewis ‘Cost-Based Oracle Fundamentals’: