Просторы интернета

19 Февраль 2015
Earlier
Пт, Окт 15, 2021

A question has just appeared on a note I wrote in 2012 about the incorrect use of the use_nl() hint in some sys-recursive SQL, linking forward to an explanation I wrote in 2017 of the use_nl() hint – particularly the interpretation of the form use_nl(a,b), which does not [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 11, 2021

This is a little case study of adaptive optimisation in Oracle 19c with a surprising side-effect showing up when the optimizer gave the execution engine the option to “do the right thing” and the execution engine took it – except the “right thing” turned out to be a wrong thing.

We [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 07, 2021

This note is one I drafted three years ago, based on a question from the Oracle-L. It doesn't directly address that question because at the time I was unable to create a data set that reproduced the problem' but it did highlight a detail that's worth mentioning, so I've [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 07, 2021

I've rediscovered this list of articles by Stefan Koehler on my laptop; it has a bias towards SAP users, and it's several years old judging by the dates so some details will have changed, but there still seem to be plenty of people using 12c (and SAP) so I thought [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 24, 2021
Things to Consider to Avoid Database Performance Problems on 19c (Doc ID 2773012.1) Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1) Things to Consider to Avoid Prominent Wrong Result Problems on 19C Proactively (Doc ID 2606585.1) Things to Consider to Avoid SQL Plan Management (SPM) [...]
Source: iusoltsev.wordpress.com
Пн, Сен 20, 2021

This is a note I drafted in March 2016, starting with a comment that it had been about that time the previous year that I had written:

I've just responded to the call for items for the “IOUG Quick Tips” booklet for 2015 – so it's probably about time to [...]

Source: jonathanlewis.wordpress.com
Вс, Сен 12, 2021
Image

One of the easiest ways is to use diagnostic events:

alter session set events 'sql_trace {callstack: fname xctend} errorstack(1)'; [...]
Source: orasql.org
Вт, Сен 07, 2021

You may know that some applications generate queries with bind variables' names like :1 or :”1″, and neither SQL*Plus nor SQLCl support such variables:

SQLPlus:

SQL> var 1 number; SP2-0553: Illegal variable name "1".

SQLCL:

SQL> var 1 number; ILLEGAL Variable Name "1"

So we can't run such queries as-is, but, obviously, we can wrap them [...]

Source: orasql.org
Вс, Сен 05, 2021

This little observation came from running a couple of tests while looking at a problem on OTN – hence the odd bit of PL/SQL.

declare cursor c1 is select * [...]
Source: jonathanlewis.wordpress.com
Пт, Сен 03, 2021

It's been such a long time since Oracle deprecated the /*+ ordered */ hint that I can't remember when it happened. The hint you should be using is the /*+ leading(…) */ hint which initially – maybe some time in 9i – would only allow you to specify the first [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 27, 2021
Наблюдали с коллегами красивое: при "мягком (без рестарта инстансов) switchover с использованием dgmgrl большая часть планов выполнения, во-первых, изменились не в лучшую сторону по сравнению со стабильными и, во-вторых, планы стали бесценными:) dbms_sqldiag.dump_trace благодаря добавленному в свежеобновлённую 19.10 улучшению Bug 31130156 Event 10053 Trace Improvement to Dump Details When and [...]
Source: iusoltsev.wordpress.com
Ср, Авг 25, 2021

Following a question (very similar to one I had been asking myself) that appeared on twitter in response to my original posting on the new qbregistry format option in calls to dbms_xplan, I've drafted a note of how I interpret the execution plan so that I can [...]

Source: jonathanlewis.wordpress.com
Вт, Авг 24, 2021

If you look at the “Outline Information” from an execution plan it shows you a list of hints that will (in theory, at least) recreate the execution plan and it's this information that gets stored as the “injection” part of an SQL Plan Baseline. Unfortunately the hints won't necessarily allow [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 23, 2021

Here's an example that appeared on the Oracle Developer Community forum about a year ago that prompted me to do a little investigative work. The question involved a distributed query that was “misbehaving” – the interesting points were the appearance of the /*+ rule */ and /*+ driving_site() */ [...]

Source: jonathanlewis.wordpress.com
Сб, Авг 21, 2021

Searching my blog recently for some details about a temporary space problem I came across a draft I'd written a few years ago about LOBs in global temporary tables. It was a summary of an exchange from OTN that did a good job of capturing my general air of [...]

Source: jonathanlewis.wordpress.com
Чт, Авг 12, 2021

I know 2 “special” exceptions that can't be processed in exception handler:

“ORA-01013: user requested cancel of current operation” “ORA-03113: end-of-file on communication channel”

Tanel Poder described the first one (ORA-01013) in details here: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ where Tanel shows that this error is based on SIGURG signal (kill -URG):

-- [...]
Source: orasql.org
Пн, Авг 09, 2021

I made a few comments in the past about setting “table preferences” for stats collection – most significantlyj the table_cache_blocks preference that affects the calculation of the clustering_factor of all the indexes on the table, the incremental preference for dictating the strategy used for dealing with partitioned tables, and the [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 06, 2021

Connor McDonald has just published a blog note about a tweak to sequences that appeared in recent versions of Oracle (19.10 – see tweet from Timur Akhmadeev).

To address the problems caused by people leaving the sequence cache size at the default of 20 (leading to contention on [...]

Source: jonathanlewis.wordpress.com
Чт, Авг 05, 2021

There are some questions about performance issues for which there is no easy answer, and sometimes the best you can do is try to generate an approximate answer then examine the results to eliminate the innocent.

Three such problems – essentially similar – appeared recently on the Oracle Developer Forum, and [...]

Source: jonathanlewis.wordpress.com
Чт, Авг 05, 2021

When defining memory usage to an Oracle instance you can specify an “SGA target” and a separate “PGA target”; alternatively you could specify a single “Memory target”. I've not seen many people using the second option and there are reasons why it's not a good idea – like the side [...]

Source: jonathanlewis.wordpress.com
Чт, Июл 22, 2021

A question came up recently on the Oracle Developer forum that tempted me into writing a short note about SQL Macro functions – a feature that was touted for 20c (or maybe 21c) but which has been back-ported to the most recent releases of 19c. Specifically I set [...]

Source: jonathanlewis.wordpress.com
Вт, Июл 20, 2021

A surprising amount of the work I do (or used to do) revolves around numbers; and once I'm outside the realm of the optimizer (i.e. getting away from simple arithmetic), one of the bits of playing with numbers that I do most often is conversion – usually decimal to hexadecimal, [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 12, 2021

How do you explain the apparent inconsistency between the two outputs from this tiny fragment of an SQL*plus script (last tested 19.11.0.0):

describe t1 create table t2 as select * from t1; describe t2

The results of the two describe commands are as follows (cut-n-paste, with no editing, including the feedback from the [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 09, 2021

Trying to find out why a plan had changed in the upgrade from 11g to 19c I came across this cunning little tweak that must have appeared in the 19c timeline. I'll start with a simple query, then the execution plans (autotrace traceonly) from 19.11.0.0 – first with the parameter [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 05, 2021

In a recent thread on the Oracle Developer Forum a user was seeing a significant increase in time spent waiting for row locks after the number of executions of a particular “select for update” had increased from a couple of hundred per hour to a thousand per hour.

It [...]

Source: jonathanlewis.wordpress.com
Чт, Июн 17, 2021
С квалифицированной помощью Алексея Спиричева по проблеме Instance restarts with ORA-07445 [kjcipctxfreereqlist] получили рекомендацию по установке Bug 31985738 — ORA-600:internal error code, arguments: [kjci_complete:invalid_req] (Doc ID 31985738.8) На 3-й неделе тестирования создаётся впечатление, что патч кроме указанной решает набор проблем типов KJSC (Kernel lock management RAC global Stats?) и KJCI [...]
Source: iusoltsev.wordpress.com
Ср, Июн 16, 2021

This is a note I drafted a little over 5 years ago in response to a question on the Oracle developer forum about turning a simple heap table into a list partitioned table; but never got around to publishing it and now it's not going to be of much [...]

Source: jonathanlewis.wordpress.com
Вт, Июн 01, 2021

This is an other detail to add to the note I wrote recently about the effects of adding a sequence number to a remote select when moving data from one database to another. It probably shouldn't have much impact on performance unless it runs into code that does a lot [...]

Source: jonathanlewis.wordpress.com
Сб, Май 29, 2021

I started drafting this note 3 years ago and never got round to finishing it, but I rediscovered it today while looking for something I had written about the “alter table … modify” command options that you can use to convert a non-partitioned table into a partitioned table, or [...]

Source: jonathanlewis.wordpress.com
Сб, Май 29, 2021

Got an interesting question today in RuOUG:

Some very simple PL/SQL procedures usually are completed within ~50ms, but sometimes sporadically longer than a second. For example, the easiest one from these procedures:

create or replace PROCEDURE XXXX ( P_ORG_NUM IN number, p_result OUT varchar2, p_seq OUT number ) [...]
Source: orasql.org
Комментирование отключено.