Search

Top 60 Oracle Blogs

Recent comments

May 2011

Getting fit (final update)…

I’ve posted previously about starting to get fit (here and here). I’ve called this the final update, not because it’s over, but because I don’t plan on making this a regular feature of the blog. It’s already too off topic without adding this into the mix also. :)

Today was my second personal training session. I’ve been to the gym every day since my last session with him and worked really hard, so I was ready for him to bear witness to my greatness… It was a big improvement, which I knew it would be. I started off with interval training and did double the amount intervals and on a higher level than the first time. After that I still felt human, so he asked me what I wanted to do next and I said, “Surprise me!” Big mistake. We moved on to cycles of:

This ruined me. If I had a bucket close I would have thrown up big-time. By next week I have to get to the point where I can cope with the interval training, followed by a few of those cycles. Man, it would be so much easier if I was skinny. :)

So in one week I’ve made a massive improvement fitness wise. Of course, the fitter you get the harder you try, so I still came out feeling like crap. Just another 30 years to go… :)

Cheers

Tim…

PS. Loved this comment from Chet, so I’ve added it to the bottom of the post so those folks that don’t venture into the comments won’t miss it.

Chet: “ugh, intervals are brutal. you feel like your HR (heart rate) will never come down…not to mention the desire to puke in the hopes that it will all just end.”

Welcome to my world… :)




Exadata drives exceed the laws of physics… ASM with intelligent placement improves IOPS

I recently had an interesting time with a customer who is all too familiar with SAN’s.  SAN vendors typically use IOPS/drive sizing numbers of 180 IOPS per drive.  This is a good conservative measure for SAN sizing, but the drives are capable of much more and indeed we state higher with Exadata.  So, how could this be possible?  Does Exadata have an enchantment spell that makes the drives magically spin faster?  Maybe a maybe a space time warp to service IO?

Slight 11.2.0.2 caveat with TTS and missing XMLDB

A small post about a problem I encountered a little while ago, quite interesting nevertheless.

The background story is that-for security reasons-my customer’s databases are all created with “Oracle Database Catalog Views” and “Oracle Database Packages and Types” only. Anything else will have to be requested by the application team. I like the idea in principle, and it is certainly a lot better than all those databases with UltraSearch, Spatial and Text installed which open the door to vulnerabilities and exploits. Have a look at the problems fixed by Critical Patch Updates and you will know what I am talking about.

As part of a project I have been asked to transport a tablespace from such a database to another host. Everything is fine until you try to export the tablespace metadata with expdp. As you know, anyone using traditional exp is a bit backward ;)

