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

19 Февраль 2015
Пт, Июн 16, 2017
Check if JVM is Installed in Oracle

I recently thought of using Java's built-in BigInteger.gcd() method inside a PL/SQL wrapper to calculate GCD in SQLs.

And so I wrote the Java code and ran it on Oracle XE.

Things didn't go as planned.

SQL> -- Create java source for GCD calculation SQL> create or replace and resolve java [...]
Source: www.oratable.com
Ср, Июн 14, 2017

An interesting observation appeared recently as a side-channel on a question on the OTN database forum – how does Oracle execute an unpivot() operation. Here's an example of such a query:

rem rem Script: unpivot_2.sql rem [...]
Source: jonathanlewis.wordpress.com
Вт, Июн 13, 2017
Oracle XML Object-Relational Storage

After the post on models for storing XML data in Oracle and examples of binary XML storage, here's a detailed look at the rigorous and performant structured XML storage or object-relational storage.

Object-relational storage of XML data is based on "shredding" the XML content into a set of SQL [...]

Source: www.oratable.com
Пн, Июн 12, 2017

If you're familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 09, 2017

At the end of my presentation to the UKOUG Database SIG yesterday I summed up (most) of points I'd made with a slide making the claim:

In 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 07, 2017
Oracle XDB Binary XML Storage

The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.

This post shows working examples of binary XML storage

1.1. without XML Schema
1.2 with XML Schema

The scripts below [...]
Source: www.oratable.com
Пн, Июн 05, 2017
Storing XML Data in Oracle

Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table, or an XMLType column in a relational table, to persist XML data.

Different storage models are available in Oracle to best fit the nature of the XML data [...]

Source: www.oratable.com
Чт, Июн 01, 2017

While reading a blog post by Maria Colgan a couple of week ago I came across an observation about histograms that I'd not noticed before; worse still, it was a feature that seemed to make some “damage-limitation” advice I'd been giving for years a really bad idea! The threat [...]

Source: jonathanlewis.wordpress.com
Пн, Май 29, 2017
SQL XML in Oracle: Generating XML from Relational Data

Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.

This post shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.

Let's [...]

Source: www.oratable.com
Чт, Май 25, 2017

Headline – if you don't want to read the note – the /*+ parallel(N) */ hint doesn't mean a query will use parallel execution, even if there are enough parallel execution server processes to make it possible. The parallel(N) hint tells the optimizer to consider the cost of using parallel [...]

Source: jonathanlewis.wordpress.com
Чт, Май 25, 2017
Активно читающий запрос из AWR: , заметность которого, похоже, обусловлена выбором и активным использованием, по-видимому, не самого оптимального плана 2944350538: — ASH TOP5: самые длинные выполнения использовали неудачный план 2944350538 — всё сходится) Сравним Outline удачного и неудачного планов: — отличия в LEADING и методе соединения "RESULT_PAGE_DATA@SEL$4 (*) Из ASH [...]
Source: iusoltsev.wordpress.com
Вт, Май 23, 2017

There's so many things that can go wrong when you start using tables with more than 255 columns – here's one I discovered partly because I was thinking about a client requirement, partly because I had a vague memory of a change in behaviour in 12c and Stefan Koehler [...]

Source: jonathanlewis.wordpress.com
Вс, Май 21, 2017
В дополнение к старому описанию особенностей DS версии 10.2 — Dynamic Sampling при индексном доступе к данным — на том же тесткейсе наблюдал как измененилось поведение Old Style Dynamic Sampling (DS) и Adaptive Dynamic Sampling (ADS) в новых версиях Oracle 12c Итак, тестовый DDL без изменений: 1) Old Style Dynamic [...]
Source: iusoltsev.wordpress.com
Пт, Май 19, 2017

This is one of my “black hole” articles – I drafted it six months ago, but forgot to publish it.

A recent post on OTN highlighted some of the interesting oddities that appear when you create tables with more than 255 columns. In fact this was a more subtle case [...]

Source: jonathanlewis.wordpress.com
Чт, Май 18, 2017
Fuzzy Match using UTL_MATCH

Oracle's UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms:

Levenshtein Distance Jaro-Winkler Distance

Let's understand the algorithms and see UTL_MATCH subprograms in action.

Note: It is not necessary to understand the nitty-gritties of the algorithms to use UTL_MATCH. The deep dive is for [...]

Source: www.oratable.com
Пн, Май 15, 2017
SQL Find Differences between Two Tables

In a recent project, a "shadow" table had to be compared periodically with its main source table to identify the differences between the two tables.

