HDTenEightyP/NetNews huggingface dataset:
From: scott@prism.gatech.EDU (Scott Holt)
Newsgroups: comp.databases.oracle
Subject: SQL*Forms performance problem
Date: 28 Aug 92 21:32:40 GMT
Organization: Georgia Institute of Technology


We have been having some performance problems with a particular SQL*Forms 3.0 
application. The form runs on an RS6000 against a local database. The database
runs at revision 6.0.34.

We have looked at various statistics (bstat/estat, monitor, etc) and haven't
noticed any contention problems. Right now all we know is that one particular
query accounts for 95% of the total execution and elapsed time the application
uses. The query requires a large number of consistent reads.

From what I have read, consistent reads should not bee that expensive. However,
in this application there seems to be a strong tie between high CPU/elapsed
times and number of consistent reads. The nature of the application is such
that multiple users are reading and writing to the table at the same time,
however, the set of rows accessed by each user is disjoint.

Any pointers as to where else we can look to increase performance would be
greatly appreciated.

- Scott 




This is an extract from tkrpof showing the offending query:

SELECT SR_ID,SR_SRVYQUE_ID,SR_SRVYFRM_ID,SR_SRVYTYP_ID,SR_RESPONSE,SR_RESPO
NSE_LONG,SR_VSRVYR_ID,ROWID FROM SURVEY_RESPONSES WHERE sr_srvyque_id = :1 
and sr_srvyfrm_id = :2 and sr_srvytyp_id = :3 order by sr_vsrvyr_id, sr_id 


            count     cpu    elap    phys      cr     cur    rows
Parse:         71      36     141       0       0       0
Execute:       71    4679   45097       0   10823       0       0
Fetch:         71       1       9       0       0       0       0

Execution plan:
SORT (ORDER BY)
  TABLE ACCESS (BY ROWID) OF 'SURVEY_RESPONSES'
    AND-EQUAL
      INDEX (RANGE SCAN) OF 'SRVYRES_SRVYSX_FK' (NON-UNIQUE)
      INDEX (RANGE SCAN) OF 'SRVYRES_SRVYFRM_FK' (NON-UNIQUE)
      INDEX (RANGE SCAN) OF 'SRVYSR_SRVYSX2_FK' (NON-UNIQUE)
-- 
This is my signature. There are many like it, but this one is mine.
Scott Holt                 		Internet: scott@prism.gatech.edu
Georgia Tech 				UUCP: ..!gatech!prism!scott
Office of Information Technology, Technical Services
From: thierry@curlie.UUCP (Thierry Lach   )
Newsgroups: comp.databases.oracle
Subject: Re: SQL*Report ... how to print detail records across
Date: Thu, 03 Sep 92 23:25:00 EDT
Organization: Not An Organization

fragante@unixg.ubc.ca (Gv Fragante) writes:

> I have a master/detail relationship which I have to print in the following
> ways:
> 
> - master records print downwards
> - detail records print across each master record and *must* be on the same li
> 
> For example, I have the following master records and corresponding details
> 
> MASTER          DETAIL
> 
> American Cars   Ford
>                 GM
>                 Chrysler
> 
> Japanese Cars   Honda
>                 Toyota
>                 Mazda
> 
> German Cars     Mercedes
>                 BMW
>                 Audi
> 
> 
> So in my report output, it should look like this:
> 
> American Cars  Ford, GM, Chrysler
> Japanese Cars  Honda, Toyota, Mazda
> German Cars    Mercedes, BMW, Audi
> 
> 
> I have come close to achieveing this but the problem is that the detail recor
> are printed below the master record. This is not quite what I need.
> 
> The reason why I need this output format is because the data will be imported
> into an Excel spreadsheet wherein one Excel row will contain one Oracle maste
> *AND* it's details. BTW, the max number of details in my situation is 6, so I
> not worried about the variable length of the SQL*Report width.
> 
> Any suggestions would be greatly appreciated. Thanks.

Let's take this one step further.  I want to be able to do this...

master    det   det    det    det    det    det
          det   det    det
master    det   det    det

etc.  There are from one to unlimited details per master.  Details
are summarized (if that really matters...).
Newsgroups: comp.databases.oracle
From: kjou@oracle.com (Kathy Jou)
Subject: Re: Declaring sequence number qualifiers in Pro*c
Organization: Oracle Corporation
Date: Wed, 26 Aug 1992 18:48:23 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

