Oakies Blog Aggregator

Uwe Hesse's picture

Join me in a FREE live webcast about Real-Time Query!

On Thursday, 2nd October, 12:30 CET I will be doing a Live Webcast with many demonstrations about Data Guard Real-Time Query.

The shown features all work with 11g already.

Register here.

805-banner-dataguardrealtime-v1-2294812

Tagged: Active Data Guard, Data Guard, OU Streams

Jonathan Lewis's picture

Shrink Tablespace

In a comment on my previous post on shrinking tablespaces Jason Bucata and Karsten Spang both reported problems with small objects that didn’t move to the start of the tablespace. This behaviour is inevitable with dictionary managed tablespaces (regardless of the size of the object), but I don’t think it’s likely to happen with locally managed tablespaces if they’ve been defined with uniform extent sizes. Jason’s comment made me realise, though, that I’d overlooked a feature of system allocated tablespaces that made it much harder to move objects towards the start of file. I’ve created a little demo to illustrate the point.

I created a new tablespace as locally managed, ASSM, and auto-allocate, then created a few tables or various sizes. The following minimal SQL query reports the resulting extents in block_id order, adding in a “boundary_1m” column which subtracts 128 blocks (1MB) from the block_id, then divides by 128 and truncates to show which “User Megabyte” in the file the extent starts in.  (Older versions of Oracle typically have an 8 block space management header, recent versions expanded this from 64KB to 1MB – possibly as a little performance aid to Exadata).


select
        segment_name, block_id, blocks , trunc((block_id - 128)/128) boundary_1M
from
        dba_extents where owner = 'TEST_USER'
order by
        block_id
;

SEGMENT_NAME               BLOCK_ID     BLOCKS BOUNDARY_1M
------------------------ ---------- ---------- -----------
T1                              128       1024           0
T1                             1152       1024           8
T2                             2176       1024          16
T2                             3200       1024          24
T3                             4224          8          32
T4                             4232          8          32
T5                             4352        128          33

As you can see t3 and t4 are small tables – 1 extent of 64KB each – and t5, which I created after t4, starts on the next 1MB boundary. This is a feature of auto-allocate: not only are extents (nearly) fixed to a small number of possible extent sizes, the larger extents are restricted to starting on 1MB boundaries and the 64KB extents are used preferentially to fill in odd-sized” holes. To show the impact of this I’m going to drop table t1 (at the start of file) to make some space.


SEGMENT_NAME               BLOCK_ID     BLOCKS BOUNDARY_1M
------------------------ ---------- ---------- -----------
T2                             2176       1024          16
T2                             3200       1024          24
T3                             4224          8          32
T4                             4232          8          32
T5                             4352        128          33

Now I’ll move table t3 – hoping that it will move to the start of file and use up some of the space left by t1. However there’s a 1MB area (at boundary 32) which is partially used,  so t3 moves into that space rather than creating a new “partly used” megabyte.


SEGMENT_NAME               BLOCK_ID     BLOCKS BOUNDARY_1M
------------------------ ---------- ---------- -----------
T2                             2176       1024          16
T2                             3200       1024          24
T4                             4232          8          32
T3                             4240          8          32
T5                             4352        128          33

It’s a little messy trying to clear up the tiny fragments and make them do what you want. In this case you could, for example, create a dummy table with storage(initial 64K next 64K minextents 14) to use up all the space in the partly used megabyte, then move t3 – which should go to the start of file – then move table t4 – which should go into the first partly-used MB (i.e. start of file) rather than taking up the hole left by t3.

Even for a trivial example it’s messy – imagine how difficult it can get to cycle through building and dropping suitable dummy tables and move objects in the right order when you’ve got objects with several small extents scattered through the file, and objects with a mixture of small extents and large extents.

marco's picture

Oracle OpenWorld 2014 – Datatype context…?!

The native JSON database functionality presentations are done. If you want to experience first hand what you can do with those new operators during Oracle...
class="readmore">Read More

hamcdc's picture

Slides from OakTableWorld and OpenWorld

Thanks to anyone that came along to my sessions at OpenWorld.

You can review the slides via the links below:

http://www.slideshare.net/hamcdc/oaktable-world-sep14-clonedb

