Oakies Blog Aggregator

Well…its been quite a year!  I downloaded the Oxford “Words of the Year” report which came out with gems such as

• Bushfires
• Impeachment
• Coronavirus
• Lockdown
• Social Distancing
• Zoom Bombing
• Mask Shaming
• Black Lives Matter
• Cancel Culture
• Superspreader

amongst others. “Covidiot” cracked a mention and remains a personal favourite , although reading the list above could perhaps be considered just a reinforcement of another of the words…”doom scrolling”.

Business as usual

It is astonishing that just 11 months ago, the year had commenced like any other. Advocates, Aces and PM’s all came together to share their knowledge at the Israel Oracle User Group event in Tel Aviv.

I grabbed this next pic from the front of Oren Nakdimon‘s place where he and his family hosted us for a magnificent dinner – definitely a highlight of the trip, and in hindsight, a brutal reminder of the importance (and subsequent loss) of face to face networking.

After the event, I took a couple of days vacation and travelled to Petra for the first time, which was breathtaking.

Enough time has passed now for me to share a funny tale about the Petra experience.

We left Petra around 5pm, and then its a 5 hour bus ride back to Tel Aviv. About half way through the ride, in the pitch black of the desert, our bus was brought to a halt by our driver at a small intersection. Two small mini-vans then arrived from another direction and our driver told us all to exit the bus and get into the mini-vans. This wasn’t mentioned on the itinerary, but with slightly raised heart rates, the ten of us travelling split into the two vans.

Off into the dark we drove in a new direction. After about 40 minutes, in the absolute middle on nowhere our minivan came to a halt and our driver switched off the engine, and thus we sat in silence and darkness. No explanation given. As we soon discovered, our driver did not speak english and none of us were fluent in the local dialect. Another ten minutes passed and every time we tried to communicate with the driver, he simply made a “Sssh” sign with his finger. Now a good deal of panic was starting to set in, and we started to discuss fleeing the bus, to rushing the driver, and countless other options that are borne out of paranoia. Just as we were doing this, two taxis emerged on the horizon. Even then, we were thinking to ourselves: “Is there where we are all never seen again?” Thankfully, one of the taxi drivers spoke english and shed some light. It all turned out to be a miscommunication – the large bus was needed elsewhere, the minivans were a substitute, and ultimately the tour company decided it would be cheaper to grab taxis from the current point and take the minivans back to their base.

Or at least that’s what we were told

Then onto Italy, where as always the Italian Oracle User Group were fantastic hosts, treating us to a wonderful tech event, as well as experiencing the sights (or should I say sites ) of Rome and Milan! Thank you again to Francesco, Roberto, Gianni and the rest of the team for their hospitality.

The only time Francesco doesn’t wave his arms when speaking, is when there is a glass in his hand

And of course, coffee as only coffee should be!

Reaching out to the next generation

In February I spoke at SINFO, a tech conference organised and run entirely by a student body in Lisbon, Portugal. It was nice to speak to a large group of tech-passionate individuals who were still “finding their way” in the ever changing I.T landscape. Many were unaware that databases underpinned so much of the software they used on a daily basis.

A nice surprise on the flight to Portugal, was that one of the flight attendants was a friend I had coached at volleyball many years ago in Perth. Sally was nice enough to take a few minutes for a chat and a selfie, but alas, the upgrade to business class did not eventuate

And then everything changed

My itinerary for March initially read like this:

• London for a meetup,
• then to Ireland for their event,
• then to Netherlands to meet with Qualogy,
• then onto Romania to present at their user group meeting,
• back to Netherlands for an APEX event,
• then to London for customer meetings and then home

Then as the COVID situation grew in seriousness and global scope, there was a manic few weeks where events were being changed and/or cancelled on a day to day basis. The Ireland event got cancelled, and so I went straight on to the Netherlands for the Qualogy event, which was a great success. Big thanks to Patrick Barel for his organisation of that event,

Then the APEX event got postponed and customer meetings in London were abandoned. I was starting to panic about travel, and thus I did the Romanian event as a virtual one via Zoom from my Amsterdam Hotel, thanks to a lot of assistance from Mirela from the Romanian User Group.