In article <BtJF1H.G5s@ibmpcug.co.uk> boxfield@ibmpcug.co.uk (Paul J Snelling) writes:
>>suthemb1@jhuapl.edu (Marvin Suther) writes:
>>>  I am writing a Pro*C application and am trying to access an already existing 
>>> sequence number. However, no matter how I declare the sequence number id, I 
>>> receive an error message  that the sequence id  does not exist. All 
>>> documentation only references the use and not the declaration. Anyone know 
>>> how this is declared?
>>
>
>Perhaps you are trying to access sequence.curval (last used value) before
>accessing sequence.nextval (next available value).
>
>Unfortunately in any session, you must access nextval before curval is
>available to you...
>
>-- 
>Paul Snelling,	BoxField Systems Ltd

Marvin,

Are you getting this error message when you precompile your PRO*C program
or during runtime?  What is the sqlcheck option set to?  SQLCHECK=limited 
does partial checks on the SQL statements, like object existence.  I think 
some older versions of PRO*C had problems distinguishing table and sequence 
names.  Precompile your PRO*C program with sqlcheck=none (the default) if the
error occurs when you precompile.  If it is a runtime error, then make sure
the user connecting to ORACLE in the program has access to that sequence
(if not the owner).  Try it in sqlplus, too.

Here's an example:

----->
EXEC SQL BEGIN DECLARE SECTION;
        int next_sequence;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT sequence_name.nextval into :next_sequence from sys.dual;
printf("The next sequence is %d.\n", next_sequence);
<-----


-- Kathy Jou
-- Oracle Corporation
-- Disclaimer:  Only my opinions are expressed here...
From: rthorstr@doc.bmd.trw.com
Newsgroups: comp.databases.oracle
Subject: Re: version 7
Date: 31 Aug 92 07:17:27 MST

In article <1992Aug29.012028.9273@cs.UAlberta.CA>, ken@cs.UAlberta.CA (Huisman Kenneth M) writes:
> Does anyone know when exactly oracle version 7 comes out for the PC???

ORACLE has announced that version 7 WILL NOT be implemented on the PC.  


> 
> We really need to get some of the .dll's for windows that comes with it,
> and we are hoping to have them by january.  Will it be available
> before that?
> 
> thanks to anyone who knows.
> 
> Ken
> 
> ken@cs.ualberta.ca
>
From: tcox@netcom.com (Thomas Cox)
Newsgroups: comp.databases.oracle
Subject: Re: Hash clustering vs Indexing in ORACLE7
Date: 2 Sep 92 18:04:30 GMT
Organization: Netcom - Online Communication Services (408 241-9760 guest)

ian@tethys.SLAC.Stanford.EDU (Ian A. MacGregor) writes:
>In article <z9hn27.tcox@netcom.com>, tcox@netcom.com (Thomas Cox) writes:
>|> mpd@hermes.dlogics.com writes:

>|> >Anybody used hash clustering in place of indexing?
>|> >Am considering it for a new app, and am wading through zillions of
>|> >pros and cons.

>|> >Thanks in advance...

>|> Why "in place of" -- I was under the impression you can do both.  Though
>|> I have no idea what trade-offs _that_ entails...

>I believe hashing is only available for clusters and not for individual
>tables. 

No.

	Hashing is an optional way of storing table data to improve the
	performance of data retrieval.  To use hashing, a HASH CLUSTER
	is created and a table (or multiple tables) is loaded into the
	cluster.  The rows of a table in a hash cluster are physically
	stored and retrieved according to the results of a hash
	function.  A HASH FUNCTION is used to generate a distribution of
	numeric values, called HASH VALUES, which are based on specific
	cluster key values.  [...]
				-- page 7-74, Oracle7 DBA Guide

So you can hash a single table.

