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

19 Февраль 2015
Earlier
Пт, Май 20, 2022

It's a long time since I've done a quiz night – but here's something that fooled me (briefly) when it appeared on the Oracle Developers' Forum. Here's a table definition – and I'm not going to make it easy by giving you a “create table” statement, but it's just a [...]

Source: jonathanlewis.wordpress.com
Чт, Май 05, 2022

This note is about a surprising performance difference between the lead() and lag() analytic functions (which turns out to be due to the behaviour of the nth_value() function) when the option to “ignore nulls” is included in their use (jump to conclusion). The detail I'll be writing about was [...]

Source: jonathanlewis.wordpress.com
Ср, Май 04, 2022

Here's a note about a data error generated by using (possibly mis-using) the dbms_redefinition package. The original code to demonstrate the problem comes from a note on the Oracle Developer forum, and was brought to my attention by a tweet from Daniel Stein.

The critical feature of the demo [...]

Source: jonathanlewis.wordpress.com
Ср, Апр 27, 2022
Коллега Сергей Щукин столкнулся: и идентифицировал опцию COMPRESS (BASIC|FOR OLTP|ROW STORE COMPRESS ADVANCED) как определяющую, опции ONLINE и PARALLEL не влияют на ошибку Специалисты поддержки Oracle предложили проверить, как оказалось, действенный workaround: , однако положительный feedback донести не удалось по причине безвременно прерванных коммуникаций и, как следствие, описание ошибки на [...]
Source: iusoltsev.wordpress.com
Ср, Апр 13, 2022

