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

19 Февраль 2015
Пн, Янв 29, 2018

It has been some time since I wrote an article walking through the analysis of information on an AWR report, but a nice example appeared a few weeks ago on Twitter that broke a big AWR picture into a sequence of bite-sized chunks that made a little story so [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 25, 2018

I had to write this post because I can never remember which way round Oracle named the two versions of gc buffer busy when it split them. There are two scenarios to cover when my session wants my instance to acquire a global cache lock on a block and [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 23, 2018

I had a sudden urge to go a bit meta – so here's a relative frequency histogram of my observations of the general use of histograms in Oracle:

Source: jonathanlewis.wordpress.com
Пт, Янв 19, 2018

A recent client was seeing a very large redo penalty from refreshing materialized views. Unfortunately they had to be refreshed very frequently, and were being handled with a complete refresh in atomic mode – which means delete every row from every MV then re-insert every row. The total redo [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 18, 2018

I've made several comments in the past about the need for being selective when gathering objects statistics with particular reference to the trade-offs when creating histograms. With Oracle 12c it's now reasonably safe (as far as I'm concerned) to set a method_opt as a table preference that identifies columns [...]

Source: jonathanlewis.wordpress.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
Пн, Янв 15, 2018

I came across a simple performance problem recently that ended up highlighting a problem with the 12c hybrid histogram algorithm. It was a problem that I had mentioned in passing a few years ago, but only in the context of Top-N histograms and without paying attention to the [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 11, 2018

Here's a follow-on from Tuesday's (serious) note about a bug in that introduces random slowdown on large-scale inserts. This threat in this note, while truthful and potentially a nuisance, is much less likely to become visible because it depends on you doing something that you probably shouldn't be [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 10, 2018

After yesterday's post one of the obvious follow-up questions was whether the problem I demonstrated was a side effect of my use of PL/SQL arrays and loops to load data. What would happen with a pure “insert select” statement. It's easy enough to check:

rem rem [...]
Source: jonathanlewis.wordpress.com
Вт, Янв 09, 2018

Here's a problem with ASSM that used to exist in older versions of Oracle had disappeared by and then re-appeared in – disappearing again by It showed up on MoS a few days ago under the heading: “Insert is running long with more waits on db [...]

Source: jonathanlewis.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
Вт, Янв 02, 2018

Following on from a Twitter reference and an update to an old posting about a side effect of constraints on the work done inserting data, I decided to have a closer look at the more general picture of default values and inserts. Here's a script that I've tested [...]

Source: jonathanlewis.wordpress.com
Вс, Дек 31, 2017

It's the end of the year and time for a retrospective of some sort so I was thinking of listing the top 10 most popular pages on my blog, but Connor McDonald beat me to it, so I decided to see if I could remember all the countries I'd visited [...]

Source: jonathanlewis.wordpress.com
Сб, Дек 30, 2017

Here's an odd little quirk that appeared when I was playing around with default values just recently. I think it's one I've seen before, I may even have written about it many years ago but I can't find any reference to it at present. Let's start with a script that [...]

Source: jonathanlewis.wordpress.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

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
Вт, Ноя 28, 2017

As usual it's hard to pick a personal agenda from the wealth of content available for the UKOUG annual conference, but this is my starting list:


13:40 – 14:25 Roger MacNicol: “My query plan says Table Access Full: what happens next ?”

14:40 – 15:30 Gert Poel: “Smart Database Development with PL/SQL [...]

Source: jonathanlewis.wordpress.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

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 – 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 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
Чт, Ноя 16, 2017
select s.module ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch from v$sql s where s.rows_processed>100 and s.executions >1 and s.fetches >1 and s.module is not null and s.command_type = 3 -- SELECTs only and s.program_id = 0 [...]
Source: orasql.org
Комментирование отключено.