Dallas Oracle Users Group Performance Meetup

I spoke at a one day DOUG meeting yesterday. It was pretty cool. Very small intimate group of about 50. The speakers were Nitin Vengurlekar, Charles Kim, Cary Millsap and myself. All are Ace Directors and either work at Viscosity or Enkitec. As a bonus, Tanel Poder showed up to weigh in on some open discussion. Anyway, I thoroughly enjoyed it. I promised the group I would post my slides and a zip file with (more...)

sreadtim

Here’s a question that appeared in my email a few days ago:

 

Based on the formula: “sreadtim = ioseektim + db_block_size/iotrfrspeed” sreadtim should always bigger than ioseektim.

But I just did a query on my system, find it otherwise, get confused,

SQL> SELECT * FROM SYS.AUX_STATS$;<

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    10-08-2014 10:45
SYSSTATS_INFO                  DSTOP                                     10-10-2014 10:42
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     680.062427
SYSSTATS_MAIN                   (more...)

Setting Up Chargeback in EM 12.1.0.4

Some time back, I posted a blog entry about Metering and Chargeback in Enterprise Manager, where I covered the basic capabilities of the Chargeback Application. It’s taken me some time to get back to the promised followup blog post on setting up Chargeback, what with Oracle Open World getting in the way of things, but that’s what I’m finally doing now.

Chargeback can be setup in EM12c by any user with the EM_CBA_ADMIN role, or (more...)

Just a couple of screenshots of sqlplus+rlwrap+cygwin+console

I previously wrote that I peeped the idea about showing the session information in terminal title from Timur Akhmadeev’s screenshots, and Timur wrote:

I’m using (a bit modified) Tanel Poder’s login.sql available in his TPT scripts library: http://tech.e2sn.com/oracle-scripts-and-tools

Scripts:
Tanel’s i.sql
My title.sql and on_login.sql

Colored prompt is the one of many features of rlwrap.

Screenshots:
Connected as simple user:
baikal-xtender
Connected as sysdba:
xtsql-sysdba

SQL*Plus on OEL through putty:
putty-to-oel-sqlplus

@inc/title “*** Test ***”
inc-title-test

Code can be scary when you simplify it

Disclaimer: I’m not posting to make me look better, we’ve all written code that we’re later ashamed of, and I’m no different!

This is some code from a system I was maintaining some time ago. I’ve kept it since then because it illustrates a number of things NOT to do:

FUNCTION password_is_valid
  (in_password IN VARCHAR2)
