This morning a colleague of mine noticed an alert from our plan change monitor and prevented any impact on the users by forcing a SQL’s plan to its normal fast plan before the users could be affected. I want to share the monitor script and describe how we use its output.
I know this is long but I want to paste the SQL text of the monitor script here. Review my comments on each step. (more...)
This is the presentation I gave at the Scotland OUG back in June 2015 (Yes, I know I’m late).
The presentation was interrupted by a fire alarm (you can read all about it here).
This is a customer case study on a proof of concept we did with a customer a while ago. The PoC was successful and we are now waiting for the application to catch up and go to Oracle 12c so (more...)
I have two last debuts from the Scotland OUG meeting – the presentations.
First, the SQLcl presentation and the demo.sql file to run the things I showed there.
I thank everybody who found the time to come hear me talk and I hope you enjoyed the presentation.
Direct access on SlideShare: http://www.slideshare.net/zohare/sqlcl-the-next-generation-of-sqlplus
alias cdcl='cd /u01/app/oracle/sqlcl/bin/'
-- ## connection
I’ve beeen a little slow in the follow-up to my previous posting on possibly redundant indexes. Before going into the slightly more complex stuff, there’s another peripheral point (but a very important one) that’s worth raising about how clever the optimizer can be. Here’s some code for 18.104.22.168 to demonstrate the point:
create table t1
with generator as (
select --+ materialize
level <= (more...)
I discovered ASM with a 10.1.0.3 RAC running on Linux Itanium and that was a big adventure. At this time there was no asmcmd. In 2005, Oracle released Oracle 10gR2 and asmcmd came into the place and we figured out how to make it work with a 10gR1 ASM. We were very excited to have a command line for ASM until… we tried it ! let’s call a spade a spade, it was very (more...)
Running a SQL trace is something that all DBAs do to varying degrees. Let’s say you are working on optimizing a SQL statement, and experimenting with some different hints for indexes and optimizer directives. This kind of effort typically goes something like this:
- modify the SQL statement
- enable tracing
- run the statement
- disable tracing
- retrieve the trace file
- use a profiler to process the trace file
this might be Method-R mrskew,Oracle tkprof, or (more...)
Followers of the blog will know I’ve been waiting to get access to the Oracle Cloud for a while. Well, I’ve finally got access to a bit of it. Specifically, the “Oracle Database Cloud Service” (DBaaS) part.
The Schema Service has been around for a few years and I had already tried that out, but IMHO it’s not really part of Oracle Cloud database vision*, so I was reserving my judgement until I got the real (more...)
We can’t help attaching meaning to optimizer statistics. After all, they represent our data, don’t they? So we refresh them as frequently as possible and use the largest sample sizes that we can use. Recently, for the first time in my life, I encountered a group of DBAs who understood that statistics do not have any intrinsic meaning; that they are nothing more than a collection of numbers that influence the generation of query plans. (more...)
I’ll be presenting at a “Lets Talk Oracle” event in Perth, with fellow Ex-Oracle ACE Directors Richard Foote and Chris Muir. Full agenda as follows:
8:30-9:00 Registration and coffee
9:00-10:30 Richard Part I – Database 12c New Features for DBAs (and Developers)
11:00-12:30 Richard Part II – Database 12c New Features for DBAs (and Developers)
1:30-2:30 Chris – Creating RESTful APIs with Oracle Data Services (for Developers and DBAs)
I’ve said a number of times, the process of writing articles is part of an ongoing learning experience for me. A few days ago my personal tech editor (Jonathan Lewis) asked about a statement I made in the SQL Plan Directive article. On further investigation it turned out the sentence was a complete work of fiction on my part, based on my misunderstanding of something I read in the manual, as well as the assumption that (more...)
it’s been a couple of months since my session at the oug_scot and I feel it’s enough time to write about it…
First of all – it was awesome!
I have never been to a user conference other than the ilOUG in Israel. I went to the Oracle Open World couple of years ago (and I’m not going this year ) but it was nothing alike…
The user group was at a lovely place near (more...)
so after much too-ing and fro-ing with Oracle Support, we finally have a solution to my datapatch problem. Prevent the timeout from happening when running datapatch! We know which part of the code was timing out, and we can do this by setting an undocumented event: 18219841.
Here’s a show test to show you how that was done:
SQL: select dbms_sqlpatch.verify_queryable_inventory from dual;
ORA-20008: timed out. Job Load_opatch_inventory_1execution time is more than (more...)
Step 1: connect as a database os user
su – oradev
startDB.sh edit and saved
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh start EBSDEV
stopDB.sh edit and saved
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh stop EBSDEV
give execute permission chmod 750 for both file
Step 2: connect as applmgr os user
startAPP.sh edit and saved
"Let me try once more," he said in an effort to explain. "In other words--"
"You mean you have other words?" cried the bird happily. "Well, by all means, use them.
You're certainly not doing very well with the ones you have now."
- Norton Juster, The Phantom Tollbooth
A seemingly constant battle in many forums is getting those who post questions to be clear in what they are asking. Some questions are (more...)
I wrote about this feature in Hebrew quite some time ago but in the last week I was asked by couple of my customers about the correct way to move datafiles with minimal downtime so I thought it would be nice to show the Oracle 12c solution before talking about the solution I gave them.
For years, moving datafiles was a pain in the DBA behinds. It required some database action, then some OS actions (more...)