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

19 Февраль 2015
Earlier
Чт, Июл 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
Сб, Май 22, 2021

Here's a little note about column counts that I've not mentioned before now in the various articles I've written about the side effects of declaring tables with more than 255 columns. The count is about “real” columns and you should ignore virtual columns – but there are some real columns [...]

Source: jonathanlewis.wordpress.com
Вс, Май 09, 2021

A recent question on the Oracle-L list server asked “Is there a way I can use an SQL_PATCH to add a driving_site() hint to a query?” to which the reply was: “Just do it, it should work.” Unfortunately this produced the response: “I'm on 11.2.0.4, I've tried it, it [...]

Source: jonathanlewis.wordpress.com
Вс, Апр 25, 2021
select title, short_url from v$blog where pubDate>=systimestamp - interval '5' month; with v$blog as ( select title ,to_timestamp_tz(pubDate,'DY, dd mon yyyy hh24:mi:ss TZR') pubDate ,short_url [...]
Source: orasql.org
Вт, Апр 13, 2021

An old question with a little extra twist came up on the Oracle Developer forum recently – someone wanted to export a table into a CSV file but wanted to do a character set conversion at the same time because various “non-printable” characters were getting corrupted.

Looking at the code [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 05, 2021

A recent question on the Oracle Developer forum posed an interesting question on “finding the closest match” to a numeric value. The OP supplied SQL to create two tables, first a set of “valid” values each with an Id, then a set of measures. The requirement was to find, [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 29, 2021
Продолжая про event, при разборе неприятной проблемы ORA-600 [532] leads to ORA-700 [ksepop:1 ksepop recursion ] аналитик поддержки с оговорками* рекомендовал в целях диагностики установить: , что (после применения параметра/рестарта) приводит и интересному эффекту в части использования PL/SQL, %rowtype и EXECUTE IMMEDIATE с IN OUT параметрами (**): При смене порядка [...]
Source: iusoltsev.wordpress.com
Пн, Мар 29, 2021
В начале месяца Алексей Фролов (Спортмастер) на семинаре RuOUG рассказал о непростом пути обновления на версии 19c В дополнение к теме имеется любопытный опыт работы с Bug 31888148 — ORA-1555 is not reported in alert log after RU 19.4.0.0.0 (Doc ID 31888148.8), к счастью, уже поправленном в современных версиях: В [...]
Source: iusoltsev.wordpress.com
Чт, Мар 25, 2021

From time to time I see people on the public Oracle forums asking whether they should adjust one or other of the resource-related parameter – and it's often the hidden or derived parameters that get targetted for this type of request. For example I came across a request fairly recently [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 12, 2021

A request for help came up some time ago on ODC reporting a query that was hanging when it included a sequence.nextval. In fact the intial “query” was an “insert / select” with a select that was a join of two remote tables. Making the fairly automatic assumption that [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 08, 2021

This note is a followup to a post from a few years back (originally dating further back to 2012) where I described an inconsistency that appeared when join elimination and deferrable constraints collided. The bug resurfacted recently in a new guise in a question on the Oracle Developer [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 04, 2021

One of the less well-known hints is the hint /*+ use_nl_with_index() */ (link to 19c reference manual) which appeared in the 10g timeline, where the manuals supply the following description:

The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 16, 2021

There's a thread on the Oracle Database Forum at present where someone has supplied a script to create some data that's guaranteed to reproduce wrong results (provided your system stats and optimizer parameters are at their default values). They've even supplied a link to the [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 01, 2021

Here's a quirky little detail – probably totally irrelevant to virtually everyone – that came up in a question on the Oracle Developer Forum a couple of days ago. It concerns the way Oracle stores and records default values for columns, and it also does a hat-tip to the [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 01, 2021

I've hardly ever touched the pivot/unpivot feature in SQL, but a recent comment by Jason Bucata on a note I'd written about Java names and the effects of newer versions of Oracle allowing longer object and column names prompted me to look at a script I wrote several [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 26, 2021

At the end of the previous post on index hints I mentioned that I had been prompted to complete a draft from a few years back because I'd been sent an email by Kaley Crum showing the optimizer ignoring an index_rs_asc() hint in a very simple query. Here, [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 25, 2021

I've lost count of the number of times I've reminded people that hinting (correctly) is hard. Even the humble /*+ index() */ hint and its close relatives are open to misunderstanding and accidental misuse, leading to complaints that “Oracle is ignoring my hint”.

Strange though it may seem, I'm still [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 20, 2021

This is a list of possible explanations of errors that you might see in the Hint Report section of an execution plan. It's just a list of the strings extracted from a chunk of the 19.3 executable around the area where I found something I knew could be reported, [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 20, 2021

A little case study based on an example just in on the Oracle-L list server. This was supplied with a complete, working, test case that was small enough to understand and explain very quickly.

The user created a table, and used calls to dbms_stats to fake some statistics into place. [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 18, 2021

Here's an anomaly that appeared in a question on the ODC recently about tweaking the output of dbms_metadata.get_ddl(), As the title suggests, though, this posting isn't about the dbms_metadata package it's about supplemental logging and something that should not to be possible and may have unfortunate side effects.

We start with [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 14, 2021

Reading Richard Foote's latest blog note about automatic indexing and “non-equality” predicates I was struck by a whimsical thought about how the optimizer handles “between” predicates. (And at the same time I had to worry about the whimsical way that WordPress treats “greater than” and “less than” symbols.)

It's probably [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 13, 2021

This is a note I drafted in 2018 but never got around to publishing. It's an odd quirk of behaviour that I discovered in 12.2.0.1 but I've just checked and it's still present in 19.3.0.0.

Here's a funny little thing that I found while checking some notes I had on adding [...]

Source: jonathanlewis.wordpress.com
Комментирование отключено.