The seriousness the COVID situation really hit home when I saw a notification from the Australian foreign affairs department advising that if I didn’t get home in the next few days, then the borders would be shut with no target date for re-opening. I then spent more time on the phone with travel companies than any other time in my life! This was followed by an anxious 24 hour wait in Amsterdam trying to get all of the ducks in a row so I could get home to Perth. Thankfully, Amsterdam is the perfect place to wander to get rid of all the stress.

Finally the confirmations came through and I manage to rearrange my travel to immediately head home. Until that moment, I was unaware how lucky I was until I discovered that I had scored one of the very last flights to Perth! Thus when I entered transit in Dubai, I was met with this sight – one that I doubt will ever be repeated. Besides a few remaining shop staff, as far as the eye could see, I was the only person in the terminal!

A virtual world

With face to face meetings on hold, database advocacy still must go on so virtual events became the norm.

Luckily for us, AskTOM had been running the Office Hours program for 2 years, so we were already well equipped in terms of running Zoom sessions, and it was a privilege to be asked by others within the Oracle organisation for tips and techniques on becoming fluent with the various hardware/software technologies that make for good virtual presentations.

For me, this was an extra challenge because most sessions I give are in the evening Perth time, so having anything resembling natural lighting is difficult. Thankfully, LEDs don’t draw too much power, otherwise I think I would need to invest in some solar power. And who needs expensive acoustic sound panels when a blanket can just be hung from the ceiling!

So whilst some very late nights have become the norm, I also appreciate the fact that the embrace of virtual events means I could “attend” some conferences that I normally would not be able to due to the travel distances. However, nothing will ever replace the fulfilment of face to face events, and already we’re starting to the see effects of “zoom burnout“. One of the big challenges for 2021 is how to avoid virtual events being viewed as “just another webinar”.

But one personal benefit of the new virtual approach to conferences, was that more time at home meant that I could bring an new addition into the family. I got Bailey from a greyhound rescue centre in April, and he occasionally makes an appearance in Zoom calls.

Wrapping up 2020

With all the challenges of 2020, I hope you have still got value out of my tech content this year. After all, my job is to make you be more successful with the Oracle Database. I think I’ve still managed reasonably productive output this year:

• Presented 46 conference sessions,
• Published 81 videos,
• Published 67 blog posts,
• Answered ~600 AskTOM questions,
• Responded to ~1000 AskTOM comments

but I’m always keen to hear your ideas, your needs and yes, your frustrations as well. There have been a lot of cool new things in the Oracle landscape this year, and in particular, lots of great leaps forward in the amount of free stuff you can use.

So I look forward to hearing from you in 2021. Until then, in whichever way you observe the festive season, please stay safe and healthy, and hopefully 2021 will continue to strengthen our Oracle community.

PS – if you’d like a funny (but very NSFW) summation of 2020, here’s a classic made by Jason Redwine on Twitter from July.

State of the Word is an annual keynote address delivered by WordPress project co-founder, Matt Mullenweg. Catch this year’s keynote in the video below!

New to State of the Word?

If this is your first time hearing of this talk and want to learn more, you’re in luck! Check out previous recordings below.

• State of the Word 2019 – WordCamp US, St. Louis
• State of the Word 2018 – WordCamp US, Nashville
• All recordings

By Franck Pachot

.
This was initially posted to CERN Database blog on Thursday, 27 September 2018 where it seems to be lost. Here is a copy thanks to web.archive.org

Did you ever try to query DBA_EXTENTS on a very large database with LMT tablespaces? I had to, in the past, in order to find which segment a corrupt block belonged to. The information about extent allocation is stored in the datafiles headers, visible though X$KTFBUE, and queries on it can be very expensive. In addition to that, the optimizer tends to start with the segments and get to this X$KTFBUE for each of them. At this time, I had quickly created a view on the internal dictionary tables, forcing to start by X$KTFBUE with materialized CTE, to replace DBA_EXTENTS. I published this on dba-village in 2006.

I recently wanted to know the segment/extend for a hot block, identified by its file_id and block_id on a 900TB database with 7000 datafiles and 90000 extents, so I went back to this old query and I got my result in 1 second. The idea is to be sure that we start with the file (X$KCCFE) and then get to the extent allocation (X$KTFBUE) before going to the segments:

