Connect By

If you have to deal with hierarchical queries (or “connect by” queries, as they’re commonly known) you might be interested in this note on the OTN Forum where someone has a problem with repeated executions of the same query (same inputs, same results, same execution plan) operating at wildly different speeds with amazing changes in [...]

INSERT/UPDATE/DELETE will generate redo log on NOLOGGING mode ?

After I read about Redo & Undo in Expert Oracle Database Architecture book by Kyte, He told about NOLOGGING...

Nologging doesn't mean all operations in that object will not generate redo log.

And Francisco Munoz A 's Paper told ... Nologging will generate a minimal number of redo log entries in order to protect the data dictionary.

That make my curious about nologging mode with DML. I use it on some tables and some indexes... So I should know a real thing about it by myself.

Francisco Munoz A 's Paper and Oracle Docs Idea ... INSERT/UPDATE/DELETE will generate a real redo with table/index NOLOGGING mode, except INSERT /*+APPEND+/

Begin test... NOLOGGING + DML on 11g Archivelog Mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled

Script:

--new.sql
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';


--diff.sql
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';


- Create TABLE -

SQL> @begin

OLD_VALUE
----------
0

SQL> create table T_NOLOG nologging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 0) OLD_VALUE

OLD_VALUE
----------
133120

REDO SIZE=133120

SQL> @begin

OLD_VALUE
----------
133120

SQL> create table T_LOG logging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 133120) OLD_VALUE

OLD_VALUE
----------
8748596

REDO SIZE=8748596

Create table with NOLOGGING... not generate redo log (just generate redo log for data dictionary)
After create table... It's time to test with DML:

- DELETE -

SQL> @begin

OLD_VALUE
----------
8881716

SQL> DELETE FROM T_NOLOG NOLOGGING;

70999 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 8881716) OLD_VALUE

OLD_VALUE
----------
27076168

REDO SIZE=27076168

SQL> @begin

OLD_VALUE
----------
35958052

SQL> DELETE FROM T_LOG;

71000 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 35958052) OLD_VALUE

OLD_VALUE
----------
27076692

REDO SIZE=27076692

- INSERT -

SQL> @begin

OLD_VALUE
----------
63034912

SQL> INSERT INTO T_NOLOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 63034912) OLD_VALUE

OLD_VALUE
----------
8493412

REDO SIZE=8493412

SQL> @begin

OLD_VALUE
----------
71528324

SQL>INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 71528324) OLD_VALUE

OLD_VALUE
----------
8493360

REDO SIZE=8493360

- UPDATE -

SQL> @begin

OLD_VALUE
----------
80021684

SQL> UPDATE T_NOLOG NOLOGGING SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 80021684) OLD_VALUE

OLD_VALUE
----------
24671048

REDO SIZE=24671048

SQL> @begin

OLD_VALUE
----------
104692732

SQL> UPDATE T_LOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 104692732) OLD_VALUE

OLD_VALUE
----------
20911424

REDO SIZE=20911424

On DML INSERT/UPDATE/DELETE ... Oracle generated redo log on nologging mode not difference... on logging mode.
And I need to know about it on INSERT /*+ APPEND */:

- INSERT "APPEND" hints -


- table NOLOGGING mode and not use APPEND hints

SQL> @begin

OLD_VALUE
----------
125604156

SQL> INSERT INTO T_NOLOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 125604156) OLD_VALUE

OLD_VALUE
----------
8586036

REDO SIZE=8586036

SQL> @begin

OLD_VALUE
----------
142830588

SQL> INSERT /*+ APPEND */ INTO T_NOLOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142830588) OLD_VALUE

OLD_VALUE
----------
29448

REDO SIZE=29448


- table LOGGING mode, and use APPEND hints

SQL> @begin

OLD_VALUE
----------
134190192

SQL> INSERT /*+ APPEND */ INTO T_LOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 134190192) OLD_VALUE

OLD_VALUE
----------
8640396

REDO SIZE=8640396

*** make table logging to nologging ***


SQL> alter table t_log nologging ;

Table altered.

SQL> @begin

OLD_VALUE
----------
142874676

SQL> INSERT /*+ APPEND */ INTO T_LOG NOLOGGING SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142874676) OLD_VALUE

OLD_VALUE
----------
27956

REDO SIZE=27956
APPEND hints & sql(nologging) on table "LOGGING" mode "select logging from dba_tables" (NO) ... not difference (generate redo)

