Thanks for the question, mo.

Asked: December 01, 2002 - 11:25 am UTC

Last updated: March 23, 2011 - 3:34 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom:

1. If you have a web application that is performing well when accessed by one borwser, how would you do testing in a real production mode with thousands of users using it?

2. If it slows down sometimes, how would you know if it is from the Network, Netscape, application, or server box? It seems Netscape 4.7 is slower than IE 6.0?

3. What is your criteria on when to use MTS (Shared server configuration). Is it when you have more than 1000 users? It seems in your book you always recommend to use dedicated server mode.

4. Since the client here is the application server, is it when everytime a user uses it using the browser, the app server creating a connection to the database even though it is the same userid/password?
How is the connection (server process) get closed or deleted? is it when the user closes his browser? would the app server(mod_plsql) have a maximum number of browser requests or database connections it can handle.

Thank you,

and Tom said...

1) I have a custom "grab" and "hurl" program that flings URLs at the web server (grab works like a proxy server, records a session, hurl -- well, you can imagine by the name what it does).

You could either build your own infrastructure or buy one -- like mecury loadrunner. there are other public domain web "flingers" out there as well you can research (on your own)

2) netscape 4.7 has issues with tables in tables in tables in tables. Netscape 4.7 is older then dirt. Netscape 6 and 7 are faster then IE in my experience and Mozilla is better then the lot (I love that I never see ads anymore, never have annoying pop-ups, no more animated silly gifs -- all thanks to mozilla. My favorite feature "block images from this server" -- ads begone!)

read
</code> http://docs.oracle.com/cd/A97329_03/core.902/a95102/toc.htm

instruementing your code helps. For example, if you see a URL on asktom with NO in it, change it to YES and see what happens. Goto the asktom home page, click on the first article there. You'll see timings down the side of the page -- very very helpful (mandatory in fact) for finding out what part of a page is causing a slowdown (if the PAGE is the bottleneck)

auditing helps -- eg: all of my pages on asktom are measured and every request is an audit trail entry in a nice little log. I can tell you anything I need - how many hits (page views - minus images and such), hits/second, hits/whatever, how many distinct users, how many sessions, and most importantly perhaps -- how long each and every page took to generate in the database. I can tell you for example that in the last couple of hours (this was a holiday weekend) that:

I had 4,277 pages views.
from 584 ip addresses
representing 694 users (firewalls/nat make some users look the "same")
they hit an average of 6-7pages
they hit 21 distinct page types
the longest it took to generate a page was 6.71 seconds (that was me, running this report)
the fastest was 0.11
the average was in the 0.27 range

Now, say asktom was having a performance issue, I can tell you from that simple report that:

a) it wasn't the database, the pages were returning from the db as fast as expected.

b) if it WAS the database (say the max response time was thru the roof or the average krept up to 1 second or something), I have a detailed log of how long each page was taking -- I could ISOLATE quickly the page in question and then I could drill down on that with the NO-YES parameter to see if it was a specific query on that page.

This all assumes you thought about these things <b>before you set down to develop your application of course!!!</b> Designing for performance and tuning is something that you need to build into the code from day one (read chapter 10 of my book "Expert one on one Oracle" for more details on this philosophy.

there are those that will say "but tom, that stuff you have in your pages -- the NO/YES trick, that is overhead". Yes, you know what -- they are right. But there are two types of overhead in this world -- the type you don't need and can get rid of and the type that if you don't have, you are totally lost. Guess which category I put this stuff into!

Now, if you add into this mix the Web Cache

http://docs.oracle.com/cd/A97329_03/caching.htm


you can start looking at network response times and bottlenecks as well - how long the database takes to generate a page is one thing, how long the app server is taking to dish it out is another -- and how long the end user is taking to get the pages -- these are things a man in the middle like the web cache can help you with (and it'll cache things to boot).


3) It seems in my book -- in the architecture chapter -- I outlined when to use MTS.  When you physically cannot run dedicated server anymore because you are out of resources.  Plain and simple.  You use MTS when you need to get the N'th user logged into the database and performance goes down the tubes due to too many OS processes from dedicated server (or when that nth user fails cause you've exceeded some OS limit).

Re-read the architecture write up I have on that.  There is even a little "graph" for that -- when you hit the point of dimishing marginal returns.

it may be 100 users
it may be 1000 users
it may be 5000 users
it may be 423 users

In short, it is not a function of users.

4) quote

"Since the client here is the application server, is it when everytime a user 
uses it using the browser, the app server creating a connection to the database 
even though it is the same userid/password?"

response "parse error -- I don't understand"

I think you are asking about connection pooling at the app server level.
see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6273432647961 <code>

Mod_plsql will reuse a connection -- so, if a connection already exists in the pool maintained at the app server, no new connection. mod_plsql might have to create a new connection if none exist.

connection pooling is available for pretty much every language out there that you can use in the middle tier. with plsql it "just happens", with java you use a jdbc connection pool.

Rating

(65 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

web testing

mo, December 15, 2002 - 1:14 pm UTC

Tom:

Thanks for this info. I always learn from the sea of info you have better than any other source. however as a followup:

1. I never heard of "hurl" and "grab" or flingers as you call them. However I am curious how do those really fill up a html form and all save it at the same time. Would they replicate exactly an environment where 200 users log in, fill up a form and try to save at same time.

2. You say you have a custom program for doing this. Is this available anywhere?

3. How do I instrument Pl/sql code. any example? I do not see timing when I click on a link on your page like you said? Do you insert things in pl/sql that show up on status bar whilerunning code?

4. You showed me a neat auditing program results I like to do the same. Is this in pl/sql? is it posted anywhere? do you save it in db?

5. Are you saying that for monitoring there are three parts: database, app server, and network. to monitor the last two you have to build utility in the web cache?

6. How do i know how many connections are available between app server and database for users. Is there a formula like for every 100 users create 10 connections or it really depends on usage too?

7. Are you saying that difference between mod-plsql and servelts that in java you have better control on the length of these connections and you can create/shut while in oracle it is pre-configured?

Thank you,

December 15, 2002 - 1:52 pm UTC

1) hurl and grab, as I said, are things I wrote myself.

you don't "fill up an HTML form", you send an HTTP request. You can write these sorts of programs only if you understand the HTTP protocol itself and how is all really works.

Yes, you can use this to replicate such a situation.

You might be amazed at what pops up on google in response to a simple search like:

web stress testing tool

2) nope.

3) you change the URL to have YES where it currently says NO - then you do see timings.

instruementing code just means putting STUFF in it - dbms_output, utl_file, conditional htp.p's (like i have here). See "Beginning Oracle Programming" -- the other book on my home page. In there, we describe and build a package we call "debug" that is a nice layer on top of utl_file for instumenting your code with trace stuff

4) we have a table:

flows@OSI> desc WWV_FLOW_ACTIVITY_LOG1$
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TIME_STAMP DATE
COMPONENT_TYPE VARCHAR2(255)
COMPONENT_NAME VARCHAR2(255)
COMPONENT_ATTRIBUTE VARCHAR2(4000)
INFORMATION VARCHAR2(4000)
ELAP NUMBER
NUM_ROWS NUMBER
USERID VARCHAR2(255)
IP_ADDRESS VARCHAR2(4000)
USER_AGENT VARCHAR2(4000)
FLOW_ID NUMBER
STEP_ID NUMBER
SESSION_ID NUMBER
SECURITY_GROUP_ID NOT NULL NUMBER

every page that runs inserts a row into this table. That is my audit trail. Then, you just write SQL on it.

5) the web cache has the functionality built INTO it already.

6) as with everything in this world -- it depends on your conditions. do you have long think times? then the ratio of USERS/CONNECTIONS is large. do you have very short think times? then the ratio of users/connections gets smaller -- goes towards 1

7) well, there are other OBVIOUS differences -- servlets = java, mod_plsql - plsql. servlets = run in middle tier, mod_plsql = run in database.

But yes, servlets = I consume resources on your app server even while I'm not doing any work (while I'm looking at a page). mod_plsql = I consume resources on your server ONLY when I'm asking you to get me a page.

web testing

mo, December 15, 2002 - 5:38 pm UTC

Tom:

