Home » SQL & PL/SQL » SQL & PL/SQL » Difference in the execution timings .. plz explain
Difference in the execution timings .. plz explain [message #218202] Wed, 07 February 2007 05:06 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
I have a SQL query which fecthes 40,000 records from a table in schema A and it takes few seconds to do that.

I also have the same table in schema B containing same records. When i try to execute the Same SQL from schema A( fetching data from schema B) it takes 4-5 minutes to give out results.

Query from Schema A / table in schema A
select count(*) from abc --Result: 40000, time 5 seconds

Query from Schema A / table in schema B
select count(*) from B.abc --result: 40000, time 4 min

Please explain wht is the cause?
Re: Difference in the execution timings .. plz explain [message #218213 is a reply to message #218202] Wed, 07 February 2007 06:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Update the statistics and try again.

[Updated on: Wed, 07 February 2007 06:04]

Report message to a moderator

Re: Difference in the execution timings .. plz explain [message #218222 is a reply to message #218202] Wed, 07 February 2007 06:32 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Mahesh Rajendran wrote on Wed, 07 February 2007 17:34
Update the statistics and try again.

Hi Mahesh,

I have heard of Update statistics but havent done as of now ... Can u tell me how do i update statistics for that particular index.

Thanks in advance!

Regards,
decci

[Updated on: Wed, 07 February 2007 06:51]

Report message to a moderator

Re: Difference in the execution timings .. plz explain [message #218226 is a reply to message #218222] Wed, 07 February 2007 06:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
To start with, do this
analyze table <table name> compute statistics; 

But in a regular basis (after every major change in data) do the same with DBMS_STATS (recomended method instead of ANALYZE TABLE).
exec dbms_stats.gather_table_stats('OWNER','TABLE_NAME',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 250',cascade=>TRUE);

By this you are gathering statistics for both table and indexes on all indexed columns. These statistics are used by CBO to decide the best path to get the data.
If you still see the difference, HWM (high water mark) might be the issue.
Search this forum for HWM/dbms_stats.gather_table_stats for more examples/cases.
Regards
Re: Difference in the execution timings .. plz explain [message #218228 is a reply to message #218226] Wed, 07 February 2007 06:52 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Thanks for a quick reply mahesh ..

I tried the same but it seems as if i dont have the sufficient privileges to do that for the particular schema .. is there any other way out?

[Updated on: Wed, 07 February 2007 07:06]

Report message to a moderator

Re: Difference in the execution timings .. plz explain [message #218230 is a reply to message #218228] Wed, 07 February 2007 07:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is schema B actually on the same database as Schema A, or are you accessing it over a database link?

If it is on the same database, then I suspect that there is some significant difference between the two tables.
Do they both have the same indexes?
Do they both have the same high water mark? (SELECT blocks from all_tables where table_name = <table>)
Re: Difference in the execution timings .. plz explain [message #218231 is a reply to message #218230] Wed, 07 February 2007 07:21 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
The schema is B is on the same database as schema A.
the schema A is actually realtime schema and schema B is archive schema.

However the table names are different. But they both contains similar set of data.

and yes .. the indexes on both tables are different.

But i guess to do anything on that particular schema i need those privileges or the user/pass for that schema .. rite ??

I'm a new user to oracle so i dont know much about the tuning techniques. i'm still on the learning curve so the help will be much appreciated.
Re: Difference in the execution timings .. plz explain [message #218262 is a reply to message #218231] Wed, 07 February 2007 09:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post the query that you're running, and a list of what indexes are available on the two tables?
Re: Difference in the execution timings .. plz explain [message #218265 is a reply to message #218262] Wed, 07 February 2007 09:31 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
JRowbottom wrote on Wed, 07 February 2007 20:33
Can you post the query that you're running, and a list of what indexes are available on the two tables?


The query is :

SELECT count(*) FROM db_arc.decci_PRESTAGE_arc
WHERE TRUNC(processdate) = '14-oct-2006'
AND PROCESSFLAG =4

The indexes on the table decci_prestage are:

IDX_prestage_1 on part_ID
IDX_prestage_2 on job
IDX_prestage_3 on applydate
IDX_prestage_4 on batch
IDX_stage on stagekey
IDX_process on processflag


The indexes on the table db_arc.decci_PRESTAGE_arc are:

IDX_stage on stagekey
prestage_IDX1 on part_ID, name and applydate


Please let me know if any other info is required.

Thanks!
Re: Difference in the execution timings .. plz explain [message #218274 is a reply to message #218265] Wed, 07 February 2007 10:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that solves the problem.
I'll bet a fair sized pot of money that when you run the query against the table in schema A, the optimizer accesses the table via the index IDX_process.
When you run the query on the table in schema B, it has no choice but to do a full table scan.

If you can't create additional indexes on the table in schema B, then I'm afraid you're going to have to live with the situation.
Re: Difference in the execution timings .. plz explain [message #218284 is a reply to message #218274] Wed, 07 February 2007 10:49 Go to previous message
decci_7
Messages: 68
Registered: March 2006
Member
JRowbottom wrote on Wed, 07 February 2007 21:45

If you can't create additional indexes on the table in schema B, then I'm afraid you're going to have to live with the situation.


Yup... thats rite.

even prior to posting the query ... i tried to create one index upon these 2 fields .. but as i've mentioned that i dont have sufficient privileges .. it failed.

Nyways thanks for sharing so much valuable information Smile

I have asked my DBA to create index on that table. Hopefully it will solve the problem.

Thanks again for looking into the problem.
Previous Topic: psp -> start/init session (merged)
Next Topic: remove duplicates from a query containing more than 1 table
Goto Forum:
  


Current Time: Sat Dec 10 16:47:32 CST 2016

Total time taken to generate the page: 0.12036 seconds