Recently I’ve seen not so smart optimizer behavior: one query took long time to parse, and ended with an error hitting PGA_AGGREGATE_LIMIT in few minutes; another query was just parsed for ages while using reasonable (under 2G :)) amount of PGA and still could hit PGA_AGGREGATE_LIMIT but after way more time – up to an hour.
Both cases were similar and involved queries which were accessing views; and those views’ code is generated by an (more...)
I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when (more...)
I’m going to Hotsos 2016 soon for the first time. I’ll speak about Middleware and the issues I see commonly everywhere. It took me quite some time to prepare for this trip: budget, visa, and, most importantly, presentation – required at least 120 hours of my time (which is a lot). In fact presentation is still work in progress and I hope to finish it just before March 8 when I’m presenting. I feel a (more...)
Recently while observing AWR reports, I’ve seen a very good example of how average value hides important pattern.
Here is a Workload Comparison section from an AWR diff report (generated with $ORACLE_HOME/rdbms/admin/awrddrpt.sql):
~~~~~~~~~~~~~~~~~~~ 1st Per Sec 2nd Per Sec %Diff 1st Per Txn 2nd Per Txn %Diff
--------------- --------------- ------ --------------- --------------- ------
DB time: 0.6 0.6 1.6 0.0 0.0 -50.0
CPU time: 0.3 0. (more...)
If you missed the interview with Jonathan Lewis or don’t want to spend 93 minutes watching it, here is its transcript. Thanks to Stanislav Osekin from Innova, who did most of the hard work. I have only corrected special Oracle terms. Also thanks to Jonathan, who helped me in several (more...)
If you follow my Twitter you probably have heard that Jonathan Lewis visited Moscow in May this year. Thanks to Innova, Jonathan gave a 1-day seminar, and I was one of the lucky people from Russian Oracle community who have been invited to the event.
Even more, I was (more...)
Continuing from the previous post, here is one more case when adjusting optimizer_index_cost_adj may hurt you.
drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id, x))
, mod(rownum, 10)
from all_source s1, all_source s2
Those who visit SQL.ru often know what the title means. It’s a very simple yet powerful technique to group data which doesn’t seem appropriate for grouping at first sight.
I learned this very nice tip long time ago but often have to re-think before applying it to SQL or (more...)
Recent thread in the OakTable mailing list prompted me to create a poll and ask about the ways DBAs use system statistics in real systems. If you struggle to understand what system statistics is and what are the available options, here is the suggested reading:
Documentation – System Statistics
A hint is an instruction to the optimizer
This is what’s written in Oracle documentation. Instruction is defined as
a code that tells a computer to perform a particular operation
Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly (more...)
Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.
Here’s a simple table T1 holding 1M rows with the indexed column X (more...)
Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:
At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:
On December 11th Tom Kyte performed “AskTom in Moscow” for the second time (first time was almost three years ago in February 2010). I was there and enjoyed presentations, tweeted a lot about the content and in the end I’ve won a signed copy of the Expert Oracle Database Architecture (2nd edition, in Russian)!
A glass for questions. It was full in the end of the day
Tom started with a bit of history of how Oracle developed its database. Old stuff, C compilers, 37kg disk drives followed by the modern, upcoming features in 12c. Pluggable Databases mostly (more...)
Lately I’ve been using SQL runtime execution statistics combined with SQL monitor for performance diagnostics and, honestly, almost forgot about SQL trace. So this note is not very useful to me but it might be to someone: along with ALL_EXECUTIONS option appeared in (I believe) 11gR2, there’s a new option starting with 184.108.40.206 which can significantly decrease amount of data in the trace files for STAT lines compared to ALL_EXECUTIONS, still providing some of them from time to time.
Here is a case to demonstrate:
n number := 0;
dbms_session.session_trace_enable(true, true, 'all_executions');
for x (more...)
I have a permanent job at the NetCracker‘s System Performance group. Recently I was offered to do one day job outside, on-site in another company, which coincidentally has an office close to NetCracker’s Moscow office. It was an opportunity to apply my skills in a completely different situation which I couldn’t miss; plus I’ve never done public presentations before and this was a good occasion to practice that. Here I’d like to write down some notes how the event went.
2 or 3 weeks before the event I’ve got the list of topics to work on. It included several (more...)
Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the SQL.ru Oracle forum reminded me of this issue.
Basically OP has a query with disjuncted (OR-ed) predicate which started to fail after 220.127.116.11 upgrade with ORA-01790: expression must have same datatype as corresponding expression. Here is a test case (I’ve renamed column and table names cause I’ve used to such naming):
drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;
create table t1 (
x int not null,
Flashback query allows to get data as of required point in time. It’s a nice feature. It’s strange in the implementation though. If you try to use AS OF SCN query with bind variables, it won’t help you to keep number of child cursors low: each execution (independently of the incoming SCN value) will cause a new child cursor to appear with FLASHBACK_CURSOR as the cause. I don’t understand why Oracle is doing it this way.
In the OakTable mailing list there was an idea that it could be due to some additional data stored alongside with the child cursor (more...)
I’ve just noticed that the new HTML MOS introduced a nice addition to the old ORA-600/ORA-7445 Error Look-up Tool: now there is a new ORA-600 Troubleshooting Tool (if the link is not opened automatically for you try refreshing the page with F5 – works for me in Chrome).
Within a few minutes it allows you to upload a generated trace file and then automatically redirects you to the search results based on the error reported in the trace. The tool should be useful to those who don’t know what to do with the trace. It also allows you to (more...)
Thanks to everyone for spreading the link and participating in the poll on statistics. As it was pointed out in the comments, the poll isn’t very specific, I know. My point was to get an idea how many people use the defaults (I considered Oracle version 11g) for stats collection, how many do a very simple and reasonable tweak to get more stable execution plans, and how many people prefer custom statistics management. Well, I think the results will not change much and here are as following (326 votes at the time of writing):
This is a short note on the topic of information exchange. I use OraNA.info to get up-to-date technology news. This is a blog aggregator I’ve tried first and have been using it since then, which is 3-4 years I guess. It’s great and I’d like to use this opportunity to say Thank You to Eddie Awad for a great blogs collection!
If you still don’t use blog aggregators and rely on your own blog collection in Google Reader or something else, then consider using one entry point instead of multiple, it’s much easier. Also I’d like to suggest using (more...)