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

19 Февраль 2015
Earlier
Сб, Апр 06, 2019
Продолжая тесты с Oracle LiveSQL 19c, проверил наличие проблемы с плохими FULL SCAN-ами при [авто]добавлении [INTERVAL] PARTITION в процессе каждодневной DML активности и запросами с условием IN Тестовая таблица простая, и в процессе первоначальной Direct PATH загрузки партиции добавляются, по крайней мере global statistics успешно собирается: NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE [...]
Source: iusoltsev.wordpress.com
Сб, Апр 06, 2019
Запись V$SESSION_LONGOPS, в частности, поля MESSAGE, TOTALWORK для операций Dynamic Sampling выглядят очень серьёзно: — при рекордно малой продолжительности ELAPSED_SECONDS = 8 для немаленькой таблицы — 278,127,738 Blocks — и только пустые SQL_PLAN_HASH_VALUE, SQL_EXEC_START, SQL_EXEC_ID да SQL_PLAN_OPTIONS выдают неладное) В ASH та же операция Dynamic Sampling также отчётливо отображена: — [...]
Source: iusoltsev.wordpress.com
Вт, Мар 26, 2019
INVALID Objects in Oracle

It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken. This articles takes a closer look at:

typical reasons why Oracle objects become INVALID how to identify INVALID objects [...]
Source: www.oratable.com
Пт, Мар 22, 2019

This is just a little shout-out about the Stats Advisor – if you decide to give it a go, what sort of things is it likely to tell you. The answer is in a dynamic performance view called v$stats_advisor_rules – which I've list below from an instance running 18.3.0.0.

SQL> set [...]
Source: jonathanlewis.wordpress.com
Чт, Мар 21, 2019

Here's a little puzzle that came up in the ODC database forum yesterday – I've got a query that has been captured by SQL Monitor, and it's taking much longer to run than it should but the monitoring report isn't telling me what I need to know about the [...]

Source: jonathanlewis.wordpress.com
Вт, Мар 19, 2019

A few months ago Franck Pachot wrote about a recursive SQL statement that kept appearing in the library cache. I discovered the note today because I had just found a client site where the following statement suddenly appeared near the top of the “SQL ordered by Executions” section of [...]

Source: jonathanlewis.wordpress.com
Вс, Мар 17, 2019
Уважаемый коллега Максим Филатов продолжает изыскания: 1) в ходе экспериментов с Infiniband на общедоступном железе Максим доказал/добился внесения важного уточнения в неоднозначную документацию поддержки Oracle Clusterware and RAC Support for RDS Over Infiniband (Doc ID 751343.1): In 12.2+ version, RDS is supported only for databases running on the engineered systems, [...]
Source: iusoltsev.wordpress.com
Ср, Мар 13, 2019
DBMS_SCHEDULER.CREATE_JOB to Run Procedures Asynchronously

Consider a PL/SQL stored procedure that handles a heavy transaction. The procedure is extremely slow – when executed from a UI, the application hangs for minutes. On analysis it is found that the procedure is performing a complex series [...]

Source: www.oratable.com
Ср, Мар 13, 2019

