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

19 Февраль 2015
Чт, Мар 30, 2017

The following question came up on the OTN database forum recently:

We have below table with columns,

Table T1 Columns: ----------- Col_1, Col_2, Col_3, Col_4, Col_5, Col_6, Col_7, Col_8, Col_9, Col_10, Col_11, Col_12, Col_13, Col_14, Col_15

on which below indexes are created.

XXTEST_Col_1 Col_1 XXTEST_Col_2 Col_2 XXTEST_Col_3 Col_3 XXTEST_Col_5 [...]
Source: jonathanlewis.wordpress.com
Вт, Мар 28, 2017
Interval Range Partitioning

Oracle 11G brought with it a beautiful new twist to range partitioning – the ability to create partitions on-the-fly rather than having to pre-create all partitions.

Hello interval partitioning.

Before Oracle 11G, DBAs would have to define all the partitions on a partitoned table and ensure there were no "cracks" between [...]

Source: www.oratable.com
Пн, Мар 27, 2017

I've waxed lyrical in the past about creating suitable column group statistics whenever you drop an index because even when the optimizer doesn't use an index in its execution path it might have used the number of distinct keys of the index (user_indexes.distinct_keys) in its estimates of cardinality.

I've also highlighted [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 23, 2017

A question came up on the OTN database forum a little while ago about a very simple query that was taking different execution paths on two databases with the same table and index definitions and similar data. In one database the plan used the “index full scan (min/max)” operation [...]

Source: jonathanlewis.wordpress.com
Вт, Мар 21, 2017

One of the difficulties with trouble-shooting is that's it very easy to overlook, or forget to go hunting for, the little details that turn a puzzle into a simple problem. Here's an example showing how you can read a bit of an AWR report and think you've found an unpleasant [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 10, 2017

The following is a straight, continuous, untouched, cut-n-paste from an SQL*Plus session on How come the update doesn't execute in parallel – noting that parallel DML has been enabled and the tablescan to identify rows to be updated does execute in parallel ?

SQL> desc t1 Name [...]
Source: jonathanlewis.wordpress.com
Чт, Мар 09, 2017

A question has just appeared on OTN describing a problem where code that works in 11g doesn't work in 12c (exact versions not specified). The code in question is a C-based wrapper for some SQL, and the problem is a buffer overflow problem. The query supplied is as follows:

select [...]
Source: jonathanlewis.wordpress.com
Вт, Мар 07, 2017

