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

19 Февраль 2015
Earlier
Пт, Янв 24, 2020

Here's another anomaly that appears when you mix and match Oracle features. In this case it's “With” subqueries (common table expressions / CTEs) and Active Dataguard (ADG) Standby databases. The problem appeared on the Oracle-l listserver and luckily for the OP another member of the list had seen it [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 23, 2020

These screenshot are from JDevelopers - 12c
1. Click on Window - Resources


2. In the Resources pallet - Click on Import to open the 'Import Catalogs and Connections' window.



3. Now browse to locate the *.rcx file containing connection details.




4. Then click on Import to import [...]
Source: oracleanil.blogspot.ru
Ср, Янв 22, 2020

It's a long time since I wrote a note with the “philosophy” theme, but a recent complaint about Oracle prompted me to suggest the following thought:

“If you write SQL that is technically incorrect it's not Oracle's fault if sometimes the SQL completes without an error.”

Consider the following fragment of code:

drop [...]
Source: jonathanlewis.wordpress.com
Пн, Янв 20, 2020

This is a case study based on a question that appeared on the Oracle Developer Community forum a few days ago.

What I'm aiming to present in this note is the pattern of thinking that you should adopt in cases like this. The final suggestion in this note isn't necessarily [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 17, 2020

Here's a bug that was highlighted a couple of days ago on the Oracle Developer Community forum; it may be particularly worth thinking about if if you haven't yet got up to Oracle 12c as it appeared in an optimizer feature that appeared in 12.2 (and hasn't been completely [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 14, 2020

When I was a child I could get lost for hours in an encyclopedia because I'd be looking for one topic, and something in it would make me want to read another, and another, and …

The same thing happens with MOS (My Oracle Support) – I search for something [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 13, 2020

This is a note I drafted in September 2015 and only rediscovered a couple of days ago while searching for something I was sure I'd written about collections and/or table functions. The intention of collections and table functions is that they should behave like tables when you use them in [...]

Source: jonathanlewis.wordpress.com
Вс, Янв 12, 2020

Regular readers will have noticed that for the last few weeks I've been adding a footnote (now deleted) to any new blog posts asking for donations to the Nepal Youth Foundation (UK), with an offer to match (up to a limit) any donations made by my readers. The [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 10, 2020

The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer's handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 03, 2020

THe problem with finding something new and fiddling with it and checking to see how you can best use it to advantage is that you sometimes manage to “break” it very quickly. In yesterday's blog note I introduced the /*+ push_having_to_gby(@qbname) */ hint and explained why it was a [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 02, 2020

I came across an interesting new hint recently when checking the Outline Data for an execution plan: /*+ push_having_to_gby() */ It's an example of a “small” change designed to reduce CPU usage by reducing the volume of data that passes through the layers of calls that an execution plan [...]

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

This is an observation that came up on the Oracle Developer Forum a couple of days ago, starting life as the fairly common problem:

I have a “select” that runs quickly but when I use in a “create as select” it runs very slowly.

In many cases this simply means [...]

Source: jonathanlewis.wordpress.com
Ср, Дек 25, 2019

A classic example of Oracle's “mix and match” problem showed up on the Oracle Developer Forum a few days ago. Sometimes you see two features that are going to be really helpful in your application – and when you combine them something breaks. In this case it was the [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 20, 2019

This is a note I first drafted about 5 years ago (the date stamp says March 2014) and rediscovered a few days ago when the question came up on a Twitter thread.

How do you purge a single SQL statement from the library cache without having to execute “alter system [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 20, 2019

This is a note I first drafted about 5 years ago (the date stamp says March 2014) and rediscovered a few days ago when the question came up on a Twitter thread.

How do you purge a single SQL statement from the library cache without having to execute “alter system [...]

Source: jonathanlewis.wordpress.com
Ср, Дек 18, 2019

This is a note courtesy of Jack can Zanen on the Oracle-L list server who asked a question about “wait for CPU” and then produced the answer a couple of days later. It's a simple demonstration of how Java in the database can be very deceptive in terms of [...]

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

Here's a worrying bug that showed up a couple of days ago on the Oracle-L mailing list (I can't link directly to the exact post at the moment – the data is currently hiding). It's a problem that I've tested against 12.2.0.1 and 19.3.0.0 – it may be present [...]

Source: jonathanlewis.wordpress.com
Пн, Дек 16, 2019
Проблемы, выраженные в повышенном соотношении DB Time/Elapsed (*) с последующим относительным всплеском Redo size Per Second (**): характеризовались нетипичным для стабильной OLTP-системы набором ожиданий (***): , кроме того, проблема была чётко ограничена по времени: и хорошо прослеживалась на отрезке ASH за 1 минуту (****), где, стартуя с наиболее частого ожидания [...]
Source: iusoltsev.wordpress.com
Пн, Дек 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
Вс, Ноя 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
Комментирование отключено.