Home » SQL & PL/SQL » SQL & PL/SQL » Select Statement suddenly takes very long!
Select Statement suddenly takes very long! [message #227495] Wed, 28 March 2007 07:08 Go to next message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
Hello from Germany,
how comes that from one week to another my select-statement "select MAX(PRIMARYKEY) from mytable t where t.IntegerColumn = 5" takes 20times longer (25s) than before (0,4s)?
The RowCount of myTable is 89899.

Best Regards
Marlon
Re: Select Statement suddenly takes very long! [message #227501 is a reply to message #227495] Wed, 28 March 2007 07:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Statistics.
DId you update the statistics?
Else gather new stats on tables/indexes and try again.
Re: Select Statement suddenly takes very long! [message #227502 is a reply to message #227495] Wed, 28 March 2007 07:28 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

We need more info. How many records were in the table last week? Are there other programs running at the same time you're running your query? Maybe another program or query is slowing down the system? It could be a lot of things.
Re: Select Statement suddenly takes very long! [message #227513 is a reply to message #227501] Wed, 28 March 2007 07:50 Go to previous messageGo to next message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
Sorry I'm completely new with statistics! How do I set or get them?
I read about the DBMS_STATS package and was able to set the stats with:
begin
DBMS_STATS.SET_TABLE_STATS (
   'myscheme', 
   'mytable');
   end;

But how to get the stats?

@Ericle: No other processes run or disturb the statement.

[Updated on: Wed, 28 March 2007 07:51]

Report message to a moderator

Re: Select Statement suddenly takes very long! [message #227518 is a reply to message #227513] Wed, 28 March 2007 07:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Did you read the documentation? Try GATHER_TABLE_STATS instead Wink

MHE
Re: Select Statement suddenly takes very long! [message #227709 is a reply to message #227518] Thu, 29 March 2007 02:35 Go to previous messageGo to next message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
Ok.
I found following and executed it in a new sql-window:
begin
DBMS_STATS.gather_table_stats(
   'myscheme', 
   'myTable');
end;


But how do I GET the results?
Marlon
Re: Select Statement suddenly takes very long! [message #227713 is a reply to message #227709] Thu, 29 March 2007 02:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The statistics are stored in the data dictionary, so you can get the values like LAST_ANALYZED / NUM_ROWS / AVG_ROW_LEN with
SELECT * FROM user_tables

for example.
Re: Select Statement suddenly takes very long! [message #227715 is a reply to message #227709] Thu, 29 March 2007 02:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Statistics are gathered for the optimizer, not for you. If the optimizer can rely on up to date statistics it will calculate the optimal access path for your query and under normal circumstances your query will run as fast as before.

How is the performance after the statistics have been gathered?

MHE
Re: Select Statement suddenly takes very long! [message #227755 is a reply to message #227709] Thu, 29 March 2007 05:11 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just to be in the safer side, i would do this.
exec 
dbms_stats.gather_table_stats('OWNER','TABLE_NAME',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 250',cascade=>TRUE);
Previous Topic: Force View
Next Topic: significance of count(1)
Goto Forum:
  


Current Time: Tue Dec 06 02:31:26 CST 2016

Total time taken to generate the page: 0.17143 seconds