A recent posting on the OTN database forum described a problem with an insert (as select) statement that sometimes ran extremely slowly: nothing interesting yet, there could be plenty of boring reasons for that to happen. The same SQL statement (by SQL_ID) might take 6 hours to insert 300K [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 01, 2017

Here's a question that came to mind while reading a recent question on the OTN database forum. It's a question to which I don't know the answer and, at present, I don't really want to bother modelling at present – although if I were on a customer site and [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 28, 2017

The hoary old question about lower cost queries running faster or slower that higher cost queries has appeared once again on the OTN database forum. It's one I've addressed numerous times in the past – including on this blog – but the Internet being what it is the [...]

Source: jonathanlewis.wordpress.com
Ср, Фев 22, 2017
The plan_baseline hintset is just the list of hints SPM tried to use to reproduce the plan В ТОПе Library Cache периодически появлялся непривычный объект: необычайно крупного размера SHARABLE_MEM > 600MB с цифровым наименованием, под коим числилось 2 объекта: — судя по наименованям типов, относящихся к SQL Management Base, где [...]
Source: iusoltsev.wordpress.com
Пн, Фев 20, 2017

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database [...]

Source: orasql.org
Чт, Фев 16, 2017

Here's one of those little improvements in 12c (including 12.1) that will probably end up being described as “little known features” in about 3 years time. Arguably it's one of those little things that no-one should care about because it's not the sort of thing you should do on a [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 13, 2017

One of the optimizer enhancements that appeared in 12.2 for SQL is the “band join”. that makes certain types of merge join much more efficient. Consider the following query (I'll supply the SQL to create the demonstration at the end of the posting) which joins two [...]

Source: jonathanlewis.wordpress.com
Вс, Фев 12, 2017

You may know about Intra-block row chaining which may occur when the number of columns in a table are more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th [...]

Source: orasql.org
Пт, Фев 10, 2017

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.

Here's a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Case [...]
Source: www.oratable.com
Чт, Фев 09, 2017

There's a thread running on OTN at present about deleting huge volumes of duplicated data from a table (to reduce it from 1.1 billion to about 22 million rows). The thread isn't what I'm going to talk about, though, other than quoting some numbers from it to explain what [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 07, 2017
Grant Select on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error:

SQL> select version 2 from v$instance; from v$instance * ERROR at line 2: ORA-00942: table or view does not exist

The error suggests that the non-admin user does not have the SELECT privilege [...]

Source: www.oratable.com
Чт, Фев 02, 2017

This is a note I wrote a couple of years ago, but never published. Given the way it's written I think it may have been the outline notes for a presentation that I was thinking about rather than an attempt to write a little essay. Since it covers a number [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 31, 2017
Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version.

Here's a rundown of ways to identify the Oracle database version using SQL and PL/SQL.


V$VERSION displays version numbers of core library components [...]

Source: www.oratable.com
Пн, Янв 30, 2017

I've written a couple of articles in the past about the problems of ASSM spending a lot of time trying to find blocks with usable free space. Without doing a bit of rocket science with some x$ objects, or O/S tracing for the relevant calls, or enabling a couple of [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 30, 2017
В процессе подготовки семинара, столкнулся с любопытным случаем, для адаптивного плана выполнения: *) в условиях реального отсутствия какого-либо Adaptive Bitmap Pruning! Неадаптивный план: Наибольший диссонанс вызвал вывод скрипта отличий секций Notes и Outline Data из OTHER_XML вышеприведённых планов выполнения: — точнее даже, несовпадение хинтов в Outline для планов с совпадающими [...]
Source: iusoltsev.wordpress.com
Вс, Янв 29, 2017
Коллега Леонид Борчук в процессе практического применения скрипта sql_profile_from_sql.sql обнаружил ошибку: и указал на ограничение: — т.е. нашёл-таки запрос с хинтом длиною более 500 символов! — для запросов OEBS нет пределов:) А, поскольку, в мировом масштабе, эта проблема уже была разрешена Carlos Sierra — Running "coe_xfr_sql_profile.sql Script (Shipped with SQLT) [...]
Source: iusoltsev.wordpress.com
Пт, Янв 27, 2017

I don't know how I missed it but Randolf Geist has been doing writing a series of posts on the performance of Oracle's DBaaS offering, using a series of long-running tests to capture not only raw performance figures but also an indication of consistency. You can find all of these [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 26, 2017

I wrote a short series a little while ago about some of the nasty things that can happen (and can't really be avoided) with Basicfile LOBs and recently realised that it needed a directory entry so that I didn't have to supply 6 URLs if I wanted to point someone [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 24, 2017
Access Control Lists in Oracle

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade. The code failed with the error:

declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at [...]
Source: www.oratable.com
Ср, Янв 11, 2017
UNPIVOT: Transpose Columns to Rows

Sometimes we need to transpose columns as rows in SQL. Oracle 11G onwards, this requirement can be easily implemented using the UNPIVOT clause.

A typical scenario:

Customer data is validated against a set of audit checks and the validation results are stored against the Customer Id as Y/N flags. The checks [...]

Source: www.oratable.com
Ср, Дек 14, 2016
Для демонстрации/проверки многообразия планов у меня есть простой скрипт SQL_PLAN_HASHS.SQL, вытаскивающий хеши планов из AWR и/или SqlArea по SQL_ID [и, опционально, по FULL_PLAN_HASH_VALUE и/или PLAN_HASH_VALUE], и один планов запросов SQL_ID=fkw6hb5mrw02t и PHV=3887377781 а течение последних нескольких дней показывал следующую примечательную картину: — в SqlArea (SRC=SQL) и в DBA_HIST_SQL_PLAN (SRC=AWR) этот [...]
Source: iusoltsev.wordpress.com
Пн, Дек 12, 2016

Внутри потоки мусора, которые можно и не смотреть.
Данная заметка специально создается для нищебродов, которые воруют чужие материалы.


1. οτεμψ "χιξσμου ι εηο οβιτατεμι

ΟΛΞΑ [...]

Source: orablog.ru
Ср, Ноя 30, 2016
После switchover-а на аналогичное железо в разы выросло время ожидания log file sync: — при этом в качестве важного промежуточного блокера в вышеприведённой цепочке по частоте и продолжительности неожиданно проявилось LGWR wait for redo copy (*), что было отчётливо заметно в цепочке ожиданий, начиная с LGWR any worker group: Ожидания [...]
Source: iusoltsev.wordpress.com
Комментирование отключено.