index creation fails because temp tablespace is too small
karel
i have a problem. I'm enabling a unique constraint, that is
creating a index on a table. The table contains 135000000 rows
( 13 GB)
my temp tablespace is 3 gb big.
when i enable the constraint it keeps failing because the temp
tablespace is to small.
is there a workaround for this issue and how big must temp be
when it wil enable succesfully.
thx
michel
Turkbear
Mainly by trial and error..
Or set the temp tablespace to autoextend ( set a max is disk space is an issue) or create a new tablespace with autoextend
for indexes to use..
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Jonathan Lewis
Assuming it's a simple heap table, and most
rows will appear in the index.
For each column in the index, find the average data size, and add two
Sum the results
Add 12 to the total
Round up to a multiple of 4
Multiply by the number of rows in the table.
That's a ball park figure for the TEMP you will need.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005
"karel" <karel...@ftd.nl> wrote in message
news:426fa9d5$0$24221$ba62...@nova.planet.nl...
Dutch_alien
Jonathan.
Would you mind explaining the formula? What do the numbers mean. And
are they dependent on block-size, or init.ora parameters?
Thanks for your reply.
Regards,
Arian
DA Morgan
You've already got good advice if on Standard Edition but if on
Enterprise Edition look at the partitioning option and the use
of local indexes.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Jonathan Lewis
news:1114624431.4...@l41g2000cwc.googlegroups.com...
Very roughly:
When sorting to produce an index, each row
of the dump to temp includes the indexed
columns, plus extended rowids plus overhead.
Oracle uses 2 length bytes for each column
The extended rowid uses two length bytes
to say that it is 10 bytes long - hence the 12.
I've just realised that I managed to forget the
4 extra bytes that Oracle seems to use for the
length of the whole entry - so add 4 bytes to
each row in the previous post.
Then each entry is written on a 4-byte boundary,
hence the rounding up to a multiple of 4.
There's also a little block overhead - about 60
bytes per block lost (from memory) - but the
whole calculation is just a rough indicator anyway,
so I wouldn't worry too much about that - there
are likely to be too many small errors with blank
columns and missing index entries to be able
to claim any greate degree of accuracy.
Dutch_alien
Thx. I appreciate the explanation, and gives me some things to try out.
Most of all, it made me wonder how sort_area_size is related to it.
(Background: One of my customers is using W2K, and we keep running into
the 2/3Gb memory limit).
But I think that would be more suitable for a new thread. So I'll be
doing some testing with unique indexes, constraints and sort_area_size.
I hope you'll be around to comment on my test results in a week or 2.
Regards,
Arian
Sybrand Bakker
wrote:
>Most of all, it made me wonder how sort_area_size is related to it.
>(Background: One of my customers is using W2K, and we keep running into
>the 2/3Gb memory limit).
sort_area_size is related in such a way: as soon as your sort exceeds
sort_area_size, you will hit the temporary segment.
It is pretty daft to set sort_area_size to 2 or 3 G, especially as
this is a *per sort limit* , *NOT* a *system wide limit*.
--
Sybrand Bakker, Senior Oracle DBA
chao_ping
Do you mean:
If we have a partitioned table a, and a non-partitioned table B,
with exact same structre and row number.
And we build local index on partitoin table and normal index on a
non-partitioned table, will we need less sort_area_size to build the
index? Can you prove it with real test data , or theory like Jonathan
Lewis's ?
(Assume we only do in-memory sort)
Regards
Jonathan Lewis
news:1114675597....@z14g2000cwz.googlegroups.com...
The point about partitioning is that if you have
locally partitioned indexes, then you can create
the index definition "unusable" which doesn't
create the index, the rebuild each partition in
turn. If the partitions are small enough, the
2-3GB of temp space should be adequate.
chao_ping
As for the build index parallel for local index on partitioned table,
I remember oracle does not do necessary use one PQ slave to read one
partition, sort one partition, and then build the index for one
partition. THat is, it does not split the workload to different PQ
slave according to partition range, it split the work load according to
the rowid, is it right? I don't find any document talking about this.
Can you give document/test result about this? Maybe we have to trace pq
coordinator to find how actually oracle does?
So if the above theory is correct, using partition to build the index
won't save temp space, unless we use the trick you mentioned above.
Right?
But the way, is there any method to let me know that someone else has
replied to the thread that I also participate? That will greatly add
our efficiency browsing the threads we are interested in.
THanks
CHao
Jonathan Lewis
> Thanks Lewis.
> As for the build index parallel for local index on partitioned table,
> I remember oracle does not do necessary use one PQ slave to read one
> partition, sort one partition, and then build the index for one partition.
I think this is version dependent, but I believe Oracle always
has the to run parallel execution against partitioned tables on
the basis of 'one slave = one (sub) partition', or on the basis
of rowid ranges. I have a test case somewhere where one
variant of 9.2.0.1did a partition-wise join, and another just
blasted its way through by rowid ranges and hash distribution -
when both data bases had the same set of spfile parameters
and the data was generated by a script.
> THat is, it does not split the workload to different PQ
> slave according to partition range, it split the work load according to
> the rowid, is it right? I don't find any document talking about this.
> Can you give document/test result about this? Maybe we have to trace pq
> coordinator to find how actually oracle does?
>
One option for tracing is to enable event 10391.
Level 64 will show you how the co-ordinator
is generate data "granules".
> So if the above theory is correct, using partition to build the index
> won't save temp space, unless we use the trick you mentioned above.
> Right?
If Oracle works by rowid ranges in this case you would be correct.
However, if it works by one-slave = one-partition, then (and I hadn't
thought of this in my original post, maybe it's what DA Morgan had
in mind) then perhaps you could set a low degree of parallelism and
find that you are building only a few index partitions at a time, and
can survive with the smaller TEMP.
As I say, the option to choose may be version dependent. On the
other hand it may be present in all recent versions, but driven by
a cost calculation whose parameters have changed so that the
break point between the options occurs at a different volume of
data.
Connor McDonald
go to www.ixora.com.au
Steve has a formula and some explanations about sorting
hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_...@yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
karel
most of it i all ready knew. the question i've asked is about a
test database. In our production database we're gonna partition
the table in question.
but for the fun of it I 'm now trying to use sqlldr with the
direct option
steps i'm taking
1 spool content off table to file
2 truncate table
3 enable all constraints including indexes
4 disable all triggers on table
5 load the data with sqlldr.
i'll hope it works.
thx all,
karel
Dutch_alien
Thx. I like the link.
(Make that links).
Regards,
Arian
Maxim Demenko
As sort_area_size is session modifiable parameter , for rapid index
creation it is of course not so bad idea to set it to 2-3 G or even
more, assumed you have enough free memory on the os level,(to take
effect on 9i upwards the workarea_size_policy must be set to manual
which fortunately is session modifiable too) . Moreover, in big
warehousing environments such big sort_area_size can be set on the
instance level - in my last project i worked on the system with 42 G
memory total, db_cache_size was set to 800 M and sort_area_size to 500
M, overall performance was ok, but i can as well imagine systems with
significantly more memory...
Best regards
Maxim
yon...@yahoo.com
Data Warehousing Guide talks about the two granule strategies, block
range granules (based on rowids; we also see the term "rowid range
granules" in x$ksppi description) and partition granules (based on
partitions). Oracle says "There is no way you can enforce a specific
granule strategy as Oracle Database makes this decision internally."
But the text seems to suggest that block range granules are used in
most cases, while partition granules are used in "parallel index range
scans and of parallel operations that modify multiple partitions of a
partitioned table or index. These operations include parallel creation
of partitioned indexes, and parallel creation of partitioned tables."
Yong Huang
Matthias Hoys
For performance reasons, wouldn't it be better to first load all the data
into the table (step 5), and afterwards create the indexes and enable the
constraints ?
Matthias
Sybrand Bakker
It sure would. Also (at least in the past) indexes created on empty
tables had always to be recreated immediately after import, because
they were inefficient (data usually isn't loaded in the 'correct'
order)
steven
index,that would be very nice.
Steven
DA Morgan
I can not. UTLXPLS.SQL is nothing more than a call to DBMS_XPLAN
to view an explain plan.
Jonathan Lewis
news:1115396206.396494@yasure...
> steven wrote:
>
>> If utlxpls.sql can estimate temp tablespace usage when building
>> index,that would be very nice.
>>
>> Steven
>
> I can not. UTLXPLS.SQL is nothing more than a call to DBMS_XPLAN
> to view an explain plan.
But an execution plan includes the column temp_space,
which is supposed to be exactly what the user wanted.
And a call to dbms_xplan will populate and report it
if CPU costing is enabled.
The materialized view advisor uses explain plan in just
this way to estimate the size of materialized views.
I am not totally convinced that the answer will be correct -
I haven't tested it thoroughly, but I have some examples
which are far too small, and one example which is much
too big, but would be correct if the plan were supposed
to be reporting the size that "sort_area_size" would have
to be to do an in-memory sort to create the index.
DA Morgan
> "DA Morgan" <damo...@x.washington.edu> wrote in message
> news:1115396206.396494@yasure...
>
>>steven wrote:
>>
>>
>>>If utlxpls.sql can estimate temp tablespace usage when building
>>>index,that would be very nice.
>>>
>>>Steven
>>
>>I can not. UTLXPLS.SQL is nothing more than a call to DBMS_XPLAN
>>to view an explain plan.
>
>
>
> But an execution plan includes the column temp_space,
> which is supposed to be exactly what the user wanted.
> And a call to dbms_xplan will populate and report it
> if CPU costing is enabled.
>
> The materialized view advisor uses explain plan in just
> this way to estimate the size of materialized views.
>
> I am not totally convinced that the answer will be correct -
> I haven't tested it thoroughly, but I have some examples
> which are far too small, and one example which is much
> too big, but would be correct if the plan were supposed
> to be reporting the size that "sort_area_size" would have
> to be to do an in-memory sort to create the index.
The column temp_space may be in PLAN_TABLE but I've not seen it
in the output of DBMS_XPLAN.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1237609277
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 7992 | 2
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 999 | 7992 | 2
(0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_SERVERS | 141 | 564 | 1
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| IX_SRVR_INST_SRVR_ID | 7 | 28 | 0
(0)| 00:00:01 |
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."SRVR_ID"="I"."SRVR_ID")
15 rows selected.
SQL>
and, at least with my testing (10.1.0.4) it is not populated by
running an explain plan.
SQL> explain plan set statement_id = 'abc' for
2 select s.srvr_id
3 from servers s, serv_inst i
4 where s.srvr_id = i.srvr_id;
Explained.
SQL> select temp_space from plan_table;
TEMP_SPACE
----------
8 rows selected.
SQL>
Am I missing something? Hopefully not something too obvious.
Thanks.
Jonathan Lewis
Try running a query that requires some temp space.
The column is not reported if none of the rows
have populated it. (The default is null, not zero).
rem 9.2.0.6
rem optimizer_mode = all_rows
rem system stats have been gathered
explain plan for
select source from sys.source$ order by source;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1324K| 2528M| | 724K
(1)|
| 1 | SORT ORDER BY | | 1324K| 2528M| 5172M| 724K
(1)|
| 2 | TABLE ACCESS FULL | SOURCE$ | 1324K| 2528M| | 4128
(2)|
---------------------------------------------------------------------------------
8 rows selected.
DA Morgan
Well I'm the one that wrote: "Hopefully not something too obvious."
SQL*Plus: Release 10.1.0.4.0 - Production on Fri May 6 18:19:12 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set linesize 121
SQL> explain plan for
2 select source from sys.source$ order by source;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 995087943
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 342K| 42M| | 12285
(1)| 00:02:28 |
| 1 | SORT ORDER BY | | 342K| 42M| 92M| 12285
(1)| 00:02:28 |
| 2 | TABLE ACCESS FULL| SOURCE$ | 342K| 42M| | 2197
(1)| 00:00:27 |
--------------------------------------------------------------------------------------
9 rows selected.
SQL>
Oradba_Linux
10G displays the SQL hashvalue in the explain plan.
But is hash value always unique?
Sybrand Bakker
<techi...@comcast.net> wrote:
>10G displays the SQL hashvalue in the explain plan.
>But is hash value always unique?
it is
Jonathan Lewis
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005
"Oradba_Linux" <techi...@comcast.net> wrote in message
news:3PWdne6ZHNh...@comcast.com...
Jonathan Lewis
"Oradba_Linux" <techi...@comcast.net> wrote in message
news:3PWdne6ZHNh...@comcast.com...
No. The concept of any hashing algorithm is to
reduce the number of comparisons you have to
make between the value you have and the value
you are checking against.
With a large enough number of digits, a hash
value is LIKELY to be nearly a unique identifier.
But in 10g, Oracle has added a new SQL_ID
column with has more bits in it to improve the
probability of uniqueness.
To guarantee uniqueness in v$sql, you need to
use the address and child_number - but for an
efficient access path, you need to use the hash_value
(or sql_id) to get started.