Re: What's wrong with this sp?

How slow is slow?
See if you are swapping 120 mgs of RAM is not very much. You are probably swapping a lot. Also look at your disk queue length. Jim

"DL" <donlcs_at_yahoo.com> wrote in message news:604bb90c.0111141913.18e44263_at_posting.google.com...
> I'm running Oracle 8 Personal Edition on Windows 2000 with 120 MG RAM,
> many services are running which sure take lots of memory. What could
> possibly cause the slowness of the query on view? Thanks.
>
> DL
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<huyI7.26286$XJ4.16197488_at_news1.sttln1.wa.home.com>...
> > Actually these are not tables but views.
> > The query should be fast. Also most likely in production this data is
> > cached in memory.
> > Jim
> > "DL" <donlcs_at_yahoo.com> wrote in message
> > news:604bb90c.0111140946.4e2abdf5_at_posting.google.com...
> > > Well, I commented out cols.column_name column by adding "--" before
> > > it, I bet
> > > "--" is also a comment sign for Oracle. All right, what I really want
> > > to do in this case is to improve query efficiency (it involves three
> > > system tables), and I now found out that none of these tables
> > > (dba_constraints, dba_cons_columns, dba_tab_columns) have any indexes,
> > > without index query is sure slow, so, now, the issue is, first to
> > > check if there's index for each table (for its relevant column), if
> > > not, create an index for it. What do you think? Thanks.
> > >
> > > DL
> > >
> > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<1cfI7.22711$XJ4.14082736_at_news1.sttln1.wa.home.com>...
> > > > I don't have a database to try this on. But possibly the error is
 at
> > > > > select c.nullable --, cols.column_name
> > > >
> > > > the -- is a comment in pl/sql.
> > > >
> > > > before defining the package do
> > > > set serverout on
> > > > define the package
> > > > then
> > > > show errors
> > > >
> > > > It should show the line and column where the problem is occuring.
> > > > My guess is some sort of syntax error.
> > > > Jim
> > > >
> > > > "DL" <donlcs_at_yahoo.com> wrote in message
> > > > news:604bb90c.0111131052.73e0fc0_at_posting.google.com...
> > > > > Hi,
> > > > >
> > > > > The doc is very limited in explaining the usage. I grab the
 concept
> > > > > and turn it into the following code, running on PL*SQL 8.1.7
 received
> > > > > err:
> > > > > package created with compilation errors:
> > > > >
> > > > > Create or replace package Pak
> > > > > type PakType IS ref cursor;
> > > > > Create or replace procedure cardi (ow IN varchar2, tbn IN
 varchar2,
> > > > > cnYN OUT Pak.PakType)
> > > > > IS
> > > > > Begin
> > > > > open cnYN for
> > > > > select c.nullable --, cols.column_name
> > > > > from dba_constraints cons, dba_cons_columns cols, dba_tab_columns
> > > > > c
> > > > > where cons.table_name = cols.table_name
> > > > > and cons.owner = own
> > > > > and c.table_name=tbn
> > > > > and cols.column_name = c.column_name
> > > > > and cons.constraint_name = cols.constraint_name
> > > > > and cons.constraint_type = 'R'
> > > > > and cons.table_name =tbn
> > > > > End
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<K%1I7.19325$XJ4.12861627_at_news1.sttln1.wa.home.com>...
> > > > > > The ref cursor documentation is in the Oracle docs you got with
 the
> > > > > > software. (in html). Look in the Application Developers guide
> > PL/SQL.
> > The
> > > > > > distinct will have more effect as the number of rows grow.(in
 the
 result
> > > > > > set)
> > > > > > Jim
> > > > > > "DL" <donlcs_at_yahoo.com> wrote in message
> > > > > > news:604bb90c.0111122027.58c30dd6_at_posting.google.com...
> > > > > > > Thanks, Jim, for throwing one stone (that almost hits two
 birds).
> > > > > > > I'll find an Oracle book tomorrow to check on ref cursor. As
 for
 the
> > > > > > > "distinct" function, I've commented it out and selects just
 one
 column
> > > > > > > now, however, it has not reduced any processing time, strange.
> > > > > > >
> > > > > > > DL
> > > > > > >
> > > > > > > "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
 news:<_LTH7.17193$XJ4.11730249_at_news1.sttln1.wa.home.com>...
> > > > > > > > You need to read the documentation on ref cursor. You are
 not
> > in
> > sqlserver.
> > > > > > > > Also if you can avoid distinct in the query - it makes the
 rdbms
 compare
> > > > > > > > every row with every other row - very ineffecient.
> > > > > > > > Jim
> > > > > > > > "DL" <donlcs_at_yahoo.com> wrote in message
> > > > > > > > news:604bb90c.0111120929.5612d6f8_at_posting.google.com...
> > > > > > > > > Create procedure first_Oracle_sp(v_owner IN varchar2, v_tn
 IN
 varchar2)
> > > > > > > > > AS
> > > > > > > > > Begin
> > > > > > > > > select distinct cols.table_name as tblname,c.nullable,
 cols.column_name
> > > > > > > > > from dba_constraints cons, dba_cons_columns cols,
> > dba_tab_columns
> > c
> > > > > > > > > where cons.table_name = cols.table_name
> > > > > > > > > and cons.owner = v_owner
> > > > > > > > > and c.table_name = v_tn
> > > > > > > > > and cols.column_name = c.column_name
> > > > > > > > > and cons.constraint_name = cols.constraint_name
> > > > > > > > > and cons.constraint_type = 'R'
> > > > > > > > > and cons.table_name = v_tn
> > > > > > > > > End;
> > > > > > > > >
> > > > > > > > > Thanks.
Received on Thu Nov 15 2001 - 05:45:19 CET