Speaking in Portland at NWOUG Monday Sept 14

Looking forward to speaking at

Please come join me !

I lived in Portland between 2006-2008 and love city. It will be fun being back.

8:15 – 9:00 Keynote “EBS Strategy and Roadmap” – Vanessa Paskill

Technical Apps Functional Vendor Oracle
9:15 10:15 Practical Tips for Oracle Business Intelligence Applications 11g Implementations (Michael Raney, Rittman Mead) Lessons learned while Integrating Procurement Cards with Internet Expense (Ashish Nagarkar, AST (more...)

Oracle Tidbits – August 2015 #oratidbit

“Oracle *daily* TidBits” (#oratidbit) published on weekdays in August 2015

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

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

What Does Your EM Agent Have Planned?

The EM agent’s scheduled activities can be listed using emctl status agent scheduler, like this:

status_agent_schedule

You’ll see a regular cycle of Response calls (anybody home?), metric uploads, and heartbeats.


Less calls…more performance (part 2)

In the previous post, I mentioned that for a programming environment fetching rows from the database, then the method of

  • open a ref cursor
  • issue a fetch call
  • close the ref cursor

might not be appropriate for those situations where the result set is known be a single row (eg primary key lookup).

A better option might be to call a procedure and get those outputs as parameters.

And I broke a cardinal rule… (more...)

Less calls…more performance

In various programming environments, a common metaphor is to open a cursor on the database (a REF CURSOR in Oracle parlance), return that cursor handle to the calling environment, and then that cursor is used to fetch or “fill” a data object, which may map to a grid on screen, or just to an array in memory.

And that’s totally fine – its an effective means to obtain a result set from the database to (more...)

Reminder: Great free computer science and Python programming class starts Wednesday

I mentioned this class earlier in a blog post but I wanted to remind people who read this blog that the class is starting again on Wednesday.  Here is the URL for the class: link

The class is completely free and taught at a very high level of quality.

It teaches computer science concepts that apply in any programming language but also teaches Python programming.

It is valuable information in the increasingly computer oriented world (more...)

Oaktable World 2015 San Francisco, Oct 26 & 27

Agenda for Oaktable World 2014, located at Creativity Museum, is

 time Monday Oct 26 Tuesday Oct 27
8
9 Mark W. Farnham – Minimizing the concurrency footprint of transactions with Logical Units of Work stored in PL/SQL Kyle Hailey – Virtual Data
10

Kerry Osborne – SQL Translation Framework

Kellyn Pot’Vin-Gorman, AWR Warehouse Trending and Analysis with OBIEE
11 Greg Rahn – Taming JSON with SQL: From Raw to Results

Marco Gralike – Improving XML performance (more...)

Improve Performance using Oracle Smart Flash Cache

The article/blog is published on my company website… Improve Performance using Oracle Smart Flash Cache

X-Window Fun

When ssh -X to another host, I am able to use X-Windows.

[dinh@ca01ts~]$ ssh -X dinh@192.168.1.137
dinh@192.168.1.137's password:
Last login: Fri Aug 21 11:55:47 2015 from 10.237.102.38
/usr/bin/xauth: creating new authority file /home/dinh/.Xauthority
[dinh@arrow ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C

However, sudo to another user and X-Windows breaks.

[dinh@arrow ~]$ sudo su - oracle
[sudo] password for dinh:
[oracle@arrow ~]$  (more...)

Avoiding public embarrassment with triggers

If you create a trigger that does not compile, any part of your application that refers to the table with the invalid trigger is basically dead until that trigger becomes valid.  Even if a DML on that table is not going affect any rows, you still wont be able to issue it.  For example,

SQL> delete from T where 1=0;
delete from T where 1=0
            *
ERROR at line 1:
ORA-04098: trigger 'TRG'  (more...)

Where is my tracefile

As a developer, you might have have been reading about SQL trace. After all, we all want to be able to diagnose and improve the performance of the code we write. So if you’ve googled on Oracle performance, you will have no doubt stumbled upon SQL trace.

So if you’ve run “alter session set sql_trace = true”, or perhaps “DBMS_MONITOR.SESSION_TRACE_ENABLE”.  you’re probably itching to get your hands on that trace file.  The (more...)

Memory added based on buffer pool advisory did not give desired result

Development asked me to look at ways to cut the run time of a series of PeopleSoft payroll batch jobs so I took an AWR report of the entire 4 hour period.  Based on the waits, the percentage of the elapsed time spent using I/O and the buffer pool advisory I chose to double the size of the buffer cache. But, this added memory did not improve the run time of the batch jobs. Maybe the affected (more...)

Fishbowl Solutions is now a Google for Work Premier Partner

As of August 1, 2015, Google promoted Fishbowl Solutions to Premier Partner status! This elevation is a testament to our commitment to selling and servicing Google Search Solutions, and we are very excited to be counted among the select companies to attain this level of partnership. Kim Negaard, the Google Product Manager at Fishbowl, took some time to talk about our history with Google and what being a Premier Partner means.

 

The post Fishbowl (more...)

Script to get previous month’s AWR report

We keep 6 weeks of history in the AWR on our databases, but I want to capture some information for long-term trending. What I really want to do is capture some metrics and put them in some database tables to use to generate reports, but I have not had time to build the scripts to do that.  So, instead I built a simple set of scripts to capture an AWR for the previous month. Since we have 6 (more...)

The Real Future of Oracle Forms – Not the Demise of Forms! Response to LinkedIn Thred

There is quite a debate going around based on Simon’s linkedin post regarding the future of Forms; but when you read it you’ll see, he doesn’t actually talk about the future of Forms – he talks about the demise of Forms and its replacement with Oracle APEX. As someone who does believe in the future of forms, there are several things  I wanted to clarify.

Firstly, it mentions that despite the release of Oracle Forms 12c, Oracle (more...)

Loading file data … easier than you think

So a request comes in from a colleague…

”Hi Connor.  I’ve created this new table, and I need to populate it with some data.  I’ve got it in Excel – can you help me load it”

Now, at this point, my heart normally sinks, because whilst the Oracle tools to load flat file data are comprehensive, I know that I now have to

  • go find out the table definition, column names and data (more...)

Mr DISTINCT might not be your friend

Whenever you have the need to use the DISTINCT keyword, its worth just pausing for a second, and making sure that you are not hiding just a larger issue. It actually might represent either incorrect use of SQL or incorrect assumptions from the data model.

Consider the following example

SELECT DISTINCT d.dname
FROM   emp e, dept d
WHERE  e.ename = 'SMITH'
AND    e.deptno = d.deptno

The query is certainly valid, but (more...)

How to Remove an Exadata Server from OEM

Just like everything else in Enterprise Manager, an Exadata is a target and all of its components are targets.  All of these pieces can be removed through the 12c console.

Select Exadata from the bottom of the main Targets drop-down to display a list of Database Machines in your environment.  Highlight the row containing the subject machine and press the Big Red X to take you into the detail page.

remove_exadata_01

The left panel shows you the (more...)