[oracle@node1 ~] $ expdp / dumpfile=tts_metadata.dmp directory=data_pump_dir logfile=tts_metadata.log \
> transport_tablespaces='TTS_TEST'
Export: Release 11.2.0.2.0 - Production on Tue May 10 16:05:49 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "ORACLE"."SYS_EXPORT_TRANSPORTABLE_01":  /******** dumpfile=tts_metadata.dmp logfile=tts_metadata.log
  directory=data_pump_dir transport_tablespaces=TTS_TEST
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK]
ORA-00904: "Q"."TABOBJ_NUM": invalid identifier
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x15e53a900     19208  package body SYS.KUPW$WORKER
0x15e53a900      8385  package body SYS.KUPW$WORKER
0x15e53a900      2935  package body SYS.KUPW$WORKER
0x15e53a900      9054  package body SYS.KUPW$WORKER
0x15e53a900      1688  package body SYS.KUPW$WORKER
0x155a4e638         2  anonymous block
Job "ORACLE"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:05:57
[oracle@node1 ~]

This is due to bug 10185688, which is fixed in 12.1, 11.2.0.3.0, or in one-off patch 10185688. Oracle 12.1 and 11.2.0.3 are not available at the time of this writing.

You will only encounter this problem if your database doesn’t have XML and dependent options compiled in. I wonder how many databases out there have options in DBA_SERVER_REGISTRY which aren’t used. When I was working at IGEFI we only introduced XMLDB to Multifonds after customers requested the ability to load XML files, prior to the move from 9i to 10g ( in 2006 that was). I still don’t understand why you’d want to prefer XML input files rather than CSV for external tables but that wasn’t my call either.

By the way, the old backward exp command doesn’t have this restriction!

[oracle@node1 ~] $ exp file=tts.dmp log=tts.log TRANSPORT_TABLESPACE=y TABLESPACES=TTS_TEST
Export: Release 11.2.0.2.0 - Production on Tue May 10 16:20:56 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS_TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                           TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Not a problem then, but quite interesting.

Oracle VM [not] running inside VirtualBox…

I know what you are thinking, sounds like a dumb idea right? Well yes, it is pretty stupid, but it’s nice to do it for testing Oracle VM when you don’t want to dedicate a whole server to it. I’ve done this before with no worries. The screen grabs for this article were taken from an installation of Oracle VM inside VirtualBox.

The reason for this post is it doesn’t seem to work for me anymore. Since I last did this successfully I’ve upgraded my host OS (to Fedora 14) and VirtualBox several times (now at 4.0.6). So before I start meddling with downgrading my OS, I would like to know if anyone else has any issues of this type of installation?

I’ve already tried installing old versions of VirtualBox (back to 3.2.8) and that doesn’t fix the issue, so it looks to me like it is the OS that is the issue (sigh). I get the same issue on two servers running Fedora14 as the host OS.

The problem: The installation of Oracle VM inside a VirtualBox VM works fine, but during the post install reboot the VM hangs and I get the following in my “/var/log/messages” file on the host.

May 10 10:42:26 maggie kernel: [  308.782339] BUG: unable to handle kernel paging request at 0000000000002dc4
May 10 10:42:26 maggie kernel: [  308.782345] IP: [] g_abExecMemory+0x1cee8/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782359] PGD 21600b067 PUD 216319067 PMD 0
May 10 10:42:26 maggie kernel: [  308.782363] Oops: 0000 [#1] SMP
May 10 10:42:26 maggie kernel: [  308.782366] last sysfs file: /sys/devices/system/cpu/cpu3/cache/index2/shared_cpu_map
May 10 10:42:26 maggie kernel: [  308.782370] CPU 3
May 10 10:42:26 maggie kernel: [  308.782371] Modules linked in: vboxnetadp vboxnetflt vboxdrv fuse cifs nfsd lockd nfs_acl auth_rpcgss exportfs sunrpc ipv6 nls_utf8 udf uinput snd_hda_codec_realtek snd_hda_intel snd_hda_codec snd_hwdep iTCO_wdt ppdev parport_pc parport r8169 iTCO_vendor_support sky2 mii asus_atk0110 i2c_i801 snd_seq snd_seq_device snd_pcm snd_timer snd soundcore snd_page_alloc microcode ata_generic pata_acpi firewire_ohci firewire_core crc_itu_t pata_jmicron nouveau usb_storage ttm drm_kms_helper drm i2c_algo_bit video output i2c_core [last unloaded: scsi_wait_scan]
May 10 10:42:26 maggie kernel: [  308.782377]
May 10 10:42:26 maggie kernel: [  308.782377] Pid: 5584, comm: VirtualBox Not tainted 2.6.35.13-91.fc14.x86_64 #1 P5K-VM/P5K-VM
May 10 10:42:26 maggie kernel: [  308.782377] RIP: 0010:[]  [] g_abExecMemory+0x1cee8/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377] RSP: 0018:ffff8801d63ffa68  EFLAGS: 00010286
May 10 10:42:26 maggie kernel: [  308.782377] RAX: 0000000000000000 RBX: 0000000000000000 RCX: 0000000000000003
May 10 10:42:26 maggie kernel: [  308.782377] RDX: 0000000000000001 RSI: ffff8801d63ffa38 RDI: 00007f579403be30
May 10 10:42:26 maggie kernel: [  308.782377] RBP: ffff8801d63ffae8 R08: 0000000000000008 R09: 0000000000000000
May 10 10:42:26 maggie kernel: [  308.782377] R10: 0000000000000fa0 R11: ffffffffa041f430 R12: 00007f57950ef260
May 10 10:42:26 maggie kernel: [  308.782377] R13: ffffc900118d3000 R14: ffffc900118eeb00 R15: 0000000000000008
May 10 10:42:26 maggie kernel: [  308.782377] FS:  00007f5795bae700(0000) GS:ffff880002180000(0000) knlGS:0000000000000000
May 10 10:42:26 maggie kernel: [  308.782377] CS:  0010 DS: 0000 ES: 0000 CR0: 0000000080050033
May 10 10:42:26 maggie kernel: [  308.782377] CR2: 0000000000002dc4 CR3: 00000001e6c77000 CR4: 00000000000026e0
May 10 10:42:26 maggie kernel: [  308.782377] DR0: 0000000000000000 DR1: 0000000000000000 DR2: 0000000000000000
May 10 10:42:26 maggie kernel: [  308.782377] DR3: 0000000000000000 DR6: 00000000ffff0ff0 DR7: 0000000000000400
May 10 10:42:26 maggie kernel: [  308.782377] Process VirtualBox (pid: 5584, threadinfo ffff8801d63fe000, task ffff8801fdce1740)
May 10 10:42:26 maggie kernel: [  308.782377] Stack:
May 10 10:42:26 maggie kernel: [  308.782377]  ffffffffa03c9830 00000000000001f4 ffff8801d63ffae8 ffffffffa03d8c17
May 10 10:42:26 maggie kernel: [  308.782377] <0> 00000000000b8000 ffff880100000002 000000000000064e ffff8801d63ffc27
May 10 10:42:26 maggie kernel: [  308.782377] <0> ffffc900118eeb00 0000000300000000 ffffc900118ee000 0000000000000002
May 10 10:42:26 maggie kernel: [  308.782377] Call Trace:
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? g_abExecMemory+0x1e070/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? g_abExecMemory+0x2d457/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] g_abExecMemory+0x3508b/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? g_abExecMemory+0x5907e/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] g_abExecMemory+0x2cece/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] g_abExecMemory+0x11e1e/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? g_abExecMemory+0xf840/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? g_abExecMemory+0xf25e/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] g_abExecMemory+0xa18d/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] g_abExecMemory+0x49f97/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] g_abExecMemory+0x1599b/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? unlock_page+0x27/0x2c
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? __do_fault+0x342/0x379
May 10 10:42:26 maggie kernel: [  308.782377]  [] supdrvIOCtlFast+0x50/0x54 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] VBoxDrvLinuxIOCtl+0x44/0x1b0 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  [] ? pmd_offset+0x19/0x40
May 10 10:42:26 maggie kernel: [  308.782377]  [] vfs_ioctl+0x36/0xa7
May 10 10:42:26 maggie kernel: [  308.782377]  [] do_vfs_ioctl+0x468/0x49b
May 10 10:42:26 maggie kernel: [  308.782377]  [] sys_ioctl+0x56/0x79
May 10 10:42:26 maggie kernel: [  308.782377]  [] system_call_fastpath+0x16/0x1b
May 10 10:42:26 maggie kernel: [  308.782377] Code: 24 60 45 89 f8 48 8b 55 a0 41 ff d3 85 c0 89 c1 44 8b 55 88 0f 85 91 fe ff ff 45 89 ff 42 8b 0c bd c0 70 40 a0 41 d3 e2 4d 01 16  83 c4 2d 00 00 10 0f 84 6a fe ff ff 41 c7 46 30 00 00 00 00
May 10 10:42:26 maggie kernel: [  308.782377] RIP  [] g_abExecMemory+0x1cee8/0x180000 [vboxdrv]
May 10 10:42:26 maggie kernel: [  308.782377]  RSP
May 10 10:42:26 maggie kernel: [  308.782377] CR2: 0000000000002dc4
May 10 10:42:26 maggie kernel: [  308.782663] ---[ end trace b439b59bc93da8ee ]---

I’ve done the standard Googling, but nothing jumped out at me as a possible solution.

Cheers

Tim…

 




New Guitar

I turned in the first draft of my last chapter in Expert Oracle Exadata last weekend. Tanel and Randy are working on their last chapters so we should be done with the basic material in a matter of days. We’ll still have a fair amount of editing work to do, but that’s a lot easier than coming up with the first pass at the material (which feels a little like giving birth). So as a reward to myself I bought a new guitar.


The picture’s a little dark, but check out the awesome paisley case in the background. I chickened out and didn’t play it on Sunday at my church gig. (takes a little while to get used to a new guitar don’t you know) Maybe next week. ;)

blogs.oracle.com.

As some of you will have noticed blogs.oracle.com has recently moved platform from Movable Type to Roller. This appears to be essentially the old blogs.sun.com platform. I do have some issues with it though – most notably the fact that the blog search now requires an oracle.com SSO account and doesn’t return results that it [...]

Consistent Gets

There’s an interesting question on the OTN database forum at present – why does an update of 300,000 rows take a billion buffer visits. (There are 25 indexes on the table – so you might point a finger at then initially, but only one of the indexes is going to be changed by the update so that should only account for around an extra 10 gets per row in a clean environment.)

The answer to the question hadn’t been reached by the time I wrote this note – and this note isn’t intended as a suggested cause of the issue, it’s just an example of the type of thing that could cause an apparent excess of buffer visits. Here’s a little bit of code I’ve just tested on 11.1.0.6 using an 8KB block size

drop sequence t1_seq;
create sequence t1_seq;

drop table t1;

create table t1 as
select
	rownum id, rpad('x',10) small_vc
from
	all_objects
where
	rownum <= 11
;

execute dbms_stats.gather_table_stats(user,'t1')

select * from t1;

pause

execute snap_my_stats.start_snap
update t1 set small_vc = upper('small_vc') where id = 11;
execute snap_my_stats.end_snap

(The calls to the “snap_my_stats” package simply record the current contents of v$mystat joined to v$statname before and after the update and print the changes.)

The code simply creates a sequence and a table with 11 rows and no indexes, then updates one specific row in the table. However, where the “pause” appears, I start up 10 separate sessions to do the following:

column seqval new_value m_seq

select t1_seq.nextval seqval from dual;

update t1 set small_vc = upper(small_vc) where id = &m_seq;

pause

exit

So when I hit return on the pause for the first session, there are 10 separate active transactions on the single block in my table, one for each row except row 11. (And now you know what the sequence was for.)

Here’s a subset of the statistics from v$mystat after my update statement – remember, all I’ve done is update one row in one block using a tablescan:


Name                                                                     Value
----                                                                     -----
session logical reads                                                       45
db block gets                                                                3
db block gets from cache                                                     3
consistent gets                                                             42
consistent gets from cache                                                  42
consistent gets from cache (fastpath)                                        3
consistent gets - examination                                               39
db block changes                                                             7
consistent changes                                                          13
calls to kcmgrs                                                             26
calls to kcmgas                                                              1
calls to get snapshot scn: kcmgss                                            7
redo entries                                                                 3
redo size                                                                  764
undo change vector size                                                    236
data blocks consistent reads - undo records applied                         13
active txn count during cleanout                                            20
table scan blocks gotten                                                     1

Note the last statistics – just one block accessed by tablescan – compared to the session logical reads at 45 buffer visits.
That 45 buffer visits comes from 3 current (db) block gets and 42 consistent gets.
Of the 42 consistent gets 39 are examinations, which – in the absence of indexes and hash clusters are visits to undo blocks
The 39 undo visits are to find 13 undo records to apply, and 26 visits (to undo segment headers) to find 13 transaction SCNs.

What you’re seeing is one session doing (relatively speaking) a lot of work to hide the effects of other sessions which have not yet committed their transactions. (This was only a quick test, so I haven’t examined why the larger figures appear in multiples of 13 rather than multiples of 10 – the number of other transactions – and since this note is just trying to demonstrate a concept I won’t be looking into it any further.)

If you have a number of “non-interfering” transactions – i.e. transactions that don’t actually lock each other out – on a single table then you could find that they spend more time hiding each other’s work than they do doing their own work.

Footnote:

The numbers change significantly if I commit the 10 transactions (but wait until they’ve all executed, so they are all active at the same time) before I do the update to the 11th row.

The numbers changed even more surprisingly when I forgot to collect stats on the table in my initial example of the test.

Getting fit (update)…

I mentioned in my last post about trying to get fit. I just thought I would give a little update…

It’s 5 days since I joined the new gym and 4 days since my disastrous first personal training session. What a difference 4 days makes. I’ve been hitting the gym every day and doing interval training (sprint-slow intervals) like I did with the personal trainer. Even though I’m fat I’ve got quite a bit of endurance for steady paced stuff. I can swim 2 miles of front crawl without a problem. I can ride a stationary bike or cross trainer for ages, providing boredom doesn’t set in. The interval training is a totally different kettle of fish. On the first attempt 15 minutes killed me. Today 30 minutes felt OK. It’s never going to be easy because the fitter you get the harder you try. I’m trying not to get too excited and extrapolate to a beating Paula Radcliffe in a marathon, but I’m pleased with the difference already.

One slight issue, that I should probably consider a good thing, is the exercise is making me gain muscle at a crazy rate. I did a lot of weight training as a kid and I guess the whole muscle memory thing kicks in. Everything has puffed up, but hidden under a liberal coating of fat it just makes me look fatter now than I did when I joined the gym. In the long term, it’s a good thing as muscle is active and burns fat even when resting, but at the moment it feels a little negative.

Anyway, new week starts tomorrow…

Cheers

Tim…




Tanel Poder's Virtual Class #2

On a fine day in some month in some year, a little scene is playing out in Acme Bank's IT department.

Developer (with the best I-told-you-these-DBAs-are morons face) harrumphs, "The database is slow again!!! Does anybody even care?"

Junior DBA enthusiastically responds with a slightly belligerent tone, "Of course we do. I opened a ticket. The ticket# is ...."

Mid-level DBA announces with an officious tone, "The session is waiting for latches" as if explains everything.

Mid-level DBA aspiring to be the top dog chimes in "the session has waited 123.904567 seconds in cache buffer chain latch, 24.096534 seconds in share pool latch, 456.742340 seconds in db file scattered read ...." and rants the list of wait events measured precisely by taking a trace and/or looking at an esoteric mix of v$ and x$ views to drive even Graham Wood mad.

SQL ASH timings III

in the last two SQL ASH timing posts we looked at

all three links:

  1. http://dboptimizer.com/2011/05/04/sql-execution-times-from-ash/
  2. http://dboptimizer.com/2011/05/06/sql-timings-for-ash-ii/
  3. http://dboptimizer.com/2011/05/06/sql-ash-timings-iii/

Another question that comes to mind, is “when did the slowest running query happen?” (and ultimately what is the timeline of executions and elapses times)

#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;">
col av for 9999999
col mx for 9999999
col mn for 9999999

select
   sql_id,
   count(*),
   round(min(delta),0) mn,
   round(avg(delta),0) av,
   round(max(delta),0) mx,
   substr(max(times),12) max_run_time
from (
   select
        sql_id,
        sql_exec_id,
        max(delta) delta ,
        -- lpad sets a fixed width on delta so it can be
        -- stripped off above with substr
        -- delta in "times" is just for sorting not displaying
        lpad(round(max(delta),0),10) || ' ' ||
        to_char(min(start_time),'YY-MM-DD HH24:MI:SS')  || ' ' ||
        to_char(max(end_time),'YY-MM-DD HH24:MI:SS')  times
   from ( select
                                            sql_id,
                                            sql_exec_id,
              cast(sample_time as date)     end_time,
              cast(sql_exec_start as date)  start_time,
              ((cast(sample_time    as date)) -
               (cast(sql_exec_start as date))) * (3600*24) delta

           from
              dba_hist_active_sess_history
           where sql_exec_id is not null
        )
   group by sql_id,sql_exec_id

)
group by sql_id
having count(*) > 10
order by mx
/
#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;">
SQL_ID          COUNT(*)       MN       AV       MX MAX_RUN_TIME
------------- ---------- -------- -------- -------- -----------------------------------
155vjqzjqp58f         27        0      115      786 11-04-11 07:39:47 11-04-11 07:41:20
7nf5917x3tq5x        113        0      129      791 11-04-11 01:03:19 11-03-02 04:16:09
asg774pz0kqga         11       11      225      799 11-04-14 12:20:10 11-03-02 12:18:58
1kf1jsykb1nk6        529        0      107      800 11-04-09 18:41:28 11-03-01 23:13:56
8rua4c9agcqkb        978        0       73      820 11-03-01 23:13:50 11-03-01 23:13:56
fdagtr1sdk8tn        313        0      242      841 11-04-08 21:30:31 11-04-08 21:30:38
7b5w34atn8q1h        124        0       25      898 11-03-07 20:53:17 11-03-07 20:53:23
9w7mbuv734tfy         11       30      509      954 11-04-14 09:00:55 11-03-02 04:27:33
88qxmg220db9s         23        4      357     1040 11-04-13 22:08:20 11-04-13 02:20:09
83ngzw213v0cs        302        1      207     1122 11-04-11 16:26:32 11-03-07 16:12:11
34cgtc9xkgxny         61      978     1163     1272 11-03-02 10:06:24 11-03-02 10:06:30
g5u58zpg0tuk8         97        1       62     1359 11-04-13 02:49:33 11-04-12 02:23:39
6tkqndn1tzf23         29        7      187     1370 11-04-13 06:56:43 11-03-02 06:56:54
g4wrnvdmf23b1         11       19      458     1397 11-04-14 12:22:28 11-03-02 12:21:19
bpkg6y9wvf717         31        0      189     1427 11-04-13 21:19:51 11-03-02 21:08:00
4d6m2q3ngjcv9        320        3      485     1701 11-04-10 18:00:54 11-03-01 23:10:55
882tp5sbq1tq5         17       39      367     1795 11-04-14 14:00:30 11-03-01 23:04:33
5k7vccwjr5ahd       2653        0       33     1963 11-04-12 09:06:08 11-03-01 23:10:15
44034jty9vnrg         19        2      188     1968 11-03-03 09:17:34 11-03-03 09:17:43
g9n69a23s3wxz         40        6      280     2009 11-04-08 13:47:23 11-03-03 09:37:38
ds8cz0fb8w147        161       13      274     2531 11-04-12 16:10:21 11-03-01 23:11:55
bzyny95313u12        114        0       47     2599 11-03-03 03:06:18 11-03-03 03:06:25
0fvrpk7476b7y         26        0      133     3068 11-03-07 07:57:56 11-03-07 07:58:00
1pjp66rxcj6tg         15       57      768     3106 11-04-09 17:25:21 11-03-07 08:58:26
8r5wuxk1dprhr         39       24      841     3510 11-04-14 14:00:47 11-03-02 09:54:37
0w5uu5kngyyty         21        0      442     3652 11-04-11 08:00:21 11-04-11 09:01:13
0hbv80w9ypy0n        161        0     1184     4089 11-04-12 22:26:09 11-03-02 04:36:15
71fwb4n6a92fv         49       30      677     4481 11-04-10 17:01:17 11-03-01 23:40:45
0bujgc94rg3fj        604        0       25     4929 11-04-08 10:53:34 11-04-08 12:15:43
64dqhdkkw63fd       1083        0        7     7147 11-03-07 04:01:01 11-03-07 06:00:08
990m08w8xav7s        591        0       52     7681 11-04-13 00:39:27 11-04-13 00:39:37
2n5369dsuvn5a         16      303     5727    10472 11-04-14 11:18:11 11-04-14 14:12:43
2spgk3k0f7quz        251        0      546    29607 11-04-12 12:11:47 11-04-12 12:11:48
36pd759xym9tc         12     1391    23862    37934 11-04-13 19:25:49 11-03-01 23:00:12
497wh6n7hu14f         49        0     5498    69438 11-04-12 19:01:17 11-04-13 12:03:54

and we can put the histogram and the longest elapsed time dates into one query

NOTE: modified query since posting to use mindelta as low_boundery in bucket_width and changed

from

 to_char(max(start_time),'YY-MM-DD HH24:MI:SS')  || ' ' ||
        to_char(min(end_time),'YY-MM-DD HH24:MI:SS')  times

to

 to_char(min(start_time),'YY-MM-DD HH24:MI:SS')  || ' ' ||
        to_char(max(end_time),'YY-MM-DD HH24:MI:SS')  times
#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;">
col 1 for 99999
col 2 for 99999
col 3 for 9999
col 4 for 999
col 5 for 99
col av for 99999.9
col ct for 99999
col mn for 999
col av for 99999.9

with pivot_data as (
select
      sql_id,
      ct,
      mxdelta mx,
      mndelta mn,
      avdelta av,
      -- high_boundary is set to maxdelta + 1 so that
      -- nothing ends up in the overflow bucket
      -- otherwise the max value ends up in the overflow bucket
      width_bucket(delta,mndelta,mxdelta+1,5) as bucket  ,
      substr(times,12) max_run_time
from (
select
   sql_id,
   delta,
   count(*) OVER (PARTITION BY sql_id) ct,
   max(delta) OVER (PARTITION BY sql_id) mxdelta,
   min(delta) OVER (PARTITION BY sql_id) mndelta,
   avg(delta) OVER (PARTITION BY sql_id) avdelta,
   max(times) OVER (PARTITION BY sql_id) times
from (
   select
        sql_id,
        sql_exec_id,
        max(delta) delta ,
        -- lpad sets a fixed width on delta so it can be
        -- stripped off above with substr
        -- delta in "times" is just for sorting not displaying
        lpad(round(max(delta),0),10) || ' ' ||
        to_char(min(start_time),'YY-MM-DD HH24:MI:SS')  || ' ' ||
        to_char(max(end_time),'YY-MM-DD HH24:MI:SS')  times
   from ( select
                                            sql_id,
                                            sql_exec_id,
              cast(sample_time as date)     end_time,
              cast(sql_exec_start as date)  start_time,
              ((cast(sample_time    as date)) -
               (cast(sql_exec_start as date))) * (3600*24) delta
           from
              dba_hist_active_sess_history
           where sql_exec_id is not null
        )
   group by sql_id,sql_exec_id
)
)
where ct > &min_repeat_executions_filter
)
select * from pivot_data
PIVOT ( count(*) FOR bucket IN (1,2,3,4,5))
order by mx,av;
/
#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;">
SQL_ID            CT         MX   MN       AV MAX_RUN_TIME                             1      2     3    4   5
------------- ------ ---------- ---- -------- ----------------------------------- ------ ------ ----- ---- ---
8rua4c9agcqkb    978        820    0     72.7 11-03-01 23:13:50 11-03-01 23:13:56    830    116    21    9   2
fdagtr1sdk8tn    313        841    0    241.8 11-04-08 21:30:31 11-04-08 21:30:38    194     45     0    0  74
7b5w34atn8q1h    124        898    0     24.8 11-03-07 20:53:17 11-03-07 20:53:23    122      0     0    0   2
83ngzw213v0cs    302       1122    1    207.5 11-04-11 16:26:32 11-03-07 16:12:11    200     68    21    6   7
g5u58zpg0tuk8     97       1359    1     62.1 11-04-13 02:49:33 11-04-12 02:23:39     92      3     1    0   1
4d6m2q3ngjcv9    320       1701    3    484.7 11-04-10 18:00:54 11-03-01 23:10:55     92    168    50    9   1
5k7vccwjr5ahd   2653       1963    0     33.4 11-04-12 09:06:08 11-03-01 23:10:15   2623     15     8    4   3
ds8cz0fb8w147    161       2531   13    273.8 11-04-12 16:10:21 11-03-01 23:11:55    136     18     5    1   1
bzyny95313u12    114       2599    0     46.5 11-03-03 03:06:18 11-03-03 03:06:25    113      0     0    0   1
0hbv80w9ypy0n    161       4089    0   1183.9 11-04-12 22:26:09 11-03-02 04:36:15     27    116     9    6   3
71fwb4n6a92fv     49       4481   30    676.9 11-04-10 17:01:17 11-03-01 23:40:45     38      6     2    2   1
0bujgc94rg3fj    604       4929    0     24.7 11-04-08 10:53:34 11-04-08 12:15:43    601      1     1    0   1
64dqhdkkw63fd   1083       7147    0      7.2 11-03-07 04:01:01 11-03-07 06:00:08   1082      0     0    0   1
990m08w8xav7s    591       7681    0     51.8 11-04-13 00:39:27 11-04-13 00:39:37    587      0     0    2   2
2spgk3k0f7quz    251      29607    0    546.1 11-04-12 12:11:47 11-04-12 12:11:48    247      2     0    0   2
497wh6n7hu14f     49      69438    0   5498.2 11-04-12 19:01:17 11-04-13 12:03:54     44      1     0    1   3

NOTE: new version to include longest SQL_EXEC_ID which makes going and looking up the wait events for the longest SQL_EXEC_ID easy thanks to Jerry Brenner for this addition as well as feedback on the original

#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;">
col 1 for 99999
col 2 for 99999
col 3 for 9999
col 4 for 999
col 5 for 99
col av for 99999
col ct for 99999
col mn for 999
col av for 99999.9
col longest_sql_exec_id for A10

WITH pivot_data AS (
SELECT
      sql_id,
      ct,
      mxdelta mx,
      mndelta mn,
      round(avdelta) av,
      WIDTH_BUCKET(delta_in_seconds,mndelta,mxdelta+.1,5) AS bucket  ,
      SUBSTR(times,12) max_run_time,
      SUBSTR(longest_sql_exec_id, 12) longest_sql_exec_id
FROM (
SELECT
   sql_id,
   delta_in_seconds,
   COUNT(*) OVER (PARTITION BY sql_id) ct,
   MAX(delta_in_seconds) OVER (PARTITION BY sql_id) mxdelta,
   MIN(delta_in_seconds) OVER (PARTITION BY sql_id) mndelta,
   AVG(delta_in_seconds) OVER (PARTITION BY sql_id) avdelta,
   MAX(times) OVER (PARTITION BY sql_id) times,
   MAX(longest_sql_exec_id) OVER (PARTITION BY sql_id) longest_sql_exec_id
FROM (
   SELECT
        sql_id,
        sql_exec_id,
        MAX(delta_in_seconds) delta_in_seconds ,
        LPAD(ROUND(MAX(delta_in_seconds),0),10) || ' ' ||
        TO_CHAR(MIN(start_time),'YY-MM-DD HH24:MI:SS')  || ' ' ||
        TO_CHAR(MAX(end_time),'YY-MM-DD HH24:MI:SS')  times,
        LPAD(ROUND(MAX(delta_in_seconds),0),10) || ' ' ||
        TO_CHAR(MAX(sql_exec_id)) longest_sql_exec_id
   FROM ( SELECT
                                            sql_id,
                                            sql_exec_id,
              CAST(sample_time AS DATE)     end_time,
              CAST(sql_exec_start AS DATE)  start_time,
              ((CAST(sample_time    AS DATE)) -
               (CAST(sql_exec_start AS DATE))) * (3600*24) delta_in_seconds
           FROM
              dba_hist_active_sess_history
           WHERE sql_exec_id IS NOT NULL
        )
   GROUP BY sql_id,sql_exec_id
)
)
where ct > &min_repeat_executions_filter
 and  mxdelta > &min_elapsed_time
)
SELECT * FROM pivot_data
PIVOT ( COUNT(*) FOR bucket IN (1,2,3,4,5))
ORDER BY mx DESC,av DESC
;
#ffffff;">
#ffffff; padding-right: .5em;"> #ffffff; padding-right: .5em;">
SQL_ID            CT         MX   MN       AV MAX_RUN_TIME                        LONGEST_SQ      1      2     3    4   5
------------- ------ ---------- ---- -------- ----------------------------------- ---------- ------ ------ ----- ---- ---
2spgk3k0f7quz    251      29607    0    546.0 11-04-12 12:11:47 11-04-12 20:25:14 16781748      247      2     0    0   2
990m08w8xav7s    591       7681    0     52.0 11-04-13 00:39:27 11-04-13 02:47:28 16786685      587      0     0    2   2
64dqhdkkw63fd   1083       7147    0      7.0 11-03-07 04:01:01 11-03-07 06:00:08 16777218     1082      0     0    0   1
0bujgc94rg3fj    604       4929    0     25.0 11-04-08 10:53:34 11-04-08 12:15:43 16814628      601      1     1    0   1
0hbv80w9ypy0n    161       4089    0   1184.0 11-03-02 04:36:10 11-04-12 23:34:18 16777290       27    116     9    6   3
bzyny95313u12    114       2599    0     47.0 11-03-03 03:06:18 11-03-03 03:49:37 16781191      113      0     0    0   1
ds8cz0fb8w147    161       2531   13    274.0 11-03-01 23:11:48 11-04-12 16:10:37 16777285      136     18     5    1   1
5k7vccwjr5ahd   2653       1963    0     33.0 11-03-01 23:10:12 11-04-12 09:06:12 16778244     2623     15     8    4   3
4d6m2q3ngjcv9    320       1701    3    485.0 11-03-01 23:10:53 11-04-10 18:01:26 16777261       92    168    50    9   1
g5u58zpg0tuk8     97       1359    1     62.0 11-04-12 02:23:37 11-04-13 02:51:09 16777217       92      3     1    0   1
34cgtc9xkgxny     61       1272  978   1163.0 11-03-02 10:06:24 11-03-02 10:27:36 16777250        4      4    14   30   9