Re: Why a query take a long time on a relative small table?
Date: 1996/08/01
Message-ID: <3200C617.6DFD_at_teldta.com>
Paul Cao wrote:
>
> Hi,
> In our software, we have a query that seems to take forever
> to response. Basically, the query is
> SELECT A, B, C, D, ...
> FROM VIEW (a view)
> WHERE A IN ('HELLO'),
> B IN ('THE'),
> C IN ('WORLD'),
> AND D IN ('OK');
> This query takes about 7 minutes or more to response (TKPROF). If
> I remove one condition in the WHERE then it responses immmediately.
> I tried to look into how the view is define in DBA_VIEWS, but only able to
> see partially how the view is defined.
>
> First, how do I export a complete table of DBA_VIEWS (I was able to export but
> the lines is toolong so it comes out missing important stuff) or find out
> how a view is defined?
> Second, any insight into above problem is greatly appreciated.
>
> Paul
The "text" field of dba_views is a long so try doing this at the sql prompt and issue your query again:
SQL> set arraysize 1 long 4096
The following is an example:
SQL> set pagesize 24 linesize 132 pause on pause 'Hit Enter To Continue...' SQL> desc dba_views
Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG
SQL> select max(TEXT_LENGTH) from dba_views; Hit Enter To Continue...
MAX(TEXT_LENGTH)
3685
SQL> select * from dba_views where text_length = 3685; Hit Enter To Continue...
OWNER VIEW_NAME TEXT_LENGTH ------------------------------ ------------------------------ -----------TEXT
SYS DBA_OBJ_AUDIT_OPTS 3685select u.name, o.name, 'TABLE',
substr(t.audit$, 1, 1) || '/' || substr(t.audit$
SQL> set arraysize 1 long 4096
SQL> select * from dba_views where text_length = 3685;
Hit Enter To Continue...
OWNER VIEW_NAME TEXT_LENGTH ------------------------------ ------------------------------ -----------TEXT
SYS DBA_OBJ_AUDIT_OPTS 3685 select u.name, o.name, 'TABLE', substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1), substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1), substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1), substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1), substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1), substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1), substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1), substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1), substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1), substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1), substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1), substr(t.audit$, 23, 1) || '/' || substr(t.audit$, 24, 1),substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1) from sys.obj$ o, sys.user$ u, sys.tab$ t where o.type = 2
and not (o.owner# = 0 and o.name = '_default_auditing_options_') and o.owner# = u.user#
Hit Enter To Continue...
OWNER VIEW_NAME TEXT_LENGTH ------------------------------ ------------------------------ -----------TEXT
and o.obj# = t.obj#
union all
select u.name, o.name, 'VIEW', substr(v.audit$, 1, 1) || '/' || substr(v.audit$, 2, 1), substr(v.audit$, 3, 1) || '/' || substr(v.audit$, 4, 1), substr(v.audit$, 5, 1) || '/' || substr(v.audit$, 6, 1), substr(v.audit$, 7, 1) || '/' || substr(v.audit$, 8, 1), substr(v.audit$, 9, 1) || '/' || substr(v.audit$, 10, 1), substr(v.audit$, 11, 1) || '/' || substr(v.audit$, 12, 1), substr(v.audit$, 13, 1) || '/' || substr(v.audit$, 14, 1), substr(v.audit$, 15, 1) || '/' || substr(v.audit$, 16, 1), substr(v.audit$, 17, 1) || '/' || substr(v.audit$, 18, 1), substr(v.audit$, 19, 1) || '/' || substr(v.audit$, 20, 1), substr(v.audit$, 21, 1) || '/' || substr(v.audit$, 22, 1), substr(v.audit$, 23, 1) || '/' || substr(v.audit$, 24, 1), substr(v.audit$, 25, 1) || '/' || substr(v.audit$, 26, 1) from sys.obj$ o, sys.user$ u, sys.view$ vwhere o.type = 4
and o.owner# = u.user#
Hit Enter To Continue...
OWNER VIEW_NAME TEXT_LENGTH ------------------------------ ------------------------------ -----------TEXT
and o.obj# = v.obj#
union all
select u.name, o.name, 'SEQUENCE', substr(s.audit$, 1, 1) || '/' || substr(s.audit$, 2, 1), substr(s.audit$, 3, 1) || '/' || substr(s.audit$, 4, 1), substr(s.audit$, 5, 1) || '/' || substr(s.audit$, 6, 1), substr(s.audit$, 7, 1) || '/' || substr(s.audit$, 8, 1), substr(s.audit$, 9, 1) || '/' || substr(s.audit$, 10, 1), substr(s.audit$, 11, 1) || '/' || substr(s.audit$, 12, 1), substr(s.audit$, 13, 1) || '/' || substr(s.audit$, 14, 1), substr(s.audit$, 15, 1) || '/' || substr(s.audit$, 16, 1), substr(s.audit$, 17, 1) || '/' || substr(s.audit$, 18, 1), substr(s.audit$, 19, 1) || '/' || substr(s.audit$, 20, 1), substr(s.audit$, 21, 1) || '/' || substr(s.audit$, 22, 1), substr(s.audit$, 23, 1) || '/' || substr(s.audit$, 24, 1), substr(s.audit$, 25, 1) || '/' || substr(s.audit$, 26, 1) from sys.obj$ o, sys.user$ u, sys.seq$ swhere o.type = 6
and o.owner# = u.user#
Hit Enter To Continue...
OWNER VIEW_NAME TEXT_LENGTH ------------------------------ ------------------------------ -----------TEXT
and o.obj# = s.obj#
union all
select u.name, o.name, 'PROCEDURE', substr(p.audit$, 1, 1) || '/' || substr(p.audit$, 2, 1), substr(p.audit$, 3, 1) || '/' || substr(p.audit$, 4, 1), substr(p.audit$, 5, 1) || '/' || substr(p.audit$, 6, 1), substr(p.audit$, 7, 1) || '/' || substr(p.audit$, 8, 1), substr(p.audit$, 9, 1) || '/' || substr(p.audit$, 10, 1), substr(p.audit$, 11, 1) || '/' || substr(p.audit$, 12, 1), substr(p.audit$, 13, 1) || '/' || substr(p.audit$, 14, 1), substr(p.audit$, 15, 1) || '/' || substr(p.audit$, 16, 1), substr(p.audit$, 17, 1) || '/' || substr(p.audit$, 18, 1), substr(p.audit$, 19, 1) || '/' || substr(p.audit$, 20, 1), substr(p.audit$, 21, 1) || '/' || substr(p.audit$, 22, 1), substr(p.audit$, 23, 1) || '/' || substr(p.audit$, 24, 1), substr(p.audit$, 25, 1) || '/' || substr(p.audit$, 26, 1) from sys.obj$ o, sys.user$ u, sys.procedure$ pwhere o.type >= 7 and o.type <= 9
and o.owner# = u.user#
and o.obj# = p.obj#
brian.maclean_at_teldta.com Received on Thu Aug 01 1996 - 00:00:00 CEST