-
dictionary managed? hmmmm
-
Pando,
I did the test both on LMT and DMT.
-
tamilselvan,
I asked you why you cutted out the timing from your previous post - well, now you have included them. But timings for what?!!?? For creating/truncating the tables! You must be kidding. The whole discussion was about the performance of full table scans, your test with latches included FTS queries and those were the timings I was asking about. So I'm repeating myself agin, and just to make things very clear - ve are talking about the performance of queries, not DDLs:
Originally posted by jmodic
We vere talking about performance, yet you cutted the dbms_output of the timing results of both runs. Any particular reason? Maybe the fact that the timing shows no difference between the two? (And if you are claiming that run1 was noticably slower than run2 - or vice versa - then you have a problem elsevere, not directly in the number of extents!)
It is perfectly clear that we are not talking about any DDL operations here, the performance in any normal context relates to queries (and DML operations). We are more or less all avare of the isues regarding very high number of extents and DDL operations, so the timings in your last post hardly show anything surprising. So you still haven't showed anything that was not allready said in the last paragraph of my previous post (or anything that would proove it wrong for that matter):
Originally posted by jmodic
.... doesn't say that extremely high nuber of extents is a good thing, but he also explain what about it is not good: It is only because of the maintainance work that has to be done in dictionary because of that. No impact on queries! None. Zero.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
=====================================
Jmodic wrote:
So I'm repeating myself agin, and just to make things very clear - ve are talking about the performance of queries, not DDLs:
=====================================
Here is the query and execution time:
set serveroutput on size 50000
declare
l_time_1 number ;
l_time_2 number ;
l_time_3 number ;
l_cnt Number ;
begin
execute immediate 'truncate table stat_info' ;
insert into stat_info select 'Before', a.* from stat_info_vw a ;
l_time_1 := dbms_utility.get_time ;
execute immediate 'select count(*) from test1' into l_cnt ;
insert into stat_info select 'Run 1', a.* from stat_info_vw a ;
l_time_2 := dbms_utility.get_time ;
execute immediate 'select count(*) from test2' into l_cnt ;
insert into stat_info select 'Run 2', a.* from stat_info_vw a ;
l_time_3 := dbms_utility.get_time ;
commit;
dbms_output.put_line('Time to complete for Run 1 '||to_char(l_time_2 - l_time_1));
dbms_output.put_line('Time to complete for Run 2 '||to_char(l_time_3 - l_time_2));
end;
/
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> @rec_call
Time to complete for Run 1 852
Time to complete for Run 2 571
PL/SQL procedure successfully completed.
SQL>
Unlike Chris, I hope JMODIC would understand the diff between 852 and 571.
The next question JMODIC would like to ask is:
WHY DON'T YOU ADD A WHERE CLAUSE IN THE SELECT STATEMENT?
Let me put my points very clear.
1 If the application performs full table scan (fast full index scans), then keep the number of extents to minimum,
even the disks are stripped.
2 On the otherside, if the segment is accessed via index look up, then you need not worry about the number of extents. B/c the amount of data fetched via index look up is very minimum.
-
Originally posted by tamilselvan
SQL> @rec_call
Time to complete for Run 1 852
Time to complete for Run 2 571
PL/SQL procedure successfully completed.
SQL>
Unlike Chris, I hope JMODIC would understand the diff between 852 and 571.
I've written this two time allready, I'll repeat myself for the third time:
Originally posted by jmodic
And if you are claiming that run1 was noticably slower than run2 - or vice versa - then you have a problem elsevere, not directly in the number of extents!
Observe this:
Code:
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> drop table test1;
Table dropped.
SQL> drop table test2;
Table dropped.
SQL> alter tablespace test coalesce;
Tablespace altered.
SQL> create table test1
2 storage (initial 16K next 16K pctincrease 0 maxextents 10000)
3 tablespace test
4 nologging
5 as select * from test_sample;
Table created.
SQL> create table test2 storage (initial 10M next 10M pctincrease 0)
2 tablespace test
3 nologging
4 as select * from test_sample;
Table created.
SQL> select count(*) from test1
2 union all
3 select count(*) from test2;
COUNT(*)
----------
224088
224088
SQL> column segment_name format a15
SQL> select segment_name, extents, bytes
2 from user_segments where segment_name like 'TEST_';
SEGMENT_NAME EXTENTS BYTES
--------------- ---------- ----------
TEST1 1546 25329664
TEST2 3 31457280
SQL> create or replace procedure test_fts as
2 l_time_1 number ;
3 l_time_2 number ;
4 l_time_3 number ;
5 l_cnt number;
6 begin
7 l_time_1 := dbms_utility.get_time ;
8 execute immediate 'select count(*) from test1' into l_cnt ;
9 l_time_2 := dbms_utility.get_time ;
10 execute immediate 'select count(*) from test2' into l_cnt ;
11 l_time_3 := dbms_utility.get_time ;
12 dbms_output.put_line('Time to complete for Run 1 (about 1500 extents):'
13 ||to_char(l_time_2 - l_time_1));
14 dbms_output.put_line('Time to complete for Run 2 (3 extents):'
15 ||to_char(l_time_3 - l_time_2));
16 end;
17 /
Procedure created.
SQL> set serveroutput on
SQL> exec test_fts
Time to complete for Run 1 (about 1500 extents):345
Time to complete for Run 2 (3 extents):343
PL/SQL procedure successfully completed.
SQL> exec test_fts
Time to complete for Run 1 (about 1500 extents):349
Time to complete for Run 2 (3 extents):343
PL/SQL procedure successfully completed.
SQL> exec test_fts
Time to complete for Run 1 (about 1500 extents):339
Time to complete for Run 2 (3 extents):336
PL/SQL procedure successfully completed.
SQL> exec test_fts
Time to complete for Run 1 (about 1500 extents):341
Time to complete for Run 2 (3 extents):336
PL/SQL procedure successfully completed.
SQL>
Now what? The proof that "many extents mean bad performance" suddenly becomes vapour, IMHO. Note that I deliberately performed the above mesurments on tables with HUGE difference in number of extents (1500 versus 3), just to make it more obvious.
Oh, yes, Run1 consistently outperformed the Run2, but I'm sure TAMISELVAN (no to mention Chris ) will notice what was the difference between the two. Btw, I was able to perform tests where Run1 (on 1500 extents) consistently outperformed Run2 (on 3 extents). But I don't drive any conclusons from that fact, because I happen to understand the reason for this. And just as a side note, tablespace TEST contains only one single file, residing on my home single-disk PC, so there is no mistery with some RAID systems or alike.
Originally posted by tamilselvan
The next question JMODIC would like to ask is:
WHY DON'T YOU ADD A WHERE CLAUSE IN THE SELECT STATEMENT?
Let me put my points very clear.
1 If the application performs full table scan (fast full index scans), then keep the number of extents to minimum,
even the disks are stripped.
2 On the otherside, if the segment is accessed via index look up, then you need not worry about the number of extents. B/c the amount of data fetched via index look up is very minimum.
Oh come on, don't you think you are making up childish arguments here? We've covered those topics many times on this forums. Do you realy think I don't know what full table scan is? And BTW, the explanation for point 2 that you have provided in your last sentence is amusingly absurd!
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi,
Thanks to me for initiating such a gr8 discussion.
Can I conclude that Many Extents is not a problem.
So I m not reorganizing this table at present and better I concentrate on tuning aspects other than just extents.
Thanks to both Jmodic and tamilselvan.
Vijay
--------------------------
The Time has come ....
-
Originally posted by jmodic
liked the following question in vijay's post:
"Then for what reasons reorganization is done?" I'd say in 95% of cases it is done only because DBBSs must do something to show to their bosses to justify their job possitions and their sallaries. In 99% of those 95% cases unfortunately those same DBBSs don't have a clue that they are just wasting time and their computer resources. They actually think that those rorgs are the prime reason why their databases are in such an excelent shape .
hmmmm, so what are the compelling conditions that will make the reorganizations necessary and its not just another "thats why i am gettign paid for".....
-
If you had something like 10,000 extents, then a reorg ( = moving the table to a locally maaged TS with lareger uniform extent size) would be justifield, but I'm happy myself to go up to a few thousand extents.
Good responses Jurij.
-
-
Let me put my points very clear.
1 If the application performs full table scan (fast full index scans), then keep the number of extents to minimum,
even the disks are stripped.
2 On the otherside, if the segment is accessed via index look up, then you need not worry about the number of extents. B/c the amount of data fetched via index look up is very minimum.
your reference to ixora contradicts your advice quoted above.
If you are performing a full table scan then the overhead of having to read multiple segment header blocks to get all the extent locations is relatively very small.
If you are performing an index lookup of a few rows, then the overhead of the extra segment header read is relatively large.
So based on that, full table scans are not as sensitive to high numbers of extents as index lookups.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|