One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views. Let’s look at an example
SQL> create or replace
2 procedure do_stuff is
I’ve been going through some SERIOUS training in just over a week. This training has successfully navigated the “Three I’s”, as in its been Interesting, Interactive and Informative. The offerings are very complete and the knowledge gained is limitless.
I’d also like to send a shout out to Steve Karam, Leighton Nelson and everyone else at Delphix who’s had a hand in designing the training, (more...)
From time to time a question comes up on OTN that results in someone responding with the mantra: “Never do in PL/SQL that which can be done in plain SQL”. It’s a theme I’ve mentioned a couple of times before on this blog, most recently with regard to Bryn Llewellyn’s presentation on transforming one table into another and Stew Ashton’s use of Analytic functions to solve a problem that I got stuck with.
If you care, you’ve probably heard the UK voted to leave the European Union (EU) yesterday. Suffice to say I’m gutted!
I’ve just deleted most of the content this post because it contained a lot of inflammatory and negative comments. I could question the motives of the leavers, but what good would that do now. Suffice to say, IMHO this is a dark day for the UK.
We had an interesting question on AskTom a few days ago. Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period. Now it is perhaps a little dubious to “invent” detailed data out of summarised data, but we can come up with a reasonable algorithm for doing so.
In the previous parts I talked about database security and the infrastructure part of database security. In this post I will discuss the development aspect of the database. Links to the previous posts: Part 1 – general introduction to security Part 2 – introduction to database security Part 3 – infrastructure aspect of database security […]
I have recently taken over our Systems Management team and we have several vacancies for people used to working with the HP toolset. I know my blog is read almost entirely by DBAs but I am sure some of you work with Systems management teams and hopefully you can pass a link on to anyone you think might be interested.
An odd little anomaly showed up on the OTN database forum a few days ago where a query involving a table covered by Oracle Label Security (OLS) seemed to wrap itself into a non-mergeable view when written using traditional Oracle SQL, but allowed for view-merging when accessed through ANSI standard SQL. I don’t know why there’s a difference but it did prompt a thought about non-mergeable views and what I’ve previously called “conditional SQL” – (more...)
Today I’d like to share another tuning example from a recent case at work, which in my opinion is good for illustrating typical steps involved in SQL optimization process.
I was handed a poorly performing query with a relatively verbose text, so I will only give the general structure here (it will also prevent me from accidentally disclosing some sensitive information from that application):
/* long list of columns here */
(select * (more...)
One of the more “weighty” questions within the IT world is in reference to the value of each company’s particular data.Many wonder what the true value of protected data is in the long-run, eventually view it as a cost center where money continuously gets used up.In order to make data work in the favor of a business and to help generate some income, companies must get smarter with their approaches to (more...)
It’s a maintenance release that fixes a bunch of nasty things. If your site hasn’t already auto-updated, you should probably log on and give it a nudge. I’ve got 5 different installations and all auto-updated successfully.
Time Slider is a feature in Solaris that allows you to open past versions of your files.
It is implemented via a service which creates automatic ZFS snapshots every 15 minutes (frequent), hourly, daily, weekly and monthly. By default it retains only 3 frequent, 23 hourly, 6 daily, 3 weekly and 12 monthly snapshots.
I am using Time Slider on several Solaris 11.x servers and I found the same problem on all of them (more...)
After what seems like eons since we first started on it, I’m excited to announce a new book I co-authored is finally out. The book is called “Building Database Clouds in Oracle 12c” and is available on Amazon. Of course, it really isn’t that long ago that we started writing the book, but there’s been a lot happening between then and now!
The book was co-authored with Tariq Farooq and Sridhar Avantsa. Tariq asked me (more...)
The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.
The question raised by the title of this post is: in case cursor sharing is enabled, does literal replacement (more...)
The next in Richard Foote’s popular Let’s Talk Database series – Let’s Talk Database: Oracle Database 12c – Built for Data Warehousing – is on in Canberra on July 28th. These are free events but due to limited places have often “sold out” in the past, so booking early is recommended to avoid disappointment.
The Oracle Database is the leading database in market but it (more...)
As you probably know by now, I’m a consultant. By consultant I don’t mean a person who sits in meetings and talks all day (even though I like to talk), I mean a professional services guy who can do DBA tasks and can design and execute projects. In many cases companies prefer to hire someone […]
There are a number of articles, webinars, and blogs online about how to read execution plans, but many of them seem to stop after the the minimum description of the simplest type of plan, so I thought I’d throw out a brief comment on a couple the slightly more complicated things that are likely to appear fairly commonly because you sometimes find plans with very similar shapes but extremely different interpretation.