| select query taking long time [message #297757] |
Sun, 03 February 2008 08:01  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
A select on a particular table is taking a lot of time.
I performed some inserts to this table and session was hanged, i closed the session.
The table has only 20 rows.
SQL>select sid,event,state from v$session_wait where sid=14;
SID EVENT STATE
------ ---------------------------------------------------------------- -------------------
14 db file scattered read WAITING
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: select query taking long time [message #297778 is a reply to message #297757] |
Sun, 03 February 2008 11:50  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
If i am understanding it correctly,
254208=total no. of blocks for table test in tablespace treas.
7904=free unused blocks for table test in tablespace treas.
246303=HWM ie the last block that contains row 20th.
Now upto 254208-246303=7905,the 19 rows are scattered.
So this table has so much fragmentation.
SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TEST';
TABLE_NAME size
------------------------------ --------------------------------------
TEST 1970432kb
SQL>create table test1 as select * from test;
Table created.
SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TEST1';
TABLE_NAME size
------------------------------ ---------------------------------------
TEST1 kb
But since the tablespace is locally managed,why didn't bitmaps take care of fragmentation?
|
|
|
|
|
|
|
|
| Re: select query taking long time [message #297789 is a reply to message #297784] |
Sun, 03 February 2008 13:10 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| varu123 wrote on Sun, 03 February 2008 19:35 | This is what iam trying to do:
Database in noarchive mode
begin
for i in 1..100 loop
insert into test select * from test;
end loop;
end;
|
Hahaha!
Even if you'd start with one row and Oracle could insert one row each nanoseconde (and you have space enough), it would take about 6000 times the Universe life time to complete.
Regards
Michel
[Updated on: Sun, 03 February 2008 13:11] Report message to a moderator |
|
|
|
| Re: select query taking long time [message #297790 is a reply to message #297757] |
Sun, 03 February 2008 13:11 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
I issued the query but i was lacking disk space,so i closed the session.
I monitored free space usage in the tablespace
SQL> /
SUM(BYTES/1048576)
------------------
27.375
After closing the session
SQL> /
SUM(BYTES/1048576)
------------------
28.375
SUM(BYTES/1048576)
------------------
29.375
SQL> /
SUM(BYTES/1048576)
------------------
29.375
SQL> /
SUM(BYTES/1048576)
------------------
30.375
SQL> /
SUM(BYTES/1048576)
------------------
31.375
I closed the session,why free space is increasing and undo tablespace free space is increasing?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: select query taking long time [message #297864 is a reply to message #297828] |
Mon, 04 February 2008 01:12 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Easy, at each loop you double the number of rows you insert.
At loop 50 you have to insert 2^49 rows, if you start with 1 row in the table (^ is power function).
So during the 50 loops, you inserted 1+2+4+8+16+...+2^49 = 2^50-1 rows.
So how much time:
SQL> select (power(2,50)-1)/365.25/24/60/60/1000000 years from dual;
YEARS
----------
35.6776151
1 row selected.
(I said 75 because I work out it in my head with 30000000 seconds per years, actually it is 31557600, and start with 2 rows instead of 1, sorry for the mistake).
Regards
Michel
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: select query taking long time [message #297917 is a reply to message #297908] |
Mon, 04 February 2008 04:09 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Something like:
SQL> create table test as
2 select mod(object_id,5) id, substr(owner,1,1) val
3 from dba_objects where object_id is not null;
Table created.
SQL> select id, val, count(*) from test group by id, val order by id, val;
ID V COUNT(*)
---------- - ----------
0 B 2
0 D 51
0 E 57
0 H 6
0 I 11
0 M 123
0 O 339
0 P 3783
0 S 4920
0 T 1
0 W 50
0 X 99
1 B 2
1 D 55
1 E 55
1 H 7
1 I 10
1 M 137
1 O 348
1 P 3780
1 S 4921
1 T 1
1 W 45
1 X 101
2 B 1
2 D 55
2 E 52
2 H 7
2 I 11
2 M 127
2 O 340
2 P 3786
2 S 4923
2 W 47
2 X 101
3 B 1
3 D 53
3 E 60
3 H 7
3 I 11
3 M 131
3 O 340
3 P 3802
3 S 4901
3 T 1
3 W 46
3 X 96
4 B 2
4 D 54
4 E 57
4 H 7
4 I 11
4 M 135
4 O 343
4 P 3775
4 S 4923
4 W 54
4 X 99
58 rows selected.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