https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/12/Capture... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/12/Capture... 1024w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/12/Capture... 768w" sizes="(max-width: 1262px) 100vw, 1262px" />

So here is the query:

column owner format a6
column segment_type format a20
column segment_name format a15
column partition_name format a15
set linesize 200
set timing on time on echo on autotrace on stat
WITH
 l AS ( /* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno */
  SELECT ktfbuesegtsn segtsn,ktfbuesegfno segrfn,ktfbuesegbno segbid, ktfbuefno extrfn,
         ktfbuebno fstbid,ktfbuebno + ktfbueblks - 1 lstbid,ktfbueblks extblks,ktfbueextno extno
  FROM sys.x$ktfbue
 ),
 d AS ( /* DMT extents ts#, segfile#, segblock# */
  SELECT ts# segtsn,segfile# segrfn,segblock# segbid, file# extrfn,
         block# fstbid,block# + length - 1 lstbid,length extblks, ext# extno
  FROM sys.uet$
 ),
 s AS ( /* segment information for the tablespace that contains afn file */
  SELECT /*+ materialized */
  f1.fenum afn,f1.ferfn rfn,s.ts# segtsn,s.FILE# segrfn,s.BLOCK# segbid ,s.TYPE# segtype,f2.fenum segafn,t.name tsname,blocksize
  FROM sys.seg$ s, sys.ts$ t, sys.x$kccfe f1,sys.x$kccfe f2 
  WHERE s.ts#=t.ts# AND t.ts#=f1.fetsn AND s.FILE#=f2.ferfn AND s.ts#=f2.fetsn
 ),
 m AS ( /* extent mapping for the tablespace that contains afn file */
SELECT /*+ use_nl(e) ordered */
 s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
 FROM s,l e
 WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
 UNION ALL
 SELECT /*+ use_nl(e) ordered */ 
 s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
 FROM s,d e
  WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
 UNION ALL
 SELECT /*+ use_nl(e) use_nl(t) ordered */
 f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.ktfbfebno fstbid,e.ktfbfebno+e.ktfbfeblks-1 lstbid,e.ktfbfeblks extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
 FROM sys.x$kccfe f,sys.x$ktfbfe e,sys.ts$ t
 WHERE t.ts#=f.fetsn and e.ktfbfetsn=f.fetsn and e.ktfbfefno=f.ferfn
 UNION ALL
 SELECT /*+ use_nl(e) use_nl(t) ordered */
 f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.block# fstbid,e.block#+e.length-1 lstbid,e.length extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
 FROM sys.x$kccfe f,sys.fet$ e,sys.ts$ t
 WHERE t.ts#=f.fetsn and e.ts#=f.fetsn and e.file#=f.ferfn
 ),
 o AS (
  SELECT s.tablespace_id segtsn,s.relative_fno segrfn,s.header_block   segbid,s.segment_type,s.owner,s.segment_name,s.partition_name
  FROM SYS_DBA_SEGS s
 ),
datafile_map as (
SELECT
 afn file_id,fstbid block_id,extblks blocks,nvl(segment_type,decode(segtype,null,'free space','type='||segtype)) segment_type,
 owner,segment_name,partition_name,extno extent_id,extblks*blocksize bytes,
 tsname tablespace_name,rfn relative_fno,m.segtsn,m.segrfn,m.segbid
 FROM m,o WHERE extrfn=rfn and m.segtsn=o.segtsn(+) AND m.segrfn=o.segrfn(+) AND m.segbid=o.segbid(+)
UNION ALL
SELECT
 file_id+(select to_number(value) from v$parameter WHERE name='db_files') file_id,
 1 block_id,blocks,'tempfile' segment_type,
 '' owner,file_name segment_name,'' partition_name,0 extent_id,bytes,
  tablespace_name,relative_fno,0 segtsn,0 segrfn,0 segbid
 FROM dba_temp_files
)
select * from datafile_map where file_id=5495 and 11970455 between block_id and block_id+blocks

And here is the result, with execution statistics:

   FILE_ID   BLOCK_ID     BLOCKS SEGMENT_TYPE         OWNER  SEGMENT_NAME    PARTITION_NAME    EXTENT_ID      BYTES TABLESPACE_NAME      RELATIVE_FNO     SEGTSN     SEGRFN    SEGBID