1. as a followup to answer 4, have you ever explained on this web site or any other book what values you insert into the audit trail and where get them? from Are you basically grabbing result of timing in seconds and saving it? would this be an overhead or it happens in milliseconds? DO i have to go to every procedure/page and insert this or I can implement it in after-procedure-execute trigger?

2. You say mod_plsql has a pool of connections available? So 10 users may use the same connection (scott/tiger) which is defined in a DAD. However let us say you want to control the user acces by database which means each user has a different login. Then you have to login userA with a different connection that a userB. The pool would be meaningless since you do not know who is going to login, and since each requires a different login? This case mean you do not specify parameters in the DAD.

3. You say that using servlets in middle tier you always have a session between browser and app server and session between app server and database? My understanding is HTTP is stateless protocol? You can not maintain a session between browser and app server? AM I WRONG?

4. do you use the debug package in the other book to show the status when downloading the page? Would the other book be useful for intermediate level or your book is enough?

5. When you use beans, jSP or servlet in middle tier do you have to run oracle in MTS mode? or that was only if they are in database?

Thank you,

December 15, 2002 - 7:08 pm UTC

1) i gave you the describe right above...

it is time in hsecs -- dbms_utility.get_time

overhead is something you can and should remove from your code. I do not believe this information, which I make a REQUIRMENT to be overhead. It is like calling "auditing" overhead -- it is not, you either have to audit or you don't. If you do, you do -- period. It isn't overhead, it is a requirement. Calling it overhead would be like saying "isn't that overhead inserting into the ORDERS table whenever someone wants to place an order?"

You have to put it in where YOU see fit. Fortunately for me -- everything runs from a procedure "F" -- very easy to audit.

2) the pool is not meaningless -- it will look for a connection you already own.

3) no, I didn't say that. You need to read about servlets in order to understand how they work. HTTP is stateless. A servlet maintains a state from call to call and the app server hooks you back up with your servlet instance. The servlet may or may not maintain a state with the database -- you decide that when you program it.

4) "show the status when downloading the page" does not compute. The Beginning Oracle Progamming book is the "prequel" to Expert one on one and covers up through 9i

5) I never personally use those technologies... Generally speaking if the middle tier is doing connection pooling -- you don't want to add yet another connection pool (MTS). That is like caching the cache -- overhead to be removed.

web testng

mo, December 21, 2002 - 9:40 pm UTC

Tom:

1. Connection pooling means you have connections available at the app server t oservice the next browser. BUT, the browser user can have a different userid/password than another user. What I am saying is that how do you implement connection pooling since you do not know who is connecting. do you connect him and then disconnect/reconnect with his login.userid?

2. what I mean by the data inserted in your table for audit is what is component type and compnent attribute. how do i know what these are and where do i get the values of these from?

3. do you know of any articles on problems with NEtscape 4.7 since you said it has some issues with tables?
THank you,

December 22, 2002 - 9:07 am UTC

1) it attempts to hook you back up with a connection that is good for you if at all possible.

2) they are meaningless to you -- they are meaningful to me. You audit what you want/need to audit. That's all

3) it is ancient history. the issues are "speed", it is dog slow doing tables in tables in tables.

trigger

mo, January 02, 2003 - 3:56 pm UTC

Tom:

1. Is there a way in oracle to create a trigger after each page creation instead of going to each procedure and insert statements for audit trail. ie. after-execute-of-any-procedure?

2. how do you get the value of the i.p address in oracle?

Thank you,

January 02, 2003 - 4:34 pm UTC

1) no
2) ip address of what
sys_context('userenv','ip_address') for the client
host_name from v$instance

ip

mo, January 02, 2003 - 4:48 pm UTC

Tom:

WHen I do:

SQL> select sys_context('userenv','ip_address') from dual;

I get totally different ip than

c:\WINDOWS\ipconfig

SHould not they be the same for the PC always?

January 02, 2003 - 6:28 pm UTC

select sys_context('usere....) returns the IP address of a client connected over net8 -- where ever the client is -- that is there ip address.

it is not the ip address of the server

tom

mo, January 02, 2003 - 9:06 pm UTC

TOm:

1. Did you mean to say that is not the ip address of the client PC in your second statement?

2. In my web audit trail I want to track the ip address of the client pc/browser user logs from. can I do:

insert into audit_log(user_id,log_date,ip_address) values
(p_user_id,sysdate,sys_context('userenv','ip_address');

or this would give me the ip address for the app server?

Thank you,

January 02, 2003 - 9:26 pm UTC

select sys_context('userenv', 'ip_address' ) from dual

returns the IP address of the "thing" that connected to the database. In MOD_PLSQL, that'll be the ip address of the app server. In client server, that'll be the ip address of the CLIENT's pc.

with mod plsql to get the clients (browser) address, use owa_util.get_cgi_env('remote_addr');

2) use owa_util then.

Curious for elaboration

Scott, January 02, 2003 - 11:42 pm UTC

G'day Tom

I'm curious about the sort of information you store in your activity table (WWV_FLOW_ACTIVITY_LOG1).

Would you be able to elaborate on the sort of information you store in the various fields in this table to gather the statistics you use?

I know it usage and structure of such an activity log table would vary from site to site, but think of this as a sort of case study... I'm interested to know more information.

Thanks

January 03, 2003 - 8:02 am UTC

TIME_STAMP DATE
COMPONENT_TYPE VARCHAR2(255)
COMPONENT_NAME VARCHAR2(255)
COMPONENT_ATTRIBUTE VARCHAR2(4000)
INFORMATION VARCHAR2(4000)
ELAP NUMBER
NUM_ROWS NUMBER
USERID VARCHAR2(255)
IP_ADDRESS VARCHAR2(4000)
USER_AGENT VARCHAR2(4000)
FLOW_ID NUMBER
STEP_ID NUMBER
SESSION_ID NUMBER
SECURITY_GROUP_ID NOT NULL NUMBER

is the describe of the table.

A sample row might look like:

flows@OSI816> @printtbl8 'select * from WWV_FLOW_ACTIVITY_LOG1$ where rownum = 1'
TIME_STAMP : 27-dec-2002 12:30:39
COMPONENT_TYPE :
COMPONENT_NAME :
COMPONENT_ATTRIBUTE : SEARCH (7)
INFORMATION :
ELAP : .26
NUM_ROWS : 11
USERID : PUBLIC_USER
IP_ADDRESS : xxx.yyy.31.130
USER_AGENT : Mozilla/4.0 (compatible; MSIE 5.01; Windows 98)
FLOW_ID : 4950
STEP_ID : 7
SESSION_ID : 1780458
SECURITY_GROUP_ID : 0
-----------------

PL/SQL procedure successfully completed.

That shows the date/time of the acces. component type/name are optional (I don't use them -- I have a small application -- just 20 or so pages. I might use this if I were a 200 page application to further refine "what page" this is -- you know, like module, action, client_info in v$session set via dbms_application_info.

component attribute is the page -- this record was a search page.

information -- optional extra stuff
elap -- time in seconds to run the page
num_rows -- arbitrary number that means different things for different pages. in this case, 11 was the estimated number of hits
userid -- the user associated with the page, as I don't make people log in, always the same
client ip address -- self explanatory
user_agent -- the browser they used
flow-id = application identifier
step-id = application step
session-id= session id we assigned to this session
security group id = another thing I'm not using

But -- you should just keep whatever you WANT to keep. others might find the REFERER to be important for example.

ip

mo, January 03, 2003 - 4:37 pm UTC

Tom:

1.
select sys_context('userenv', 'ip_address' ) from dua

and x:=owa_util.get_cgi_env('remote_addr');

both gave me the same ip address. I tried that too from two different PCs/browsers. I think it is the app server i.p. not browser? when I do "ipconfig" on my pc I get a totally different number and this is what i am trying to get in pl/sql.

2. Id printtbl8 something you wrote to print table record in for layout? it is pretty neat. Is procedure available anywhere?

3. you printed time with date. I always had to use the to_char(date,'mm/dd/yy hh24:mi:ss') to get that. Is this the same way?

January 03, 2003 - 6:42 pm UTC

remote addr gives you the client (browser) ip address.

do a web page

begin
htp.p( sys_context('userenv','ip_address') );
htp.br;
htp.p( owa_util.get_cgi_env('remote_address' ) );
end;

unless you are RUNNING on the app server -- they will return different values.

2) </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1035431863958 <code>

3) my printtbl routine does an alter session to get the nls_date_format changed.