Here's an important thought if you've got any large tables which are purely hash partitioned. As a general guideline you should not need partition level stats on those tables. The principle of hash partitioned tables is that the rows are distributed uniformly and randomly based on the hash key so, [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 11, 2019

I've made use of the function a few times in the past, for example in this posting on the dangers of using reverse key indexes, but every time I've mentioned it I've only been interested in the “leaf blocks per key” option. There are actually four different variations of [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 11, 2019
Наиболее точное определение хинта DYNAMIC_SAMPLING можно найти у Джонатана Льюиса: You can use the dynamic_sampling() hint in two different ways in an SQL statement. If you use the hint in the “cursor-level” form: /*+ dynamic_sampling ({level}) */ e.g. /*+ dynamic_sampling(4) */, this is equivalent to setting the parameter optimizer_dynamic_sampling to [...]
Source: iusoltsev.wordpress.com
Вс, Мар 10, 2019
Если вдруг в приступе доброты душевной разработчик решил не ограничивать себя в фантазиях потребление ресурсов и, например, в определении materialized view (или в запросе) по-простому вставил /*+ PARALLEL */, и это породило не всегда приятный для администраторов DML вида: , который в полном соответствии с Automatic Degree of Parallelism (AutoDOP) [...]
Source: iusoltsev.wordpress.com
Пт, Мар 08, 2019

One of the questions that came up on the CBO Panel Session at the UKOUG Tech2018 conference was about the /*+ append */ hint – specifically how to make sure it was ignored when it came from a 3rd party tool that was used to load data into the database. [...]

Source: jonathanlewis.wordpress.com
Ср, Мар 06, 2019

I published a note a few years ago about using the 12c “with function” mechanism for writing simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I've just been prompted by a question on ODC to supply a couple more [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 04, 2019

I wrote this note a little over 4 years ago (Jan 2015) but failed to publish it for some reason. I've just rediscovered it and it's got a couple of details that are worth mentioning, so I've decided to go ahead and publish it now.

A recent [ed: 4 year old] [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 01, 2019

Franck Pachot did an interesting presentation at the OBUG (Belgium user group) Tech Days showing how to use one of the O/S debug/trace tools to step through the function calls that Oracle made during different types of joins. This prompted me to ask him a question about a possible [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 25, 2019
Keep the Response Body with Non-2xx Responses in PL/SQL

When UTL_HTTP web service calls nicely return 2xx status codes, all is well with the world. Things get tricky when a web service call encounters an error and sends back a non-2xx response. [...]

Source: www.oratable.com
Сб, Фев 16, 2019

Compare the results of the following query with the clause “fetch first 2 rows only”

with t1(a) as (select * from table(odcinumberlist(1,3))) ,t2(a,b) as (select * from table(ku$_objnumpairlist( [...]
Source: orasql.org
Сб, Фев 16, 2019

Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).

Obviously, the main reason of that is different calculation of rownum:

If we pull the [...]

Source: orasql.org
Вт, Фев 05, 2019

Just short note: If your imported Oracle Linux image hangs on boot in the Oracle cloud, just set GRUB_DISABLE_UUID=”true” in /etc/default/grub

[...]
Source: orasql.org
Чт, Янв 31, 2019

I've written in the past about oddities with descending indexes ( here, here, and here, for example) but I've just come across a case where I may have to introduce a descending index that really shouldn't need to exist. As so often happens it's at the boundary [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 18, 2019

This note is a follow-up to a recent comment a blog note about Row Migration:

So I wonder what is the difference between the two, parallel dml and serial dml with parallel scan, which makes them behave differently while working with migrated rows. Why might the strategy of serial dml [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 17, 2019

Nigel Bayliss has posted a note about a frequently requested feature that has now appeared in Oracle 19c – a mechanism to help people understand what has happened to their hints. It's very easy to use, it's just another format option to the “display_xxx()” calls in dbms_xplan; so [...]

Source: jonathanlewis.wordpress.com
Вс, Дек 30, 2018

Three interesting myths about rowlimiting clause vs rownum have recently been posted on our Russian forum:

TopN query with rownum “fetch first N rows only” is always faster than rownum “SORT ORDER BY STOPKEY” stores just N top records during sorting, while “WINDOW SORT PUSHED RANK” sorts all input and [...]
Source: orasql.org
Пт, Дек 21, 2018

One last post before closing down for the Christmas break.

Here's a little puzzle with a remarkably easy and obvious solution that Ivica Arsov presented at the UKOUG Tech2018 conference. It's a brilliant little puzzle that makes a very important point, because it reminded me that most problems are easy [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 14, 2018
С Иваном Постниковым наблюдали/разбирали нечасто встречаемое ожидание library cache load lock, в течение заметного времени наблюдавшееся у ряда сессий 2-го инстанса: , содержимое ROW_WAIT_OBJ# не очень полезно: , судя по пустым SQL_EXEC_START / SQL_EXEC_ID запросы/курсоры находятся в процессе погрузки в Library Cache (на что указывает и название ожидания длительностью SECONDS_IN_WAIT [...]
Source: iusoltsev.wordpress.com
Пн, Дек 10, 2018
До обновления 12.1 -> 12.2 запрос удовлетворительно (SLA, timeouts,…) быстро выполнялся по плану: , с использованием OJPPD — Old-stype (non-cost-based) Join Predicate Push-Down (*) высокоизбирательного условия ("INVOICE.CLIENT_ID) в обзор (**) вида UNION-ALL PARTITION (***) с последующим быстрым индексным доступом (****) В версии 12.2, похоже, что-то существенно изменилось, OJPPD в процессе [...]
Source: iusoltsev.wordpress.com
Пт, Дек 07, 2018

Recently I had to install the patch for fixing cross-platform PDB transport bug onto the docker images with Oracle, so these are easy way how to do it:

1. create directory “patches” and create “install_patches.sh”:

#!/bin/bash unzip -u ./*.zip CURDIR=`pwd` for D in *; do if [ -d "${D}" ]; [...]
Source: orasql.org
Вт, Ноя 20, 2018
Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows [...]

Source: www.oratable.com
Вт, Ноя 20, 2018
Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows [...]

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