---------- ---------- ---------- -------------------- ------ --------------- ---------------- ---------- ---------- -------------------- ------------ ---------- ---------- ----------
      5495   11964544            8192 INDEX PARTITION LHCLOG DN_PK           PART_DN_20161022 1342         67108864 LOG_DATA_20161022            1024       6364       1024        162

Elapsed: 00:00:01.25

Statistics
----------------------------------------------------------
        103  recursive calls
       1071  db block gets
      21685  consistent gets
        782  physical reads
        840  redo size
       1548  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Knowing the segment from the block address is important in performance tuning, when we get the file_id/block_id from wait event parameters. It is even more important when a block corrution is detected ans having a fast query may help.

Cet article Efficiently query DBA_EXTENTS for FILE_ID / BLOCK_ID est apparu en premier sur Blog dbi services.

By Franck Pachot

.
This was initially posted on CERN Database blog where it seems to be lost. Here is a copy thanks to web.archive.org
Additional notes:
– I’ve tested and got the same behaviour in Oracle 21c
– you will probably enjoy reading Hatem Mahmoud going further on Write consistency and DML restart

Posted by Franck Pachot on Thursday, 27 September 2018

Here is a quick test I did after encountering an abnormal behavior in write consistency and before finding some references to a bug on StackOverflow (yes, write consistency questions on StackOverflow!) and AskTOM. And a bug opened by Tom Kyte in 2011, that is still there in 18c.

The original issue was with a task management system to run jobs. Here is the simple table where all rows have a ‘NEW’ status and the goal is to have several threads processing them by updating them to the ‘HOLDING’ status’ and adding the process name.

set echo on
drop table DEMO;
create table DEMO (ID primary key,STATUS,NAME,CREATED)
 as select rownum,cast('NEW' as varchar2(10)),cast(null as varchar2(10)),sysdate+rownum/24/60 from xmltable('1 to 10')
/

Now here is the query that selects the 5 oldest rows in status ‘NEW’ and updates them to the ‘HOLDING’ status:

UPDATE DEMO SET NAME = 'NUMBER1', STATUS = 'HOLDING' 
WHERE ID IN (
 SELECT ID FROM (
  SELECT ID, rownum as counter 
  FROM DEMO 
  WHERE STATUS = 'NEW' 
  ORDER BY CREATED
 ) 
WHERE counter <= 5) 
;

Note that the update also sets the name of the session which has processed the rows, here ‘NUMBER1’.

Once the query started, and before the commit, I’ve run the same query from another session, but with ‘NUMBER2’.

UPDATE DEMO SET NAME = 'NUMBER2', STATUS = 'HOLDING' 
WHERE ID IN (
 SELECT ID FROM (
  SELECT ID, rownum as counter 
  FROM DEMO 
  WHERE STATUS = 'NEW' 
  ORDER BY CREATED
 ) 
WHERE counter <= 5) 
;

Of course, this waits on row lock from the first session as it has selected the same rows. Then I commit the first session, and check, from the first session what has been updated:

commit;
set pagesize 1000
select versions_operation,versions_xid,DEMO.* from DEMO versions between scn minvalue and maxvalue order by ID,2;

V VERSIONS_XID             ID STATUS     NAME       CREATED        
- ---------------- ---------- ---------- ---------- ---------------
U 0500110041040000          1 HOLDING    NUMBER1    27-SEP-18 16:48
                            1 NEW                   27-SEP-18 16:48
U 0500110041040000          2 HOLDING    NUMBER1    27-SEP-18 16:49
                            2 NEW                   27-SEP-18 16:49
U 0500110041040000          3 HOLDING    NUMBER1    27-SEP-18 16:50
                            3 NEW                   27-SEP-18 16:50
U 0500110041040000          4 HOLDING    NUMBER1    27-SEP-18 16:51
                            4 NEW                   27-SEP-18 16:51
U 0500110041040000          5 HOLDING    NUMBER1    27-SEP-18 16:52
                            5 NEW                   27-SEP-18 16:52
                            6 NEW                   27-SEP-18 16:53
                            7 NEW                   27-SEP-18 16:54
                            8 NEW                   27-SEP-18 16:55
                            9 NEW                   27-SEP-18 16:56
                           10 NEW                   27-SEP-18 16:57

