Re: create_index_cost procedure
Date: Mon, 6 Jul 2009 17:26:10 -0500
Message-ID: <OF9E528566.B423BF29-ON862575EB.007B0886-862575EB.007B422A_at_discover.com>
Jonathan:
Here is my output :
SQL> _at_est_index
SQL> declare
2 l_used_bytes number; 3 l_alloc_bytes number; 4 begin 5 dbms_space.create_index_cost ( 6 ddl => 7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' || 9 ' tablespace unica_x_01 ',
10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1740636160
PL/SQL procedure successfully completed.
SQL> declare
2 l_used_bytes number; 3 l_alloc_bytes number; 4 begin 5 dbms_space.create_index_cost ( 6 ddl => 7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' || 9 ' tablespace users ',
10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1735000064
PL/SQL procedure successfully completed.
SQL> SQL> SQL> declare 2 l_used_bytes number; 3 l_alloc_bytes number; 4 begin 5 dbms_space.create_index_cost ( 6 ddl => 7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' || 9 ' tablespace PCM_D_03 ',
10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1736441856
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 l_used_bytes number; 3 l_alloc_bytes number; 4 begin 5 dbms_space.create_index_cost ( 6 ddl => 7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' || 9 ' tablespace unica_d_01 ',
10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1782579200
PL/SQL procedure successfully completed
These are some of the TS parameters:
1 select tablespace_name, block_size, initial_extent, next_extent,
logging, allocation_type, extent_management
2 from dba_tablespaces
3* where tablespace_name in
('UNICA_D_01','PCM_D_03','USERS','UNICA_X_01') order by 3 asc
SQL> /
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENTLOGGING ALLOCATIO EXTENT_MAN
------------------------------ ---------- -------------- ----------- --------- --------- ---------- USERS 16384 131072 131072 LOGGING UNIFORM LOCAL PCM_D_03 16384 2097152 2097152 LOGGING UNIFORM LOCAL UNICA_X_01 16384 10485760 10485760 LOGGING UNIFORM LOCAL UNICA_D_01 16384 52428800 52428800LOGGING UNIFORM LOCAL thank you
Gene Gurevich
"Jonathan Lewis"
<jonathan_at_jlcomp.
demon.co.uk> To
Sent by: <oracle-l_at_freelists.org>
oracle-l-bounce_at_f cc
reelists.org
Subject
Re: create_index_cost procedure
07/06/2009 05:13
PM
Please respond to
jonathan_at_jlcomp.d
emon.co.uk
Didn't change at all, or didn't change much ?
How about posting the output from one dbms_xplan, and a list of extent sizes with allocated sizes.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message ----- From: <genegurevich_at_discover.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org>; <oracle-l-bounce_at_freelists.org> Sent: Monday, July 06, 2009 4:44 PM Subject: Re: create_index_cost procedure
> Johathan,
>
> Thank you for your article. I am still unclear though why in my example
the
> value of allocated parameter did not change
> even though I have specified different tablespaces with varying
next_Extent
> values (from 128K to 50M). All these
> tablespaces a local with uniform extent value. I expected the allocated
> value to differ based on the size of an extent.
> IS that incorrect?
>
-- http://www.freelists.org/webpage/oracle-l Please consider the environment before printing this email. -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 06 2009 - 17:26:10 CDT