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

19 Февраль 2015
Earlier
Пн, Мар 23, 2020

I've made casual remarks in the past about how “ANSI”-style SQL introduces extra complications in labelling or identifying query blocks – which means it's harder to hint correctly. This is a note to show how the optimizer first transforms “ANSI” SQL into “Oracle” syntax. I'm going to write a simple [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 20, 2020
В процессе тестирования Oracle 19c попытка сгенерировать CBO trace неожиданно неудачна: , файл трейса обрывается на этапе SPM plan reproduce: — относительно свежей (неопределяемой на сайте поддержки, по крайней мере, для моего аккаунта) ошибкой ORA-07445: exception encountered: core dump [kkopmDumpPlanInfo()+… При этом целевой запрос имеет 4-х летней выдержки SQL Patch [...]
Source: iusoltsev.wordpress.com
Ср, Мар 18, 2020

The Oracle Developer Community forum often sees SQL that is hard to read – sometimes because it's a brutal tangle of subqueries, sometimes because the format it bad, sometimes because the use of table and column aliases is poorly done. One particular case of the last weakness is the code [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 13, 2020

You're probably aware of the “identity” construct that appeared in 12.1 and uses Oracle's sequence mechanism to model the Autonumber or Identity columns that other databases have. A posting from Clay Jackson on the Oracle-L list server suggests that something about their code path has introduced a surprising [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 12, 2020

I've just been prompted to complete and publish a draft I started a few years ago. It's (ultimately) about a feature that appeared in 9i but doesn't seem to show up very often at client sites or as a common solution to performance problems on the various Oracle forums – [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 01, 2020
После формально успешного создания нестандартного индекса по 5 полям — многовато, но так советовал SQL Tuning Advisor!) — для стандартной таблицы EBS: с коллегой Максимом Филатовым наблюдали необычно абсолютное игнорирование этого индекса в трейсе CBO: т.е. по таблице XLA_DISTRIBUTION_LINKS и всем её индексам статистика приводится/рассматривается, кроме нового, с виду вполне [...]
Source: iusoltsev.wordpress.com
Вт, Фев 25, 2020

I've just received an email asking (yet again) a question about counting the number of rows in a table.

We have a large table with a CLOB column, where the CLOB occupies 85% storage space.
when we use select count(*) from , the DBA says that you should not use count(*) as [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 24, 2020

Many years ago (2011) I wrote a note describing how you could attach the Outline Information from one query to the SQL_ID of another query using the official Oracle mechanism of calling dbms_spm.load_plans_from_cursor_cache(). Shortly after publishing that note I drafted a follow-up note with an example demonstrating that even when [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 18, 2020

A quirky little feature of interval partitioning showed up on Twitter today – a parallel insert that would only use a single PX slave process to do the inserting. With 1.1 billion rows and the option for running parallel 32 this this made the loading process rather slower than [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 17, 2020

It is possible to take subquery factoring (common table expressions / CTEs) too far. The most important purpose of factoring is to make a complex query easier to understand – especially if you can identify a messy piece of text that is used in more than one part of the [...]

Source: jonathanlewis.wordpress.com
Пт, Фев 14, 2020

The semantics of comparing character columns of different types can lead to some confusion, so before I get into the main body of this note here's a little test based on a table with one row:

create table t1(c2 char(2), c3 char(3), vc2 varchar2(2), vc3 varchar2(3)); insert into t1 values ('XX','XX','XX','XX'); commit; select count(*) [...]
Source: jonathanlewis.wordpress.com
Вт, Фев 04, 2020

The view v$undostat is a view holding summary information about undo activity that can be used by the automatic undo mechanism to deal with optimising the undo retention time (hence undo space allocation). The view holds one row for every ten minute interval in the last 4 days (96 hours) [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 29, 2020

I hesitate to call something a bug simply because Oracle doesn't do what I thought it would do; but when a trace file says:

“I'm not going to do X because P is not true“

followed a little later by

“I'm going to do Y because P is true“

then I think it's [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 27, 2020

I am seeing “traditional” Oracle SQL syntax being replaced by “ANSI”-style far more frequently than I used to – so I thought I'd just flag up another reminder that you shouldn't be too surprised if you see odd little glitches showing up in ANSI style that don't show up when [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 24, 2020

Here's another anomaly that appears when you mix and match Oracle features. In this case it's “With” subqueries (common table expressions / CTEs) and Active Dataguard (ADG) Standby databases. The problem appeared on the Oracle-l listserver and luckily for the OP another member of the list had seen it [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 23, 2020

These screenshot are from JDevelopers - 12c
1. Click on Window - Resources


2. In the Resources pallet - Click on Import to open the 'Import Catalogs and Connections' window.



3. Now browse to locate the *.rcx file containing connection details.




4. Then click on Import to import [...]
Source: oracleanil.blogspot.ru
Ср, Янв 22, 2020

It's a long time since I wrote a note with the “philosophy” theme, but a recent complaint about Oracle prompted me to suggest the following thought:

“If you write SQL that is technically incorrect it's not Oracle's fault if sometimes the SQL completes without an error.”

Consider the following fragment of code:

drop [...]
Source: jonathanlewis.wordpress.com
Пн, Янв 20, 2020

This is a case study based on a question that appeared on the Oracle Developer Community forum a few days ago.

What I'm aiming to present in this note is the pattern of thinking that you should adopt in cases like this. The final suggestion in this note isn't necessarily [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 17, 2020

Here's a bug that was highlighted a couple of days ago on the Oracle Developer Community forum; it may be particularly worth thinking about if if you haven't yet got up to Oracle 12c as it appeared in an optimizer feature that appeared in 12.2 (and hasn't been completely [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 14, 2020

When I was a child I could get lost for hours in an encyclopedia because I'd be looking for one topic, and something in it would make me want to read another, and another, and …

The same thing happens with MOS (My Oracle Support) – I search for something [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 13, 2020

This is a note I drafted in September 2015 and only rediscovered a couple of days ago while searching for something I was sure I'd written about collections and/or table functions. The intention of collections and table functions is that they should behave like tables when you use them in [...]

Source: jonathanlewis.wordpress.com
Вс, Янв 12, 2020

Regular readers will have noticed that for the last few weeks I've been adding a footnote (now deleted) to any new blog posts asking for donations to the Nepal Youth Foundation (UK), with an offer to match (up to a limit) any donations made by my readers. The [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 10, 2020

The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer's handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 03, 2020

THe problem with finding something new and fiddling with it and checking to see how you can best use it to advantage is that you sometimes manage to “break” it very quickly. In yesterday's blog note I introduced the /*+ push_having_to_gby(@qbname) */ hint and explained why it was a [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 02, 2020

I came across an interesting new hint recently when checking the Outline Data for an execution plan: /*+ push_having_to_gby() */ It's an example of a “small” change designed to reduce CPU usage by reducing the volume of data that passes through the layers of calls that an execution plan [...]

Source: jonathanlewis.wordpress.com
Пн, Дек 30, 2019

This is an observation that came up on the Oracle Developer Forum a couple of days ago, starting life as the fairly common problem:

I have a “select” that runs quickly but when I use in a “create as select” it runs very slowly.

In many cases this simply means [...]

Source: jonathanlewis.wordpress.com
Ср, Дек 25, 2019

A classic example of Oracle's “mix and match” problem showed up on the Oracle Developer Forum a few days ago. Sometimes you see two features that are going to be really helpful in your application – and when you combine them something breaks. In this case it was the [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 20, 2019

This is a note I first drafted about 5 years ago (the date stamp says March 2014) and rediscovered a few days ago when the question came up on a Twitter thread.

How do you purge a single SQL statement from the library cache without having to execute “alter system [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 20, 2019

This is a note I first drafted about 5 years ago (the date stamp says March 2014) and rediscovered a few days ago when the question came up on a Twitter thread.

How do you purge a single SQL statement from the library cache without having to execute “alter system [...]

Source: jonathanlewis.wordpress.com
Ср, Дек 18, 2019

This is a note courtesy of Jack can Zanen on the Oracle-L list server who asked a question about “wait for CPU” and then produced the answer a couple of days later. It's a simple demonstration of how Java in the database can be very deceptive in terms of [...]

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