There's a question on the Oracle Forums at the moment about a query that's taking a long time to parse. Even after being reduced to one prebuilt (currently remote) table with two non-correlated outer joins to it the parse time is several hundred seconds. This seems fairly bizarre – I [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 11, 2022

Sometimes wishes come true and in 19c – with fix_control QKSFM_DBMS_STATS_27268249 – one of mine did. The description of this fix (which is enabled by default) is: “use approximate ndv for computing leaf blocks and distinct keys”.

Here's a key item in the output file from running tkprof [...]

Source: jonathanlewis.wordpress.com
Чт, Апр 07, 2022

This is a simple script that I wrote more than 20 years ago (for 8.1.7.4 apparently) to get the session wait event figures from parallel query slaves as they were running. I'm posting it now because there's a question on the Oracle Database Forum where it would be useful as [...]

Source: jonathanlewis.wordpress.com
Чт, Апр 07, 2022

This is a simple query that I wrote more than 20 years ago (for 8.1.7.4 apparently) to get the session activity from parallel query slaves as they were running. I'm posting it now because there's a question on the Oracle Database Forum where it would be useful as a way [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 04, 2022

I don't suppose many people edit and compile PL/SQL code from the SQL*Plus command line any more, but the following reminder about avoiding mistakes is probably valid in other development environments even though it may be enabled through a difference mechanism.

You can set the level of warning that you get [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 28, 2022

In the previous note about a problem dropping virtual columns the “guilty party” that made it impossible to drop any columns was based on a complex data type owned by the MDSYS (Spatial) schema. This note demonstrates the same problem with a very simple example created from scratch in an [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 24, 2022

Here's a problem that appeared recently on the Orace Developer forum showing one of the classic symptons of new features namely that “mix and match” often runs into problems. This example has been a long time coming so “new” is something of a misnomer, but the alternative source of unlucky [...]

Source: jonathanlewis.wordpress.com
Вт, Мар 22, 2022

Here's a little surprise that showed up in the most recent (March 2022) article that I sent to Simpletalk for the series on transformations. I had been using 19c (19.11.0.0) to create and run my little demos but the editor had used 12.2.0.1 to check the examples and questioned [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 21, 2022

In one of my notes about excavating the x$ tables I said that there was a problem “hidden in plain sight” with the dynamic performance view (g)v$fixed_view_definition. If you haven't previously spotted the problem here's what the view looks like when you describe it:

SQL> desc gV$fixed_view_definition Name [...]
Source: jonathanlewis.wordpress.com
Пн, Мар 21, 2022

Here's a script I've used from time to time to help me get some ideas about how to hack through some x$ tables. It's obviously something that can only be run by the SYS schema and it's not something to apply arbitrarily to every x$ table as the side effects [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 20, 2022

This is a list of articles I've written that pick up some detail about using features of the ANSI syntax that are (or were) less commonly used in traditional Oracle SQL.Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

[...]
Source: jonathanlewis.wordpress.com
Пт, Мар 18, 2022

One of the “curiosity” questions that comes up from time to time on the various forums is the one about the cascade of names from synonym to view to object that Oracle uses to provide access to the dynamic performance views. Typically we see the pattern:

v$XXX is a public synonym [...]
Source: jonathanlewis.wordpress.com
Чт, Мар 17, 2022

If you ever feel the need to dig down into the “dynamic performance views (v$)”, and then go a little deeper to get to the “internal structures (x$)” there are 5 objects in the list of x$ that you will eventually want to know about.

x$kqfta – headline information about the [...]
Source: jonathanlewis.wordpress.com
Пт, Мар 11, 2022

This note is an extended answer to a question the Kyle Hailey raised on the oracle-l list server a couple of days ago. Rather than repeat the question immediately, I'll start with a little scripts (to be run by a suitably privileged account – which eventually will have to [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 07, 2022

This is a draft note that's been hanging around a very long time for a final edit and print. I was prompted to rediscover it today when I noticed a question on the Oracle Developers' forum asking about reducing the size of a SYSAUX tablespace that currently included 400GB [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 07, 2022

Here's a quick and dirty script I thought I'd published a very long time ago. It has to be run as SYS. It queries v$sql_hint to produce a list of all the things which (at least in theory) could be used as hints somewhere in the code.

I've ordered by list [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 02, 2022

A question arrived on the MOS Community forum yesterday (needs an account if you want to see the original) that reported a couple of fragments of a CBO trace (10053) file:

----- Current SQL Statement for this session (sql_id=4c85twjpdg8g9) ----- select /*+ 123456 */ count(*) from gl_detail where prepareddatev='2022-01-22 15:00:00' Final query [...]
Source: jonathanlewis.wordpress.com
Пн, Фев 21, 2022

This is a list of all articles I've written to examine claims that Oracle has “ignored a hint”, including articles where other aspects of hinting (such as SQL Plan Baslines) don't work as expected.

Each entry has a date stamp and a short note of the contents. Some of these will [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 21, 2022

For many years I've told people that when you materialize a CTE (common table expression / “with” subquery) the result set will be written to the temporary tablespace using direct path writes and will be read back using cached reads. This stopped being an accurate description in 12c.

There is a [...]

Source: jonathanlewis.wordpress.com
Сб, Фев 19, 2022

This is a list of articles I've written that get a little heavily into technical details and won't necessarily be very helpful to the typical DBA or developer. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

Dumping redo [...]
Source: jonathanlewis.wordpress.com
Сб, Фев 19, 2022

This is a list of all articles I've written about locks (or enqueues). Each entry has a date stamp and a short note of the contents. The articles are grouped in a couple of different categories (including one specifically on deadlocks), but otherwise are generally listed most-recent first.

General Lock Modes [...]
Source: jonathanlewis.wordpress.com
Пт, Фев 18, 2022

Here's a little script that I wrote more than 10 years ago when investigating some undesirable behaviour with indexes. I've just rediscovered it after seeing it mentioned in a comment to an old article that I had been prompted to revisit. This isn't going to help you solve any [...]

Source: jonathanlewis.wordpress.com
Ср, Фев 16, 2022

This is a list of articles I've written about materialized views. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

You may also find some useful ideas in the list of MOS articles (needs an account) in Doc ID 1353040.1: [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 07, 2022

This is a list of all articles I've written about sequences. Each entry has a date stamp and a short note of the contents. The articles are grouped in a couple of different categories (mainly because I hadn't previously created an index for a short series I wrote for Simpletalk), [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 01, 2022

This is a list of all articles I've written that describe some troubleshooting ideas. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so [...]

Source: jonathanlewis.wordpress.com
Сб, Янв 29, 2022

This is a list of all articles I've written that make some comments about things that might be worth remembering when you're thinking about adding some new functionality to a system. Each entry has a date stamp and a short note of the contents. The articles will generally be listed [...]

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