connection pooling

Scott, January 05, 2003 - 7:46 pm UTC

G'day

Thanks for the info in regards to the activity logging.

As a developer new to mod sql, I am still coming to understand connection pooling.

You said in your summary, your visitors hit an average of 6-7 pages.
I performed a quick test on our development site, and after I logged in, the session ID being used changed on occasion, presumably because of the connection pooling.

How is it you know how many pages a visitor hits if you are using connection pooling?

Thanks.

January 05, 2003 - 7:49 pm UTC

I am not privy to the internal algorithm - not sure how many times they reuse a connection before restarting it. Sorry.

web page timing

mo, January 14, 2003 - 11:32 am UTC

Tom:

I inserted an audit record (dbms_utility.get_time) at the begining of each pl/sql procedure and round ((dbms_utility.get_time-l_start)/100,2) at the end
that generate html reports. Results are as this:

 SQL> /

PAGE                 TIME                 CREATION_DATE
-------------------- -------------------- --------------------
Report 1             4.87 seconds.
Report 1             2.87 seconds.
Report 1             8.26 seconds.        14-jan-2003 11:21:20
Report 1             6.59 seconds.
Report 2             1.8 seconds.
Report 2             2.4 seconds.         14-jan-2003 11:22:12
Report 3             .48 seconds.         14-jan-2003 11:22:24
Report 4             25.9 seconds.
Report 4             24.21 seconds.       14-jan-2003 11:22:52
Report 4             24.78 seconds.

10 rows selected.

1.  Sometimes a page takes 8 seconds to appear while the pl/sql program logs in 4 seconds. How do you explain this?

2.  How do you I get different results for the same report sometimes run the same way?

3.  How do you know if this is the best time you can get a query to perform? For example, the 4th report is a pivot query that has about 8 calculated functions per row. there is about 1000 records?  How would I know if I can get this to run in 10 seconds or better or that this the best you can get it at? is it by trying different things?

THanks

January 14, 2003 - 12:44 pm UTC

1) middle tier, network, browser latency. the db is just a piece of the pie.

2) different user loads, more or less PIO's during one run then the other....

3) you would know because someone said "you have a requirement to run this page this fast". Else, you never know.

I have a theory -- I can make anything go 1-5% faster. However, each 1% takes 10 times as long as the prior 1% and since it is 1% of a SMALLER number, it is less and less worth it.

You have to look at something and say "I'm happy with its performance". Look at the LIO's (logical io's), cpu time and justify that they seem right given the data.

excellent

A reader, March 12, 2003 - 10:16 am UTC

this was excellent and very helpful, especially the initial response.

web app

mo, July 16, 2003 - 5:55 pm UTC

Tom:

1. If I have a web app running on oracle 8i database. It is accessed from browser using mod-plsql. Is it possible to make a copy of this app and install on a laptop for travel and modify the code on the laptop and then send it to the server rather than spend a lot of time over a dial up connection. If yes, what do you need to install on the laptop? oracle 8i enterprise edition and anything else?

2. If I have an instance for a web application which will be a portal for all apps in the future. Now I need to add another small application. Would it be beneficial to have a separate instance or just add 10 tables to the current one. Any disadvantages to that?

Thank you,

July 16, 2003 - 6:16 pm UTC

1) se will do, pe will do. I do it all of the time.

2) an INSTANCE per machine, do NOT create a new database. stuff as much into one as possible

Grab and Hurl

ray, July 18, 2003 - 8:57 am UTC

Is your grab and hurl program available (public).

Would there be any chance I can borrow it.

thanks

July 19, 2003 - 10:41 am UTC

No, i haven't made it public - there are lots of them out there tho, better then grab and hurl (which are so rudimentary as to make 'supporting' them out of the question)

oracle on laptop

mo, July 26, 2003 - 2:44 pm UTC

Tom:

I installed 8i R 8.1.7 on a laptop with XP o/s and everything went smooth.

I did and export on the unix prompt and got a dump of the database anap.dmp. I GZIP the file and copied it over to windows PC and emailed it to the laptop. I unzipped ion windows. When I tried to import the files i saw all kinds of problems.

1. I did
imp userid=u/p full=y
if i want to check all the errors occused do i just add log=logfile.

2. After I did that the tables were there. A lot of proedure erred (dont know why) but I realized I may need to create some database links since code references those which means installing another database. Do I have any other options here for database links?

3. Microsoft gave me a severe error message after the import. IT has some binary addresses? Do you recommend installing 8i on windows 98 or is this an XP bug.

4. Even though I managed to see a lot of procedures in database using SQL Navigator, I could not call them using the DAD I created and accessed using
</code> http://127.0.0.1/pls/anap/myprocedure <code>

after I copy the code and create a text file and compile it using SQL plus then I would see it. What do you think the reason for that?

5. Do you recommend me deleting everything out and re-installing oracle or just re-importing again after creatingg link or other stuff? If yes how do i clean everything the imp did?

Thank you.

July 26, 2003 - 4:20 pm UTC

1) yes.

2) well, if you want the code that references the database links to become valid, you better have the db links present? what else can we do?

3) contact support, that shouldn't happen.

4) my car won't start.. I'm really bummed. Any ideas? really -- what else can i say?

5) I personally recommend:

o fdisk to repartition the entire laptop hard disk to remove windows
o install linux

but that is me. 8i works on XP just fine in general. to "clean" what the imp did is either:

o drop the user(s) that imp imported into
o drop the database and recreate it using dbca if it was a full database
export you were importing

web app

mo, July 26, 2003 - 10:47 pm UTC

Tom:

1. The user I imported to is the default "system/manager". Can you drop this and re-create it?

2. DO you advise of creating a user account by loggin in as system/maanger and then importing to that user in the future.

3. How do i drop database using dbca? is this an oracle provided tool? ANy hints.

4. In order for the links to work I guess I have to create other databases? IS there a workaround other than changing the code not to use links?

5. The option of fidk and using linux is hard because it is company borrowed laptop and not personal?

Thank you very much,

One instance versus multiple

mo, September 05, 2003 - 10:24 am UTC

Tom:

1. Can you tell me why technically you support this:
<an INSTANCE per machine, do NOT create a new database. stuff as much into one as possible>.

2. I have one server with three oracle instances/databases and I use database links between them. Each instance is for a different application e.g (inventory/financial).

Two applications will be migrated to another server. Would this affect the database links in any way or they would still work.

3. Would you be in favor of merging all three applications into one instance/database. Do you know of any article that discusses advantages/disadvantages of have multiple instances/databases for many applications versus one instance/database for all applications.

Thank you,

September 05, 2003 - 4:27 pm UTC

1) it is easier, more managable, consumes less resources, gives me the ability to actually TUNE something.

2) it will not affect the database links, other then they might be slower

3) if they were custom apps (you wrote them) YES, 100% YES, 110% yes even.

if they are cots apps -- stuff you buy, maybe not -- they have their specific version/configuration setup issues that can preclude this.

There are no advantages to many instances on a single machine, unless you are FORCED into it.

instance

mo, September 08, 2003 - 2:02 pm UTC

Tom:

Thanks.

1. Does oracle have any special tools for merging databases or you would just use export from one database and import into the other?

2. Can you change an instance name or is it easier to create a new one and do exp/imp?

September 08, 2003 - 2:16 pm UTC

1) there are many methods

o streams
o replication
o sqlplus copy
o dump and load (sqlldr)
o database links and sql
o export and import

to name a few.

2) the instance name is just a sid really in single instance oracle. very easy to change.

