Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Oakies Blog Aggregator

Directly Executing a Query is Slower than Returning a REFCURSOR from a PL/SQL Procedure – What is Wrong with this Quote?

February 20, 2012 My copy of the ”Oracle Database 11gR2 Performance Tuning Cookbook” arrived from Amazon, and I will say that I like the seven steps for solving performance problems that is found on page 12, although the diagram of the process on page 14 may lead to a condition known as Compulsive Tuning Disorder. I am [...]

Hotsos Symposium 2012

It’s almost time for Hotsos’s 10th annual Symposium. This year’s conference will be held March 4 – 8 in Irving, Texas. The Hotsos Symposium is probably the best performance oriented Oracle conferences in the world. I am happy and humbled to be speaking at it again this year as the lineup of speakers is once again world class. It’s great to have several other Enkitec’ies on the bill as well. Both Karen Morton and Tanel Poder will be presenting too. Enkitec is also a sponsor of the event this year, so we’ll be hosting a couple of Exadata focused happy hours as well. There’s still time to register. Hope to see you there.

 

RMOUG Debrief

I’m back home after a very pleasant few days in Denver. As always the RMOUG conference was great fun, the location delightful, and the people were friendly, and the organizers did a great job of looking after the presenters and making things happen. It’s a terrific event – just big enough to have a real buzz, but small enough (I’d guess about 700 – 800 people) that you don’t feel lost in the crowd.

All three of my presentations seemed to go well - one person even started to applaud (which got the whole room going) when I got to the really important slide in the first session.

Jonathan Gennick of Apress had brought 100 copies of Oracle Core and was selling them off at a special event price of USD 20 – and I think he only had one or two copies left by the end of the event; I know that I autographed an easy 40 or 50 copies.

One of the nicest things about the event happened just a few minutes ago, though. Just as I was about to start typing up this little note, I got the following email, which I reproduce with permission:

I just wanted to say I appreciate the time and effort you put into your presentations on table access and joins at RMOUG.  They were excellent, as always.  Based on what you showed us, I have rewritten one problem query in our application such that I am getting an 82 percent performance gain.  I am excited about the possibilities and look forward to sharing what you showed us at RMOUG to our development team.

Thank you again for the support you have shown for RMOUG and Oracle professionals.

Sandy Becker
Database Operations Manager
Oildex, a service of Transzap, Inc.

It’s really nice to hear that someone has taken a mechanism you’ve described and found examples where it can be applied to such good effect and done it so soon after the event.

 

 

Friday Philosophy – Tosh Talked About Technology

Sometimes I can become slightly annoyed by the silly way the media puts out total tosh and twaddle(*) that over-states the impact or drawbacks about technology (and science ( and especially medicine (and pretty much anything the media decides to talk about)))). Occasionally I get very vexed indeed.

My attention was drawn to some such thing about SSDs (solid State Discs) via a tweet by Gwen Shapira yesterday {I make no statement about her opinion in this in any way, I’m just thanking her for the tweet}. According to Computerworld

SSDs have a ‘bleak’ future, researchers say

So are SSDs somehow going to stop working or no longer be useful? No, absolutely not. Are SSDs not actually going to be more and more significant in computing over the next decade or so? No, they are and will continue to have a massive impact. What this is, is a case of a stupidly exaggerated title over not a lot. {I’m ignoring the fact that SSDs can’t have any sort of emotional future as they are not sentient and cannot perceive – the title should be something like “the future usefulness of SSDs looks bleak”}.

What the article is talking about is a reasonable little paper about how if NAND-based SSDS continue to use smaller die sizes, errors could increase and access times increase. That is, if the same technology is used in the same way and manufacturers continue to shrink die sizes. It’s something the memory technologists need to know about and perhaps find fixes for. Nothing more, nothing less.

The key argument is that by 2024 we will be using something like 6.4nm dies and at that size, the physics of it all means everything becomes a little more flaky. After all, Silicon atoms are around 0.28nm wide (most atoms of things solid at room temperature are between 0.2nm and 0.5nm wide), at that size we are building structures with things only an order of magnitude or so smaller. We have all heard of quantum effects and tunneling, which means that at such scales and below odd things can happen. So error correction becomes more significant.