I have used flashback query to see all versions of the rows. All 10 have been created and the the first 5 of them have been updated by NUMBER1.

Now, my second session continues, updating to NUMBER2. I commit and look at the row versions again:

commit;
set pagesize 1000
select versions_operation,versions_xid,DEMO.* from DEMO versions between scn minvalue and maxvalue order by ID,2;

V VERSIONS_XID             ID STATUS     NAME       CREATED        
- ---------------- ---------- ---------- ---------- ---------------
U 04001B0057030000          1 HOLDING    NUMBER2    27-SEP-18 16:48
U 0500110041040000          1 HOLDING    NUMBER1    27-SEP-18 16:48
                            1 NEW                   27-SEP-18 16:48
U 04001B0057030000          2 HOLDING    NUMBER2    27-SEP-18 16:49
U 0500110041040000          2 HOLDING    NUMBER1    27-SEP-18 16:49
                            2 NEW                   27-SEP-18 16:49
U 04001B0057030000          3 HOLDING    NUMBER2    27-SEP-18 16:50
U 0500110041040000          3 HOLDING    NUMBER1    27-SEP-18 16:50
                            3 NEW                   27-SEP-18 16:50
U 04001B0057030000          4 HOLDING    NUMBER2    27-SEP-18 16:51
U 0500110041040000          4 HOLDING    NUMBER1    27-SEP-18 16:51
                            4 NEW                   27-SEP-18 16:51
U 04001B0057030000          5 HOLDING    NUMBER2    27-SEP-18 16:52
U 0500110041040000          5 HOLDING    NUMBER1    27-SEP-18 16:52
                            5 NEW                   27-SEP-18 16:52
                            6 NEW                   27-SEP-18 16:53
                            7 NEW                   27-SEP-18 16:54
                            8 NEW                   27-SEP-18 16:55
                            9 NEW                   27-SEP-18 16:56
                           10 NEW                   27-SEP-18 16:57

This is not what I expected. I wanted my second session to process the other rows, but here it seems that it has processed the same rows as the first one. What has been done by the NUMBER1 has been lost and overwritten by NUMBER2. This is inconsistent, violates ACID properties, and should not happen. An SQL statement must ensure write consistency: either by locking all the rows as soon as they are read (for non-MVCC databases where reads block writes), or re-starting the update when a mutating row is encountered. Oracle default behaviour is in the second case, NUMBER2 query reads the rows 1 to 5, because the changes by NUMBER1, not committed yet, are invisible from NUMBER2. But the execution should keep track of the columns referenced in the where clause. When attempting to update a row, now that the concurrent change is visible, the update is possible only if the WHERE clause used to select the rows still selects this row. If not, the database should raise an error (this is what happens in serializable isolation level) or re-start the update when in the default statement-level consistency.

Here, probably because of the nested subquery, the write consistency is not guaranteed and this is a bug.

One workaround is not to use subqueries. However, as we need to ORDER BY the rows in order to process the oldest first, we cannot avoid the subquery. The workaround for this is to add STATUS = ‘NEW’ in the WHERE clause of the update, so that the update restart works correctly.

However, the goal of multithreading those processes is to be scalable, and multiple update restarts may finally serialize all those updates.

The preferred solution for this is to ensure that the updates do not attempt to touch the same rows. This can be achieved by a SELECT … FOR UPDATE SKIP LOCKED. As this cannot be added directly to the update statement, we need a cursor. Something like this can do the job:

declare counter number:=5;
begin
 for c in (select /*+ first_rows(5) */ ID FROM DEMO 
           where STATUS = 'NEW' 
           order by CREATED
           for update skip locked)
 loop
  counter:=counter-1;
  update DEMO set NAME = 'NUMBER1', STATUS = 'HOLDING'  where ID = c.ID and STATUS = 'NEW';
  exit when counter=0;
 end loop;
end;
/
commit;

This can be optimized further but just gives an idea of what is needed for a scalable solution. Waiting for locks is not scalable.

Cet article Oracle write consistency bug and multi-thread de-queuing est apparu en premier sur Blog dbi services.