Oracle Database supports modern ways of authentication using some of the cloud directories – OCI and Azure. I’ve tested Azure AD and set up authentication-only integration of an Oracle Autonomous DB (always free tier) with Azure AD (no special subscriptions as well). Continue reading →
Kyle Hailey wrote a blog Most important performance metric. It’s about Average Active Sessions as the most important database performance metric. I’ve read it and twitted my concerns. Since Twitter reply is limited, here are a few more thoughts on this topic.
90% databases that I see for the first time have the same issue with UNDO tablespace: it’s over sized, yet still causing infamous ORA-1555 errors at times. Here is why.
Last year I’ve been to a few conferences. At some point I thought I need to record some of the sessions to let more people see them as well.
So I took a cheap action camera & recorded several presentations. Video quality is not good (mostly) due to lighting but still is enough to get an idea of how was it back there in a room.
Here’re links to the videos. Enjoy!
Few days back I was (again) angry at My Oracle Support “The page has expired” message appearing in an open tab I left for some time. I tried to find out how to avoid it, and it was relatively easy to do even though I don’t know JavaScript. Then I tried to make a plugin out of it so I it can be on by default & won’t need my attention.
Here it is: plugin for Chrome. It took me 2 seconds to come up with a name – MOS-cow and about 3 hours to prepare plugin.
Then I realized that having such plugin independently is probably not a good idea (it’s only for Chrome, and there are other browsers), and it may be enough to just add a custom JavaScript to Tampermonkey so it’s possible to use in almost any browser. So here it is. I don’t plan to add any new features at the moment, but maybe later. I know what else is needed but have no idea how much time it would take to implement, probably too much, which is not an option.
Anyway, I hope this little stupid piece of JavaScript helps you!
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.
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 call for papers was still open, I realized that there’s a good chance I can make it to Hotsos in 2016: I had a budget, visa is a doable thing, and most importantly I knew I had a topic to talk about which most likely will not be in competition with other speakers.
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 bit nervous about my topic since the conference is all about Database Performance, the quality of presentations is always high and they are super technical, while I’m planning to have a light talk focused not on the Database mainly – just my rants on the things done wrong I usually see in the wild everywhere. It’s not something on the cutting edge, and some of my notes are so bloody old. Anyway I have 1h available and will try to spend it with pleasure saying anything I want (like “Hello Mom”). In the worst case I’ll never be accepted again there, which is bad but not the end of the world, right?
Anyhow first I need to survive tight connection at Frankfurt this Saturday, plus jet lag and then enjoy almost a week of fun.
See you in Dallas!
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 complex cases where I couldn’t understand the wording.
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 offered to take an interview with Jonathan which was recorded! It was my first time interview and I was very excited (long before the recording) and scared to death (right in front of the camera) so please forgive me for speaking like a dull. I know that, and it almost doesn’t matter. What matters is Jonathan was brilliant and so excited at times, that 90+ minutes of sitting in a cold room right under air conditioner for me flew like a 1 minute.
Well, enough the words, just enjoy it (this is uncut version recorded in 1 go by the way)!
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 even search for the correct way of doing it when my brains give me denial of service. Recently I needed to do this type of query again and thought I should document the process, so I won’t forget it next time.
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 and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.
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.
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:
The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks
That’s the key, the statistics are the same, except for the number of blocks. Which effectively means, you can gather the statistics on the specific X$-table in the same way as you do with a normal table, by using DBMS_STATS.GATHER_TABLE_STATS procedure. The only difference would be you will not populate the number of blocks (but you can do this manually). Now, since these statistics are essentially the same as statistics for normal tables, it makes no sense to run the GATHER_FIXED_OBJECTS_STATS procedure on all RAC nodes. After all, the database is still the same and it doesn’t care about the table statistics in relation to RAC node (at least for now in 11g).
I wish I was that smart back then 🙂 I’ve found the answers recently via MOS notes updates: In RAC, Should We Execute GATHER_FIXED_OBJECTS_STATS And Gather_dictionary_stats In Both Nodes? [ID 1479804.1] which gives the definitive answer “No” and Why do some Fixed Tables not have Optimizer Statistics [ID 1355608.1] which explains that some fixed tables cannot have optimizer statistics by design decisions made by Oracle.
What I don’t understand after getting better idea of fixed stats is why Oracle made the choice to introduce a specific new procedure, without any parameters/configuration options if they could have better done it inside the standard procedure for statistics gathering? To me it looks very strange and I would prefer not to introduce one more complication to the already over-complicated statistics package.
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)!
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 11.2.0.2 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.