You also mention the use of indexes for enforcing primary key
uniqueness.  You're better off defining a PRIMARY KEY constraint instead
of an index -- indexes for lookup speed; constraints for uniqueness etc.
(my interpretation of what's on p. 7-41, ibid).

Cheers.
	-- Tom
-- 
Tom Cox      DoD #1776   '91 CB 750 Nighthawk   tcox@netcom.netcom.com
 This note is my exercise of my First Amendment right to make a fool of
 myself in public.  I do not speak for anyone but myself.  Ever.
Newsgroups: comp.databases.oracle
From: tcox@netcom.com (Thomas Cox)
Subject: Re: Parallel architectures and Oracle
Date: Tue, 25 Aug 92 19:55:19 GMT
Organization: Netcom - Online Communication Services (408 241-9760 guest)
Keywords: yes

In article <nirad.713977503@newdelphi> nirad@newdelphi.ceu.uq.oz.au (Nirad Sharma) writes:
>Has Oracle v.6/7 been ported to any parallel architectures ?  

Yes:  VAXcluster, HA/6000, Sequent (new box not yet out), nCube, KSR-1, NCR,
and a few others.

Call your local Oracle reps.  Ask about version 6.2 and about the v7 Parallel
Option (or whatever it's called).

>Nirad Sharma  (nirad@ceu.uq.oz.au)			Phone : (+61 7) 365 7575
>Continuing Education Unit				Fax :	(+61 7) 365 7099
>The University of Queensland.  QLD  4072.  AUSTRALIA

Cheers.
	Tom

-- 
Tom Cox      Dod #1776   '91 CB 750 Nighthawk   tcox@netcom.netcom.com
"My opinions are just that:  mine, and opinions."
From: dtb@otto.bf.rmit.oz.au (David Bath)
Newsgroups: comp.databases.oracle
Subject: Re: Freeing disk space
Date: 27 Aug 92 01:17:20 GMT
Organization: RMIT Computer Centre, Melbourne Australia.

howard@monitor.com (Howard Brenner) writes:

>When you issues a drop or delete command in oracle (then a commit) does  
>oracle free up the disk space to be reused by someone else or does it just  
>move on to the next block it thinks has been usused?  If it doen't "free"  

Whoa ! You essentially preallocate disk and give it to Oracle to manage
(especially if you are using raw disk).

No disk is given back to the OS when a table is deleted
(Have I understood your comment correctly?)

I you are only talking about dataspace inside an Oracle tablespace,
then that is a different matter and I am not sure of the answer


========================================================================
David T. Bath
email: 	dtb@otto.bf.rmit.oz.au
Work: 	Global Technology Corporation, Carlton, Vic, OZ  (03) 3477511
=======================================================================
Newsgroups: comp.databases.oracle
From: tcox@netcom.com (Thomas Cox)
Subject: Re: Hash clustering vs Indexing in ORACLE7
Date: Tue, 01 Sep 92 01:07:34 GMT
Organization: Netcom - Online Communication Services (408 241-9760 guest)

mpd@hermes.dlogics.com writes:
>Anybody used hash clustering in place of indexing?
>Am considering it for a new app, and am wading through zillions of
>pros and cons.

>Thanks in advance...

Why "in place of" -- I was under the impression you can do both.  Though
I have no idea what trade-offs _that_ entails...

Cheers.
	-- Tom
-- 
Tom Cox      DoD #1776   '91 CB 750 Nighthawk   tcox@netcom.netcom.com
My opinions!  Mine!  Not those of Netcom, or Oracle, or anybody else I know...
From: pveltkam@ems.cdc.com (Paula Veltkamp)
Newsgroups: comp.databases.oracle
Subject: Re: Can Dynamic SQL handle NUMBER datatype without precision and scale.
Date: 27 Aug 92 18:26:11 GMT
Reply-To: pveltkam@ems.cdc.com
Organization: Empros Systems International, a division of CDC

Hi,

I've written a PRO*C program which uses dynamic sql. The purpose of the program
is to export data out of the database using selects created from existing .ctl
files.  I'm using ORACLE version 6.0.31. and PRO*C version 1.3.

Apparently, dynamic sql can't handle NUMBER datatypes without a precision
and scale defined.  The datatype value (sdp->T[i]) comes back as a CHAR 
value and the Length (sdp->L[i]) is set to 0.
I've gotten around the problem of having the program crash when
it tries to allocate memory for the sdp->V[i] value by setting the sdp->L[i]
value to the character byte size. However, I get no data back.

Anybody know I can retrieve the data?

Paula
From: sinha@watson.bms.com (Anil Sinha)
Newsgroups: comp.databases.oracle
Subject: Customization of SQL*Forms.
Keywords: SQL*Forms, Oracle CASE
Date: 26 Aug 92 21:11:00 GMT
Organization: X
News-Software: VAX/VMS VNEWS 1.4-b1


	Many times there is a need to customize SQL*Forms forms generated by 
the Oracle CASE tools. Many sites have written awk scripts ( in unix o/s) or 
other utilities to modify the ".inp" file. These utilities help to automate 
the customization procedure. Now the dreaded word going around is that Oracle
may get rid of .inp files for SQL*Forms 4.0. (????)

	A method to avoid modifying .inp file would be to generate the 
customization file which would be input of keystrokes to  the forms designer
using option -r. Has anyone taken this route ? Is it worthwhile ? Your input 
is appreciated. Any other alternative. 

	I will post the summary.

Thanks in advance.


Anil Sinha
internet:sinha@watson.bms.com
From: mmcgreg@uswnvg.com (Mark McGregor)
Newsgroups: comp.databases.oracle
Subject: Re: How to print something in PL/SQL
Date: 4 Sep 92 18:08:26 GMT
Organization: U S WEST NewVector Group, Inc.
X-Newsreader: Tin 1.1 PL5

I'm sure others will give the same answer, but you are basically SOL.

Oracle, in their infinite wisdom, has deemed it appropriate that PL/SQL
have no native print utilities.  A rather hideous means of "printing"
is to write results to a table and then print them out with a select
statement following the PL/SQL script.  But this is horrible (and costly
in terms of extra database access) alternative to a print statement.

I personally wish they would provide such things (with warnings on usage
as appropriate) and let us worry about the consequences.  Assume we know
what we want and need to do.  What an amazing concept.

Mark McGregor (Another less than satisfied user of PL/SQL print commands)

p.s.  One would hope that Oracle fixes this "feature" at some point.
From: twood@windward (Tim Woodward)
Newsgroups: comp.databases.oracle
Subject: Re: Oracle Precompilers and Nulls
Keywords: Precompiler Null
Date: 4 Sep 92 14:32:46 GMT
Distribution: usa
Organization: U S WEST Information Technologies

In article <Bu0zGv.941@mentor.cc.purdue.edu> coghawks@mentor.cc.purdue.edu (john) writes:
>I recently happened into a problem using Pro-C with embedded SQL with
>Nulls and comparison differences between = and IS (NOT) NULL.
>It was when I had built a cursor which selected into a host variable,
>which was then used as part of a selection criteria on the next
>SQL statement. What occured was that when using the where clause
>for ex. WHERE EMPD.DESCRIPT = :host_emp, if either value was null
>then this never would return true or if they both were null
>it still wouldn't be true.  So either I had to have 2 versions and check
>explicitly if and when things were NULL before I use them in a WHERE 
>clause,  or set them to a default value.  Which in oracle is syntactily
>supported, but not implemented when declaring a table.  Another problem
>with setting things to a default value is then building indexes etc..
>those values are all grouped.  I am new at this and am looking forward
>to any replies.
>
>Thank you
>John Hawkins.
>

	Use indicator variables to test for NULLS. 
	Ex: WHERE (EMPD.DESCRIPT = :host_emp) OR ((EMPD.DESCRIPT is NULL)
		AND (:host_emp:ind_host_emp IS NULL));
		

 See Testing for NULLS in the MANUAL. 


 Tim Woodward
Newsgroups: comp.databases.oracle
From: kjou@us.oracle.com (Kathy Jou)
Subject: Re: Oracle7 userids
Organization: Oracle Corporation, Redwood Shores CA 94065
Date: Wed, 2 Sep 1992 17:12:36 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

In article <1992Sep1.161828.337@hhcs.gov.au> pihlab@hhcs.gov.au writes:
>
>Under Oracle6 when you revoke all access for a user there is no easy
>way of actually removing them completely from the database ie they still
>appear in the DBA_USERS table as a valid but inactive user.
>
>Has this changed under Oracle7 ?
>

You can really get rid of a user with:

	DROP USER scott;
  or
	DROP USER scott cascade;	/* drop user and all objects */


-- Kathy Jou
-- Oracle Corp
-- Disclaimer: just my own opinion, not Oracle's.
Newsgroups: comp.databases.oracle
From: wkaufman@us.oracle.com (William Kaufman)
Subject: Re: Customization of SQL*Forms.
Keywords: SQL*Forms, Oracle CASE
Organization: Oracle Corporation, Redwood Shores CA
Date: Thu, 27 Aug 1992 20:09:23 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

    I should say right now that I'm not with the SQL*Forms group, so I
don't know exactly what their plans are.  If any of the Forms folks read
this group, please follow up with the Low Down.

In article <!-cn0-c.tcox@netcom.com> tcox@netcom.com (Thomas Cox) writes:
] >Now the dreaded word going around is that Oracle
] >may get rid of .inp files for SQL*Forms 4.0. (????)
] 
] 	I have a hard time believing that!

    I don't know if the .inp files are going away, but, *if* they do,