-- do NOT copy this code!!! ...
  RETURN VARCHAR2 IS
  l_valid VARCHAR2(1);
  l_sql VARCHAR2(32000);
  CURSOR cur_rules IS
    SELECT REPLACE(sql_expression
                  ,'#PASSWORD#'
                  ,''''  (more...)

Patching Time

Just a quick note to point out that the October PSU was just released. The database has a few more vulnerabilities than usual (31), but they are mostly related to Java and the high CVSS score of 9 only applies to people running Oracle on windows. (On other operating systems, the highest score is 6.5.)

I did happen to glance at the announcement on the security blog, and I thought this short (more...)

Oracle E-Business Suite Updates From OpenWorld 2014

Oracle OpenWorld has always been my most exciting conference to attend. I always see high energy levels everywhere, and it kind of revs me up to tackle new upcoming technologies. This year I concentrated on attending mostly Oracle E-Business Suite release 12.2 and Oracle 12c Database-related sessions.

On the Oracle E-Business Suite side, I started off with Oracle EBS Customer Advisory Board Meeting with great presentations on new features like the Oracle EBS 12. (more...)

Managing your Database in a Zombie Apocalypse

Zombie Server Room

Only two things are really certain: network latency over long distances, and the fact that humanity will soon rapidly degenerate into undead brain-eaters.

When that day comes, when the dead are crowding at your door and the windows are busted out and ripped up rotted arms are clawing at the inside of your home, I know what you’ll be thinking: is my database protected?

Don’t worry, my friends. The Oracle Alchemist has you covered. We (more...)

Latest Oracle allows SELECT without SELECT…FOR UPDATE

Digging through a backlog of Oracle blogs, I came across an gem in a presentation from AMIS (on Slideshare). Got to bullet point 5 on slide 63 and boom !

You all know that when you grant SELECT on a table to a user, they can do a SELECT FOR UPDATE, locking records in the table and preventing other updates or deletes. [Some client tools may do that in the background. ]

Well finally Oracle (more...)

Another Great OpenWorld

Steve at the Delphix Booth

Last week I attended Oracle OpenWorld 2014, and it was an outstanding event filled with great people, awesome sessions, and a few outstanding notable experiences.

Personally I thought the messaging behind the conference itself wasn’t as amazing and upbeat as OpenWorld 2013, but that’s almost to be expected. Last year there was a ton of buzz around the introduction of Oracle 12c, Big Data was a buzzword that people were totally excited (more...)

Oracle OpenWorld 2014 is over – What’s next?

Last week Oracle OpenWorld 2014 took place in San Francisco. I did not have the pleasure to attend this event. thanks to the Social Media and the World Wide Web you could be able to follow the highlights. If we check out the Keynote of Thomas Kurian, we can learn that there are three Major Trends; Big…Read more Oracle OpenWorld 2014 is over – What’s next?

Using EMCLI to Create Named Credentials

One of my blog readers asked me to write a sample EMCLI codes to create named credentials for Database. To be able to create a named credential, you need to know the target name (unless you create a global credential), target type and credential type associated with the target type. Let’s say I want to create a named credential for my database named “TESTDB”. First I need to login to our EM12c server, and list (more...)

BBED Block Browser EDitor for Oracle 11g

BBED (Block Browser Editor) is a tool for Oracle internal use, and it helps you to read and manipulate data at the Oracle Database block level. No need to say that it’s very powerful and also extremely dangerous because you can corrupt data/header blocks. There’s an unofficial but very comprehensive manual for BBED. It’s written by Graham Thornton. You can download it as PDF: http://orafaq.com/papers/dissassembling_the_data_block.pdf

Before Oracle 11g, BBED object code is shipped (more...)

Oracle OpenWorld 2014: Non-life altering observations (but still interesting and useful)

This article does not necessarily discuss the big themes and major stories of Oracle OpenWorld 2014. It does mention a number of facts that I discovered, overheard, observed or otherwise found out about during last week’s conference. They are not necessarily from formal Oracle sources, they have have been part of conversations on the demo grounds or sessions for which no slide evidence exists. Do not base major decisions on these notes – but perhaps (more...)

Bringing Clarity To The Avalanche Part 1 – OOW14

Since the prior post here, I've had some people ask why I compared Oracle OpenWorld this year to an avalanche.  Well, to be honest, there are two reasons.  First, it was certainly an avalanche of news. You can check all the Oracle press releases related to the conference here (warning: it's pages and pages of information).  Second, I'm tired of using the analogy of sipping or drinking from a firehose...time to try something new.

(more...)

Little script for finding tables for which dynamic sampling was used

You can always download latest version here: http://github.com/xtender/xt_scripts/blob/master/dynamic_sampling_used_for.sql
Current source code:

col owner         for a30;
col tab_name      for a30;
col top_sql_id    for a13;
col temporary     for a9;
col last_analyzed for a30;
col partitioned   for a11;
col nested        for a6;
col IOT_TYPE      for a15;
with tabs as (
      select 
         to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1))  owner
        ,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2))  tab_name
        ,count(*)                                                                    cnt
        ,sum(executions)                                                             execs
        ,round(sum(elapsed_time/1e6),3)                                              elapsed
        ,max(sql_id) keep(dense_rank first order by elapsed_time desc)               top_sql_id
      from v$sqlarea a
       (more...)

NYOUG Training Event: "High Performance PL/SQL" on Nov 7 2014

If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:

http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258

I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to (more...)

enhanced SQL trace

As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits.
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities (more...)

Clarity In The Avalanche

So I've spent the days since Oracle OpenWorld 14 decompressing...puttering in the garden, BBQing for family, running errands.  The idea was to give my mind time to process all the things I saw and heard at OOW this year.  Big year - it was like trying to take a sip from a firehose.  Developing any clarity around the avalanche of news has been tough.

If you average out all of Oracle's new product development, (more...)

Why the In-Memory Column Store is not used (II)

Now after some research - I detected one simple rule for provoking In-Memory scans :