But taking a reality check, is this really an issue:

  • I look at my now 4-year-old 8GB micro-USB stick (90nm die?) and it is 2*12*30mm, including packaging. The 1 TB disc on my desk next to it is 24*98*145mm. I can get 470 of those chips in the same space as the disc, so that’s 3.8TB based on now-old technology.
  • Even if the NAND materials stay the same and the SSD layout stays the same and the packaging design stays the same, we can expect about 10-50 times the current density before we hit any problems
  • The alternative of spinning platers of metal oxides is pretty much a stagnant technology now, the seek time and per-spindle data transfer rate is hardly changing. We’ve even exceeded the interface bottleneck that was kind-of hiding the non-progress of spinning disk technology

The future of SSD technology is not bleak. There are some interesting challenges ahead, but things are certainly going to continue to improve in SSD technology between now and when I hang up my keyboard. I’m particularly interested to see how the technologists can improve write times and overall throughput to something closer to SDRAM speeds.

I’m willing to lay bets that a major change is going to be in form factor, for both processing chips and memory-based storage. We don’t need smaller dies, we need lower power consumption and a way to stack the silicon slices and package them (for processing chips we also need a way to make thousands of connections between the silicon slices too). What might also work is simply wider chips, though that scales less well. What we see as chips on a circuit board is mostly the plastic wrapper. If part of that plastic wrapper was either a porous honeycomb air could move through or a heat-conducting strip, the current technology used for SSD storage could be stacked on top of each other into blocks of storage, rather then the in-effect 2D sheets we have at present.

What could really be a cause of technical issues? The bl00dy journalists and marketing. Look at digital cameras. Do you really need 12, 16 mega-pixels in your compact point-and-shoot camera? No, you don’t, you really don’t, as the optics on the thing are probably not up to the level of clarity those megapixels can theoretically give you, the lens is almost certainly not clean any more and, most significantly, the chip is using smaller and smaller areas to collect photons (the sensor is not getting bigger with more mega-pixels you know – though the sensor size is larger in proper digital SLRs which is a large part of why they are better). This less-photons-per-pixel means less sensitivity and more artefacts. What we really need is maybe staying with 8MP and more light sensitivity. But the mega-pixel count is what is used to market the camera at you and I. As a result, most people go for the higher figures and buy something technically worse, so we are all sold something worse. No one really makes domestic-market cameras where the mega-pixel count stays enough and the rest of the camera improves.

And don’t forget. IT procurement managers are just like us idiots buying compact cameras.

(*) For any readers where UK English is not a first language, “twaddle” and “tosh” both mean statements or arguments that are silly, wrong, pointless or just asinine. oh, Asinine means talk like an ass :-) {and I mean the four-legged animal, not one’s bottom, Mr Brooks}

Tables and Their Indexes Should be Located on Different Disks – What is Wrong with this Quote?

