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

19 Февраль 2015
Earlier
Пн, Окт 22, 2018

Sometimes a good thing becomes at bad thing when you hit some sort of special case – today's post is an example of this that came up on the Oracle-L listserver a couple of years ago with a question about what the optimizer was doing. I'll set the scene [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 19, 2018

The following request appeared recently on the Oracle-L mailing list:

I have one scenario related to capturing of sql statement in history table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or more.. How do I get the sql stmt which took less time say [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 17, 2018

Here's a little question that popped up on the Oracle-L list server a few days ago:

I am facing this issue running this command in 11.2.0.4.0 (also in 12c R2 I got the same error)

SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL; SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, [...]
Source: jonathanlewis.wordpress.com
Пн, Окт 15, 2018

This is a short index of articles I've written on how to create the different types of histogram that the optimizer uses:

Faking a frequency histogram How to create frequency histograms (using a numeric column for the example) Histogram Tip An example of creating a simple [...]
Source: jonathanlewis.wordpress.com
Вс, Окт 14, 2018

When my mother-in-law comes round to Sunday lunch we often have roast chicken – and a serious error in estimating the requirement for the vegetable bed I was roasting on led to the discovery of home-made soup. (I did warn you that my post-operative posts would be light-weight)

Ingredients Remnants of cooked [...]
Source: jonathanlewis.wordpress.com
Пт, Окт 12, 2018

I've been a long time, though occasional, user of the undocumented dbms_system package, typically using it to write messages or insert break marks in trace files (or the alert log). Thanks to an email from Cary Millsap I've recently discovered that the procedures for writing to trace files have been [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 10, 2018

Oracle 12c introduced the “Hybrid” histogram – a nice addition to the available options and one that (ignoring the bug for which a patch has been created) supplies the optimizer with better information about the data than the equivalent height-balanced histogram. There is still a problem, though, in [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 10, 2018

By the time you read this I will have had the lenses in both my eyes replaced, so I won't be staring at a computer screen for a while – and that means, in particular, I won't be doing any further investigation into join cardinality for a while.

For those who [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 09, 2018

In the previous posting I listed the order of precision of histograms as:

Frequency Top-Frequency Hybrid Height-balanced None

Having covered the Frequency/Frequency join (for a single column, no nulls, equijoin) in the previous posting I've decided to work down the list and address Frequency/Top-Frequency in this posting. It gets a little harder to generate data [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 09, 2018
Conditional unique index on multiple columns

When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.

What is a Function-Based Index?

A function-based index is created on the value of [...]

Source: www.oratable.com
Пн, Окт 08, 2018

Here's a problem that (probably) won't affect the day to day running of most systems – but it could be a pain in the backside for people who write programs to generate repeatable test data. I'm not going to say much about the problem, just leave you with a test [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 05, 2018

In the previous note I posted about Join Cardinality I described a method for calculating the figure that the optimizer would give for the special case where you had a query that:

joined two tables used a single-column to join on equality had no nulls in the join columns had a perfect frequency [...]
Source: jonathanlewis.wordpress.com
Ср, Окт 03, 2018

Following up my “Hacking for Skew” article from a couple of days ago, Chinar Aliyev has written an article about a method for persuading the optimizer to calculate the correct cardinality estimate without using for any undocumented, or otherwise dubious, mechanisms. His method essentially relies on the optimizer's [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 03, 2018
Java Stored Procedures in Oracle

Some solution requirements are best addressed with Java code in the Oracle database. This article shows you use cases where Java methods in Oracle are useful, how Oracle lets you store Java methods and how they can be called from PL/SQL.

Java [...]
Source: www.oratable.com
Вс, Сен 30, 2018

A question about reading execution plans and optimising queries arrived on the ODC database forum a little while ago; the owner says the following statement is taking 14 minutes to return 30,000 rows and wants some help understanding why.

If you look at the original posting you'll see that we've [...]

Source: jonathanlewis.wordpress.com
Пт, Сен 28, 2018

In my presentation to the UKOUG SIG yesterday “Struggling with Statistics – part 2” I described a problem that I wrote about a few months ago: when you join a fact table with a massively skewed distribution on one of the surrogate key columns to a dimension holding the unique [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 27, 2018

I seem to have written a number of aricles about column groups – the rather special, and most useful, variant on extended stats. To make it as easy as possible to find the right article I've decided to produce a little catalogue (catalog) of all the relevant articles, with a [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 12, 2018

A little while ago I added a post-script to a note I'd written five years ago about gathering stats on a virtual column and had updated with a reference to a problem on the Oracle database forum that complained that stats collection had taken much longer after the addition [...]

Source: jonathanlewis.wordpress.com
Пн, Сен 10, 2018

I wrote a note a couple of years ago explaining how I used to get a rough idea (with some errors) of how much time was spent in the overnight stats collection by each object. One of the nice little enhancements that appeared in 12c was the appearance of [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 05, 2018

From time to time I've wanted to optimize a query by forcing Oracle to execute existence (or non-existence) subqueries in the correct order because I know which subquery will eliminate most data most efficiently, and it's always a good idea to look for ways to eliminate early. I've only just [...]

Source: jonathanlewis.wordpress.com
Ср, Сен 05, 2018
Джойны - это запросы, комбинирующие строки из нескольких таблиц, представлений или материализованных представлений, которые перечислены в секции FROM запроса.

INNER JOIN
Inner join или просто join возвращает только те строки из двух таблиц, которые удовлетворяют условию указанному в ON:

SELECT
s.id,
s.supplier_name,
[...]
Source: OracleForBeginners
Пн, Сен 03, 2018
REGEX to Split String into Rows

A typical scenario that involves splitting a comma-separated string into rows:

in a UI-driven query, the user would enter a list of IDs into an input box, and the application should retrieve details for the input list of IDs

The [...]

Source: www.oratable.com
Ср, Авг 29, 2018

My entrance at the Polish Oracle User Group conference 2017 has just resurfaced on Twitter. There is a back-story to this which contains an allegorical lesson in using Oracle. As I said in the opening remarks in the subsequent presentation: “The lesson began before I got to the stage”.

Like [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 24, 2018

Error logging is a topic that I've mentioned a couple of times in the past, most recently as a follow-up in a discussion of the choices for copying a large volume of data from one table to another, but originally in an addendum about a little surprise you [...]

Source: jonathanlewis.wordpress.com
Ср, Авг 22, 2018

Following on from Monday's posting about reading execution plans and related information, I noticed a question on the ODC database forum asking about the difference between “in ({list of values})” and a list of “column = {constant}” predicates connected by OR. The answer to the question is that [...]

Source: jonathanlewis.wordpress.com
Пн, Авг 20, 2018

A recent thread on the Oracle developer community database forum raised a fairly typical question with a little twist. The basic question is “why is this (very simple) query slow on one system when it's much faster on another?” The little twist was that the OP was using “Streams [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 17, 2018
Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы: — при этом для некоторых matview Cardinality вычислялась с двойной точностью [...]
Source: iusoltsev.wordpress.com
Пт, Авг 17, 2018
Поскольку некоторые инструменты разработки генерируют связанные переменные вида :"1", :"2",.., а некоторые классы запросов могут по факту вытолнятся только на Readonly Standby, не присутствуя на Primary ни в каком виде, для фиксации/оптимизации плана бывает необходимо каким-то образом поместить такой запрос (sql_id) в Primary Shared Pool, что можно сделать стандартным способом [...]
Source: iusoltsev.wordpress.com
Пн, Авг 06, 2018
Post image for How to Install and Configure SQL*Plus Instant Client on Windows

GUI tools like TOAD and SQL Developer are all fine, but those who have cut their teeth on SQL*Plus will not do without the good old command-line interface. And so, a message like the one below [...]

Source: www.oratable.com
Чт, Авг 02, 2018

Following on from the previous posting which raised the idea of faking a frequency histogram for a column group (extended stats), this is just a brief demonstration of how you can do this. It's really only a minor variation of something I've published before, but it shows how you [...]

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