they will probably be replaced with Oracle resource files.  Resource
files can be converted into text files and back again at any time (with
"resprint" and "resparse", respectively).  If they're using resource
files now, you can always resprint them, edit them, and resparse them
back.

] 	I *have* heard that "fields" are going away, but .inp files?
] 	Can anybody confirm this .inp file rumor?  

    Not precisely,...

] 	(Yeah, "fields" are being replaced with "items", I think, as
] 	in "radio button item", "checkbox item", etc.  Something like
] 	that.) 

    More accurately, instead of getting all your information through
fields, you get it through any of several types of "items".  One type of
item is a field, which works as you remember it; but you can also
represent your data as a group of radio buttons (if a database field can
be only one of a list), or a checkbox (if it's just "true" or "false"),
etc.,...

                                           -- Bill K.

Bill Kaufman               | " While not a master of intellect, the blatantly
wkaufman@us.oracle.com     |   obvious things *WE* take for granted never
                           |   escape *HIS* keen eye! "          -- Bob Burden
Newsgroups: comp.databases.oracle
From: howard@monitor.com (Howard Brenner)
Subject: Freeing disk space
Reply-To: howard@monitor.com (Howard Brenner)
Organization: Monitor Company / IE
Date: Tue, 25 Aug 1992 21:04:26 GMT

When you issues a drop or delete command in oracle (then a commit) does  
oracle free up the disk space to be reused by someone else or does it just  
move on to the next block it thinks has been usused?  If it doen't "free"  
the disk is there a way i can tell oracle that I'm really done with it and  
let it reuse the space.  I seem to notice that when I create some very  
large tablespaces after a lot of drops, creates and deletes i run out of  
tablespace!

thanks!
From: sjs@netcom.com (Stephen Schow)
Newsgroups: comp.databases.oracle
Subject: Re: Customization of SQL*Forms.
Date: 26 Aug 92 23:48:50 GMT
Organization: Netcom - Online Communication Services  (408 241-9760 guest)

What is all this talk about no more INP files?  If they eliminate INP files,
they better be providing a different text interface.  Otherwise they might
as well be chopping our fingers off.  INP files are extremely useful for
doing automatic things.  I can write awk scripts that manipulate them, generate
them, copy them with global substitutions, etc...  More often than not, copying
an object from one INP file to another is much easier than using the copy
utilitiy in forms3.0.  I can quickly copy an entire form and than do a global
search and replace to create a similar-yet-different new form.

This list goes on.

I know that Oracle does not support the editing of INP files, but that does
not take away from their usefullness.

Another great example I just thought of if the use of RCS.  I usually check
my INP files into RCS and therefore have complete revision tracking and 
control.  Not only that, but I can have the current revision value 
automatically appear on the form through creative INP usage.

I hope that the right people read this news group and realize that everyone
I have ever talked to about Oracle on the net does stuff with the INP file
directly and they are probably making a huge mistake by assuming that
developers don't want to know about it...unless this is some sort of ploy
to get us to buy their CASE tools, which many sites can't afford anyway.

COMMENTS ANYONE?


-- 
------------------------------------------------------------------
Steve Schow    | But you don't have to use the claw, if you
sjs@netcom.com | pick the pear with the big paw paw......
               | Have I given you a clue......?
               |                       - Baloo the Bear
------------------------------------------------------------------
From: carl.pedersen@dartmouth.edu (L. Carl Pedersen)
Newsgroups: comp.databases.oracle
Subject: Re: SQL*plus -- limiting maximum number of returned rows?
Date: 31 Aug 92 22:26:50 GMT
Followup-To: comp.databases.oracle
Organization: Dartmouth College

In article <BtuK40.8By@ef2007.efhd.ford.com>, wwm@ef2007.efhd.ford.com
(William Meahan) wrote:
> 
> In SQL*Plus (not embedded SQL!) is there any way of limiting the number
> of rows returned by a query?
> 
> I am working on a report that is supposed to find the "top 5" items in 
> a catagory.  The query returns 0 or more rows based on the selection
> criteria, sorted in the appropriate order.  Some queries return a dozen
> or more rows, but I'm only interested in the first 5.

In some cases, checking for ROWNUM <= 5 may work, but in general you can do
something like:

    select *
      from t a
     where 5 >=
           (select count(*) from t b
             where b.value >= a.value );

This works if value is unique.  If it isn't, you can concatenate ROWID to
it.

I make no guarantee that this will perform well, but the basic technique
works.