February 17, 2012 As I mentioned in a previous blog article, I placed an order for the book “Oracle Database 11gR2 Performance Tuning Cookbook“, mostly because I was curious how the book would compare with the “Oracle Database 11g Performance Tuning Recipes” book (my review of that book was a total of about 35 typewritten pages in [...]

Subquery Factoring

When I wrote a note last week about the fixes to the subquery factoring optimizer code in 11.2.0.3, I finished with a comment about having more to say on the test case if I materialized the subquery. Today’s the day to talk about it. As a reminder, here’s the query, but with the /*+ materialize */ hint in place:

with subq as (
	select
	/*+ materialize */
		outer.*
	from
		emp outer
	where
		sal > 1000000
	and	outer.sal > (
			select
				avg(inner.sal)
 			from	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
select	*
from	subq
;

Here are the three plans – from 10.2.0.5, 11.1.0.7, and 11.2.0.3 respectively:


PLAN_TABLE_OUTPUT 10.2.0.5
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 834666169

----------------------------------------------------------------
| Id  | Operation                  | Name                      |
----------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |
|   1 |  TEMP TABLE TRANSFORMATION |                           |
|   2 |   LOAD AS SELECT           |                           |
|*  3 |    HASH JOIN               |                           |
|*  4 |     TABLE ACCESS FULL      | EMP                       |
|   5 |     VIEW                   | VW_SQ_1                   |
|   6 |      SORT GROUP BY         |                           |
|   7 |       TABLE ACCESS FULL    | EMP                       |
|   8 |   VIEW                     |                           |
|   9 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6602_AAB1DB |
----------------------------------------------------------------

PLAN_TABLE_OUTPUT 11.1.0.7
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 706934928

-----------------------------------------------------------------
| Id  | Operation                  | Name                       |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |
|   1 |  TEMP TABLE TRANSFORMATION |                            |
|   2 |   LOAD AS SELECT           |                            |
|*  3 |    HASH JOIN               |                            |
|*  4 |     TABLE ACCESS FULL      | EMP                        |
|   5 |     VIEW                   | VW_SQ_1                    |
|   6 |      SORT GROUP BY         |                            |
|   7 |       TABLE ACCESS FULL    | EMP                        |
|   8 |   VIEW                     |                            |
|   9 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D660E_50EDB63 |
-----------------------------------------------------------------

PLAN_TABLE_OUTPUT (11.2.0.3)
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2718753531

------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |
|   1 |  TEMP TABLE TRANSFORMATION |                             |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D662A_40002053 |
|*  3 |    HASH JOIN               |                             |
|*  4 |     TABLE ACCESS FULL      | EMP                         |
|   5 |     VIEW                   | VW_SQ_1                     |
|   6 |      SORT GROUP BY         |                             |
|   7 |       TABLE ACCESS FULL    | EMP                         |
|   8 |   VIEW                     |                             |
|   9 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D662A_40002053 |
------------------------------------------------------------------

Can you spot the problem ?
Apart from anything else, the 11.2.0.3 plan has two references to the “temporary table” while the other two plans only have one reference – so the plan_hash_value for 11.2.0.3 is going to be different from the plan_hash_value for the other two (the plan hash value is derived from the line numbers, operations, and object names).

But then again, 10.2.0.5 and 11.1.0.7 have exactly the same plan but different plan_hash_values – and that’s because you can see (when you look closely) that they have different name for the temporary table. In fact, every time the statement is optimised you’ll see a new sequential value for (the middle section of) the temporary table name. At least, that’s what happens until 11.2.0.3 where bug fix 10162430 comes into play and any object names starting with SYS_TEMP are ignored. (Thanks to Timur Ahkmadeev  for pointing that out).

Why does this matter? Because when Oracle tries to use an SQL Baseline, it optimizes the query with the baseline in place and checks to see if the resulting plan has the same plan_hash_value as the one stored with the baseline. If the values don’t match, the baseline isn’t used. So if, just before upgrading, you were planning to create some baselines for queries that have materialized factored subqueries (or any other constructs, such as star transformations and grouping set queries, that create temporary tables) then any baselines you do generate won’t work after the upgrade, unless you’re upgrading FROM 11.2.0.3.

One option, perhaps, is to fake in a new baseline after the upgrade to 11.2.0.3, and this might be relatively easy if you first try to run the system with parameter optimizer_features_enable set to your earlier release but switch on the fix for bug 10162430 and capture baselines with that configuration in place. This will give you a fixed plan_hash_value for the plan and if most cases will probably give you the plan you had seen in the previous release; the only little oddity will be that the outline will include the entries: OPT_PARAM(‘_fix_control’ ’10162430:1′) and OPTIMIZER_FEATURES_ENABLE(‘{your chosen version}’).

Footnote:
Bug 10162430 was reported against 10.2.0.4, and is reported as fixed in 12.1. To revert to older behaviour in 11.2.0.3 you can use the fix_control mechanism:

alter session set "_fix_control"='10162430:OFF';

To revert to an older set of optimizer features but enable this bug fix you would do something like:

alter session set optimizer_features_enable='10.2.0.5';
alter session set "_fix_control"='10162430:ON';

Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE Queries

My last post was on slow dictionary queries caused by having many, many thousands of extents in your database. This post is about a special case of this problem, which I encountered recently. It was on an Exadata box – Exadata is not particularly relevant to the issue, but I’m curious to see if mentioning Exadata will make this post more popular :-)

I was querying the used and free space on a very small database on the client’s X2-2 1/4 rack Exadata machine. The query was really slow, taking about 30 seconds. This is a FAST box, what is going on?

I quickly realised that the problem was specific to one tablespace:

>@SPC_SUM
Enter the tablespace (or leave null)> DATA_01

TS_NAME              ORD      SUM_BLKS        SUM_K  MAX_CHNK_K NUM_CHNK
-------------------- ----- ----------- ------------ ----------- --------
DATA_01              alloc     262,144    2,097,152   2,097,152        1
                     free       63,128      505,024     504,384       11
2 rows selected.
Elapsed: 00:00:00.21

> @SPC_SUM
Enter the tablespace (or leave null)> USERS

TS_NAME              ORD      SUM_BLKS        SUM_K  MAX_CHNK_K NUM_CHNK
-------------------- ----- ----------- ------------ ----------- --------
USERS                alloc     748,320    5,986,560   5,372,160        2
                     free      127,904    1,023,232       6,144    3,058
2 rows selected.

Elapsed: 00:00:26.05

We can see it takes 0.21 seconds for tablespace DATA_01, 26.05 seconds for the USERS. Yes, USERS is larger but not 150 times larger. What is going on? Well, as the title of this post suggests, it is down to dropping tables. The below shows my route to that conclusion.

What I could see was that for the USERS tablespace the maximum chunk of free space was relatively small compared to the sum of free space – 6MB out of 1GB, pretty much – and that there was a lot of individual pieces of free space, 3,058. This tablespace was shattered into a lot of bits. So, what sort of extent size management do we have? How big are the tables and indexes in this tablespace {NB I already knew we had no partitions so I did not have to worry about that}.

select tablespace_name,initial_extent,next_extent
      ,extent_management,min_extlen,allocation_type
from dba_tablespaces where tablespace_name ='USERS'

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN MIN_EXTLEN ALLOCATIO
------------------------------ -------------- ----------- ---------- ---------- ---------
USERS                                   65536             LOCAL           65536 SYSTEM

select tablespace_name,blocks,count(*) from dba_extents
where tablespace_name = 'USERS'
group by tablespace_name,blocks
having count(*) >1
order by blocks desc,tablespace_name

TABLESPACE_NAME                    BLOCKS   COUNT(*)
------------------------------ ---------- ----------
USERS                                2560          2
USERS                                2048          3
USERS                                1536          7
USERS                                1408          5
USERS                                1280          2
USERS                                1248          2
USERS                                1152          2
USERS                                1024        229
USERS                                 896         15
USERS                                 768         21
USERS                                 736          3
USERS                                 720          3
USERS                                 704          2
USERS                                 672          2
USERS                                 640         25
USERS                                 624          2
USERS                                 576          2
USERS                                 512        117
USERS                                 400          2
USERS                                 384         34
USERS                                 360          2
USERS                                 312          2
USERS                                 288          4
USERS                                 256         49
USERS                                 248          2
USERS                                 240          2
USERS                                 192          5
USERS                                 160          4
USERS                                 128       1165
USERS                                   8       1788

30 rows selected.

So we have system controlled extent allocation, this should be fine. I did a quick check of the table contents – 300 or so tables and 200 or so indexes. And, as you can see from the check of extents above, very few larger extents and a lot of small ones. {As a little aside, note the relatively large number of 8-block, 128-block, 256-block and 1024-block extents; These are the sizes that LMTs with system managed extents tend to use unless a similar sized chunk is found to be available, in which case it is used}.

I did some other checks on indexes and segment sizes and it all boiled down to one thing. There were not a lot of things in this tablespace, what was there was small and… The total of all those table and index blocks was way less than the used space in the tablespace.

That is the major indicator of what is going on here. The difference in space used in the tablespace and the total of the visible tables and indexes.

The last link in the chain is the recycle bin.

select owner,ts_name,count(*) from dba_recyclebin group by owner,ts_name

OWNER                          TS_NAME                          COUNT(*)
------------------------------ ------------------------------ ----------
USER1                          USERS                              542356
USER1                                                                  2
WEGWEGWEG                      USERS                                  97
KKKUKUYLLX                     USERS                                 149
USOVFPKEKS                     USERS                                   3
....
ERHJTRTTTURT                   USERS                                   4

11 rows selected.

That’s 542,356 objects in the recyclebin for one user, in the one tablespace. My problem tablespace. The penny clicked, something I had already noticed and was a little uncomfortable about fell into place.

The client is using Oracle Data Integrator (ODI) to pull data together and put it into the database. The process they are using basically creates a table, uses it to load some data into and then pushes the data into the target tables. Then drops the table. The drop does not do a “DROP TABLE … PURGE;”. This is done many, many times per load cycle, which is intended to run several times a day.

Something you should always keep in mind with the recyclebin is that the tables, indexes and their extents that go into the recycle bin do not get reported in several of the key data dictionary views. I did mention this in a blog post way back but maybe a whole post on it is called for.

So, the tablespace was being shattered by the constant creating and dropping of small tables. It was hidden from easy view due to how the recyclebin is (not) exposed in data dictionary views.

It is not good practice to constantly create and drop lots of tables. As well as the admittedly rather odd impact that this posting is all about, there is a lot of internal work involved for the oracle database in creating and dropping tables. It is an overhead best avoided. The client had good reasons for this approach but now they are going to look at the alternatives.

I’ve not actually proven in this posting that all those dropped tables is the actual cause of the slow performance in querying the free space in that tablespace {though the corroborative evidence is very strong}. Neither have I said how we cleared up the mess. I’ll cover both of those in the next post on this mini-thread.

RuOUG in Saint Petersburg

On February 10th I was in Saint Petersburg on a seminar organized by Russian Oracle User Group. Actually, it was mostly prepared by company called Devexperts. Seminar took place in their office, with most presentations done by their people.

  • First presentation was called “AWR, ASH – use cases and 11.2 enhancements”. The content was targeted to people who are not very familiar with these facilities. It was good enough and covered (I think) most of new features of the AWR reports such as more OS level data, Wait Event Histogram, aggregate IO statistics (based on V$IOSTAT_FUNCTION_DETAIL), colored SQL and others. After AWR presentation went to what’s new in ASH. Most of it comes to a fact that number of columns in dba_hist_active_sess_history is almost doubled in 11.2.0.2 compared to 10.2.0.5. There were several examples of custom queries to ASH to find what’s missing in averages and how to identify a skew (aka spike). There are references to other good presentations at the end. One presentation that is missing an active link is John Beresniewicz. Practical Active Session History. And here are some random notes on topic of AWR and alike:
    • the function dbms_xplan.display_awr to display execution plan stored in AWR is not working correctly – it misses predicates information even in 11.2.0.3. Major gap.
    • v$sql is too costly way to find hard queries due to its nature – this is shared cursors structure that needs to be protected by latches/mutexes. v$sqlstats is more suitable for such task, and it has bigger retention time
    • there’s a thread on SQL.ru where it is noted that some of the Top N SQL sections of AWR are derived from the Top SQL by Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count. Namely, Top N SQL by Gets, Physical reads and Executions are formed based on the previously selected Top SQLs. This could potentially lead to a situation when SQL is missing in the reports even if it’s, for example, absolute #1 query by buffer gets. This doesn’t sound right. I have no confirmation or denial of that, but I believe in it right now./
    • there are, of course more easier way to miss a query in the AWR report – SQL could be loaded and flushed from the shared pool in between of snapshots
    • Igor Usoltsev has recently published a note about how AWR snapshot creation could be stopped due to a slow query in 11g caused by a missing fixed statistics. Although I know that there’re indeed cases when fixed objects statistics helps Oracle to build a good plan, I’ve also seen a number of cases where gathering fixed objects statistics lead to other problems. So it’s not a win-win suggestion and have to be carefully tested – and the only way to test it “right” is to do so in production or, if you can reproduce the environment and the load, on its copy.
    • If you haven’t seen Doug Burns’ post on a query to find ASH outliers (written by John Beresniewicz), here it is. I haven’t tried it myself (yet).
  • Second presentation was “My experience building a custom ETL system“. It was done by a developer who has written custom ETL solution based on Streams & Java. He has even written a custom query language – mix of Java & PL/SQL. Most of the things were developer related so I listened not very carefully (especially since Yandex DBAs sitting next to me recovered one of the production databases at the same time – it was more interesting :) ). One issue that caught my attention was around Streams Apply process. As I understood it, there’s possibility to get two changes associated with the same point in time (SCN). I’ve never worked with Streams and can’t comment on such topic; a quick MOS search didn’t reveal anything close to this.
  • Next was the most interesting presentation – “Exadata: Deployment story“. It talks about real-life Exadata implementation with the issues encountered: more than 1 day deploy time, broken power supply, slow NFS to copy data, RAT bug, and ORA-00600 after upgrade. Since the environment has Streams in place, they used a tricky procedure to guarantee fallback if something goes wrong during upgrade. There’re also some performance metrics of the average waits (which are not representative, you know) and SQL performance. Very nice presentation plus a discussion afterwards on the patching and installing additional software on the DB grid in Exadata.
  • Final presentation was “GoldenGate Overview” (it’s mostly in Russian). I can’t add something about it, except it was a good presentation, as usual :)

