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

19 Февраль 2015
Earlier
Пн, Дек 16, 2019
SQL Optimization, на примерах из Oracle E-Business Suite [...]
Source: iusoltsev.wordpress.com
Пт, Дек 06, 2019

A question about hunting down the source of the error “ORA-01652 unable to extend temp segment by NNN in tablespace XXX” shows up on the Oracle-L mailing list or the Oracle developer community forum from time to time. In most cases the tablespace referenced is the temporary tablespace, which means [...]

Source: jonathanlewis.wordpress.com
Чт, Дек 05, 2019


Instance Auto recovery is the functionality to recover the failed instances with in a stipulated time period. The Instances which are faulted and marked to be recovered can be recovered in a particular time.
At sometime it's a disadvantage if your instances are falling which might lead increase table spaces issue [...]
Source: oracleanil.blogspot.ru
Ср, Дек 04, 2019

This note was prompted by an error I made at the UKOUG TechFest19 yesterday. It's fairly well-known that when you read an execution plan that includes the rowsource execution stats – so you get the E-rows (estimated) and A-rows (Actual) reported – then a sensible check of the quality of [...]

Source: jonathanlewis.wordpress.com
Пн, Дек 02, 2019

As of Oracle 19c OJPPD doesn't support connect-by and TABLE():

OJPPD: OJPPD bypassed: query block contains START WITH/CONNECT BY. OJPPD: OJPPD bypassed: View contains TABLE expression. [...]
Source: orasql.org
Вс, Ноя 17, 2019

This is a note I started drafting In October 2012. It's a case study from an optimizer (10053) trace file someone emailed to me, and it describes some of the high-level steps I went through to see if I could pinpoint what the optimizer was doing that fooled it into [...]

Source: jonathanlewis.wordpress.com
Вс, Ноя 17, 2019

In resonse to a comment that one of my notes references a call to a packate “snap_libcache”, I've posted this version of SQL that can be run by SYS to create the package, with a public synonym, and privileges granted to public to execute it. The package doesn't report the [...]

Source: jonathanlewis.wordpress.com
Сб, Ноя 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
Комментирование отключено.