The nature of differences fell into one of these buckets:

Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: [...]
Source: www.oratable.com
Вс, Май 14, 2017
, т.е. в случаях, когда использование VARCHAR2-функций типа LISTAGG заканчивается LISTAGG_CLOB Использовавшаяся поначалу User-Defined Aggregate Function типа LISTAGG_CLOB в плане скорости выполнения показывала самые грустные рез-ты: *) — пересылая по SQL*Net 50MB клиенту и 27MB обратно(!), совершая 400,000(!) roundtrip-ов, что, вероятно, является неприятной особенностью передачи CLOB данных и коственно подтверждается [...]
Source: iusoltsev.wordpress.com
Чт, Май 11, 2017
Convert VARCHAR2 to DATE

It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I'm about to [...]

Source: www.oratable.com
Ср, Май 10, 2017

Here's a not very serious note that makes a serious point. I've got a small tablespace made up of 4 files, and here's a little report I can run against the data dictionary for that tablespace:

select 'File space' What, nvl(sum(user_bytes)/1048576,0) MB from dba_data_files where tablespace_name = 'LOB_TEST' union all select 'Free [...]
Source: jonathanlewis.wordpress.com
Пн, Май 08, 2017

The opt_estimate hint is one of many that shouldn't be used in end-user code and isn't officially documented. Nevertheless – like so many other hints – it's a hint that is hard to ignore when you see it floating around the code generated by the Oracle software. This note is [...]

Source: jonathanlewis.wordpress.com
Пт, Май 05, 2017
наблюдались в виде: — очень похоже на Bug 17323222 ORA-1555 / ORA-3170: deadlocked on readable physical standby (undo segment x) on ADG, т.е. ORA-3170 аналогично указывает на несуществующий SEGMENT_ID, а ORA-1555 возникает на коротком Query Duration Похоже, лечится изменением параметра: — случайно в то же время применённым для исправления ORA-07445 [...]
Source: iusoltsev.wordpress.com
Вт, Май 02, 2017

Here's a performance problem that came up on OTN recently. The following query (reforematted) takes “ages” to run – how do you address the problem:

SELECT COUNT(*) FROM smp_dbuser2.workflow_step_report WHERE report_ID IN ( SELECT report_id FROM smp_dbuser2.workflow_report WHERE trunc(start_time) = '28-Apr-2017' AND user_id = 'nbi_ssc' ) ;

Various pieces of relevant information were supplied (the workflow_report table holds 1.4M rows the workflow_step_report table holds 740M [...]

Source: jonathanlewis.wordpress.com
Пн, Май 01, 2017

Imagine you're fairly new to Oracle and don't have a lot of background information at your fingertips; then one day someone tells you to read the manual pages for the view dba_free_space. Look carefully at this sentence:

Note that if a data file (or entire tablespace) is offline in a [...]

Source: jonathanlewis.wordpress.com
Пн, Май 01, 2017

A recent posting on SQL.RU asked why Oracle doesn't raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn't find a link to the answer (though I read about it many years ago, but I don't remember where…), I've decided to post [...]

Source: orasql.org
Чт, Апр 27, 2017

If this is the closing section of a trace file from a single end-user session that has a performance problem, what's the most obvious deduction you can make about the cause of the problem, and what sort of action would you take next ?

Source: jonathanlewis.wordpress.com
Пт, Апр 21, 2017

It's hard to understand all the ramifications of Oracle's undo handling, and it's not hard to find cases where the resulting effects are very confusing. In a recent post on the OTN database forum resulted in one response insisting that the OP was obviously updating a table with frequent commits [...]

Source: jonathanlewis.wordpress.com
Пт, Апр 21, 2017

I've wrote in previous post

4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

But it's not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
You can check it on 12.2 and [...]

Source: orasql.org
Пт, Апр 14, 2017

A recent OTN posting asked how the optimizer dealt with “like” predicates for character types quoting the DDL and a query that I had published some time ago in a presentation I had done with Kyle Hailey. I thought that I had already given a detailed answer somewhere [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 10, 2017

Oracle Scene, the magazine of the UK Oracle User Group is piloting a new regular feature called Ask Jonathan, a chance to get an answer to any question you may have about how the Oracle database engine works.

I'm aiming to answer two or three questions per issue over the [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 10, 2017

A recent posting on OTN raised the question of whether or not the “parallel” hint and the “first_rows(n)” hint were mutually incompatible. This reminded me that from time to time other posters on OTN (copying information from various websites, perhaps) have claimed that “parallel doesn't work with first rows” [...]

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