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

19 Февраль 2015
Earlier
Вт, Авг 27, 2019

This is another of the blog notes that have been sitting around for several years – in this case since May 2014, based on a script I wrote a year earlier. It makes an important point about “inconsistency” of timing in the way that Oracle records statistics of work done. [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 26, 2019

A recent thread on the Oracle Developer Community starts with the statement that a query is taking a very long time (with the question “how do I make it go faster?” implied rather than asked). It's 12.1.0.2 (not that that's particularly relevant to this blog note), and we have [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 23, 2019

I've got a number of examples of clever little tricks the optimizer can do to transform your SQL before starting in on the arithmetic of optimisation. I was prompted to publish this one by a recent thread on ODC. It's worth taking note of these tricks when you spot one [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 21, 2019

This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables. In that article I suggested (before testing) that you could [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 21, 2019
In this article we will see how to stop a running SOA composite process. It is normal to stop from Enterprise Manager (http: // {hostname}: {port number} / em), but I thought this was not "user friendly".

How can I use the API to identify the SOA composite instance ID? [...]
Source: oracleanil.blogspot.ru
Ср, Авг 21, 2019
This content has already been introduced in many entries, but I will keep it personally as many people still ask me.
Log in to Enterprise Manager Fusion Middleware Control.
http: // {host name or IP}: {port number} / em Open the SOA folder, right click on the soa_infra (soa_server1) folder and [...]
Source: oracleanil.blogspot.ru
Ср, Авг 21, 2019
Two new Oracle Service Bus samples have been registered on the Oracle Technology Network site. The first sample project is osb-205-SimpleREST. This demonstrates how to fully implement a REST service in OSB. “Complete” means that the following HTTP methods are implemented using OSB.
GET POST PUT DELETE HEAD OPTIONS Another REST sample is now over. This is [...]
Source: oracleanil.blogspot.ru
Ср, Авг 21, 2019
Error

When starting the SOA server, the following exception may occur.




log is opened. All server side log events will be written to this file.> cation for user weblogic denied weblogic.security.SecurityInitializationException: Authentication for user weblogic denied at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.doBootAuthorization(CommonSecurityServiceM at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initialize(CommonSecurityServiceManagerDel [...]
Source: oracleanil.blogspot.ru
Ср, Авг 21, 2019

I faced the problem that it took a long time to execute select in DB Adapter. There were three tables that contained a large amount of data and contained many columns, and these tables were being handled by the DB Adapter.

The database side configured a primary key / foreign key [...]
Source: oracleanil.blogspot.ru
Вт, Авг 20, 2019

It's strange how one thing leads to another when you're trying to check some silly little detail. This morning I wanted to find a note I'd written about the merge command and “stable sets”, and got to a draft about updatable join views that I'd started in 2016 in [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 14, 2019

What happens when you execute dbms_stats.gather_system_stats() with the ‘Exadata' option ?

Here's what my system stats look like (12.2.0.1 test results) after doing so. (The code to generate the two different versions is at the end of the note).

System Stats ============ Status: COMPLETED Timed: 13-Aug-2019 15:00:00 - 13-Aug-2019 15:00:00 -------------------------------------------------- CPUSPEED [...]
Source: jonathanlewis.wordpress.com
Вт, Авг 13, 2019

Here's a problem (and I think it should be called a bug) that I first came across about 6 years ago, then forgot for a few years until it reappeared some time last year and then again a few days ago. The problem has been around for years [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 12, 2019

Here's a question to provoke a little thought if you've got nothing more entertaining to do on a Sunday evening. What threats do you think of when you see a statement like the following in (say) an AWR report, or in a query against v$sql ?

update tableX set [...]
Source: jonathanlewis.wordpress.com
Пт, Авг 09, 2019

An anecdote with a moral.

Many years ago – in the days of Oracle 7.2.3, when parallel query and partition views were in their infancy and when RAC was still OPS (Oracle Parallel Server), I discovered a bug that caused parallel queries against partition views to crash (Maxim – mixing two [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 09, 2019

This is a little case study on “pre-emptive trouble-shooting”, based on a recent question on the ODC database forum asking about splitting a range-based partition into two at a value above the current highest value recorded in a max_value partition.

The general principle for splitting (range-based) partitions is that if [...]

Source: jonathanlewis.wordpress.com
Чт, Авг 08, 2019

Several years ago I wrote a note about reporting dba_free_space and dba_extents to produce a map of the space usage in a tablespace in anticipation of messing about with moving or rebuilding objects to try and reduce the size of the files in the tablespace. In the [...]

Source: jonathanlewis.wordpress.com
Вт, Авг 06, 2019

In the “Parse Puzzle” I posted a couple of days ago I showed a couple of extracts from an AWR report that showed contradictory results about the time the instance spent in parsing and hard parsing, and also showed an amazing factor of 4 difference between the DB Time [...]

Source: jonathanlewis.wordpress.com
Вс, Авг 04, 2019

Here are some details from an AWR report covering a few minutes in the lifetime of an instance of 18.3. It's a carefully constructed demonstration and all I've done is take an AWR snapshot, execute a single SQL statement, then take another snapshot, so the only thing captured by the [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 02, 2019

This is a blog note that started life in September 2001 (which was before I started blogging, I think), got drafted as a blog in January 2014 because of a question on the Oracle-L list server, and has finally got to publication following a question on the ODC database [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 31, 2019
Тесты с простым тестовым запросом проводились на версии 18c — там трассировка и комментарии Oracle полнее, хотя описываемые особенности известны и наблюдаемы, как минимум, с версии 12.1 Итак, для запроса одновременно созданы два элемента SPM — SQL Patch и SQL Plan Baseline: , Baseline фиксирует индексный доступ: , в то [...]
Source: iusoltsev.wordpress.com
Вс, Июл 28, 2019
Обсуждая с Юрием Пудовченко длительный разбор/long parse запроса, обратили внимание на расхождения отображение длительности процесса parsing-а в отчётах tkprof и V$ACTIVE_SESSION_HISTORY, в то время как tkprof показывает: , данные ASH говорят о том, что в течение этих 14 секунд запрос был как бы одновременно IN_SQL_EXECUTION, IN_PARSE, IN_HARD_PARSE с нулевым SQL_EXEC_ID, [...]
Source: iusoltsev.wordpress.com
Пт, Июл 12, 2019

If you've been wondering why I resurrected my drafts on the opt_estimate() hint, a few weeks ago I received an email containing an example of a query where a couple of opt_estimate() hints were simply not working. The critical features of the example was that the basic structure of the [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 10, 2019
Столкнулся с принципиальной невозможностью форсировать параллельное выполнение даже на этапе EXPLAIN PLAN: — т.е. текст запроса форсирует /*+ PARALLEL(8) */, а DBMS_XPLAN.DISPLAY использует DOP 1 because of hint (*) => когнитивный диссонанс detected) К счастью, ответ легко находится в частичных трейсах оптимизатора: Становится понятнее принципиальная невозможность создания параллельного индекса на [...]
Source: iusoltsev.wordpress.com
Вт, Июл 09, 2019
🇩🇪

Over the last few days I've been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:

Practical Oracle 8i p.29

"If you are going to depend on a technological feature of Oracle, you need to make sure that you have tried to break [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 08, 2019
DETERMINISTIC functions in Oracle

PL/SQL functions in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.

A little elaboration is in order here.

1. …will always produces the [...]
Source: www.oratable.com
Вс, Июл 07, 2019
Особенность первая Проблема с непараллельным выполнением казалось бы гарантированно захинтованного запроса, SQL Monitor которого показывал: (*) DOP Downgrade 100% и, как следствие/признак serial execution — отсутствующую секцию Parallel Execution Details (**) медленное выполнение по честному (без PX COORDINATOR FORCED SERIAL) параллельному плану одним процессом, однако с выраженным ожиданием выделения PX-процессов [...]
Source: iusoltsev.wordpress.com
Ср, Июл 03, 2019

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It's a good question as it's fairly common knowledge that distributed queries can be very much slower [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 01, 2019

In the previous article in this series on the opt_estimate() hint I mentioned the “query_block” option for the hint. If you can identify a specify query block that becomes an “outline_leaf” in an execution plan (perhaps because you've deliberately given an query block name to an inline subquery and [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 28, 2019

This is just a quick note to throw out a couple of of the lesser-known options for the opt_estimate() hint – and they may be variants that are likely to be most useful since they address a problem where the optimizer can produce consistently bad cardinality estimates. The first is [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 26, 2019

Here's a question just in from Oracle-L that demonstrates the pain of assuming things work consistently when sometimes Oracle development hasn't quite finished a bug fix or enhancement. Here's the problem – which starts from the “scott.emp” table (which I'm not going to create in the code below):

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