Home » RDBMS Server » Performance Tuning » select query taking long time
select query taking long time [message #297757] Sun, 03 February 2008 08:01 Go to next message
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 #297758 is a reply to message #297757] Sun, 03 February 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
About 400 posts and you don't know that Performances question has to be posted in... tada... Performance Tuning forum.

Quote:
session was hanged

You should say you think session hangs.
More if it is waiting on "db file scattered read" as you seem to say, it is not hanged, it is waiting for disk read access.

Regards
Michel
Re: select query taking long time [message #297760 is a reply to message #297757] Sun, 03 February 2008 09:19 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Oh yes,And after posting the question i realised that. Confused
Yes its waiting for disk access but why only this table is taking so much time?
If i query other tables,it takes no time.
Re: select query taking long time [message #297764 is a reply to message #297760] Sun, 03 February 2008 09:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How "high" is the high water mark? Determine its value using the following OraFAQ suggestion.
Re: select query taking long time [message #297766 is a reply to message #297760] Sun, 03 February 2008 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate a 10046 trace and look at how much blocks are read.
Also, if you want us to help you, you have to post full information.
An "insert values" can't wait on "db file scattered read" (at least not long enough you can see it).
Post the actual query.
I bet it is an "insert select" and what takes long is the select part not the insert one.

Regards
Michel
Re: select query taking long time [message #297772 is a reply to message #297757] Sun, 03 February 2008 11:11 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Recover datafile 11/14 solved it.
Now back to PT,
Quote:
I bet it is an "insert select" and what takes long is the select part not the insert one

Michel,you know everything.

When the session hanged,i closed it.
Now the table has just 20 records and it hangs after displaying the 19th record. Confused

  1  SELECT BLOCKS
  2          FROM   DBA_SEGMENTS
  3*         WHERE  OWNER=UPPER('treas') AND SEGMENT_NAME = UPPER('test')
SQL> /

    BLOCKS
----------
    254208

SQL> ANALYZE TABLE treas.test ESTIMATE STATISTICS;
Table analyzed.
  1  SELECT EMPTY_BLOCKS
  2          FROM   DBA_TABLES
  3*         WHERE  OWNER=UPPER('treas') AND TABLE_NAME = UPPER('test')
SQL> /

EMPTY_BLOCKS
------------
        7904

HWM=254208-7904-1=246303
Re: select query taking long time [message #297775 is a reply to message #297772] Sun, 03 February 2008 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Recover datafile 11/14 solved it.
Now back to PT,

This should never appear in this topic, I will remove this post.

Quote:
Now the table has just 20 records and it hangs after displaying the 19th record.

Once again activate trace and see what the session is doing.

Quote:
HWM=254208-7904-1=246303

so Oracle has to read 246303 blocks and this takes time.

Regards
Michel
Re: select query taking long time [message #297778 is a reply to message #297757] Sun, 03 February 2008 11:50 Go to previous messageGo to next message
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 #297781 is a reply to message #297778] Sun, 03 February 2008 12:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If possible (i.e. you have INSERT INTO statements saved in a file), truncate the table. It will return the high watermark back to zero; afterwards, run the INSERT INTO script once again to restore those 20 records.

Otherwise, you might try to use the
ALTER TABLE table_name MOVE;
and see what happens.

If you were on 10g, you might also use its SHRINK feature as
ALTER TABLE table_name ENABLE ROW movement;

ALTER TABLE table_name SHRINK SPACE;
Re: select query taking long time [message #297784 is a reply to message #297757] Sun, 03 February 2008 12:35 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
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;

Where should i add truncate statement?
Re: select query taking long time [message #297789 is a reply to message #297784] Sun, 03 February 2008 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #297791 is a reply to message #297790] Sun, 03 February 2008 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your query, "/" is a bit weak as information.

Read my previous post and goo luck!

Regards
Michel

[Updated on: Sun, 03 February 2008 13:14]

Report message to a moderator

Re: select query taking long time [message #297793 is a reply to message #297757] Sun, 03 February 2008 13:26 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
the query is:
SQL> select sum(bytes/1048576)from dba_free_space
where tablespace_name='TREAS';

Quote:
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.


Shocked

The table has just two columns,i bet it won't take that much of time but a lot of space.
I shall reduce it to 50 loops.
Re: select query taking long time [message #297795 is a reply to message #297793] Sun, 03 February 2008 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right with 50 loops you only needs 75 years if rows are inserted one per microsecond.

Regards
Michel

[Updated on: Sun, 03 February 2008 13:45]

Report message to a moderator

Re: select query taking long time [message #297828 is a reply to message #297757] Sun, 03 February 2008 23:02 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
You're right with 50 loops you only needs 75 years if rows are inserted one per microsecond.


CAn you post your mathematical formulae?
Re: select query taking long time [message #297864 is a reply to message #297828] Mon, 04 February 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 #297889 is a reply to message #297757] Mon, 04 February 2008 03:15 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So i chose the impossible ways to bulk insert records.
I shall use two tables then.

Re: select query taking long time [message #297898 is a reply to message #297889] Mon, 04 February 2008 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you want to do?

Regards
Michel
Re: select query taking long time [message #297903 is a reply to message #297757] Mon, 04 February 2008 03:45 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I am trying to learn the basics.
Re: select query taking long time [message #297904 is a reply to message #297903] Mon, 04 February 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, but what do you want to do with this PL/SQL block?
Why did you write it?

Regards
Michel

[Updated on: Mon, 04 February 2008 03:46]

Report message to a moderator

Re: select query taking long time [message #297908 is a reply to message #297757] Mon, 04 February 2008 03:56 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
haha,
That plsql code inserts redundant records. Thats it.
Later on i will try to delete duplicate records.
As they say,i am playing with oracle. Smile
Re: select query taking long time [message #297917 is a reply to message #297908] Mon, 04 February 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: select query taking long time [message #297922 is a reply to message #297757] Mon, 04 February 2008 04:18 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
BUt where is the duplicacy?
Every row is a unique row.
Re: select query taking long time [message #297926 is a reply to message #297922] Mon, 04 February 2008 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If count(*) is > 1 then there are duplicates. count(*) is not part of the rows, the query shows the duplicates.

Regards
Michel
Re: select query taking long time [message #297927 is a reply to message #297757] Mon, 04 February 2008 04:53 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
oh.k,
Now i see the duplicates in the query.
Thanks Michel.
Previous Topic: patch 4589082
Next Topic: Query not running in oracle 10.2.0.3
Goto Forum:
  


Current Time: Thu Dec 08 20:09:47 CST 2016

Total time taken to generate the page: 0.06962 seconds