If "alter table nologging" before, and then insert (append)
So, Oracle will generate a minimal number of redo log... with INSERT /*+ APPEND */ when table be nologging ... select logging from dba_tables (NO)
That just testing... But helpful to understand something ;)
Written By: Surachart Opun http://surachartopun.com

Aren’t Customers Choosing Oracle Database Machine?

This is just a quick blog entry to point to the first heavily customer-focused news release about the Oracle Database Machine (based on Oracle Exadata Storage Server). Here is the link: Customers are Choosing the Oracle Database Machine

Oracle Press Release: Customers are Choosing the Oracle Database Machine

Greg Rahn | Jun 29, 2009 07:51 -0600
Oracle put out a press release today entitled “Customers are Choosing the Oracle Database Machine” mentioning the new Exadata and Oracle Database Machine customers. I’ve quoted a few parts of it below. Oracle cites twenty initial customers. Initial Customers Initial Oracle Exadata customers including Amtrak, Allegro Group, Automobile Association of the UK, CTC, Garanti Bank, [...]

olsnodes make CPU spikes

After I read on otn forums.
They told they see high cpu usage, when use "olsnodes" command.
$ time olsnodes
node01
node02

real 0m0.153s
user 0m0.082s
sys 0m0.068s
And then ... solve this by delete * files at ORA_CRS_HOME/log/hostname/client/ path.
$ rm -f $ORA_CRS_HOME/log/node01/client/*

$ time olsnodes
node01
node02

real 0m0.037s
user 0m0.017s
sys 0m0.014s

$ ls $ORA_CRS_HOME/log/node01/client/*
css.log

$ time olsnodes
node01
node02

real 0m0.045s
user 0m0.016s
sys 0m0.015s

$ ls $ORA_CRS_HOME/log/node01/client/*
css1.log css.log

What about idea?
When use "olsnodes" command successfully, it scans $CRS_HOME/log/hostname/client PATH and generates a new cssxxx.log file.
metalink:729349.1 ...said it's a bug:
Olsnodes produces CPU spikes with many css*.log files in ORA_CRS_HOME/log/nodename/client directory

fixed in 11.1.0.7 and 10.2.0.4 CRS bundle Patch #2 onwards. Bundle Patch information is available in Note 756671.1

Try to check on 11.1.0.7 :
$ crsctl query crs softwareversion
Oracle Clusterware version on node [test01] is [11.1.0.7.0]

$ ls $ORA_CRS_HOME/log/test01/client/ | wc -l
32738

$ time olsnodes
test01
test02

real 0m0.030s
user 0m0.015s
sys 0m0.014s
OK... No problem on 11.1.0.7
Written By: Surachart Opun http://surachartopun.com

One more date trick

ebuddelm | Jun 29, 2009 01:11 -0600

Tyler Muth has a useful addition to the date functions I have published here before.

If you ever want to know how long ago a date is and you want to display it in ‘human readable’ format you (and I) could use his function.

Like this:

select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;   
select date_text_format(sysdate - 120) the_date from dual;   
select date_text_format(sysdate - 365) the_date from dual;   
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago  

One more for the toolbox.

Twitter Weekly Updates for 2009-06-28

Share/Save/Bookmark

Related posts:

  1. Twitter Weekly Updates for 2009-06-28 blog post
  2. Twitter Weekly Updates for 2009-05-31 blog post
  3. Twitter Weekly Updates for 2009-06-14 blog post

Related posts brought to you by Yet Another Related Posts Plugin.

Twitter Weekly Updates for 2009-06-28

Share/Save/Bookmark

Related posts:

  1. Twitter Weekly Updates for 2009-06-28 blog post
  2. Twitter Weekly Updates for 2009-05-31 blog post
  3. Twitter Weekly Updates for 2009-06-14 blog post

Related posts brought to you by Yet Another Related Posts Plugin.

Google pages will be migrating to Google sites :(

Google pages is migrating to Google sites and Google sites is forbidden at Türkiye, since I maintained for years all my SQL scripts mentioned here at my Google pages domain and these files are not a part of the planned migration this migration will cause lots of trouble for me and of course for the [...]

I Love ADDM

I'll get back to adaptive thresholds at some point but something's been bugging me.

I'm not just trying to be controversial but I've a feeling I'm about to be, given that ADDM is the probably the most infamous of the 10g 'Automatic Tuning' features. What should be the beautiful swan of those features has somehow become the unloved, barely-mentioned ugly duckling.

One of my favourite running lines on a mailing list is that whenever a performance problem crops up, one of the participants will always say 'So what did ADDM say in this case?' or something similar. It's funny because ADDM is barely mentioned and people seem to need reminding that it exists or maybe they just don't like it? I suspect it's the latter. Maybe there are psychological factors at play here? Even the people who turn up at the courses I teach, who are obviously interested in the subject, or those good people at the good sites like the one I've working at at the moment all seemed focussed on AWR. ASH and ADDM don't get much of a look in, 5 years after they were released. Maybe people like AWR because it's so similar to Statspack and they're used to Statspack?

However, I keep coming across different examples where Automatic Database Diagnostic Monitor has identified what's causing a performance problem. That's not the same as fixing it, of course, but I've watched people flailing around making a variety of guesses, looking at ASH data or debating AWR reports before getting round to thinking - 'Why don't we just see what ADDM has to say. Just out of interest.' (The truth is I often prompt that thought ;-)) This has happened many times in recent months and lo-and-behold, ADDM either formed the same correct conclusions in a fraction of the time people had been 'analysing' things or found things that the initial analysis hadn't.

It stands to reason. If you think about how you analyse performance, don't you find where sessions are spending their time and identify the causes? Don't you see which SQL statements are running for long periods of time or are consuming most resources? Don't you identify those segments which are most active? I could go on. The fact is that every sensible performance analysis method I'm personally aware of could be reduced to looking at where time is being spent and focussing on the most significant contributing factors. Which is all that ADDM does, too.

In case I seem like some deluded fan-boy, let me highlight some of the things I don't like about ADDM.

1) I haven't seen this problem for a while and I suspect it's because of improvements on the earlier releases, but ADDM used to suggest frequently that CPU was not a bottleneck when it clearly was.

2) What is it with it's constant suggestions to increase memory areas? You could say maybe that's because I'm configuring too little memory all the time, but I've talked to lots of people about this and worked on lot of systems and everyone I know experiences the same. Somewhere down the bottom of those recommendations it'll tell you to increase SGA and PGA memory. So you do. So it asks again. So you do ... I'll save you weeks and months of tedium and just point out that this can go on for a very long time and in very small increments. Rare is the ADDM report that doesn't suggest increasing some memory area or other.

I don't think that ADDM's recommendations should be followed slavishly though. I'm much more interested in using it as a tool that will analyse where time is being spent and show me the big-hitters. I'll always be interested in it's advice, too, but I'd be extremely unlikely to click to implement any of it's recommendations without some serious thought. I usually confirm what it's telling me through my own experience and ASH and AWR data. In fact, one of my favourite games when I have the time is to leave the ADDM analysis until after I've analysed the problem myself. Believe me, my ego is big enough to cope with the large percentage of times that ADDM ends up reporting what it took me 30-100 times as long to work out ;-) (If that makes me sound slow, remember that ADDM has already done the job and maybe took a minute)

For example, at work last week there were some serious performance problems with the overnight batch. Unfortunately we didn't find out about them until the next day so we needed tools suited to analysing problems in the past. There are quite a few options, most of which I had no access to, so I requested the privileges and that the DBAs run some reports for me while I was waiting. I found myself looking at ASH reports and comparing AWR reports for nights where performance was acceptable and unacceptable. I could identify a couple of things in the AWR reports - the auto stats gather job seemed to be running during the second night and the single block read time averages were double what they usually are. But it took a little while to spot these anomalies because the reports looked very similar even though they were for identical time periods but completely different workload (maybe more on that another time, just to prove I understand that all tools have some weakness or other). Eventually the privileges came through and I was able to look at the ADDM runs for the periods covering the batch schedule. ADDM spotted the same couple of issues in a fraction of the time and picked out a few SQL statements that I could focus on, one of which was part of a new release.

OK, so then some serious tuning started, but if I'd used ADDM in the first place, there was probably an hour or two that I could have used for tuning instead of analysing. Of course, it often highlights a bunch of SQL statements to focus on, so how has that helped me with solving the problem? Let me turn that around. How often is a performance problem down to a few SQL statements that need tuning? Even in the case of the application I'm working on at the moment, where the design is all wrong, what I'm really interested in is the impact of that design on system performance. ADDM won't help me redesign it, but I can assure you that it's highlighting the negative impact of the original design decisions.

What really gets me about this is that you're paying the overhead anyway and if you have Diagnostics Pack licences (which I'm finding more people do these days) why on earth would you choose to ignore further confirmation of your own analysis, if nothing else. Is there something about using ADDM's analysis which somehow undermines our performance egos? LOL

If you're in the 10g/Diagnostics situation, just do me a favour and at least just look at the relevant ADDM runs when you've experienced a performance problem in the past. It's not hard and, with my restricted privileges and limited server access at my current site, it comes down to this. (You don't even need Grid or DB Control.)

1) Get the ADVISOR privilege.

2) Look in DBA_ADVISOR_TASKS and the ADDM runs will be there. The work's already been done.

select task_name, execution_end 
from dba_advisor_tasks 
where advisor_name='ADDM' 
and status='COMPLETED' 
and owner='SYS' 
order by execution_end desc;

3) View the report.

select DBMS_ADVISOR.GET_TASK_REPORT('<task_name from previous query>', 'TEXT', 
'TYPICAL', 'ALL', 'SYS') from dual;

If the recommendations turn out to be complete rubbish then I promise I'll be very keen to hear about that. If not, then maybe people will start taking ADDM a little more seriously and look at it on it's merits without starting off from the biased view-point that an Oracle tool couldn't possibly be as good at this stuff as you are ;-)

Really, if you're paying for it, why aren't you looking at it?

Oracle ACE Director Meeting in USA

On an invitation from Oracle Corporation, I attended the Oracle ACE Director meeting at the Oracle HQ in Redwood Shores, California.

Oracle had everyone at the meeting sign a non-disclosure agreement so we cant say much about what was discussed. The main discussions were on Fusion Middleware.

One interesting probability for the future is that Weblogic server will be big in everything - including Enterprise Manager. This will enable Grid Control to scale enormously.

The ACE Director meeting was followed by the ODTUG conference in Monterey, California where I met industry stalwarts such as Tom Kyte, Don Burleson, and Cary Millsap.

A few photos of my trip to the Oracle HQ are enclosed.





Are you sure you will be able to activate your standby??

A couple of weeks I faced a scenario where the standby database crashed

On looking at the alert.log I see the following message in the alert.log of the standby

***********************************************************
Sat Jun 6 06:48:52 2009
Recovery interrupted!
cannot find needed online log for redo thread 1
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Sat Jun 6 06:48:53 2009
Errors in file /u01/app/oracle/admin/TEST/bdump/test1_mrp0_24533.trc:
ORA-10576: Give up restoring recovered datafiles to consistent state: some error occurred
ORA-16037: user requested cancel of managed recovery operation
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Sat Jun 6 06:48:54 2009
Waiting for MRP0 pid 24533 to terminate
************************************************************

Hmmm... this means that if the standby does not have the redo and cannot get it from the primary you will not be able to online media fuzzy files using supported methods

The same issue is explained in Bug 5956646
as an architectural limitation.

This is a very unlikely scenario but a possibility none the less

Oracle Open World 2009: XML DB Presentations known so far…

For me and those who are interested, just like the year before , hereby an attempt to find all XMLDB related presentations, workshops and other events during Oracle Open World 2009. I will try to add info, time and days later on (and/or you might here as well) if I find them and/or if [...]

Oracle Enhances XBRL Reporting Capabilities with UBmatrix, Inc.

Oracle posted a press release about their involvement and commitment for UBMatrix XBRL technologies last Wednesday saying (among others): “To help publicly held companies facilitate the preparation, publishing and automatic exchange of financial statements in XBRL (eXtensible Business Reporting Language), Oracle will embed UBmatrix, Inc.’s leading XBRL technology into Oracle’s Enterprise Performance Management (EPM) System, Oracle [...]

(Secret) Preview of Oracle 12g CBO leaked from Oracle labs…

After doing my seminar in Spain last month, one of the attendees sent me a secret note about what the CBO would look like in Oracle 12g. Apparently it’s re-engineered from ground and completely different from anything we’ve seen before. It goes beyond being automatic, intelligent and auto-tuning, it’s actually edible too!

The New CBO

The New CBO

Thanks to John Ospino for sending me this insider-info ;)

United Airlines are rubbish!

Tim... | Jun 26, 2009 17:34 -0600

Why is it, every time I fly on United Airlines they have oversold the flight? I have a seat allocated already so I’m OK, but there are a bunch of people here who have paid, but there is no seat for them.

They are now offering a night in a hotel, food and a first class flight tomorrow. No doubt the same will happen on that flight.

I have an amazing idea, why not count the seats on your plane and only sell that number of tickets you bunch of donkies!

The joys of traveling… :)

Cheers

Tim…

Post-Conference Lull…

Tim... | Jun 26, 2009 12:36 -0600

The conference finished yesterday at about midday, so I went into town with Chris Muir and Marcel Kratochvil to check out the aquarium. I managed not to get sunburnt this time. Later we met up for some food and basically chilled out. I’ve added some more photos here.

I leave for San Francisco airport in a few minutes, then I have to look forward to an 11 hour flight, 2 hour wait, then a 1 hour flight. Lovely. :)

Thanks to the ladies and gents from OTN and the ACE program for getting me here and thanks to ODTUG for putting on a cracking conference. Not surprisingly, there was no mention of the new Oracle games console, which we are not allowed to mention…

Doing conferences is always a strange experience. They are a little daunting before you start, quite exciting while they are happening, then I get a post-conference lull. It’s like your brain says, “enough is enough”, and you close down for a few days. Having to travel home doesn’t help much.

Cheers

Tim…

Blogroll Report 19/06/2009 – 26/06/2006

coskan | Jun 26, 2009 12:07 -0600

<—- Blogroll Report 12/06/09 – 19/06/09

Here are  technical highlights from Oracle Blogosphere with my keywords,

1- ORA-38029 for index creation

Randolf Geist – Locked table statistics and subsequent create index

2- How to start RAC instance in non-RAC  mode

Chandra Pabba – ORA-29702 – Starting RAC instance in non-rac mode

3- How to set cardinality for pipelined and table functions

Adrian Billington – setting cardinality for pipelined and table functions

4- How to format index tree dump by SQL and RegEXP

Dion Cho – Simple formatting on index tree dump

5- Restrictions for aggregate materialized views

Rob Van Wijk – Fast refreshable materialized view errors, part three: aggregate MV’s

6- dbms_stats.copy_table_stats min/high value alteration bug

Amit Bansal – dbms_stats.copy_table_stats does not alter low/high value

7- Automatic top subheap dumping with heapdump

Tanel Poder Oracle memory troubleshooting, Part 3: Automatic top subheap dumping with heapdump

8- Views in Explain Plan output – no_merge

Jonathan Lewis – Explain View

9-  Importance of Size  for  Autoextend on Next

Hemant K Chitale  - AUTOEXTEND ON Next Size

10- Partition pruning problem for MIN/MAX of partition key column

Jeff Moss – No pruning for MIN/MAX of partition key column

11- ORA-02070 with VPD  and DBMS_SCHEDULER

Amardeep Sidhu – DBMS_SCHEDULER, DBMS_RLS and SYS_CONTEXT

12- How to check RAC option in Oracle Binary

Surachart Opun – check RAC Option in Oracle Binary

13- Diagnosing paralel query and interconnect  performance on RAC with UDPSNOOP

Riyaj Shamsudeen – RAC, parallel query and udpsnoop

Oracle Forums

1- Cache Fusion and Past Image

http://forums.oracle.com/forums/thread.jspa?threadID=917350

2-CBC Latch and Buffer Busy wait on same table.

http://forums.oracle.com/forums/thread.jspa?threadID=917866

3-Latch problem

http://forums.oracle.com/forums/thread.jspa?threadID=918767

What Did My New Index Mess Up?

osborne | Jun 26, 2009 11:22 -0600

This week I got to look at a system where a new version of an application had been rolled out. I came across a SQL statement that had taken a turn for the worse in terms of its average elapsed time. It was using an index I hadn’t noticed before, so I took a look to see if the index was new (it was - apparently it was added as part of the code roll out). So I got to wondering if there were any other statements that had suffered a backward movement in performance. So I wrote this little script called whats_changed.sql. It’s the same basic idea as one I wrote about earlier here called unstable_plans.sql. Both of these scripts are based on a calculated variance. The original unstable_plans script uses variance in average elapsed times for statements with multiple plans (i.e. it shows statements that have multiple plans which exhibit a significant difference in average elapsed time between plans). The whats_changed.sql script shows statements that exhibit significant differences in average elapsed time before and after a point in time (i.e. when you rolled out new code, or added an index, etc…) A short example may explain it better than I can:

$ !sql
sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 25 22:24:35 2009
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
 
SQL> @whats_changed
Enter Days ago: 3
Enter value for min_stddev:
Enter value for min_etime:
Enter value for faster_slower:
 
SQL_ID               EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER   NORM_STDDEV RESULT
------------- ------------ ---------------- --------------- ------------- ------
an9114vtxukz2           17             1.25            5.00        2.1148 Slower
803u6vwz0ah1p           29             0.04            0.18        2.1552 Slower
bvf3fxv3hatw7        2,390             0.03            0.14        2.4147 Slower
5sgs7q9pjnzg5            2             2.21            0.42        3.0130 Faster
b0zkuv9qtcvnn           48             0.16            0.85        3.1500 Slower
9ws5c6p77d384        1,852             0.57            3.30        3.3609 Slower
dy98ajwqc8s2x           15             1.32            8.34        3.7558 Slower
bkka22vb5y7t6          596             0.19            1.26        3.8804 Slower
6qfwkqgvjkn2q           12             0.51            3.60        4.2641 Slower
05xcf43d9psvm        1,197             0.02            0.14        5.4263 Slower
8wgmc9w5ubtsy          183             0.49            4.32        5.4946 Slower
fwfpuf1vfwfu2            6             0.02            0.22        5.6314 Slower
50c18dwvxq4sb          222             0.54            5.29        6.1586 Slower
aukanfjd3d8fa            3             0.88           10.00        7.3496 Slower
44bq4r5z2xrsm           54             3.13           43.39        9.0946 Slower
0az7czjdw8z7j          110             0.62            0.02       17.5933 Faster
f41agfq9qdhk6            3             0.24            8.06       22.6765 Slower
 
17 rows selected.
 
SQL> -- or to see just the slower ones
SQL>
SQL> @whats_changed
Enter Days ago: 3
Enter value for min_stddev:
Enter value for min_etime:
Enter value for faster_slower: Slower
 
SQL_ID               EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER   NORM_STDDEV RESULT
------------- ------------ ---------------- --------------- ------------- ------
an9114vtxukz2           17             1.25            5.00        2.1148 Slower
803u6vwz0ah1p           29             0.04            0.18        2.1552 Slower
bvf3fxv3hatw7        2,390             0.03            0.14        2.4147 Slower
b0zkuv9qtcvnn           48             0.16            0.85        3.1500 Slower
9ws5c6p77d384        1,852             0.57            3.30        3.3609 Slower
dy98ajwqc8s2x           15             1.32            8.34        3.7558 Slower
bkka22vb5y7t6          596             0.19            1.26        3.8804 Slower
6qfwkqgvjkn2q           12             0.51            3.60        4.2641 Slower
05xcf43d9psvm        1,197             0.02            0.14        5.4263 Slower
8wgmc9w5ubtsy          183             0.49            4.32        5.4946 Slower
fwfpuf1vfwfu2            6             0.02            0.22        5.6314 Slower
50c18dwvxq4sb          222             0.54            5.29        6.1586 Slower
aukanfjd3d8fa            3             0.88           10.00        7.3496 Slower
44bq4r5z2xrsm           54             3.13           43.39        9.0946 Slower
f41agfq9qdhk6            3             0.24            8.06       22.6765 Slower
 
15 rows selected.

A couple of comments on the script:

  • The default minimum normalized standard deviation is 2. So only statements that are 2X slower were listed.
  • The default minimum average elapsed time per execution is 0.1. So only statements that ran longer than 0.1 seconds were reported.
  • The DAYS_AGO parameter sets a pivot point, we’ll call it the REFERENCE_TIME. The calculations are based on averages from before and after the REFERENCE_TIME.

And a brief explanation of what’s in the output:

  • SQL_ID - the statement id
  • EXECS - the total number of executions in the AWR tables
  • AVG_ETIME_BEFORE - the average elapsed time per execution before the REFERENCE_TIME
  • AVG_ETIME_AFTER - the average elapsed time per execution after the REFERENCE_TIME
  • NORM_STDDEV - this is a normalized standard deviation (i.e. how many times slower/faster it is now)

So the above output indicates that only a few statements are significantly worse (avg. elapsed time 2X or greater) since our new code rollout, which occurred about 3 days ago. Statement 44bq4r5z2xrsm obviously sticks out with a new average elapsed time of 43+ seconds and about 9 times slower than before. This was the one that had picked up the new index. The rest were primarily due to increased physical i/o caused by some newly introduced statements which were doing a few million lio’s per execution and thus causing all the other statements to suffer slightly.

Let me know what you think. Your comments are always welcome.

Log Buffer #151: a Carnival of the Vanities for DBAs

Welcome to the 151st edition of Log Buffer, the weekly review of database blogs. We’re going to take a fast tour through the best blogs from the week gone by, beginning this time, with Oracle.

Jonathan Lewis writes, “It occurred to me recently that I might be making casual use of terms that weren’t necessarily very well known to the less experienced user. So I’ve decided to build a glossary of terms – and I’ll try to add to it from time to time whenever I have a few minutes.”

Jonathan might want to add “Method R” to the glossary. Cary Millsap was making it understood, as he shows in Profiling with my Boy: “Today I’m going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.”

Vivek Sharma offers a thorough look at the Cost-Based Optimizer: Inefficient Input yields Inefficient Output. Vivek begins, “Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers.  . . .  Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade . . . ”

Randolf Geist had some info to share on the matter of locked table statistics and subsequent create index. “ . . . in 10g and later index statistics are generated along with an index creation  . . .  so a newly created index usually has computed statistics. 10g also introduced the option to lock table statistics. Now if you lock statistics in 10g in later  . . .  and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command.”

Rob van Wijk gave us part three of his series on fast refreshable materialized view errors: aggregate MV’s. “In the third part I’m going to examine all restrictions for aggregate materialized views, as described in the documentation.  . . .  So this will be quite a lengthy and even tedious post, as you can imagine by the list above … but for a good cause.”

In MySQL blogs, the MySQL Performance Blog announced Percona.tv. Ryan Loew writes, “We’ll be uploading technical screencasts, conference video, and anything else cool we can think up.”

The Open Query blog published Good Practice/Bad Practice: Table Aliases. “When writing queries, try making a habit out of using short table aliases, no matter how small the query is.” An example using the World DB follows, as does a worthwhile discussion.

Roland Bouman examined MySQL stored functions: the impact of DECLARE HANDLER on performance, responding to Get the error return value in a variable by Peter Gulutzan.

Ronald Bradford looked into using statpack with SHOW STATUS. He writes: “Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack.  . . .  Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings.  . . .  This post is more about detailing those little annoyances that I’d like to improve, or see improved.”

Morgan Tocker writes, “I think 5.1 gets some bad press for not being a compelling upgrade. It’s not the big features that make the difference, but the subtle ones. I wanted to highlight some of the these that may make your life easier when it’s time to upgrade . . . ” The post is Hidden gems in 5.1.

Let’s turn to SQL Server now, starting with a post from Aaron Alton, The HOBT. He writes, “If you’ve been working with SQL Server for any length of time, you’ve probably seen a number of different variations of the UPDATE command.  . . .  Anyway, today’s blog post is more like a public service announcement. It seems that all too many developers are still dangerously unaware of the ability to create very ambiguous UPDATE statements in TSQL.” Click through for Tricky Updates: Weird, Wacky, and Wonderful UPDATE Syntax Variations in SQL Server.

Louis Davidson looked into the question of read/write ratio versus read/write ratio. That’s what it says, really.

Alexander Kuznetsov posted one of those skill-testing quizzes—calculating third Wednesday of the month with inline UDFs. Alexander begins, “Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as ‘third Wednesday of the month’, and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.”

Linchi Shea also was working the paradoxes: Performance impact: Logical scan fragmentation — 100% may not be as bad as 85%. “In [a] previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans.  . . .  The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.”

Reading is cool. I learned this from the Fonz. But look at this, Jeremiah Peschka says there are circumstances when less reading is a good thing. I’m confused! What else does he say? “Less reading is a good thing when you’re reading from a physical disk.” Ahhh. “During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called.  . . .  It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it. So, how did I fix this travesty of my youthful ignorance?” I bet he hits his server machine in just the right way.

In PostgreSQL blogs, David Wheeler and his readers discussed the pgTAP Set-Testing Update.

The Postgres OnLine Journal exposed a restore of functional indexes gotcha.

In the DB2 world, Craig Mullins advised: know your ISOLATION levels. Craig writes, “Did you know that DB2 provides a way to change the way that a program or SQL statement acquires locks? That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement.”

Willie Favero published the second of a couple articles collecting resources for both DB2 z/OS and DB2 LUW.

That’s all for now. Please add your favourite blogs from this week to the comments. And of course, don’t forget that you can publish an edition of Log Buffer on your own blog. Just send me an email to get started.

Till next time!