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

19 Февраль 2015
Earlier
Пт, Дек 02, 2016

I've written notes in the past about the improvements 12c introduces for histograms – particularly the frequency and top-N histograms which can be generated “free of charge” while the basic “approximate NDV” scans are taking place to gather stats. Gathering histograms in 12c is much safer than it [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 30, 2016
После switchover-а на аналогичное железо в разы выросло время ожидания log file sync: — при этом в качестве важного промежуточного блокера в вышеприведённой цепочке по частоте и продолжительности неожиданно проявилось LGWR wait for redo copy (*), что было отчётливо заметно в цепочке ожиданий, начиная с LGWR any worker group: Ожидания [...]
Source: iusoltsev.wordpress.com
Ср, Ноя 30, 2016
Разбираясь с использованием ожидания/активности db file scattered read при разных операциях планов выполнения, можно видеть: (*) Scattered чтения без SQL_PLAN_OPERATION большей частью относятся к фазе Soft parse (***), определяемой здесь как промежуток, когда PLAN_HASH_VALUE уже сформирован (PHV exists), но выполнение запроса ещё не началось (SQL_EXEC_ID is null): (**) SAMPLE операции [...]
Source: iusoltsev.wordpress.com
Вт, Ноя 29, 2016

In the previous post I threw out a couple of options for addressing the requirement to transfer data from one table to another (“cut and paste” rather than just “copy”) without running into odd inconsistency errors. This triggered of a wonderful comment trail of alternatives based on how large the [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 23, 2016
Select Conditionally

Let's say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row's priority.

A typical example is selecting one contact number for a customer, based on contact types.

The possible contact [...]

Source: www.oratable.com
Вт, Ноя 22, 2016

Many of the questions that appear on OTN are deceptively simple until you start thinking carefully about the implications; one such showed up a little while ago:

What i want to do is to delete rows from table where it matches condition upper(CATEGORY_DESCRIPTION) like ‘%BOOK%'.

At the same time i want [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 16, 2016
ORA-12560: TNS:protocol adapter error

Sometimes you try to login to your Oracle XE on your home computer, and get this curt blocking response:

Here's the top reason why it might happen and how to fix it.


Database is not up

The first thing to check: is the Oracle service running? If you are seeing ORA-12560:TNS protocol [...]

Source: www.oratable.com
Пн, Ноя 14, 2016

Here's an interesting (and potentially very useful) observation from an OTN database forum thread that appeared at the end of last week. It concerns the problems of pulling data from remote systems, and I'll start by building some data:

rem rem Script: [...]
Source: jonathanlewis.wordpress.com
Пн, Ноя 14, 2016
На прошлой неделе появилась полная версия документации Oracle Database 12c Release 2 , следом Mike Dietrich публикует триптих: New SPFILE parameters in Oracle Database 12.2.0.1 Obsolete SPFILE Parameters 12.2.0.1 Deprecated Parameters in Oracle Database 12.2.0.1 , и Российский Oracle, по слухам, в конце месяца готовит целенаправленный семинар с более подробным [...]
Source: iusoltsev.wordpress.com
Пн, Ноя 14, 2016
Активизировавшаяся конкуренция за объекты Library Cache в форме ожиданий library cache lock, и в меньшей степени kksfbc child completion, library cache: mutex X: оказалось вызвана PX-slave процессами (P…) одного параллельно выполнявшимся запроса dx0fckp3gckku в стадии подготовки (IN_PARSE) адаптивного плана (SQL_ADAPTIVE_PLAN_RESOLVED = 1): , конкурировавшими за конкретный объект Library Cache типа [...]
Source: iusoltsev.wordpress.com
Чт, Ноя 10, 2016
На страничке Scripts — [почти полное] описание используемого набора скриптов (своих и позаимствованных), с практическими примерами применения и ссылками на zip и GitHub Хорошо работают в Command Window PL/SQL Developer, эпизодически проверяю в SQL*Plus и SQL Developer — там возможны проблемы оформления/форматирования, замечания и предложения пишите в комментариях к странице [...]
Source: iusoltsev.wordpress.com
Чт, Ноя 10, 2016
Источник Oracle Database 12c Release 2 (12.2) New Features


Добавлена PL/SQL pragma для пометки устаревших программных единиц.


Не обновленные материализованные представления могут быть использованы в query rewrite для получения корректного результата используя materialized view logs.


К событиям обновления материализванного представления ON COMMIT и ON DEMAND добавлено событие ON STATEMENT.


Добавлены [...]
Source: atyshenko.blogspot.com
Пн, Ноя 07, 2016

A current question on the OTN database forum asks: “What's the difference between object and tablespace reorganization?” Here's an analogy to address the question.

I have three crates of Guiness in the boot (trunk) of my car, one crate has 4 bottles left, one has 7 bottles left and one [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 07, 2016

There's a current thread on the OTN database forum showing an execution plan with a slightly unusual feature. It looks like this:

----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation [...]
Source: jonathanlewis.wordpress.com
Пн, Окт 31, 2016

… and thanks for all the fish.

Today I removed myself from the OTN ACED program. This isn't a reflection on the anything to do with the ACE program – quite the reverse, in fact – it's because they're introducing steps to ensure that the ACE Directors can justify their titles. [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 24, 2016

Happy anniversary to me!

On this day 10 years ago I published the first article in my blog. It was about the parameter optimizer_index_cost_adj (hence OICA), a parameter that has been a source of many performance problems and baffled DBAs over the years and, if you read my first [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 20, 2016

I recently tweeted about a comment I'd picked up at the Trivadis performance days regarding tablescans and performance.

“If you can write your SQL in conjunctive normal form it can help the optimizer to offload more predicates”

Inevitably someone asked me if I had an example to demonstrate this – I [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 18, 2016
В процессе разбора очередного случая с неправильными рез-том в Oracle 12.1.0.2.*, на этот раз при параллельном выполнении запроса, и предварительно проверив варианты, перечисленные в How to Narrow Down Wrong Results Issues from Parallel Execution (Doc ID 1340246.1), по предложению представителя поддержки, проверил относительно новый функционал — DBMS_SQLDIAG с ключом PROBLEM_TYPE_WRONG_RESULTS [...]
Source: iusoltsev.wordpress.com
Пн, Окт 17, 2016

There are quite a lot of systems around the world that aren't using the AWR (automatic workload repository) and ASH (active session history) tools to help them with trouble shooting because of the licensing requirement – so I'm still finding plenty of sites that are using Statspack and I recently [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 11, 2016

To mark the OTN Appreciation Day I'd like to offer this thought:

“Our favourite feature is execution plans … execution plans and rowsource execution statistics … rowsource execution statistics and execution plans … our two favourite features and rowsource execution stats and execution plans … and ruthless use of SQL [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 10, 2016

It should be fairly well known by now that when you enable the 12c InMemory (Columnar Store) option (and set the inmemory_size) your SQL may take advantage of a new optimizer transformation know as the Vector Transformation, including Vector Aggregation. You may be a little surprised to learn, though, [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 07, 2016
— дважды встречена на ADG Standby при выполнении запросов с использованием TEMP TABLE TRANSFORMATION после применения обновлений Database Patch Set Update : 12.1.0.2.160419 или Database Bundle Patch: 12.1.0.2.160719 (в виде [kteclck()+586]) Workaround в виде "_with_subquery = inline* был найден самостоятельно и быстро, а по ре-там SR, заведённого / доведённого Сергеем [...]
Source: iusoltsev.wordpress.com
Чт, Окт 06, 2016

My old website (www.jlcomp.demon.co.uk) will be disappearing in a couple of weeks – but there are a couple of timeless articles on it that are worth saving and although the popularity of this one has probably been surpassed by Tanel Poder's Snapper script, or other offerings by Tom Kyte [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 03, 2016

My old website (www.jlcomp.demon.co.uk) will be disappearing in a couple of weeks – but there are a couple of timeless articles on it that are worth saving and a method for soaking up all the CPU on your system with a simple SQL statement against a small data set [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 29, 2016

In the right circumstances Index Organized Tables (IOTs) give us tremendous benefits – provided you use them in the ideal fashion. Like so many features in Oracle, though, you often have to compromise between the benefit you really need and the cost of the side effect that a feature produces.

The [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 26, 2016

One of the little myths of Oracle appeared on the Oracle-L list server a few days ago – the one that says: “you don't need a histogram on a single column unique/primary key”.

Not only can a histogram be helpful on a column that's declared to hold unique values, the optimizer [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 22, 2016

Very simple example:

Test case set feed on; -- simple table: create table xt_curr1 as select level a,level b from dual connect by level create table xt_curr1 as select level a,level b from dual connect by level -- same table but with empty trigger: SQL> create table xt_curr2 as select level a,level b from dual [...]
Source: orasql.org
Вт, Сен 20, 2016

This note comes to you prompted by “Noons” in a recent twitter exchange

In response to a complaint by Lukas Eder about having to educate people in 2016 that there is no (performance) difference between count(*) and count(1), Nuno asked me to blog about my claim that [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 16, 2016

Yesterday I posted a note about querying dba_optstat_operations to get a quick report of how long calls to dbms_stats had been taking but said I had another script that helped to fill some of the gaps it left. One of my readers points out fairly promptely that 12c enhances [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 15, 2016

I don't really remember how long it's been since Oracle created an automatic log of how long a call to the dbms_stats package took, though it was probably some time in the 10g time-line. It wasn't until it had been around for several years, though before I wrote little script [...]

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