The conference was broadcasted for the first time at anymeeting.com. Live stream allowed me to ask questions to presenters from those who followed online and make several screenshots to twit them. It was positive experience with some (I think minor) sound issues. I hope it will become a standard thing and more people could virtually attend such events.
I’d like to say big thanks to people who organized this – it was a pleasure to be there!

PS. I was in a book store for the first time in many years and found three books on Oracle on the book shelf. Don’t know if it’s right or wrong.
PPS. If you want to see St Petersburg beauties online, have a look at Yandex maps for panoramic pictures taken from the air.

Filed under: Oracle Tagged: ASH, AWR, Exadata, RuOUG

Product Review

A while ago I said that I’d be posting a product review here. Unfortunately work got in the way of me posting the review as quickly as I would have liked. The nice people at Ucertify sent me a review kit for the OCA Oracle Database 11g: Administration 1 exam. I very much like the […]

Slow querying of DBA_EXTENTS, DBA_FREE_SPACE and dropping tables

Are you finding queries to identify free space, the size of segments or details of extents in your database are very slow? You could have a database with a very, very large number of extents. If you are on version 9 or up, I would suggest you check the extent management settings for any tablespace holding large segments (see later).

{Caveat – I’m writing this posting based on what I remember of some issues I had on two V10.2 databases 2 and 3 years ago respectively, and those systems are no longer available to me. So please treat everything I say here with some caution – I’ve not checked and verified it to the level I normally would. But I wanted to move this information from another posting I am writing on a recent experience I will verify…}

