Q: Why so long?

Q: Why so long?

Post by r.. » Sun, 31 Dec 1899 09:00:00



Hi,

I am having a problem with a business objects generated query. This
comes from the universe designer rather than the users. The query goes
something like this run from sqlplus

select c.table_name,c.constraint_name,cc.column_name,cc.position
from all_constraints c,all_cons_columns cc
where c.constraint_name=cc.constraint_name
and c.table_name = 'SALTB003'
order by 1,2,4

This is not the exact business objects query, though suffers the same
problem in that It will take 16-20 mins to run? Then if run again
immediately afterwards will execute sub-second. There are about 2500
rows in all_cons_columns and 2000 in all_constraints.

Thanks

Rebecca

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Q: Why so long?

Post by g.. » Sun, 31 Dec 1899 09:00:00




> Hi,

> I am having a problem with a business objects generated query. This
> comes from the universe designer rather than the users. The query goes
> something like this run from sqlplus

> select c.table_name,c.constraint_name,cc.column_name,cc.position
> from all_constraints c,all_cons_columns cc
> where c.constraint_name=cc.constraint_name
> and c.table_name = 'SALTB003'
> order by 1,2,4

> This is not the exact business objects query, though suffers the same
> problem in that It will take 16-20 mins to run? Then if run again
> immediately afterwards will execute sub-second. There are about 2500
> rows in all_cons_columns and 2000 in all_constraints.

> Thanks

> Rebecca

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Looks like it query Oracle data dictionary  and it can take long time
if the data dictionary is "big". The reason that the 2nd time fast
execution is due to cache.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Q: Why so long?

Post by Sybrand Bakke » Sun, 31 Dec 1899 09:00:00


