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

19 Февраль 2015
Earlier
Вт, Апр 10, 2018

No-one should be using exp/imp to export and import data any more, they should be using the datapump equivalents expdp/impdp – but if you're on an older (pre-12c) version of Oracle and still using exp/imp to do things like moving tables with their production statistics over to test systems then [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 09, 2018

Here's a little-known feature that has been around since at least Oracle 10, though I don't think I had ever seen it in the wild until today when someone reported on the ODC (OTN) database forum that they had a problem getting repeatable results. It's always possible, of [...]

Source: jonathanlewis.wordpress.com
Пт, Апр 06, 2018

I've mentioned the SQL Monitor report from time to time as a very useful way of reviewing execution plans – the feature is automatically enabled by parallel execution and by queries that are expected to take more than a few seconds to complete, and the inherent overheads of monitoring are [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 28, 2018

Every now and again I have to check what a particular lock (or enqueue) type is for and what the associated parameter values represent. This often means I have to think about the names of a couple of views and a collection of columns – then create a few column [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 25, 2018
При выполнении ALTER TABLE DROP CONSTRAINT случился TM — library cache deadlock распределённого (RAC) вида, отразившийся в ASH следующим образом: где: 1) стартовые условия (session_id,session_serial#) in ((4460,16750)) относятся к сессии, выполняющей DDL sql_id='7kpzqksdg4wu8′ и ожидающей TM-блокировки на целевой пользовательской таблице MY.PAYMENT_Z, при этом столбец DATA_OBJECT_P1RAW отражает название ASH.CURRENT_OBJ#, либо приведённое [...]
Source: iusoltsev.wordpress.com
Ср, Мар 21, 2018
По причине ошибки ORA-00600: Internal Error Code, Arguments: [qmxtcsxmlt:xmltype] (Doc ID 2079499.1), характерной для использования функции XMLAgg в бд состояния MOUNTED (standby or non-standby), переписал скрипт ASH_IO_WAITS.SQL для поиска наиболее активных в WAIT_CLASS-ах ‘User I/O‘ и ‘System I/O‘ процессов/запросов на STANDBY и прочих MOUNTED системах Например, при замедлившемся накате standby [...]
Source: iusoltsev.wordpress.com
Пн, Мар 19, 2018

The partitioning option “partition by reference” is a very convenient option which keeps acquiring more cute little features, such as cascading truncates and cascading splits, as time passes – but what does it cost and would you use it if you don't really need to.

When reference partitioning came into existence [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 09, 2018
Всплеск нагрузки на IO подсистему: сопровождался, по нашим с Дмитрием Якубеней наблюдениям, дисбалансом в отчёте AWR — совершенно незначительные цифры пользовательской нагрузки (*): никак не коррелировали с Total (MB) нагрузкой AWR IO Profile (**): , основная часть которой была сосредоточена в районе Redo Read (***) Подходящее ожидание log file sequential [...]
Source: iusoltsev.wordpress.com
Сб, Фев 24, 2018
В процессе тестирования с Максимом Филатовым Patch 20933264: OUT-OF-PLACE COMPLETE REFRESH NOT USING DIRECT LOAD IN POPULATING OUTSIDE TABLE наблюдали следующее воспроизводимое поведение: После чего SYS.SNAP_REFOP$ содержит три операции: — первая из которых (INSERT INTO "SCOTT.EMP_SNAPSHOT2″) кажется не очень подходящей для исправленной патчем опции out_of_place => TRUE Трейс 10046 показывает, [...]
Source: iusoltsev.wordpress.com
Чт, Фев 22, 2018
— живой и интересный! Несколько иллюстраций к рассказанному Александром: Список используемых/наблюдаемых в системе опций подсказки RESULT_CACHE: Franck Pachot. RESULT_CACHE hint expiration options — тесты недокументированных опций: SNAPSHOT — гарантированное /независимое от DML/ время хранения/валидности Result Cache SHELFLIFE — гарантированное время инвалидации кэша в секундах, логично применяемое для кэширования системных объектов [...]
Source: iusoltsev.wordpress.com
Пн, Фев 19, 2018
DML Error Logging DBMS_ERRLOG

When you have an enormous load of data to copy/merge from one table to another, you are probably concerned about:

Performance: How fast can the program copy/merge all the data? Exception handling: How well does the program deal with [...]
Source: www.oratable.com
Сб, Фев 17, 2018
На нескольких запросах столкнулись со ситуацией, когда при по умолчанию включенными optimizer_adaptive_features=true план запроса может не применять преобразование OR-Expansion, что значительно увеличивает формальную стоимость/Cost и реальное время выполнения: Судя по трейсу 10053 оптимизатор выполняет попытки: , не фиксируя невозможности/недопустимости операции в виде: или по какой-то другой причине, но не доходит [...]
Source: iusoltsev.wordpress.com
Вт, Фев 06, 2018
FORALL INSERT Bulk DML

Oracle PL/SQL gives you the ability to perform DML operations in bulk instead of via the regular row-by-row FOR loop. This article shows you how to use bulk DML and handle exceptions along the way.

Why Bulk DML at all?

You [...]

Source: www.oratable.com
Вт, Янв 30, 2018
JSON_TABLE Options

In the article on SQL/JSON query functions we saw how JSON_TABLE converts JSON data to relational form. This article further explores various JSON_TABLE options for parsing and handling errors in JSON data.

Sample JSON Structure

For this demo consider JSON data [...]

Source: www.oratable.com
Пн, Янв 15, 2018
Предварительно усилив параллельность выполнения — как несложный и действенный метод ускорения FULL SCAN-ов, HASH JOIN-ов, сокращения direct path temp read / write- операций и потребления TEMP пространства, соответственно: , получаем: — стартовый рез-т, SQL MONITOR которого показывает много direct path read на MAT_VIEW ACCESS FULL (*) + всё ещё заметное, [...]
Source: iusoltsev.wordpress.com
Вт, Янв 09, 2018
Unique Constraint vs Unique Index

A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.

You might [...]

Source: www.oratable.com
Ср, Янв 03, 2018
How To Lock A Row In Oracle

Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.

This can be achieved by locking the row in Oracle before [...]

Source: www.oratable.com
Сб, Дек 16, 2017
with seg as ( select owner,segment_name ,segment_type ,tablespace_name ,sum(blocks) blocks ,sum(bytes) bytes [...]
Source: orasql.org
Чт, Дек 14, 2017
DBMS_LOB.FRAGMENT_DELETE

One way to get a smaller CLOB from another CLOB is to follow the extract CLOB from JSON example: use DBMS_LOB INSTR/SUBSTR functions to obtain the CLOB fragment of interest. We could look at the same problem from another angle: [...]

Source: www.oratable.com
Ср, Дек 13, 2017

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it's much better to create another pipelined function [...]

Source: orasql.org
Вт, Дек 12, 2017
Extract long string or CLOB from JSON

"Wheels within wheels", as Monty Bodkin would say. Extracting a very long string or CLOB from a JSON CLOB (very long => larger than max_string_size of 32767), in a pre-12.2 Oracle database, turned out to be more complex than it appeared at first sight.

This case study shows how to [...]

Source: www.oratable.com
Вс, Дек 10, 2017

When “serveroutput” is enabled, SQL*Plus executes “BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;” after each command.
That's why I don't like when it is always enabled: it adds extra calls and round-trips and it is inconvenient when I want to get a plan of the last executed query:

SQL> set serverout on; SQL> select * from [...]
Source: orasql.org
Сб, Дек 09, 2017

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it's located at the end of package body:

create or replace package body PKGXXX as ... function ffff return number is nRes [...]
Source: orasql.org
Ср, Дек 06, 2017
Convert Relational Data to JSON

In the JSON series so far, we've talked about how to store JSON data in Oracle and apply JSON conditional checks, and how to query JSON data and convert it to relational form. What if you want the opposite i.e. to convert relational data to JSON form? [...]
Source: www.oratable.com
Пт, Ноя 24, 2017

Sometimes it's really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll [...]

Source: orasql.org
Ср, Ноя 22, 2017
JSON Conditional Logic JSON_EXISTS JSON_TEXTCONTAINS

In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.

JSON conditionals check for the existence [...]

Source: www.oratable.com
Вт, Ноя 21, 2017
Querying JSON Data in Oracle

The last article talked about how to store JSON in the database. This article shows you how to retrieve it meaningfully using various query approaches in Oracle 12c.

For the demo, we'll use the same old CUSTOMER table with JSON metadata.

The table:

-- Customer table DDL with JSON metadata CREATE [...]
Source: www.oratable.com
Вс, Ноя 19, 2017

In one of the previous posts I showed How even empty trigger increases redo generation, but running the test from that post, I have found that this behaviour a bit changed on 12.2:
In my old test case, values of column A were equal to values of B, [...]

Source: orasql.org
Вс, Ноя 19, 2017

On versions 11.2.0.2 – 11.2.0.4 Oracle uses objects' statistics to make direct path reads decision (of course, if “_direct_read_decision_statistics_driven” haven't changed it to “false”), and we can force serial direct reads on statement level using sql profiles with hints INDEX_STATS/TABLES_STATS, but since at least 12.1.0.2 this decision ignores statistics.

Btw, thanks [...]

Source: orasql.org
Чт, Ноя 16, 2017
JSON in the database

JSON is a simple data interchange format, an alternative to XML that's gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.

Here's a [...]

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