First, what this article is NOT about. It has been known for a very long time that creating tables with a large number of extents can have a negative impact on SQL performance on that table. Except it never really did and it was a myth. Way back prior to Oracle 7.3 you could only create so many extents per segment, depending on block size, but that was another issue. It used to be argued that SQL select against such tables with many extents was slower. I did some tests and it was not – unless you were in the strange situation where your extent size was less than your multi-block read count, and even then the impact was not huge, it was a slowdown of a few percent to maybe 25%.

However, dropping such tables, truncating such tables and queries against the dictionary objects that deal with extents and free space could and still can become very slow. As we have progressed through the Oracle versions from 8 ,9 and 10 this problem has become less common and the impact has become less, mostly due to Locally Managed Tablespaces (LMTs) and Automatic Segment Space Management {though that is more in respect of concurrent DML than select}.

LMTs in particular have generally removed the issue. If you do not use LMTS and have no very,very pressing reason to not {like 3rd party support for applications}, then swap to LMTs. LMTs have been the default for user defined tablespaces since oracle 9 and have two options. Uniform (Fixed) extent sizes or Automatic, where oracle uses 8 block extents for a new segment to start, then 64 block extents, then 1024 block extents as the segment grows {I might be wrong on the exact size details but don’t worry about it, I certainly don’t}. You can check the settings for tablespaces as is demonstrated below. I create two tablespaces, one with uniform extent sizes and then one with automanaged extent sizes, and check the relevant information (this is on 11.2.0.3):

