Plan change monitor prevents user impact from bad plan

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...)

Raspberry Pi Controlled Airplane Launcher

Everything I do with the Raspberry Pi is about finding ways to incorporate STEAM, (Science, Technology, Engineering, Art and Math) into my projects.  This is no easy feat, but I have a lot of fun while I try.  This latest project, we’re still working on ways to “enhance” and understand how code can be used with multiple projects and to satisfy multiple requirements.

Science Kit Projects, to the Nth Level

Our first project used DC (more...)

oug_scot: Oracle DB In Memory Presentation

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...)

oug_scot: SQLcl the next generation of SQLPlus Presentation

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

Embedded:

alias cdcl='cd /u01/app/oracle/sqlcl/bin/'
cdcl

-- ## connection
sql /nolog
connect hr/hr
connect hr/hr@[tab]
connect hr/hr@192.168.56.102:1521/pdb1
show  (more...)

Index Usage – 2

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 11.2.0.4 to demonstrate the point:

create table t1
nologging
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <=  (more...)

Creating a Trace File from EM12

Creating a trace file from EM12c is quite easy and doesn’t require a DBA offering up the world to allow a developer or support person to perform this action.

Enterprise Manager also removes some of the syntax options that could get a developer or app support person that is less specialized in creating trace files, all the options and the knowledge of the syntax, etc. from getting into trouble, which creates an option for everyone to be successful.

(more...)

Add Color to your SQL

Topic: this post is about some simple techniques to add color to SQL scripts and their terminal output using ANSI escape codes.

Colors can be used to improve the output of command line tools. This is common practice, for example with the (bash) shell. Colors can also be very useful to improve the quality of the output of SQL scripts. In my experience this is not used frequently, probably because of the the need of (more...)

amcmd> a better “du”

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...)

Simplify Oracle Tracing with Creative Scripting

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
  • disconnect
  • retrieve the trace file
  • use a profiler to process the trace file
    this might be Method-R mrskew,Oracle tkprof, or (more...)

Oracle Cloud : First Impressions

cloudFollowers 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...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 39: Unfriending the optimizer statistics

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...)

Creating User Schema Table and Projections in Vertica

Vertica is a an exciting database with some real nifty features. Projections is a ground breaking unique feature of Vertica which dramatically increases performance benefits in terms of querying and space benefits in terms of compression.



Following test commands are impromptu sesssion in which a user is being created, then a schema is created, and that user is authorized on that schema. Then a table is created with a default superprojection and then a projection (more...)

Presenting in Perth on September 9th, 2015

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)
10:30-11:00 Break
11:00-12:30 Richard Part II – Database 12c New Features for DBAs (and Developers)
12:30-1:30  Lunch
1:30-2:30   Chris – Creating RESTful APIs with Oracle Data Services (for Developers and DBAs)
2:30-2:45   Break
 (more...)

Adaptive Query Optimization in Oracle 12c : Ongoing Updates

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...)

OUG_SCOT: I was on fire and then the fire alarm went off

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...)

Oracle 12C Problem with datapatch. Part 2, the “fix”

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;

VERIFY_QUERYABLE_INVENTORY
--------------------------
ORA-20008: timed out. Job Load_opatch_inventory_1execution time is more than  (more...)

oracle applications r12 auto start on linux

Step 1: connect as a database os user

su – oradev

startDB.sh edit and saved

#!/bin/sh
. /u01/EBSDEV/db/tech_st/11.2.0/EBSDEV_ebsdev.env
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addbctl.sh start
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh start EBSDEV

stopDB.sh edit and saved

#!/bin/sh
. /u01/EBSDEV/db/tech_st/11.2.0/EBSDEV_ebsdev.env
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addbctl.sh stop
/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

(more...)

Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password (more...)

“But, You Don’t Understand…”


"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...)

Moving Datafiles Online in Oracle 12c

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...)