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

19 Февраль 2015
Earlier
Вт, Фев 16, 2021

There's a thread on the Oracle Database Forum at present where someone has supplied a script to create some data that's guaranteed to reproduce wrong results (provided your system stats and optimizer parameters are at their default values). They've even supplied a link to the [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 01, 2021

Here's a quirky little detail – probably totally irrelevant to virtually everyone – that came up in a question on the Oracle Developer Forum a couple of days ago. It concerns the way Oracle stores and records default values for columns, and it also does a hat-tip to the [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 01, 2021

I've hardly ever touched the pivot/unpivot feature in SQL, but a recent comment by Jason Bucata on a note I'd written about Java names and the effects of newer versions of Oracle allowing longer object and column names prompted me to look at a script I wrote several [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 26, 2021

At the end of the previous post on index hints I mentioned that I had been prompted to complete a draft from a few years back because I'd been sent an email by Kaley Crum showing the optimizer ignoring an index_rs_asc() hint in a very simple query. Here, [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 25, 2021

I've lost count of the number of times I've reminded people that hinting (correctly) is hard. Even the humble /*+ index() */ hint and its close relatives are open to misunderstanding and accidental misuse, leading to complaints that “Oracle is ignoring my hint”.

Strange though it may seem, I'm still [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 20, 2021

This is a list of possible explanations of errors that you might see in the Hint Report section of an execution plan. It's just a list of the strings extracted from a chunk of the 19.3 executable around the area where I found something I knew could be reported, [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 20, 2021

A little case study based on an example just in on the Oracle-L list server. This was supplied with a complete, working, test case that was small enough to understand and explain very quickly.

The user created a table, and used calls to dbms_stats to fake some statistics into place. [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 18, 2021

Here's an anomaly that appeared in a question on the ODC recently about tweaking the output of dbms_metadata.get_ddl(), As the title suggests, though, this posting isn't about the dbms_metadata package it's about supplemental logging and something that should not to be possible and may have unfortunate side effects.

We start with [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 14, 2021

Reading Richard Foote's latest blog note about automatic indexing and “non-equality” predicates I was struck by a whimsical thought about how the optimizer handles “between” predicates. (And at the same time I had to worry about the whimsical way that WordPress treats “greater than” and “less than” symbols.)

It's probably [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 13, 2021

This is a note I drafted in 2018 but never got around to publishing. It's an odd quirk of behaviour that I discovered in 12.2.0.1 but I've just checked and it's still present in 19.3.0.0.

Here's a funny little thing that I found while checking some notes I had on adding [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 07, 2021

I created this simple service a couple of years ago. It's pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync

[...]
Source: orasql.org
Ср, Дек 23, 2020

There are times when an upgrade makes a big difference to performance because an enhancement to the optimizer code path changes the costing of a plan that was always possible, but badly costed. I noticed an example of this while testing the code in the email I mentioned in last [...]

Source: jonathanlewis.wordpress.com
Ср, Дек 23, 2020

Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text's from v$sqlarea. And thanks to Oracle SqlCL and [...]

Source: orasql.org
Пн, Дек 21, 2020

Here's a little puzzle – and if you don't see the answer almost immediately you did need to read this note. The question comes from a thread on the Oracle Groundbreakers' Forum –

“Why I am getting 0020 instead of 2020 for below query?”

select to_char(to_date(sysdate,'dd-mm-yyyy'),'iyyy') from dual.

Instead of sysdate [...]

Source: jonathanlewis.wordpress.com
Пн, Дек 21, 2020

Sometimes it's the little things that catch you out (perhaps only briefly) on an upgrade. Here's one that came up on Oracle's Groundbreakers Developers Community [sic] (who knows what it will be called this time next year and, while we're at it, who can tell where the apostrophe(s) [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 11, 2020

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution with v(start_hsecs, delta, flag) as ( select hsecs as start_hsecs, 0 as delta, 1 as flag from v$timer union [...]
Source: orasql.org
Ср, Дек 09, 2020

This note is a list of links to the presentations that Bryn Llewellyn gave to the Swedish Oracle Users Group in a full-day seminar on EBR in 2017. I've had it as a draft note on my blog for some time, but have just revisited it and thought that others [...]

Source: jonathanlewis.wordpress.com
Вт, Дек 08, 2020

This is a follow-up to a note that's 10 years old [opens in new tab], prompted by a thread on the Oracle Developer Community forum asking about the order of operation in an execution plan, and following up with a question about PGA memory [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 30, 2020

If you're like me there are probably lots of little things that you know but find terribly easy to forget. Here's one of my bêtes noires – starting with a little quiz:

rem rem Script: lob_in_row.sql rem [...]
Source: jonathanlewis.wordpress.com
Чт, Ноя 26, 2020

Several years ago I published a couple of examples of scripts that I'd been using since Oracle 6 (with minor changes over time) to analyse the content of tables in some detail. The first was a breakdown of the lengths of the rows in the table, the second [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 26, 2020

Many years ago Tom Kyte published a small PL/SQL procedure to output each row in a table as a list of (column_name , value). I often use the original version, even though Tom refined it several times over the years. From time to time the links in my blog [...]

Source: jonathanlewis.wordpress.com
Чт, Ноя 12, 2020

Here's a little demonstration of the benefit of rowsets. It started with a very simple question that arrived in my in-box from Kaley Crum.

“Why does the query with the UNION ALL take so much longer than the query without the UNION ALL?”

Here are the two queries – [...]

Source: jonathanlewis.wordpress.com
Ср, Ноя 11, 2020

This is one of those notes on the “thinking about the data / indexes” theme that I like to write; it's a draft I wrote over two and a half years ago that I've just rediscovered and polished slightly and refers to a “recent” question that came up on [...]

Source: jonathanlewis.wordpress.com
Вт, Ноя 10, 2020

Here's a fun little item that I've just rediscovered from 2016.

There's a fairly commonly cited query that aggregates and tabulates the first_time column from the v$log_history view by hour of day, reporting the hour across the page and the date down the page. It often appears in the responses to [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 09, 2020

Here's a lovely little mechanism new to Postgres 13 that can minimise sorting costs: the “incremental sort”. It would be nice to see it in Oracle as well as it could make an enormous difference to “fetch first N” queries.

The concept is simple – if a rowsource moving up [...]

Source: jonathanlewis.wordpress.com
Вт, Ноя 03, 2020

This note has has been sitting with the other 800 drafts since some time in May 2019, and started with a comment about following on from “a recent talk on how to engineer indexes properly”. Unfortunately I don't remember when I wrote it, or why it came about.I mention this [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 21, 2020

Here's an odd little detail about the statistics of column groups. At first glance it's counter-intuitive but it's actually an “obvious” (once you've thought about it for a bit) consequence of the approximate_ndv() algorithm for gathering stats.

I'll present it as a question:

I have a table with two columns: [...]

Source: jonathanlewis.wordpress.com
Сб, Окт 10, 2020

Interval partitioning is a popular strategy for partitioning date-based data. It's an enhanced variant of range partitioning that allows you to define a starting partition and an interval that should be used to derive the high values for all subsequent partitions – and Oracle doesn't even have to create intervening [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 09, 2020

If you've ever used subquery factoring (“with” subqueries or common table expressions (CTEs) as they are often called) then you're probably aware of the (undocumented) hints /*+ materialize */ , which forces Oracle to create a local temporary table to hold the result of the subquery for subsequent use, [...]

Source: jonathanlewis.wordpress.com
Чт, Окт 08, 2020

This is a little addendum to a note I wrote a couple of days ago about serial direct path reads and KO (fast object checkpoint) enqueue waits.

The original note was prompted by a problem where someone had set the hidden parameter “_serial_direct_read” to ‘always' because there were running 11g [...]

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