create tablespace mdw_uni_1m
datafile 'C:\APP\MARTIN\ORADATA\MDW1123\MDW_UNI_1M'
size 100m
extent management local uniform size 1M;

create tablespace mdw_auto
datafile 'C:\APP\MARTIN\ORADATA\MDW1123\MDW_AUTO'
size 100m
extent management local autoallocate;

select tablespace_name,initial_extent,next_extent
      ,extent_management,min_extlen,allocation_type
from dba_tablespaces where tablespace_name like 'MDW%';

TABLESPACE_NAME    INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN MIN_EXTLEN ALLOCATIO
------------------ -------------- ----------- ---------- ---------- ---------
MDW_UNI_1M                1048576     1048576 LOCAL         1048576 UNIFORM
MDW_AUTO                    65536             LOCAL           65536 SYSTEM

As you can see, tablespace MDW_UNI_1M uses uniform extents of 1M and MDW_AUTO has system managed allocation and starts with 64K extents – 8 blocks with my 8k block size.

As a quick demo, I’ll just create and populate two simple tables and see what extent sizes are created for them:

create table mdw_big
tablespace mdw_auto
as select rownum id,lpad('A',1000,'A') pad
from dual connect by level <10000

create table mdw_big_uni
tablespace mdw_uni_1m
as select rownum id,lpad('A',1000,'A') pad
from dual connect by level <10000

