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

19 Февраль 2015
Earlier
Сб, Янв 28, 2023

This example appeared quite recently on one of the public Oracle Forums, and the person who posted it had already worked out (the important bit of) what was going on before anyone asked for more information or supplied any suggestions for action (although there was one response pointing to [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 27, 2023
На инстансах EBS в зависимости от кол-ва и интенсивности дочерних процессов Concurrent Manager-ов общего назначения (****) можно видеть активные запросы типа: — при этом первые 2 (в этом случае) запроса непосредственно от дочерних процессов Concurrent Manager-ов выполняются со стабильным и совпадающим PLAN_HASH_VALUE: , а запросу с заметным кол-вом выполнений sql_id [...]
Source: iusoltsev.wordpress.com
Чт, Янв 26, 2023

Here are the introductory comments I made at a recent “Ask me Anything” session about indexing arranged by the All India Oracle User Group:

There are 4 fundamental thoughts that you need to bear in mind whenever you're thinking about what indexes your application needs:

The intent of indexing is to [...]
Source: jonathanlewis.wordpress.com
Вс, Янв 15, 2023

Here's a little extract from one of my “snap instance activity stats” packages picking out the figures where the upper case of the statistic name is like ‘%PARALLEL%'. I was very careful that nothing except one of my SQL*Plus sessions had done anything in the few seconds between the start [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 11, 2023

I may have given the answers to this little puzzle elsewhere on the blog already, but if so I can't find where, and it's worth a linttle note of its own.

I have a non-partitioned heap table that is a subset of all_objects, and I'm going to execute a series [...]

Source: jonathanlewis.wordpress.com
Пт, Янв 06, 2023

Here's a query that appeared on the MOS “SQL Performance” forum (needs a support account) with the request: “Can someone please assist me to optimize this query?”

It looks like a nice simple query (though looks can be deceiving) so I thought I'd use it as another example on [...]

Source: jonathanlewis.wordpress.com
Вт, Дек 20, 2022
Если вы не видете список вакансий, возможно у вас включен адблокер. [...]
Source: OracleForBeginners
Пн, Дек 05, 2022

This is nothing more than a basic update of a note that I wrote 8 years ago. The update was triggered by a brief comment made by Martin Widlake at the recent UKOUG annual conference “Breakthrough 2022” in Birmingham. In his presentation on “wide tables”, he mentioned row migration and [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 28, 2022

There's a question on the MOSC forum (needs an account) at present that started with the performance of the datapump API over a database link but moved on to the topic of how to handle a scenario that I've described in the past involving a table where rows [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 21, 2022

An email on the Oracle-L list server a few days ago described a performance problem that had appeared after an upgrade from 11.2.0.4 to 19c (19.15). A long running statement (insert as select, running parallel 16) that had run to completion in 11g using about 20GB of temporary space (with [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 16, 2022

I've just discovered that the space management bitmaps for the tablespace I normally use in my 21c tests are broken. In a tablespace that's supposed to be completely empty a query of dba_free_space shows 4 gaps totalling several thousand blocks:

SQL> select * from dba_free_space where tablespace_name = 'TEST_8K_ASSM'; TABLESPACE_NAME [...]
Source: jonathanlewis.wordpress.com
Вт, Ноя 15, 2022

I wrote a batch of notes about the opt_estimate() hint a couple of years ago, including one where I explained the option for using the hint to specify the number of rows in a query block. I've just come across a particular special case for that strategy that others [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 02, 2022

Oracle 12c introduced Partial indexing and sometimes it works fine for simple partitioned tables with literals, but it has a few problems.

For example, consider the following simple partitioned table:

create table t2 ( pkey int not null, val int, padding varchar2(100) ) partition by range(pkey) ( partition p1_on [...]
Source: orasql.org
Пн, Окт 31, 2022

All Oracle specialists know that any predicate X=NULL can't be true and we should use “X is NULL” in such cases. Oracle optimizer knows about that, so if we create a table like this:

create table tnulls as select level as [...]
Source: orasql.org
Пн, Окт 24, 2022

A tweet from Franck Pachot [July 2021] about fully qualified names in Postgres prompted me to highlight a note I wrote a few years ago about using the label mechanism in Oracle's PL/SQL to avoid collisions between variable names and table names. This led to a brief twitter [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 13, 2022

There's a question on the Oracle developer forum at the moment asking how a tablescan could be reported as taking 94,000 seconds so far when a count(*) shows that it holds only a couple of hundred thousand rows (and it's not storing megabytes of LOB per row if that's [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 11, 2022

I don't really do tips because I often see simple tip that have a specific purpose being mis-used abused; but it's a special day in the Oracle community, so here's a quick tip in tribute to a great sharer.

I've been doing some work with datapump recently, and needed to get [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 29, 2022

A recent question on the Oracle Developer Community forum asked for help with a statement that was taking a long time to run. The thread included the results from a trace file that had been passed through tkprof so we have the query and the actual execution plan with [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 12, 2022

In the past I've sometimes had to dump the contents of the redo log to a trace file when I needed to find out what work Oracle was doing behing the scenes. To minimise the volume dumped by the “alter system dump logfile” command and make it easier to find [...]

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

“Why did my query do parallel?”

It's a question that crops up from time to time, usually followed by a list of reasons why it shouldn't have gone parallel – no hints in the query, table is not declared parallel, parallel_degree_policy is set to manual etc.

When the question appeared recently [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 02, 2022

If you want to do something about “wasted” space in an index what are the differences that you need to consider between the following three options (for the purposes of the article I'm ignoring “rebuild” and “rebuild online”):

alter index xxx coalesce; alter index xxx shrink space compact; alter index xxx shrink space;

Looking [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 22, 2022

Here's a strange problem (with possible workaround) that appeared in a thread on the Oracle developer forum a couple of days ago. It looks like the sort of problem that might be a memory overflow problem in a rarely use code path, given that it seems to need a [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 05, 2022

This note is about some testing I did on the consequences of the (new in 12c) “deferred global index maintenance” feature that Oracle introduced as a strategy to reduce the impact of dropping partitions from a partitioned table.

Looking at my notes I see that created my first test in August [...]

Source: jonathanlewis.wordpress.com
Вт, Июл 26, 2022

This is just a lightweight note on the risks of hinting (which might also apply occasionally to SQL Plan Baselines). I've just rediscovered a little script I wrote (or possibly last tested/edited) in 2007 with a solution to the problem of how to structure a query to use an [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 22, 2022

Prompted by an unexpectedly high CPU usage on a hash join of two large dadta sets Stefan Koehler posted a poll on twitter recently asking for opinions on the following code fragment:

FROM TAB1 INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL1 AND TRIM(TAB1.COL3) > TRIM(TAB2.COL3)

While [...]

Source: jonathanlewis.wordpress.com
Чт, Июл 21, 2022

There's a thread on the MOSC database tuning forum (needs an account) at the moment asking why a “fetch first N” query to fetch next 41 rows with an offset of 8602 rows takes longer to run than the same query when the offset is zero rows. Here's [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 20, 2022

There's a thread on the Oracle developer forum at present asking why calls to dbms_lob.getlength() and calls to dbms_space.space_usage() produce such different results for the storage used by a LOB column.

It's a really good question to demonstrate two points. First that it's hard to supply all the details that [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 18, 2022

A recent thread on the MOS database admin forum (needs an account) demonstrated a number of little issues with bugs, debugging, complex syntax, and the never-ending list of “not quite complete” code that shows up when features (in this case LOBs and partitioning) collide.

It's a silly little thing, but [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 15, 2022

This is a little case study on working around a problem caused by a 3rd-party application that couldn't be changed quickly and easily. There's a touch of irony in this case as the problem I'll address is a side effect of the best possible workaround to a more generic design [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 08, 2022

This is the follow-up to an initial post that covered some details of using the errorstack and ksq traces as and aid to finding the cause of an intermittent ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. We were (hypothetically) looking at a scenario where a [...]

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