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

19 Февраль 2015
Earlier
Сб, Ноя 09, 2019

This note is a little side effect of answering a question about the total space associated with a table, including its indexes, LOB columns and nested tables. The first suggestion I published failed to take account of the fact that nested tables can contain their own nested tables so [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 06, 2019

There's a request on the Oracle Developer Forum for assistance to write a report that shows the total space allocation for a table – including its indexes and LOB columns. There is no requirement to allow for partitioned tables, but it's not entirely clear whether the code should allow [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 31, 2019

It's another of my double-entendre titles. The optimizer can turn a hash join involving an index-organized table into a real performance disaster (though you may have to help it along the way by using a silly definition for your primary key columns). This post was inspired by a question [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 31, 2019
После возникновения/обсуждения в группе @RuOUG проблемы с перераспределением временных сегментов в между read-only ADG RAC инстансами пришлось завести SR, в том числе по рез-там которого, надеюсь, в сентябре появился док-нт Temporary Tablespace Allocation Fails with ORA-1652 in RAC Standby DB Even When There is Still Free Temp Space (Doc ID [...]
Source: iusoltsev.wordpress.com
Ср, Окт 30, 2019

A question came up on the Oracle-L list server a couple of days ago expressing some surprise at the following execution plan:

-------------------------------------------------------------------------------------------------------- | Id | Operation [...]
Source: jonathanlewis.wordpress.com
Пт, Окт 25, 2019

A couple of days ago I posted a note with some code (from Stew Ashton) that derived the clustering_factor you would get for an index when you had set a value for the table_cached_blocks preference but had not yet created the index. In the note I said that [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 24, 2019

A few days ago I published a little note of a script I wrote some time ago to estimate the clustering_factor of an index before it had been built. At the time I pointed out that one of its limitations was that it would not handle cases where you [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 22, 2019

Here's a thought that falls somewhere between philosophical and pragmatic. It came up while I was playing around with a problem from the Oracle database forum that was asking about options for rewriting a query with a certain type of predicate. This note isn't really about that question but the [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 18, 2019

I've decided to do a little rewriting and collating so that I can catalogue related ideas in an order that makes for a better narrative. So this is the first in a series of notes designed to help you understand why the optimizer has made a particular choice and why [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 16, 2019

Originally drafted July 2018

“How do you find out what the clustering_factor of an index would be without first creating the index ?”

I'm not sure this is really a question worth asking or answering[1], but since someone asked it (and given the draft date I have no idea who, where, when [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 11, 2019

Here's an odd, and unpleasant, detail about querying v$session in the “most obvious” way. (And if you were wondering what made me resurrect a posting on “my ssession id” a couple of days ago, this is the reason). Specifically if you want to select some information for your own [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 09, 2019

Here's a funny little detail that I don't think I've noticed before – needing only a simple demo script:

rem rem Script: cursor_sharing_oddity.sql rem Author: Jonathan Lewis rem [...]
Source: jonathanlewis.wordpress.com
Ср, Окт 09, 2019

Here's a little note that's been hanging around as a draft for more than eight years according to the OTN (as it was) posting that prompted me to start writing it. At the time there were still plenty of people using Oracle 10g. so the question didn't seem entirely [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 07, 2019

There are two questions about temporary space that appear fairly regularly on the various Oracle forums. One is of the form:

From time to time my temporary tablespace grows enormously (and has to be shrunk), how do I find what's making this happen?

The other follows the more basic pattern:

My process sometimes [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 04, 2019

This is just a list of the notes I've written about the opt_estimate() hint.

opt_estimate – using the hint to affect index calculations: index_scan and index_filter opt_estimate 2 – applying the hint to nested loop joins, options: nlj_index_scan and nlj_index_filter opt_estimate 3 – a couple of little-known options for the [...]
Source: jonathanlewis.wordpress.com
Чт, Окт 03, 2019

A recent blog note by Martin Berger about reading trace files in 12.2 poped up in my twitter timeline yesterday and reminded me of a script I wrote a whle ago to create a simple view I could query to read the tracefile generated by the current session while [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 02, 2019

At the recent Trivadis Performance Days in Zurich, Chris Antognini answered a question that had been bugging me for some time. Why would Oracle want to set the default value of _cursor_obsolete_threshold to a value like 8192 in 12.2 ?

In 11.2.0.3 the parameter was introduced with the default value 100; [...]

Source: jonathanlewis.wordpress.com
Сб, Сен 28, 2019

At the Trivadis Performance Days 2019 I did a presentation on using execution plans to understand what a query was doing. One of the examples I showed was a plan from an Exadata system (using 11.2.0.4) that needed to go faster. The plan was from the SQL Monitor report and [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 16, 2019

Here's a quick “how to”.

If you want to update a column in table A with a column value from table B, then there's a simple way to check if the required result can be achieved through an updatable join view.

Step 1: write a query that joins table A to table [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 16, 2019

A peripheral question in a recent comment (made in response to me asking whether a loop had been written with a sleep time of 1/100th or 1/1000th of a second) asked “How do you sleep for 1/1000th of a second in pure PL/SQL?”

The answer starts with “How pure is pure [...]

Source: jonathanlewis.wordpress.com
Сб, Сен 14, 2019

Use ORAMDS for all XSD / WSDL files on the project (oramds:...) in all composite.xml and wrappers in location tag. Doing that you will guarantee that you can deploy anywhere even when you don't have remote access to a environment.

Also, in this way it is not necessary to change [...]
Source: oracleanil.blogspot.ru
Сб, Сен 14, 2019
Background Transaction context between different BPEL services are controlled by mainly two properties/attributes. Those are Delivery and Transaction attributes.
Details Case 1 : If BPEL Process is Async or one-way process then, Delivery policy attribute can have three values. Those are 1)- async.persit 2)- async.cache 3)- sync
meaning: - async.persit: Delivery [...]
Source: oracleanil.blogspot.ru
Пт, Сен 13, 2019

Bobby Durrett recently published a note about estimating the volume of non-logged blocks written by an instance with the aim of getting some idea of the extra redo that would be generated if a database were switched to “force logging”.

Since my most recent blog notes have included various extracts [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 13, 2019

At the UKOUG TechFest 2019 event in Brighton this year I'll be chairing the CBO Panel session on Monday 2nd December at noon. The panellists will be Maria Colgan, Nigel Bayliss, Christian Antognini and Richard Foote.

It will be possible to propose questions on the day – written or spoken, but [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 13, 2019
Первоначально проблема представлялась просто как запрос с нестабильным временем выполнения на Read-Only Standby , при более внимательном рассмотрении выяснилось, что запрос не просто небыстро выполняется, а периодически-трагически гибнет: с сообщениями в alert.log: LMHB trace file: *) обращает на себя внимание, что ожидание LGWR library cache lock не превышает max=5.500000 sec [...]
Source: iusoltsev.wordpress.com
Вт, Сен 10, 2019

Somewhere I think I've published a note about an anomaly that's been bugging me since at least Oracle 10g – but if it is somewhere on the Internet it's hiding itself very well and I can't find it, though I have managed to find a few scripts on my laptop [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 09, 2019

Upgrades cause surprises – here's a pair of results from a model that I constructed more than 15 years ago, and ran today on 12.2, then modified and ran again, then ran on 11.2.0.4, then on 12.1.0.2. It's very simple, I just create a table, gather stats, then update every [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 04, 2019
Аналогично описанному для версий 11-12.1 — JPPD в присутствии удалённой таблицы и View Merging — и справедливому до 19.3 включительно (тесткейс там есть — можно проверить) наблюдению, CBO версии 12.2 также может полностью блокировать применение Join Predicate Push Down с резолюцией JPPD bypassed: Remote table referenced — в этот раз [...]
Source: iusoltsev.wordpress.com
Вт, Авг 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
Комментирование отключено.