just export a new sid (i'm talking unix, windoze -- read the windows admin guide to figure out how to create a new service and so on with oradim)...

rename your init.ora
mv your $ORACLE_HOME/admin/$ORACLE_SID directory to the new sid

and pretty much start up.

9iAS

mo, November 23, 2003 - 8:06 am UTC

Tom:

I started using 9iAS with 9i database and foiund the following:

1. app server does not display error messages (just 404 error) or parameters submitted if you have an error on the next page displayed like 8iAS. How do you configure 9iAS to display parameters and error messages?

2. I created two procedures and had one page submitting to the other. When I submit the first one I keep getting the "page not found" message even though the procedure is there. Do you know what could be happening here?

Thank you,

November 23, 2003 - 9:11 am UTC

by default, mod_plsql will return 404 not found when there is an error -- rather then displaying tons of information that your typical end user should NOT see (it is a security thing here, to supress the messages like that)

check out support note:

Article-ID: <Note:179530.1>
Alias: SOL:OSS147396
Circulation: PUBLISHED (EXTERNAL)
Folder: internet.Applicationserver.HTTPserver
Topic: PL/SQL Gateway (mod_plsql)
Title: How to Get More Diagnostic Information From ModPLSQL In
Case of HTTP-errors

in short.....

The reporting mode from the PL/SQL gateway is defined by the Error_Style
directive in the configuration file wdbsvr.app.

This parameter can be specified at the DAD level or at the Global level and
must be changed by manually editing the file. This parameter accepts the
following values:

1. error_style = WebServer (default for mod_plsql)

PL/SQL Gateway indicates to the Web Server what HTTP error was encountered.
The Web Server then generates the error page. This can be used with
Apache's ErrorDocument directive to produce customized error messages.

2. error_style = Gateway

The PL/SQL Gateway generates the error pages, usually a short message
indicating the PL/SQL error that was encountered (e.g. "scott.foo
PROCEDURE NOT FOUND").

3. error_style = GatewayDebug

The PL/SQL Gateway generates information specified by the "Gateway&
quot;
setting and also produces server configuration information. This mode is
for debugging purposes only. Do not use in a production system since
displaying internal server variables could produce a security risk.

Note: If the PL/SQL Application generates its own error page, then the
error_style setting is ignored and the page generated by the application is
displayed.

To customise the error page returned with the setting of WebServer,
edit the plsql.conf file located in the 9iAS ORACLE_HOME/Apache/modplsql/cfg
directory and add the following declaration between the <LOCATION /pls>
and </LOCATION> tags:

9iAS Errorrs

mo, November 24, 2003 - 3:03 pm UTC

Tom:

DBA did this and he thinks wdbsvr.app was part of Oracle 9iAS Release 1. It did not affect anything.

1. Is there a dfferent file for 9iR2?

2. The article you posted seems to be cut off. How do I get to this article in support? Is there a web site for this?

Thank you,

November 24, 2003 - 3:26 pm UTC

metalink.oracle.com

9iAS Errors

Mark A. Williams, November 24, 2003 - 5:25 pm UTC

Munzer,

1 - the file is called dads.conf under 9iAS R2 and is located at oracle_home\Apache\modplsql\conf\dads.conf

The parameter error_style is now called PlsqlErrorStyle. Valid values are: ApacheStyle, ModplsqlStyle, and DebugStyle.

2 - Login to MetaLink, click the advanced button, enter the note id from above (179530.1) in the text box field, click the 'Doc ID' radio button, then click the 'Search' button.

HTH,

Mark

web testing

mo, June 17, 2004 - 6:51 pm UTC

Tom:

I have an oracle web application. I was using it using a 9IAS installed on a secured server "A" with the database. I was asked to use a different 9IAS installed on machine "B" and it is a public one. There is a DAD on machine B pointing to the database on "A".

I noticed while testing that when I hit the "back" button I get a lot of "Warning: Page Expired" messages and hit refresh. Also, when a user pressed "Save" and I get the confirmation page for "Record Saved", the back end show that two duplicate records were saved.

Is this coming as a result of a Web server setting or it is coming from Internet Explorer setting. I have not changed my settings and when I go back using the first one it does not show up. It must be something on the 9IAS.

DO you have any hints?
Can you print the 9IAS setting (Environment variable values) from the web?

Thank you,

June 17, 2004 - 6:52 pm UTC

it cannot have anything to do with ias or the fact there are 2 servers.

it is all about the web browser...

web testing

mo, June 17, 2004 - 10:54 pm UTC

Tom:

If it is the web browser issue the app should behave the same way regardless of which 9IAS I used to access the database?

Are you aware of a browser setting that make pages expire?

June 18, 2004 - 10:21 am UTC

Look -- for a web request to be made twice, well, you have to have the browser do it twice.

for a page to say "sorry, I've expired", well, you have to have the browser expiring stuff in its cache.

no other way really.

web testing

mo, June 18, 2004 - 3:20 pm UTC

Tom:

The "Page Expire" issue turned out as a result of no space the temporary internet folder. I cleaned that and it is gone.

The other issue of "Duplicate record" is really weird. A user fills out a form and submits it to a procedure "form handler" where i have one insert statement. I do not hit refresh too afterwards. What do you think maybe causing this? Can it be any setting on the 9 IAS web server.

Thank you,

June 18, 2004 - 4:29 pm UTC

no, it cannot be any setting on the webserver.

add some debug and auditing information to your routine. gather things like the "referer" and other environment variables, log them.

the only way for this to happen is "browser sends requestion twice"

debugging

mo, June 18, 2004 - 5:30 pm UTC

Tom:

Can you explain more about "referer" and other variables that help in finding the problem. I did add "print variable for the sequence" and it comes out skipped by one. If the procedure is getting run twice that is not going to help me find out the problem.

Also, how can a browser submit a page twice if you have one <form action="xxx" method="post"> and one submit button.

June 18, 2004 - 6:11 pm UTC

owa_util.print_cgi_env

a browser does what an end user tells it to.. "back" and "forward", "reload". that it how (don't believe the end user if they say "i didn't do that", end users, like eye witness's -- are very unreliable).

tell you what, set a cookie when you build that page.
unset that cookie in the page that it submits to.
have that page it submits to verify cookie is set first.

no cookie -- no joy, no joy -- no insert.

or at least put a primary key on something, I use primary keys and never have "duplicates".

debugging

mo, June 18, 2004 - 6:30 pm UTC

Tom:

I will try that.

However I do have a primary key (Application_ID). It is a sequence number. What is happening is that it is inserting two exact records with different consecutive sequence number.

June 19, 2004 - 7:51 am UTC

assign the sequence value on the page that BUILDS the page that is calling this procedure twice. put it on there as a hidden field.

In that fashion, when this page submits -- it'll tell you "here is the id". Two of the same ids will therefore fail and you'll find it straight away.

testing

mo, June 21, 2004 - 11:39 am UTC

June 21, 2004 - 1:59 pm UTC

it has nothing to do with the "port". ports are end points of tcp/ip connections.

this is nothing to do with ports.

9IAS testing

mo, June 21, 2004 - 12:41 pm UTC

Tom:

Here is th 9ias config:

>>[Ports]
>>Oracle HTTP Server port = 7777
>>Oracle HTTP Server listen port = 7778
>>Oracle HTTP Server SSL port = 4443
>>Oracle HTTP Server SSL listen port = 4444
>>Oracle9iAS Clickstream Collector Agent = 6667
>>Oracle HTTP Server Jserv port = 8009
>>Oracle Intelligent Agent = 1748, 1754, 1808, 1809
>>Java Object Cache port = 7000
>>Oracle Notification Service request port = 6003

DAD config
<Location /pls/hurd>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername huradmin
PlsqlDatabasePassword !bmxzYWRtaW4=
PlsqlDatabaseConnectString <server2>:1521:HURD PlsqlAuthenticationMode Basic PlsqlErrorStyle DebugStyle PlsqlExclusionList 'sys.* PlsqlExclusionList dbms_* PlsqlExclusionList owa_util.showsource'
</Location>

>>Oracle Notification Service local port = 6101
>>Oracle Notification Service remote port = 6201
>>Enterprise Manager Repository Port = 1521
>>Enterprise Manager Servlet Port = 1810
>>Oracle9iAS Web Cache HTTP Listen(non-SSL) Port = 7777
>>Oracle9iAS Web Cache HTTP Listen(SSL) Port = 4443
>>Oracle9iAS Web Cache Admin Port = 4000
>>Oracle9iAS Web Cache Invalidation Port = 4001
>>Oracle9iAS Web Cache Statistics Port = 4002

It seems the application creates duplicates when it starts with 7778. if you start with 7777 it does not.

web testing

mo, June 21, 2004 - 4:44 pm UTC

Tom:

Then how do you explain a second request going to the database. There is only one insert statement in the code, and there is no refresh in the code. Application works fine using the 9IAS running on the old machine. Duplicates start occuring when we switch to use the 9IAS on the second machine. It has to be something at the second 9IAS when it tries to connect with the database on first machine.

June 21, 2004 - 8:45 pm UTC

I cannot, other then the very basic fact that the app server (web server, whatever) only responds to url requests - nothing more, nothing less.

obviously there is something wrong, but it isn't about the app server.

web testing

mo, June 22, 2004 - 4:53 pm UTC

Tom:

Do you think this maybe the problem: I noticed that button type = "submit" instead of "button" since we are submitting via a javascript. When I changed that it did not create duplicates (at least for now). I just can't explain why the same thing was not happening with the other 9IAS.

htp.print('<input type="submit" tabindex="29" name="button" value="Save"
onClick=" return doSave()">');

HTP.P('function doSave(){ ');

HTP.P(' if (document.hr_form.i_app_date.value.length < 1) ');
htp.p(' { window.alert ("Please Enter the Date"); ');
htp.p(' document.hr_form.i_app_date.focus(); ');
HTP.P(' return false; } ');
HTP.P(' else { ');
htp.p(' document.hr_form.action = "HR_APPLICATION.save_app" ');
HTP.P(' document.hr_form.submit();');
HTP.P(' } ');
HTP.P(' }

June 22, 2004 - 10:19 pm UTC

way beyond where I go. I've been fortunate enough to never have to write in javascript.

Your javascript is buggy....

Tom, June 23, 2004 - 8:19 am UTC

The javascript in conjunction with the button type is the problem. A submit button will always submit the form unless the onclick handler returns false. This means that when the button is clicked, your function is called and that submits the form [via document.formname.submit()]. The form is then submitted a second time by the submit handler since the javascript did not return false.

The standard way to do this in html is either

<script language="javascript">
function validateForm(){
if( <test condition goes here> ){
alert('<error message goes here>');
return false;
}
}
</script>
<form name="myform" action="myhandler" onsubmit="return validateForm()">
...
form content here
...
<button type="submit">submit</button>
</form>
or
--

<script language="javascript">
function validateAndSubmitForm(){
//test the conditions
if( <test condition goes here> ){
alert('<error message goes here>');
return false;
}
//and submit if it passed them
document.myform.submit();
}
</script>
<form name="myform" action="myhandler">
...
form content here
...
<button type="button" onclick="validateAndSubmitForm()">submit</button>
</form>

Essentially your approach is a mix of the two...hence the double submit. Hope this helps

June 23, 2004 - 9:34 am UTC

excellent, thanks so much!

web testing

mo, June 23, 2004 - 9:40 am UTC

Tom:

Thanks, you confirmed my doubts. What was puzzling me is that it would not happen all the time.

The "randomness" of it is a timing issue

Tom, June 24, 2004 - 11:01 am UTC

Mo,

The reason it does not happen all the time is that sometimes the form is submitted and the page exited before IE gets round to doing the second submit. It all depends on how fast the next page comes back.....if you put a dbms_lock.sleep(10) in the handler procedure you will probably find it happens all the time...though this is turning into asktom.javascript.com! Apologies to the real tom.

June 24, 2004 - 2:28 pm UTC

no worries, it is really cool when the answer comes from elsewhere.

How to test database load

Sean, July 21, 2004 - 8:50 am UTC

Hi Tom,

In terms of tuning application in multiple-user mode, you mentioned using third party tool like Mercury Loadrunner. But during the development of application, I want to test my stored procedure or even insert or update (the frond end portion is not yet ready at that time). For example:
(1) I want to 100 sessions to call one stored procedure at the same time.
(2) I want to 100 sessions to insert record into emp table at the same time.
(3) My question is really how to test database load during the development of application.

Thanks such much for your help.

July 21, 2004 - 9:02 am UTC

well, the answer would/could be the same -- if you have that tool, you can use that.

Otherwise, you are in "do it yourself" land (if you haven't gotten a load testing tool of some sort - there are others out there).

I've used the job queues many times myself -- in 8i, you were limited to 32 sessions, in 9i 1,000.

I'll give you an example, in 10g I was trying to bang on a data structure, to see how it would work. I set up a "producer" thread:

create or replace procedure produce_iot( p_time in number ,
p_trace in number default 0)
as
type numType is table of
number index by binary_integer;
l_ips numType;
l_start date := sysdate;
begin
select distinct ip_address
bulk collect into l_ips
from ip_audit_tab_heap
order by dbms_random.value;
if ( p_trace = 1 )
then
execute immediate q'|
alter session set events
'10046 trace name context forever, level 12'
|';
end if;

for j in 1 .. 9999999
loop
exit when (((sysdate-l_start)*24*60*60) > p_time);
for i in 1 .. l_ips.count
loop
exit when (((sysdate-l_start)*24*60*60) > p_time);
insert into ip_audit_tab_iot
( ip_address, request_time, seq, bytes, url )
values
( l_ips(i), localtimestamp, iot_seq.nextval,
100, rpad('*', 80+mod(j,150), '*' ) );
commit;
end loop;
end loop;
end;
/

it generated new data. I set up a consume thread:

create or replace procedure consume_iot( p_time in number ,
p_trace in number default 0)
as
type numType is table of
number index by binary_integer;
l_ips numType;

cursor c is select rowid rid, a.* from ip_audit_tab_iot a;
l_rec c%rowtype;
l_start date := sysdate;

begin
select distinct ip_address
bulk collect into l_ips
from ip_audit_tab_heap
order by dbms_random.value;
if ( p_trace = 1 )
then
execute immediate q'|
alter session set events
'10046 trace name context forever, level 12'
|';
end if;

loop
exit when (((sysdate-l_start)*24*60*60) > p_time);
for i in 1 .. l_ips.count
loop
exit when (((sysdate-l_start)*24*60*60) > p_time);
begin
select rowid, a.*
into l_rec
from ( select *
from ip_audit_tab_iot
where ip_address = l_ips(i)
order by request_time
) a
where rownum = 1;

delete from ip_audit_tab_iot
where rowid = l_rec.rid;

commit;
exception
when no_data_found then null;
end;
end loop;
end loop;
end;
/

These would be my "jobs". I then ran the simulation:

create or replace procedure simulation
( p_procedure in varchar2, p_time in number, p_trace in number := 0)
authid current_user
as
l_job number;
l_cnt number;
l_parms varchar2(25) := '(' || p_time || ',' || p_trace || ');';

begin
dbms_job.submit( l_job, 'consume_' || p_procedure || l_parms );
dbms_job.submit( l_job, 'produce_' || p_procedure || l_parms );

statspack.snap;
commit;
loop
dbms_lock.sleep(3);
select count(*) into l_cnt
from user_jobs
where what like 'consume%'
or what like 'produce%';
exit when (l_cnt = 0);
end loop;
statspack.snap;
end;
/

If I wanted to load up the system -- i could have passed in the number of consumer jobs to create and the number of producer jobs to create.

oracle web

kattumannar koil senthil kumaran, February 15, 2005 - 9:33 pm UTC

web transit

web portal

mo, April 09, 2005 - 7:33 pm UTC

Tom:

I have 2 oracle web apps and 2 powerbuilder apps with same user. I am thinking of creating a portal page for user authentication and authorization and then let user navigate from one application to the other from the portal page.

1. Can it be done for the powerbuilder apps. Bascially call the client/server app pass the userid/password into powerbuilder and get to the menu using a URL. DO i have to use ACTIVE X components or OLE.

2. Would not this be dependent on what browser you are using. I think Netscape does not support Active X and in IE you have to turn the support on?

3. Can I have a URL on the web page that submits the page to a PL/SQL procedure which calls Powerbuilder application. Can this be done?

Thanks,

April 09, 2005 - 7:34 pm UTC

no ideas, never used it (powerbuilder), no idea what it can and cannot do in this case.

portal

mo, April 10, 2005 - 11:05 am UTC

Tom:

Yes but i am asking about IE/HTML or oracle Procedure and not PB.

From a web page, can I call/run an external app using either
IE/HTML or oracle packages.

It is like you have a URL and you click it and you go to excel application or a URL and you click it and you are in MS Word. Here I want be in PB.

April 10, 2005 - 11:13 am UTC

You asked me all about activex, browsers, support therein.

I've no idea.

PB is a tool, it runs in the middle tier or on the client, you would be best asking Sybase about their tool and how to configure/install/run it.

web app

mo, May 11, 2005 - 7:32 pm UTC

Tom:

Would I be able to export a web application (whole database) from an oracle 8i on IBM aix server to laptop running oracle 9i on XP.

Would it work? I am trying to get a local copy of the system to work on while on travel.

Oracle does not seem to support the 8i anymore and I want to install a 9i database.

Also, would I be able to create two databases on 9i laptopn since I have two instances on the IBM AIX with database links.

Thanks,

May 12, 2005 - 7:27 am UTC

yes, exports are cross platform and backwards compatible.

so 9i can read 8i dmp files (backwards compatible)
and windows (intel) can read aix (risc)

just binary ftp it! else windows will corrupt it.

import

mo, May 13, 2005 - 3:24 pm UTC

Tom:

When i did a full database export and import there was a probkem with creation of tablespaces. the import was trying to run something of /ora817/ and it did not exist.

Can I just do import for tables, rows, procedures/packages/functions, trrigers and seqences from the whole export dump file

Thanks,

May 13, 2005 - 4:32 pm UTC

if you precreate the tablespaces, that won't happen.

you can get just tables/rows
but getting just the other bits won't happen from a dmp file using imp.

export

mo, May 13, 2005 - 6:13 pm UTC

Tom:

Do the tablespaces have to be identical in both databases or do i only need the one where the data is. Does it have to match in size and other specs.

Do you mean I can run one simple create tablespace and everything will work.

May 13, 2005 - 6:43 pm UTC

it needs to be big enough to hold the data and have the same name

other than that, it can differ in every regard.

export

mo, May 13, 2005 - 6:24 pm UTC

Tom:

I did create one tablespace and he is what I got. Any better suggestions.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "PICDATA" DATAFILE '/ora817/dbs/picd/picd2.dbf' SIZE 104"
"8576000 DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXT"
"ENTS 2147483645 PCTINCREASE 0) ONLINE PERMANENT EXTENT MANAGEMENT DICTIONA"
"RY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "TEMP" DATAFILE '/ora817/dbs/picd/picd3.dbf' SIZE 209715"
"200 , '/ora817/dbs/picd/picd5.dbf' SIZE 1048576000 DEFAULT STOR"
"AGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50) ON"
"LINE TEMPORARY EXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "PICRBS" DATAFILE '/ora817/dbs/picd/picd4.dbf' SIZE 3145"
"72800 DEFAULT STORAGE(INITIAL 106496 NEXT 106496 MINEXTENTS 1 MAXEXT"
"ENTS 1024 PCTINCREASE 0) ONLINE PERMANENT EXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:
"CREATE TABLESPACE "DRSYS" DATAFILE '/ora817/dbs/picd/drsys01.dbf' SIZE 419"
"430400 DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTE"
"NTS 505 PCTINCREASE 50) ONLINE PERMANENT EXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00015: following statement failed because the object already exists:
"CREATE USER "OUTLN" IDENTIFIED BY VALUES '4A3BA55E08595C81'"
IMP-00015: following statement failed because the object already exists:
"CREATE USER "DBSNMP" IDENTIFIED BY VALUES 'E066D214D5421CCC'"
IMP-00017: following statement failed with ORACLE error 12911:
"CREATE USER "SQLNAV" IDENTIFIED BY VALUES '85034B5E1B7769A8' DEFAULT TABLES"
"PACE "PICDATA" TEMPORARY TABLESPACE "PICDATA""
IMP-00003: ORACLE error 12911 encountered
ORA-12911: permanent tablespace cannot be temporary tablespace
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "SELECT_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "SELECT_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "EXECUTE_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "EXECUTE_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "DELETE_CATALOG_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "DELETE_CATALOG_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "RECOVERY_CATALOG_OWNER""
IMP-00015: following statement failed because the object already exists:
"REVOKE "RECOVERY_CATALOG_OWNER" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "AQ_ADMINISTRATOR_ROLE""
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "AQ_USER_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "AQ_USER_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "OEM_MONITOR""
IMP-00015: following statement failed because the object already exists:
"REVOKE "OEM_MONITOR" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "HS_ADMIN_ROLE""
IMP-00015: following statement failed because the object already exists:
"REVOKE "HS_ADMIN_ROLE" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "JAVAUSERPRIV""
IMP-00015: following statement failed because the object already exists:
"REVOKE "JAVAUSERPRIV" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "JAVAIDPRIV""
IMP-00015: following statement failed because the object already exists:
"REVOKE "JAVAIDPRIV" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "JAVASYSPRIV""
IMP-00015: following statement failed because the object already exists:
"REVOKE "JAVASYSPRIV" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "JAVADEBUGPRIV""
IMP-00015: following statement failed because the object already exists:
"REVOKE "JAVADEBUGPRIV" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "JAVA_ADMIN""
IMP-00015: following statement failed because the object already exists:
"REVOKE "JAVA_ADMIN" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "JAVA_DEPLOY""
IMP-00015: following statement failed because the object already exists:
"REVOKE "JAVA_DEPLOY" FROM SYSTEM"
IMP-00015: following statement failed because the object already exists:
"CREATE ROLE "CTXAPP""
IMP-00015: following statement failed because the object already exists:
"REVOKE "CTXAPP" FROM SYSTEM"
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UNLIMITED TABLESPACE TO "SQLNAV""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SQLNAV' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "CONNECT" TO "SQLNAV""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SQLNAV' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "RESOURCE" TO "SQLNAV""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SQLNAV' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT "PUBLIC" TO "SQLNAV" WITH ADMIN OPTION"
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SQLNAV' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
"ALTER USER "SQLNAV" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'SQLNAV' does not exist
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE ROLLBACK SEGMENT R03 STORAGE(INITIAL 106496 NEXT 122880 MINEXTENTS 4"
" MAXEXTENTS 1024) TABLESPACE "PICRBS""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE ROLLBACK SEGMENT R01 STORAGE(INITIAL 106496 NEXT 122880 MINEXTENTS 4"
" MAXEXTENTS 1024) TABLESPACE "PICRBS""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE ROLLBACK SEGMENT R02 STORAGE(INITIAL 106496 NEXT 122880 MINEXTENTS 4"
" MAXEXTENTS 1024) TABLESPACE "PICRBS""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE ROLLBACK SEGMENT RBSBIG STORAGE(INITIAL 10485760 NEXT 5242880 MINEXT"
"ENTS 2 MAXEXTENTS 1024) TABLESPACE "PICRBS""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
. importing PICADMIN's objects into PICADMIN
. importing SQLNAV's objects into SQLNAV
"ALTER SESSION SET CURRENT_SCHEMA= "SQLNAV""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully

May 13, 2005 - 6:43 pm UTC

ignore=y

A reader, June 02, 2005 - 8:13 pm UTC

Tom,

v$session shows permanent session stats for a local application. If I have to test web application activity is there an alternative to v$session? I'd like to gather information for sqltrace. I didn't see web application stats in v$session.

June 03, 2005 - 7:00 am UTC

a "web application" is not any different from any other application -- I mean, they use the same api's and everything to talk to the database. Nothing special or magic about them.

Now, a connection pool get get seriously in the way and the developer must help us by instrumenting the code, making it possible to trace it (even in client server that was true to a degree)

A reader, June 03, 2005 - 8:33 am UTC

Tom,

For this web application we need to trace activity for a period of time. There is no instance tracing available. I'll be fine if I can get process information in v$session, otherwise the requests come and go and I wouldn't be able to locate session details. I guess this happened to me earlier. Also, could you tell a little bit about what kind of instrumentation will help here.

June 03, 2005 - 8:45 am UTC

*the developer* needs to enable this in the *application*.

The developer needs to have thought about this, planned for this. As their logical session hip hops over many real sessions in the database, they have to enable/disable this (that is the "instrumentation", they need to have thought about this.

A reader, June 03, 2005 - 9:15 am UTC

Tom,

One last question. Does that mean when the application is properly instrumented, there will be enough log file information from the application. If I have to look at an sql and see its execute/parse ratio etc .. is there any way I can do it here? Again, I do not have any process information available to record sqltrace.

June 03, 2005 - 9:59 am UTC

depends on how the application is instrumented. The developer will have to know what is available, what they can do, and how to do it.

This is definitely a chapter I'm adding to volume II of my rewrite of Expert one on one Oracle -- it is a big topic.

The application can capture *anything* it wants as far as metrics go

A reader, June 03, 2005 - 10:01 am UTC

Tom,

In continuation with the above question, I could somehow trace the application using. I can see the sql in v$sqlarea. However, the sql is not described in the trace file. I am sure I am looking at the latest trace file. Any reason why it's not been recorded in the trace file while trace was enabled for that session? Thanks in advance.

June 03, 2005 - 10:10 am UTC

if trace was enabled
and
session executed that sql
then
it would be in the trace file for that session
end if;

but if sql was parsed
AND THEN
trace was enabled
AND THEN
the sql was bound/executed
then
the sql text itself might not be in the trace file since that happened long ago.
end if

A good free tool for web testing

Rumburak, June 03, 2005 - 10:22 am UTC

is </code> http://jakarta.apache.org/jmeter/ <code>

Bye,

Rumburak

how would you design clickstream data,

sns, October 24, 2005 - 10:11 am UTC

This question is pretty general. When the business is seeing more than 100 million clicks per day, it is very challenging to design the clickstream model in order to make the queries run faster and also effectively using the disks.

Have you seen any clickstream model which you admire?
Could you please share your knowledge in this matter?

Thanks,

October 24, 2005 - 11:47 am UTC

define "clickstream data" - what do you want to do with it.

clickstream cont...

sns, October 24, 2005 - 11:55 am UTC

In our warehouse environment there are more than 100 million clicks per day (lot of people visiting dell.com no wonder).

There are two issues for us now. The storage place is growing bigger and the extraction rate of the queries are running too slow.

The user wants 5 quarters of data which is about 8-9 TB stored in two different tables, but we also have 5 weeks of data in a similar set of tables which is 1-2 TB.

We are thinking of partitioning the data based on visitor's date.

I am just wondering, is there any criteria to be considered in designing such kind of warehouses? And also, any technical input to handle this kind of situation.

Thanks,

October 24, 2005 - 12:06 pm UTC

You could use a smaller partition (day/week) and as they fill up, compress them to cut the size down.

There is insufficient data to come up with "criteria" here - you have a pretty normal situation in the world today - lots of data, more on the way.

I'd be looking at the questions asked of the data before anything else - to see if there is some structure that can facilitate both data retrieval as well as data purge.

You would need to have a grasp on the questions asked of the data.

clickstream cont...

sns, October 24, 2005 - 12:27 pm UTC

I agree your words. I am in the process of getting information about the data and the kind of report the users require.

My fear is, if I get some ideas to improve at the design level, how to implement when there is so much of data already there.

Another question... You said this is pretty normal case. Can Oracle handle this kind of voluminous data? Over here they use teradata and in their latest version they have introduced partitioning concept.

October 24, 2005 - 1:11 pm UTC

how to implement? you would "reorg" the data.

Can Oracle handle this volume? Yes, it does, yes it has, yes it can.

How can I delete a record from the database when the user closes his browser?

Srinivasa Rao, January 07, 2006 - 1:04 am UTC

If the user is go through the Logout button I can delete the record fronm the database by calling session.invalidate() and overridding the session valueUnbound(). But if the user closes the browser I can't able to delete the record from the dayabase.
How can I delete a record from the database when the user closes his browser?

January 07, 2006 - 10:16 am UTC

typically, people would "expire" these records and have a job that runs periodically to clean out "expired sessions"

eg: this record has a timestamp associated with it that is updated each time the application hits the database.

records with old timestamps are "ignored" by the application and eventually cleaned out by a maintenance routine.

http - being the stateless thing that it is - doesn't permit you to know the "browser is gone". You have to time out your sessions.

web server testing

sam, July 31, 2006 - 1:36 pm UTC

Tom:

Do you know of a pl/sql program or another tool that you can use to load/stress test the 9IAS web server performance? I am upgrading several mod_plsql applications from 8i/8IAS to 9i/9IAS but I would like to assess the new web server performance before migration.

July 31, 2006 - 2:45 pm UTC

</code> http://www.google.com/search?q=load+testing+tools&start=0&ie=utf-8&oe=utf-8&client=firefox-a&rls=org.mozilla:en-US:official <code>

testing

sam, October 05, 2006 - 6:13 pm UTC

Tom:

Can you explain this. I have two procedures that work fine in 8i. In 9i, when I submit the procedure A to B I get blank page. I created an intentional error in B just to see what is getting passed and I see a lot of junk characters. These are not showing in "View source" in the first page.

save_shipm: PROCEDURE DOESN'T EXIST

DAD name: tttp
PROCEDURE : save_shipm
URL : </code> http://xxx.xxx.xx:7777/pls/tttp/save_shipm <code>
PARAMETERS :
===========
I_REQUEST_ID:
10003

PANISH

4

I_ASSIGNED_TO:
pgor

a

I_CHANGE_FLAG:
N

ð
HUNGARIAN

HUNGARIAN
UNGARIAN
GARIAN
I_USER_ID:
mike

October 06, 2006 - 8:33 am UTC

laughing out loud.

with information supplied - well, the short answer is....

no.

session expire with Hardware Loadbalancer

MJani, October 08, 2006 - 2:34 pm UTC

Hi,
Info and knowledge provided by you are really extra-ordinary ,thanks for all your efforts !
We have couple of Web-application , till now it was working fine with 9i DB and AS . Recentlly we have changed its configuration with adding one Hardware Load-balancer and one more Machine for AS . Now problem happens during we start the testing of these web-application with new hardware setup .
After the establishing the connection from browser to Server , suddenlly after next hit ,it throwing abrupt Error. After invstigeting we found that ones the Browser is connceted to AS on second hit or next hit it tries to by pass it other AS machine , and at that time session expires ! . Till now I refer the docs and books and it shows that there must be same VIP to which Load-balancer will call ,but in this case it not happening .
Can you please suggest that at this situation , where we should look first ? Or in which situation such porblems arise ?
Thanks

October 08, 2006 - 2:41 pm UTC

not really, I am not at all familar with your environment, the products you have chosen, the software you wrote in the middle tier, its expectations about how sessions will be handled, how you have configured it all, and so much more....

MJani, October 08, 2006 - 3:06 pm UTC

Hi
Thanks for the quick response ! In the middle tier we have oracle 9i AS R2 on Linux , and the for the Load balancer we have one machine ,which passing the request of client to AS ( For AS there are two machines ) as according to Load on them.
Its like LB->(AS1 or AS2)-> DB .
LB :Load balancer (hardware)
AS1 : Application Servers
AS2
DB :Oracle 9i DB R2
Now problem happnes when subsequent request pass through Load-balancer to AS different from previous ,becase at that time its having different URL for same client .Thus session expires .
Actually the whole detail configuration I dont have,becuase the whole set-up done by other people and now we have to whether its due to the application ,or this new set-up.

Thanks

October 08, 2006 - 4:37 pm UTC

(I think you missed my point, no I would not be able to diagose this for you).

It would seem to me that your middle tier doesn't like to be load balanced willy nilly, your session state is not available in both places.

It is obviously this new setup (the cause). Now as to whether you fix it in the application or somewhere else is another story :)

sorry, middle tier and load balancers - not my particular area, they do not respond to "select" see....

A reader, November 30, 2006 - 1:16 pm UTC

Hi Tom,

I am new to Oracle Web Application Programming with PL/SQL.
Can you please point me to some documentation that helps me
how to develop, execute and test stored procs?

Thanks a lot!

November 30, 2006 - 2:02 pm UTC

</code> http://apex.oracle.com/ <code>

use that framework - don't code it by hand.

get sqldeveloper to code and debug plsql procedures (see asktom home page, link to it there)

Alexander the ok, November 30, 2006 - 3:01 pm UTC

I could have sworn there was a rant on your blog about writing instrumented code instead of using GUIs to debug code ;)

You must mean something else here...

A reader, November 30, 2006 - 3:30 pm UTC

Thanks for the response Tom.
But currently we are not using any tool and have to code all the scripts. So I don't know how execute and debug the script. Can you point me some documentation that explains all these?

Thanks a lot!

A reader, December 04, 2006 - 11:51 am UTC

December 04, 2006 - 1:18 pm UTC

you do not know how to debug code?

can you find a programmer to help you then - because you really won't be able to do much yourself since you are not the programmer/developer.

Alexander, June 04, 2008 - 3:53 pm UTC

Tom,

With regard to this

< http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6690299776039#21233226120107 >

I was hoping to use that to test a user load on our system. It's not completely obvious to me what your procs are doing. I see that it's scheduling jobs to run procs but I don't see where the actual work is being done.

Also does the ip address table just have 1 column (number)?

June 05, 2008 - 9:25 am UTC

it "ip_address" table was what I was simulating against - I was comparing data structures, it was NOT part of the test harness, it was what was being TESTED.

Ok, I'll give you a slightly better (eg: newer) one...

I wanted to see what happened when lots of people hard parsed simultaneously - so I needed a way to run N users doing hard parses at the same time...

I started by creating the procedure to do the "work", this is the workload procedure. It needed some inputs - specifically in my case, the number of iterations (times to loop). I always use the same approach with "JOBS", I pass parameters via a table, so to begin:

drop table job_table;
create table job_table ( job number primary key, iters number ) organization index;

create or replace procedure do_sql( p_job in number )
as
    l_cursor sys_refcursor;
    l_rec    job_table%rowtype;
begin
    select * into l_rec from job_table where job = p_job;<b>
    for i in 1 .. l_rec.iters
    loop
        open l_cursor for 'select * from t t' || p_job || ' where x = ' || i;
        close l_cursor;
    end loop;</b>
    delete from job_table where job = p_job;
    commit;
    dbms_alert.signal( 'MULTIUSER', '' );
    commit;
end;
/

Now, remember, this is MY JOB (you will put your code there, what is your workload). I wanted to simulate lots of parsing - that is what my loop does. You'll replace the bit in BOLD with YOUR CODE. The rest all stays there - read the job record (create your job_table with YOUR INPUTS), process whatever (in bold), remove our job record - commit and signal "finished"

Now, we need to schedule N users - this does that (remember, do_sql was the name of MY job, you put in your job name)... In my script, I passed in how many users to simulate and how many iterations they should do...

declare
    l_job    number;
begin
    for i in 1 .. &NumUsers
    loop
        dbms_job.submit( l_job, '<b>do_sql</b>(job);' );
        insert into job_table values ( l_job, <b>&NumIters</b> );
    end loop;
    dbms_alert.register( 'MULTIUSER' );
    commit;
end;
/

the commit at the end - it released the jobs to run (make sure job_queue_processes is set high enough on your system!!)

and then I:

exec statspack.snap
declare
    l_cnt number;
    l_msg long;
    l_status number;
begin
    loop
        select count(*) into l_cnt from job_table where rownum=1;
        exit when l_cnt = 0;
        dbms_alert.waitone( 'MULTIUSER', l_msg, l_status, 600 );
    end loop;
end;
/
exec statspack.snap

that could be an AWR snap as well - whatever you want - I started measuring, waited for things to run and then finished measuring....

then, run the statspack and see what you see.

Alexander, June 05, 2008 - 9:56 am UTC

That's awesome thank you I'll give it a try.

error logging

A reader, August 14, 2008 - 3:10 pm UTC

Tom:

What is the best setting for detail error logging for mod_plsql:
warn, debug, error

We noticed it sometimes does not give us good details about the error like http 500.

can it be set just like apache error logging

thanks

August 18, 2008 - 10:11 am UTC

if you get a 500, that is "internal server error", all bets all - something *crashed* - you would look to the apache logging to see what was happening then.

if there was a "best" level, there would be an "only level" - why have others if there is a universal best ;) you set logging to the level you desire, to suit your needs.

log

A reader, August 18, 2008 - 12:55 pm UTC

Tom:

Are you saying that oracle app server will have two error logs:

a. apache error log
b. mod_plsql log

WE only saw the mod_plsql log which did not have much about the http 500.

I thought you configue the app server to do one kind of log.

2. We do not have control over the setting of logging. We can only request. We are just wondering which would provide better reports for debugging: "debug" or "warn" or "error".

August 20, 2008 - 10:10 am UTC

the oracle application server will have many logs.

The combination of the Apache http server and the mod_plsql module will have two logs - the apache log and the log generated by the application that is mod_plsql.

2) that is entirely up to you, you decide.

testing

A reader, March 12, 2009 - 5:43 pm UTC

Tom:

I have an application with hundreds of web pages that uses mod_plsql. it is running on server A where db is.

Each page uses a stylesheet and javascript library like
<link rel="stylesheet" href=" http://www.xxx.com/app.css type="text.css">
<script language="javascript" src=" http://www.xxxx.com/app.js type="text/javascript"></script>

These files re stored on unix file system on server C which has an apache web server (no oracle)

Users call the oracle application by using 9i application server on server B (mod_plsql).

Now we are moving application from 9i to 10g application server secure port (https) on server B.
So the procedure calls will be via https and port 4446.

Do i have to go to every procedure and change the links to " https://www.xxx.com:4443/app.css" to avoid the annoying dialog that prompts user that page has mixed content and whether they want to change it or not.

We plan to leave the files same location on server C and just have a redirector fro the https link to http location where the file are (just a workaround to avoid the mixed content pages).

March 13, 2009 - 7:22 am UTC

this would be a question best asked of someone that knows about cross site scripting and such - eg: not me :)

nothing really to do with the database, everything to do with browser security and html standards...

(but I can say, if you have to go to every procedure..... You probably did it wrong, why wouldn't you just be updating a table or a template to fix it once and have it cascade everywhere??? eg: be data driven, not hard coded...)

web

A reader, March 13, 2009 - 9:40 am UTC

Tom:

you are right. BUt a quick question on oracle

When you call the procedure, is the stylesheet cached in mod_plsql or database or browser so follow-up calls retreive it from cache or it depends on how the browser settings are set up "check for new versions of the paage"

March 13, 2009 - 10:57 am UTC

the file would not be retrieved by mod_plsql or the database - it is all about the web server at that point. A webserver might cache the file to be returned, a browser might cache it - but the database/mod_plsql - they are not even remotely involved in touching it.

mod_plsql -runs stored procedures
database - contains stored procedures to be run

files - file system, nothing to do with mod_plsql or database.

web testing

sam, March 23, 2011 - 3:20 pm UTC

Tom:

When you test a web application performance and log how long each page takes (instrumenation), what is your standard for acceptable response time or not?

Would anything that takes less than 3 seconds OK and anything over needs to be looked at for possible tuning or refactoring.

can you advise?

I assume you also do stress testing and simulate many users by flinging URLS and see how performance changes.

March 23, 2011 - 3:34 pm UTC

what is your standard for acceptable response time or not?

come on sam, what do you think my answer will be.

It will be:

it depends

it totally depends.

I'd like to spend a lot less than 3 seconds for most pages (the answer VARIES from page to page, from application to application).

Execution Time

H, September 25, 2011 - 7:50 am UTC

in original reply, you said:

instruementing your code helps. For example, if you see a URL on asktom with NO in it, change it to YES and see what happens. Goto the asktom home page, click on the first
article there. You'll see timings down the side of the page -- very very helpful (mandatory in fact) for finding out what part of a page is causing a slowdown (if the PAGE is the bottleneck)

I changed this url
http://asktom.oracle.com/pls/apex/f?p=100:12:0::NO::P12_QUESTION_ID,P12_PREV_PAGE:6690299776039,11

to this
http://asktom.oracle.com/pls/apex/f?p=100:12:0::YES::P12_QUESTION_ID,P12_PREV_PAGE:6690299776039,11

but I couldn't see any timing. Anyways, Sir, could you please elaborate how to display timings for a web procedure on a web page?

Thanks

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.

Web Application Testing

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

web testing

web testing

web testng

trigger

ip

tom

Curious for elaboration

ip

connection pooling

web page timing

excellent

web app

Grab and Hurl

oracle on laptop

web app

One instance versus multiple

instance

9iAS

9iAS Errorrs

9iAS Errors

web testing

web testing

web testing

debugging

debugging

testing

9IAS testing

web testing

web testing

Your javascript is buggy....

web testing

The "randomness" of it is a timing issue

How to test database load

oracle web

web portal

portal

web app

import

export

export

A good free tool for web testing

how would you design clickstream data,

clickstream cont...

clickstream cont...

How can I delete a record from the database when the user closes his browser?

web server testing

testing

session expire with Hardware Loadbalancer

error logging

log

testing

web

web testing

Execution Time

More to Explore

Security