http://www.slideshare.net/hamcdc/openworld-sep14-12c-fordevelopers

 

oraclebase's picture

Oracle OpenWorld 2014 : Monday

Monday started with the San Francisco bay swim. I was actually pretty scared about doing this. I like to swim, but I’m a child of the “Jaws” generation, so open water is not my thing! Added to that, the water is cold. The coldest my training pool ever gets is 25 degrees C, which feels quite cold. The water in the bay is unseasonably warm at 19 degrees C. For anyone that doesn’t know, that means it is damn cold!

Some brave souls ran and dived in. Rather than risk a heart attack, I walked in. :) After the initial shock I figured I better actually swim or die. Once I got moving, it was a lot better. Having never done any open water swimming, I found it quite difficult to keep my face down in the water. On the few occasions I did, I thought I saw dark shapes in the water with me and started to panic! I didn’t swim too far. It was only a couple of hundred metres, but by that time nearly everyone else had got out. It was more of a “bay dip” than “bay swim”. :)

From there is was back to the hotel to get cleaned up. I then headed down to the conference to hit the demo grounds. Being part of the ACE Director briefing means we’ve heard most of the important announcements already, so it can sometimes be hard to find sessions to watch. The demo grounds are a different thing entirely. There is always something interesting that can be gleaned from the staff manning the demo booths.

From there is was on to a hands-on session on the new JSON features of 12.1.0.2 with Mark Drake and some of the development team. The hands-on labs are a very quick way to get a feel for the subject. I think the JSON support will probably be the next article I write. Hopefully, I’m also on to the beta program for the REST API support for this JSON stuff too. Fingers crossed.

I kind-of ducked out of all evening commitments and spent the evening in my room. There was lots of cool things to do,


Oracle OpenWorld 2014 : Monday was first posted on September 30, 2014 at 6:16 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
oraclebase's picture

Oracle OpenWorld 2014 : User Group Sunday

Sunday started with the walk across the Golden Gate Bridge. It was meant to be a run, but a few of us lazy folk met up early and walked it instead. I did run about 5 paces, so I don’t feel quite so guilty about having a “I ran the bridge” type T-shirt. :)

After getting back and getting changed I headed down to the Moscone. I was involved in the “12 things about 12c” sessions by UKOUG. This was a double session with 12 speakers, each with 5 minutes. My section was 5 minutes on “A case for the Multitenant option with a single PDB”. I was pretty nervous on the lead up to this, which sounds a little silly for a 5 minute presentation, but shorter presentations require you to be a lot more focussed on the message. One extra anecdote and you’ve blown your time limit. :) I think it went OK. :)

I like this type of format. I think every conference needs something like this at the start of the event, so you get a quick feel for what is going on in the subject. It can also act as a pointer to help you decide what sessions you want to focus on during the event.

In the evening it was the Oracle ACE dinner. If someone asked me to define a perfect night out for me, it would probably include Oracle geeks, food, contortion and a silks performer. I love speaking about Oracle. I live eating food. I love circus acts, especially contortion. When they organised the ACE dinner I think they must have read my mind! :)

Cheers

Tim…


Oracle OpenWorld 2014 : User Group Sunday was first posted on September 30, 2014 at 5:41 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
arupnanda's picture

OOW14 Session: SQL Tuning Without Trying

Many thanks to all those who attended my session "SQL Tuning without Trying" at Oracle Open World 2014 #oow14. I hope you found the session useful.

If you want to download the slide deck, here it is. As always, I would really, really like to hear from you.

Johnjayking's picture

Oracle Open World 2014 Begins

Lot’s of new stuff at Oracle Open World; if you’re here:
- Developers should look for new Mobile Application Framework (MAF) features and tools
- DBAs will find lots of information on In-Memory database and other new features of 12.0.1.2
- Applications users will see some pretty-cool User Experience and ease-of-use improvements
- Everyone will learn lots about Oracle’s Cloud offerings
I’ll post more once the official announcements are over!

marco's picture

Oracle OpenWorld 2014 – New Data Formats and Integration

Especially on this Monday it seems all about new database data intern ration technologies and support for more data formats. Now adding in 12.1.0.2 native...
class="readmore">Read More