Typical case of incorrect driving table
(or your datadictionary must have been analyzed and contain statistics for
SYS. If they are there remove them (for SYS only)
It should be
from all_cons_columns cc, all_constraints c
The second time the records are still in cache, so it is not surprising they
return so quick.

Hth,

Sybrand Bakker, Oracle DBA


> Hi,

> I am having a problem with a business objects generated query. This
> comes from the universe designer rather than the users. The query goes
> something like this run from sqlplus

> select c.table_name,c.constraint_name,cc.column_name,cc.position
> from all_constraints c,all_cons_columns cc
> where c.constraint_name=cc.constraint_name
> and c.table_name = 'SALTB003'
> order by 1,2,4

> This is not the exact business objects query, though suffers the same
> problem in that It will take 16-20 mins to run? Then if run again
> immediately afterwards will execute sub-second. There are about 2500
> rows in all_cons_columns and 2000 in all_constraints.

> Thanks

> Rebecca

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Q: Why so long?

Post by tedc.. » Sun, 31 Dec 1899 09:00:00


robecca, do explain for your query to find out what's going on. ted


> Hi,

> I am having a problem with a business objects generated query. This
> comes from the universe designer rather than the users. The query goes
> something like this run from sqlplus

> select c.table_name,c.constraint_name,cc.column_name,cc.position
> from all_constraints c,all_cons_columns cc
> where c.constraint_name=cc.constraint_name
> and c.table_name = 'SALTB003'
> order by 1,2,4

> This is not the exact business objects query, though suffers the same
> problem in that It will take 16-20 mins to run? Then if run again
> immediately afterwards will execute sub-second. There are about 2500
> rows in all_cons_columns and 2000 in all_constraints.

> Thanks

> Rebecca

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Q: Why so long?

Post by r.. » Sun, 31 Dec 1899 09:00:00






> > Hi,

> > I am having a problem with a business objects generated query. This
> > comes from the universe designer rather than the users. The query
goes
> > something like this run from sqlplus

> > select c.table_name,c.constraint_name,cc.column_name,cc.position
> > from all_constraints c,all_cons_columns cc
> > where c.constraint_name=cc.constraint_name
> > and c.table_name = 'SALTB003'
> > order by 1,2,4

> > This is not the exact business objects query, though suffers the
same
> > problem in that It will take 16-20 mins to run? Then if run again
> > immediately afterwards will execute sub-second. There are about 2500
> > rows in all_cons_columns and 2000 in all_constraints.

> > Thanks

> > Rebecca

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Looks like it query Oracle data dictionary  and it can take long time
> if the data dictionary is "big". The reason that the 2nd time fast
> execution is due to cache.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

I think that the data dictionary is "big", but during the 16 mins of
execution, the server shows no sign of any activity on disk or  session
logical reads, but 1 cpu is running 100%?

Rebecca

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Q: Why so long?

Post by r.. » Sun, 31 Dec 1899 09:00:00



I tried to do an explain from top sessions, but was told that I didn't
have  permissions on the underlying tables.

Rebecca

> robecca, do explain for your query to find out what's going on. ted


> > Hi,

> > I am having a problem with a business objects generated query. This
> > comes from the universe designer rather than the users. The query
goes
> > something like this run from sqlplus

> > select c.table_name,c.constraint_name,cc.column_name,cc.position
> > from all_constraints c,all_cons_columns cc
> > where c.constraint_name=cc.constraint_name
> > and c.table_name = 'SALTB003'
> > order by 1,2,4

> > This is not the exact business objects query, though suffers the
same
> > problem in that It will take 16-20 mins to run? Then if run again
> > immediately afterwards will execute sub-second. There are about 2500
> > rows in all_cons_columns and 2000 in all_constraints.

> > Thanks

> > Rebecca

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Q: Why so long?

Post by r.. » Sun, 31 Dec 1899 09:00:00




Thanks, I changed the order of the tables in the from clause and got the
time down to 6 mins from 16. Though this is still way more than I would
expect. There is probably something else wrong.

Rebecca

> Typical case of incorrect driving table
> (or your datadictionary must have been analyzed and contain
statistics for
> SYS. If they are there remove them (for SYS only)
> It should be
> from all_cons_columns cc, all_constraints c
> The second time the records are still in cache, so it is not
surprising they
> return so quick.

> Hth,

> Sybrand Bakker, Oracle DBA




Quote:> > Hi,

> > I am having a problem with a business objects generated query. This
> > comes from the universe designer rather than the users. The query
goes
> > something like this run from sqlplus

> > select c.table_name,c.constraint_name,cc.column_name,cc.position
> > from all_constraints c,all_cons_columns cc
> > where c.constraint_name=cc.constraint_name
> > and c.table_name = 'SALTB003'
> > order by 1,2,4

> > This is not the exact business objects query, though suffers the
same
> > problem in that It will take 16-20 mins to run? Then if run again
> > immediately afterwards will execute sub-second. There are about 2500
> > rows in all_cons_columns and 2000 in all_constraints.

> > Thanks

> > Rebecca

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Q: Why so long?

Post by Doug Cowl » Sun, 31 Dec 1899 09:00:00


I'm not trying to be cute... but is your SGA big enough?  Clearly
the speed increase must be from cache, but there is a dictionary
cache in the shared pool that may not be large enough...just a
thought.. And although I'm not certain, it seems that
"and c.table_name = 'SALTB003' is a much more limiting expression than
"where c.contraint_name = cc.constraint_name" and therefore should
come first like..

select c.table_name,c.constraint_name,cc.column_name,cc.position
from all_constraints c,all_cons_columns cc
where c.table_name = 'SALTB003'
and c.constraint_name=cc.constraint_name
order by 1,2,4

It is my understanding that limiting expressions should appear first
as the where clause is parsed left to right as oppossed to the from
clause that is parsed right to left...

Comments welcome... just a couple shots in the dark....

- Dc.


>Hi,

>I am having a problem with a business objects generated query. This
>comes from the universe designer rather than the users. The query goes
>something like this run from sqlplus

>select c.table_name,c.constraint_name,cc.column_name,cc.position
>from all_constraints c,all_cons_columns cc
>where c.constraint_name=cc.constraint_name
>and c.table_name = 'SALTB003'
>order by 1,2,4

>This is not the exact business objects query, though suffers the same
>problem in that It will take 16-20 mins to run? Then if run again
>immediately afterwards will execute sub-second. There are about 2500
>rows in all_cons_columns and 2000 in all_constraints.

>Thanks

>Rebecca

>Sent via Deja.com http://www.deja.com/
>Before you buy.

 
 
 

Q: Why so long?

Post by Roland Svensso » Sun, 31 Dec 1899 09:00:00



>Thanks, I changed the order of the tables in the from clause and got the
>time down to 6 mins from 16. Though this is still way more than I would
>expect. There is probably something else wrong.

As Sybrand noted, your datadictionary must have been analyzed,
CBO parsetime for this query is 16 (or 6) mins. The second time
no parsing is done and you get the answer immediatly.
You should get rid of the datadictionary statistics,
you could also try a RULE hint.

--
Regards,
Roland

 
 
 

1. Can you tell me why this takes so long

We have an application that takes a long time to update the database after a
report is printed.  The vendor seems to think there is no problem.  Today an
error occurred during the update and the error listing included this SQL
Statement.  I think that this is the statement that is taking a long time to
execute, but I don't know enough about how it is executed to be sure.  The
table identified as B has about 2000 records, the table A has about 1000
records.  Typically an update involves 10 detail records from B, and 1
master record from A.  If you can tell me why this would take 12 minutes on
a 333 machine on an access database that resides on a Windows NT server, I'd
greatly appreciate it.

Here is the SQL Statement:

UPDATE InvDtl B, Job A, SET B.process_flg='P' WHERE A.job_no=B.job_no AND
A.cmpny_cd=B.cmpny_cd AND A.cmpny_cd='10' AND A.tm_process_flg=' ' AND
B.process_flg='Y' AND B.print_detail<>0 AND (B.bill_date<=#10/26/1999# or
B.bill_date is null) AND B.job_no IN (SELECT C.job_no FROM InvT C where
B.cmpny_cd=C.cmpny_cd AND B.job_no=C.job_no AND C.rep_flg=' ' AND C.mch_no=1
AND C.sesson_no=1 AND C.exe_hwnd=1592 Group by C.job.no)

Your help will be appreciated.

Bob

2. PROGRESS or ORACLE?

3. Why does data syncronization no longer work after upsize to SQL7

4. Approach 3.0 Print Problem

5. why connecting takes such a long time(3 minutes)

6. is there a way to loop through collection of parameters IN a stored proc?

7. Why DELETE takes longer than INSERT?

8. problems with restore - my experiences

9. Why is user no longer associated?

10. Why is it taking so long to delete????

11. Why does dropping an index take so long

12. Can you tell me why this takes so long

13. why views take too long to return data???