select owner,segment_name,blocks,count(*)
from dba_extents
where segment_name like 'MDW%'
group by owner,segment_name,blocks

OWNER           SEGMENT_NAME        BLOCKS   COUNT(*)
--------------- --------------- ---------- ----------
ERIC            MDW_BIG_UNI            128         12
ERIC            MDW_BIG                  8         16
ERIC            MDW_BIG                128         11

3 rows selected.

So, how do issues with large numbers of extents still arise with modern oracle systems? Well, the two situations I’ve seen on Oracle 10 had the same cause {and, as a teaser for later this week, I’ve seen a variation of this issue on a nice, shiny Exadata X2-2 Oracle 11.2.0.3 box :-) You can readabout that here }. What alerted me was slow performance querying the data dictionary, in particular my scripts for checking free space, the size of segments and how many extents they consisted of.

If you create a tablespace with fixed extent sizes of eg 64K and then create a table in there that is 100GB in size, you will get an awful lot of extents. Now make it worse and have a partitioned table that ends up being several hundreds of GB in size with all those segments in that tablespace (or similarly defined tablespaces).

Since I hit the problem twice myself, I’ve chatted to others who have had the same issue. The above usually happens because of a mistake. The tablespace(s) in question are set up to hold small tables and then get used for large tables, either by a mistake in stating the exact tablespace to use or having the tablespace default to your default tablespace – which just happens to be a tablespace with fixed but small extent sizes.

The end result is a massive number of small extents in these tablespaces, usually with extents for different objects mixed in. Some dictionary queries slow down and, in particular, anything to do with looking at extents. For one site, I was trying to use my own code to gather statistics on tables that replaced the standard automated job. It’s fairly “smart” code and chooses a sample size based on the size of the segments. Only, the data dictionary was performing so slowly for the check on segment size that it was taking over 5 seconds to get the information – longer than some of the stats gathers.

You can logically understand why dropping or truncating the table is slow. Oracle has to sort out all those extents, remove the information from the data dictionary. This is not helped by the fact that part of the data dictionary is being slowed down due to all those pesky records…

You MAY be able to get some relief from this situation by gathering fixed object statistics. I did so at one site, where the queries against free_space and segment size sped up by around 80%. I have no demonstrated proof of this, it is just what I saw in one situation, so feel free to try it but don’t sue me if it does not help. Also, it took over 3 hours to gather the fixed object stats and you only do this sort of thing, untested, on a production system if you are already in a bad place.

{update – I just tested this on a private 11.2 db that was taking 10.3 seconds to count all extents, all 12,742 of them. Gathering fixed object stats made no difference at all.}

However, the real answer is to laboriously rebuild those segments in tablespaces with correctly specified uniform extent sizes. Which we did, over several weeks, and it made a difference.

If I was doing this task today, if I could get the outage to do it, I would create COPIES of those segments that were in the wrong tablespaces, re-name and re-apply any constraints and move the other other, smaller tables and indexes to a new tablespace – and then drop the tablespaces including contents. Why? As dropping a table with lots and lots of small extents seemed to take a very long time (many minutes per partition and we had thousands of them). Again, my proof is lost in the mists of time, but that’s what I would aim to do.