Skip to Main Content
  • Questions
  • Package/Session State Management - Mod PL/SQL

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Scott.

Asked: February 12, 2003 - 9:17 am UTC

Last updated: March 21, 2011 - 10:02 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

G'day Tom

Boy, you're a hard man to catch at the moment - even harder for us Aussies!

I have a question regarding Package/Session State Management of the PL/SQL Gateway.

Our current setting is Stateless (Reset package state).
Sometimes, I can find this painful when trying to communicate between pages.

I'm curious to know if you have an opinion regarding any problems, programming quirks etc between resetting the package state and preserving it.
I'm a fan of using persistant variables in packages, but being new to Mod PL/SQL, I fear there may be some pitfalls with using this method.

And what is the difference between preserving the package state, and the "stateful" option?
Would stateful affect connection pooling? (we are operating with a multiple user system) And retaining information in package body global variables specific to each user?

Thanks.

and Tom said...

The entire beauty of a Ntier architecture is the scalability you can achieve -- IF you remain pretty much entire STATELESS.

Keep a state -- that means you are chomping up resources on my machine when you are NOT even using it!

I've never used the "stateful" mode of mod_plsql -- ever. I do not recommend it.

here is what we do on asktom. you get a session id. We make it so the sessionid is pretty much unique to you (you cannot kidnap someones session). We have a state table that is keyed by -- well, that sessionid. You want to keep a state, we keep it in a table.

I know, that is an select or an update or an insert for each page view -- but, you know databases where born to read and write data -- so I use them that way!

As you work through a question on this site, we store various bits and pieces in a table for you. when you are done, we move it from table "state" to "real-table".

works very nicely, scales awesome, I can bounce the webserver without killing sessions, you do not use my machine EXCEPT when I'm generating a page for you -- it is the way I think it should be done.


if you go stateful, you'll bind a SESSION to a mod_plsql instance -- one to one. You have a thousand concurrent users, you would need 1000 sessions. Using stateless, I might have the same 1000 concurrent users with 10 sessions.




Rating

  (43 ratings)

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

Comments

stateful

mo, February 12, 2003 - 6:17 pm UTC

Tom:

how do you do this:
<I can bounce the webserver without killing
sessions>

Thanks,

Tom Kyte
February 12, 2003 - 6:31 pm UTC

apachectl stop
apachectl start

I don't have stateful sessions maintained in the middle tier. In fact -- I can bounce my entire SERVER -- apache, database, os, everything and you might never ever know from page to page -- you would get an error if you tried to hit a page whilst I was rebooting but eventually you would be right back where you left off. I am stateless. my state persists in this thing called a database. As long as the database is there, my session is OK.

Not quite understood

Scott Wesley, February 12, 2003 - 7:04 pm UTC

G'day

When you say
"We make it so the sessionid is pretty much unique to you (you cannot kidnap someones session)"
and
"Using stateless, I might have the same 1000 concurrent users with 10 sessions"
I must be getting confused with your terminology, because these two statements seem to contradict each other.
It is my understanding the your sessionid forms part of the address at AskTom, so I presume when you bounce the server when I'm on one page, and then I navigate to the next, the sessionid you store in that table you refer to remains.

Does the statement
"I might have the same 1000 concurrent users with 10 sessions"
still hold true when people are signing on with different usernames?

And I'm not sure you covered the difference between
Stateless (Reset package state)
and
Stateless (Preserve package state)
and the use of package global variables.
I think I understand why not to use Stateful, so I'll avoid that...

Thanks.

Tom Kyte
February 12, 2003 - 9:48 pm UTC

I should have said with 10 DATABASE SESSIONS -- sorry about that.

Each user is assigned a sessionid.

they share about 10 database sessions -- regardless of the number of asktom users there are at a point in time.

as for the stateless -- that is all i use. I don't know what you mean by "preserve package state". stateless means -- "package variables gone, wiped out"

Clarification

Scott Wesley, February 12, 2003 - 10:14 pm UTC

G'day

I thought that was what you meant.
My understanding is that after a page is generated for me, that db session is not needed anymore, and can be used by someone else. When I navigate to another page, I grab a spare db session to generate the code for me.

You said the assigned sessionid is inserted into a state table.
If you are sharing sessions, is the only way to obtain this information from the state table through the sessionid parameter passed to f?
(this is the alternative to global package variables, I presume, and also why you have a bookmark link).

And as a clarification, when I refered to preserve package state, in the DAD admin,
/pls/<dad_name>/admin_/adddad.htm?<dad_name>
There is an option for Package/Session State Management.
In this poplist, there is 3 options...
1) Stateless (Reset package state)
2) Stateless (Preserve package state)
3) Stateful

All the information I found in doco is in the help:
/admin_/help/feature.htm#1018347
<quote>
1.2.2 Stateless (Preserve Package)
In this state, the mod_plsql calls htp.init after each request to only clear OWA packages, and not any application-specific variables and cursors. Furthermore, an implicit commit is done if there are no errors in processing the request.
PL/SQL applications are responsible for cleaning out the session at the end of request, but are not responsible for issuing commit in their code.
</quote>
Mind you, in the last sentence, it's a little ambiguous to whether sessions are reused by other users or not - but it does say variables are retained - but thinking further, it said app specific, not user specific - which kinda relates to our initial testing.

By the way, to let you know, I've been receiving two e-mails each time for your updates to the question.

Thanks.

Tom Kyte
February 12, 2003 - 10:44 pm UTC

yes, the sessionid is maintained by the browser and sent back to us, so we can "rejoin" our state -- our data in the table.

#2 looks very very dangerous. I would utterly and entirely 100% discourage it's use in 100% of all cases. I never speak in absolutes -- (hey, I just did tho, didn't I) but this time I will - that is the most dangerous thing I've ever heard of. Don't do it.

The variables, transactions -- everthing are "saved" and reused by anyone that comes along.


You recieve one email from me, and one email from you. If you update this q with a review -- since you FILED the question -- that counts as an "update" to the question ;)

My software doesn't know its really you -- as the reviews thingy doesn't ask for an email address -- so I've no way to know that it is you following up your own.

Real table?

Vikas Sharma, February 13, 2003 - 2:00 am UTC

Hi Tom,

Please explain

" when you are done, we move it from table "state" to "real-table".

i know about state table but what is the real table why do you store values from the state table to real table.

Reagrds

Vikas Sharma

Tom Kyte
February 13, 2003 - 9:33 am UTC

when you go through the process of

o asking me a question
o shopping on amazon
o filling out a complex form on the web

you generate a "state". when you ask me a question -- It takes two or three windows. You give me basic information -- then the question -- then you review the question. When you are happy with the question, you submit it.

It is at that point it moves from the STATE table (where it is just "stuff you are working on") into my "real" question table where I for the first time see it. It is not a real question until it is in the "real" quesiton table. Before then it is just stuff in the state table.

statefull

mo, February 13, 2003 - 10:04 am UTC

Tom:

1. As a developer, can I query something that tells me how the mod_plsql is configured in an instance.
1) Stateless (Reset package state)
2) Stateless (Preserve package state)
3) Stateful

2. You always recommend to use #1?

3. When you say you maintain state in a database table by tracking session id and state. If you have 1000 users and 10 database sessions would the 1000 users have the same 10 session ids or each one would have a unique number since they all using 10 sessions.

4. Do you only have two columns in your session table (session_id,state)? Do you update the state every time the user calls a page? Do you save also variable values from each page? any sample record for you state table?

5. Just curious, why do people implement J2EE 4 tier complex stuff (like amazon) when you can do it simply in the database (two tier no app server)? why do you need to have an app server that maintains state and burden the system with a lot of resources when you can do it simply like your way? Is it lack of knowledge on their behalf (going with the tech whiz) or there is actual benefits that I am not aware off?

Thank you,

Tom Kyte
February 13, 2003 - 10:37 am UTC

1) you have to peek at the config files for mod_plsql

2) yes

3) they would each and every one have a separate, non-guessable, unique session identifier. so their "state" is "their state", not "our state"

4) depends, sometimes we have a row

sessionid column1 column2 column3

using marvel (the software behind asktom) we have

sessionid variable_name variable_value

so we can generically store as many name/vale pairs as we need.


we update the state when the state changes -- only "changed values" are saved/resaved (updated)

5) you eventually would run out of steam. mod_plsql goes far -- very very far -- much farther then many (i would suggest even most but that is an opinion) then most people ever need to go.

amazon -- they would not be able to do it all without a monolithic architecture.

Asktom -- it would be silly to have 14 tiers for such a thing.

It is a matter of using the right tool for the right job, but most people only know how to use a "tool" so that is what they use for all jobs.

state

mo, February 13, 2003 - 10:49 am UTC

Tom:

<It is a matter of using the right tool for the right job, but most people only
know how to use a "tool" so that is what they use for all jobs.>

This is exactly what I am asking. How do you know which tool is the right answer for project. For example why would not you design AMAZON with several mod_plsql rather than several app servers?

2. in your 10,000users/10 sessions config what happens when 50 people call a page at the same time? DO you increase sessions to 50 or persons will wait till the other is finished?

Thanks,



Tom Kyte
February 13, 2003 - 4:47 pm UTC

because mod_plsql means everything -- 100% of everything is running in the DB. It would most likely run out of steam for oh say, 1 billion users or so ;)

It is a matter of scale. I know on my 4 cpu hardware (from 1996/97 btw -- not state of the art at all) I know I can do about 16page views/second (our threshold is about 0.25 cpu seconds per page) if they arrive randomly....

I could get that to 100's or 1,000's of hits per seconds with the right hardware -- clustering and design, but I would probably hit a wall eventually (only so many nodes in a rac cluster, only so many cpu seconds on a single machine).

Now, if I can front end that with an infinite number of app servers -- even though individually they cannot do as much as a single database could do - they can scale out better in this extreme case.


2) depends on the machine -- can the machine do 50 concurrent sessions without getting saturated?

clean up

Scott Wesley, February 13, 2003 - 7:52 pm UTC

Thankyou for your assistance.

When do you clean up your state table?

ie - a user has browsed for a while, asked a question, you've used his/her sessionid in the background, but once the user closes the browser, the sessionid is still in the state table.
When is it cleaned? Does it expire after a while?

Tom Kyte
February 13, 2003 - 7:58 pm UTC

expires after a bit... job cleans it out

Do it using cookies

Gabriel Ilarda, February 16, 2003 - 7:31 pm UTC

Thought I'd mention I maintain session states using a cookie. I store the username, password (encrypted)that was entered in at login, and the expiry time (15 minutes) in the cookie and at the beginning of every procedure I check that the cookie hasn't expired. If it has then the user views the 'Your session has expired message.' If not they are allowed to keep surfing and the expiry time is reset. I store the password in the cookie because I pass the username through every procedure (sort of like a session ID) and then at the start of each procedure when it checks if the cookie has expired, it also checks that the password in the cookie matches the username passed into the procedure. This is primarily done to stop if a user logs in and figures out how to call my procedure, and they go ahead and try to call it passing in a different username in order to get different access to my system. My procedures will check the password in the cookie and if it doesn't match the username that is passed to the procedure they are thrown out of the system. Probably could have done this using a table but system working fine and I developed this a while ago when i couldn't find much help on mod_plsql. Might change it in the future if I think of using tables to stores session variables.

mod_plsql -- who developed it ?

Robert, August 28, 2003 - 2:49 pm UTC

Tom, do you know who developed mod_plsql ?
I thought it's Oracle Corp until I hit these 2 links:


</code> http://www.total-knowledge.com/progs/mod_plsql/ http://freshmeat.net/projects/mod_plsql/?topic_id=66%2C92 <code>

Tom Kyte
August 29, 2003 - 8:24 am UTC

something near and dear to me...

Here is the history.....

in the beginning there was this thing called the "WOW gateway" (Web Oracle Web). It was a cgi-bin that crudely translated a url into a stored procedure call (didn't use bind variables!!! that is why I call it "crude").... It was written originally by Magnus Lonnroth of Sweeden.

This became OWA (Oracle Web Agent) in version 1 of OIS (the Oracle Internet Server). It too was a cgi-bin.


Later, with version 2 of OWS (OIS -> OWS, the Oracle Web Server), this OWA became a "cartridge" in OWS speak. Like a module for apache but ran in the spyglass mosaic webserver (which is what we used).

At that time, some guy named -- oh wait, that was me -- wrote this thing called OWAREPL (historically archived here:
</code> http://asktom.oracle.com/~tkyte/owarepl/doc/ <code>
). It greatly extended the capabilities of OWA giving it file upload/download, db authentication and many of the features you take for granted in mod_plsql today.

We are now at OWAS version 3.0 (Oracle Web Application Server)....

Then, came along this thing called "webdb" (our group wrote that initially). We wanted a smaller footprint then OWAS for simple installs so we took the OWAREPL code and built a small lightweight listener around it - this was the webdb listener. It did http and "owa" functionality.

Then, OAS (Oracle Application Server) v4.0 came out -- they had been incorporating many of the ideas of OWAREPL into their OWA but differently (file upload and download for example is "there" in OWA but done very very differently then either OWAREPL or webdb)....


Flash forward a bit -- spyglass goes away, cartridges go with it -- it is now 9iAS using apache. The decision was made to use the webdb functionality in mod_plsql -- the code was "merged" if you will. So, OWA+OWAREPL+webdb -> mod_plsql, with owarepl/webdb (which was really owarepl written in the Oracle coding standard instead of "toms standard") being the predominant theme.

And here we are.

The others you refer to are "imposters" if you will. OWA begat OWAREPL begat webdb -> mod_plsql.

History of mod_plsql

Robert, August 29, 2003 - 12:57 pm UTC

Thanks for the gory details, Tom :)

Wow...lots of cooks been stirring this pot, huh ?

Have you ever heard of this pl/sql web-app development framework called "Orbit" (www.runnertechnologies.com)

Thanks


Tom Kyte
August 29, 2003 - 1:14 pm UTC

no i hadn't -- interesting, looks just like marvel -- aka html db -- aka a feature of the next release of the database.

When and When not the use of MOD_PLSQL

Mark Wooldridge, August 29, 2003 - 1:48 pm UTC

Tom,

Interesting history of mod_plsql. I can relate to it since I had also been working with WOW back in 1995 but we implemented multiple HTTP listeners on Trusted Solaris and hooked into Trusted Oracle to get different results at different levels.

Later when OLS was developed we created a 'c' function to "hijack" the mod_plsql oci connect call to get the level of the http request and set the necessary OLS level/compartment information on the context and continue to the original oci connect call in mod_plsql. This allowed us to run with Oracle and OLS on Trusted Solaris and have connections to the database that matched the level of the network connection. This function also worked when using SQL*Plus on the host machine and an attempt ws made to get it working with SQL*Net but we really needed a multi-level SQL*Net listener as Trusted Oracle had.

Sorry for the extra comments.

Given the monolithic nature of mod_plsql, your comments about Amazon not being able to do it all, and how many users you feel mod_plsql could support with the right hardware, how does one decide if mod_plsql is the "right" tool for right job? What portions of amazon could not be done with mod_plsql?

p.s. hopefully mod_plsql will be updated to support posting clobs since 9i has been updated with better handling of clobs.


Tom Kyte
August 29, 2003 - 2:57 pm UTC


mod_plsql is constrained by the number of database instances you have running on your database -- so RAC extends this number out, as it gives you more cpu cycles, but cannot extend it out as far as an infinite number of app servers could.

plsql uses cpu -- say it used 0.10 cpu second per page (pretty good goal on a conventional machine today).

that means it can do 10 requests per cpu if they all arrive "evenly".

so, in theory, a 4 cpu machine might be able to do 40tps -- if they arrive evenly (and then never do, so it is less then that)...

it is just math really after that -- what is the arrival pattern, what is the amount of cpu resource consumed by plsql -- how many cpus do you have.

at some point, you cannot get enough cycles. That is when moving to a physically separate tier makes sense. When this is -- depends on your setup and how efficient your code is to a degree.

it is "big" to say the least.

What exactly is DAD ?

robert, September 02, 2003 - 10:55 am UTC

Please explain in "Tom Speak"
what exactly Database Access Descriptor (DAD) is.
Why is it an important piece of info for mod_plsql/apache.
Where is it stored, how it is used...

I read the official iAS doc but not sure I understand it.

Thanks

Tom Kyte
September 02, 2003 - 2:51 pm UTC

it is to mod_plsql what a tnsnames.ora entry is to sqlplus.


sqlplus scott/tiger@database
^^^^^^^^ big mess of data in a tnsnames.ora file
that says how to connect to a database


A DAD (formally known as a DCD, database connection descriptor) tells mod plsql "how to" connect to a database. In my URL for example:

</code> http://asktom.oracle.com/pls/ask/f?p=4950:1: <code>
^^^

ask is my "DAD" and it consists of information like this:

[DAD_ask]
connect_string = AskUs
password = nope_not_a_password
username = public_user
default_page = f?p=4950
reuse = Yes
enablesso = No
stateful = No


that tells mod_plsql to connect as public_user/nope_not_a_password@AskUs, the default page will be the function f with inputs p=4950, we are reusing connections (pooling) and sso (single sign on) is disabled and stateful sessions are not needed/supported.


it is stored in a config file -- wdbsvr.app in the iAS home/modplsql/cfg directory.






Package/Session Management Type

Robert, September 15, 2003 - 1:53 pm UTC

Tom, for "Package/Session Management Type" in DAD config
, for Marvel, do you "Preserve State" or "Reset State" ---
what's the difference (to Marvel app) anyway in terms of functionality, performance ?

Thanks
Robert

Tom Kyte
September 15, 2003 - 2:49 pm UTC

reset, always.

i never use a stateful model on the web, never. too many resources consumed for too long.

in marvel, the state is managed 100% in the database, you always want reset.

Getting the Session Id

Bill, November 21, 2003 - 3:55 pm UTC

Tom,
You stated above that "the sessionid is maintained by the browser and sent back to us"

How do you get the sessionid from the browser using mod_plsql

Tom Kyte
November 21, 2003 - 5:37 pm UTC

you make one up


we use sequences and random numbers to generate session ids. they are just made up unique numbers.

Other leftover session "state"?

andrew, January 23, 2004 - 4:15 pm UTC

Tom

8.1.7 database, 9.0.2 9iAS.

The "reset state" property is achieved by running DBMS_SESSION.reset_package after the client call is - right? So package state is destroyed, but other residual session "state"/configuration is left like:
1.) Modified NLS settings
2.) Modified optimizer mode
3.) Open DB links etc...

I have more than one App using the same DAD. My problem is that some transactions open DB links and then subsequent transactions having autonomous transactions run into "ORA-00164: autonomous transaction disallowed within distributed transaction".

Can I determine the current settings for any of the above (specifically #3)? I know that I could try to ensure that the transaction which opens the DB link could manually close it too, but in my case it may be more efficient to simply close any open links before I run the autonomous transaction.

Tom Kyte
January 24, 2004 - 3:53 pm UTC

v$dblink will show you all of the open dblinks in your session.

for safety sake, you would issue the alter sessions needed in all cases.

connection -pooling

robert, March 15, 2004 - 10:43 am UTC

Tom , my app is "breaking" under Apache/mod_plsql !

The app was running against OAS (4.0.8). I use global temp table (on delete PRESERVE rows) every page to transfer the static html stuff over from regular table.

The problem I am facing:
Obviously under mod_plsql, with connection-pooling ON, the GTT is NOT cleared from page to page as under OAS.

How can I deal with this ? make GTT "on commit delete rows" and use "Autonomous Transactions" ?

thanks

Tom Kyte
March 15, 2004 - 10:51 am UTC

make the gtt on commit delete rows -- for sure. IF you need a gtt at all even.

do not use autonomous transactions.

DBMS_SESSION.UNIQUE_SESSION_ID - SAME value in 2 browsers

robert, March 16, 2004 - 2:08 pm UTC


>>
make the gtt on commit delete rows -- for sure. IF you need a gtt at all even.
do not use autonomous transactions.
<<
hi tom,

8.1.7
Well, how else can I avoid locking table in which I store static HTML ?
I transfer to GTT & do updates/substitution replace at run-time.

Now I made the GTT "on commit delete" and put a COMMIT in pkg init section. and the app seems to work fine.

With connection pooling on, one thing I discoverd is that I htp.p out DBMS_SESSION.UNIQUE_SESSION_ID to the page and the
values are the SAME in 2 separate browser(sessions) (SAME Basic Auth. login)...Is this supposed to be like this ?

thanks

Tom Kyte
March 16, 2004 - 2:16 pm UTC

yes, it is the same session after all.

sys_guid() or a sequence will be something to look into.

Softer Soft Parse Vs. Soft Parse

A reader, April 29, 2004 - 12:31 am UTC

Tom,

Very good and important information on this thread!

When you use the stateless setting, I assume the dbms_session.reset_pkg call will reset your session state. This allows DATABASE session reuse (which is a good thing).

Here are my questions:

1) What happen to the reminding session? Does the static SQLs in the package get cached (i.e. are the cursors cached too)?

2) Will the next same package call to these static SQLs perform a "softer soft parse"?

3) Can the Oracle9iAS (for Java servlet) connection pooling share the same DATABASE sessions or can it only share the PHYSICAL DATABASE connections (i.e. not reusing the DB session)?

4) If Java servlet cannot reuse DB session, how can it perform a "softer soft parse"?

5) Can you refer me to some mod_plsql performance benchmarks? I am evaluating mod_plsql and servlet for a new big project that serves 15,000-30,000 users.

6) Does it make sense for people to use Oracle9iAS just for mod_plsql? It doesn't make sense to me to use an application servers since all the execution are done in the database instance.

7) Does mod_plsql have or use MVC framework? It is great to use package for query and DML statements (i.e. data related operation). However, for a medium or large project, I am not too sure if PLSQL should be used to write HTML/JavaScript UI code. This is because UI designing and logic coding require different skill set. I would rather hire cheaper web designers to work on HTML/DHTML UI piece (using whatever tools that they want like Dreamweaver, Flash, etc) than forcing them to code PLSQL. On the other hand, I also don't want my software developers to design and write UI stuffs because they can contribute more on writing more robust code.

As always, thank you for all the helps.

Peace,
Jon

Tom Kyte
April 29, 2004 - 7:38 am UTC

1) the reminding session?

with dbms session reset -- the session state is ripped down, plsql cursors closed. you can have serious cursor leaks with dbms_sql dynamic sql as those cursors are destroyed but not closed (so becareful with them)

2) they will perform soft parses and they could be softer soft parses, yes

3) you can do either

4) it cannot

5) not aware of any. I do know that think.com is "larger" than that and they use it....

6) just apache is all you need. in as much as the http server of 9ias is apache, you are running ias -- but you are not running the rest of the services.

7) </code> http://htmldb.oracle.com/ <code> -- that is what we used to write this site, parts of think.com, and many others.

In this fashion, you build 100% function database applications without dreamweaver, flash, etc -- you just build stuff that works, works fast, works easy. You can get *very* sophisticated, but remain extremely portable, fast and easy to use/understand.

Softer Soft Parse Vs. Soft Parse

A reader, April 29, 2004 - 2:11 pm UTC

Tom,

Thank you for the response.

Perhaps my questions are not very clear. I am still not sure that I completely understand your answers.

<quote>
2) Will the next same package call to these static SQLs perform a "softer soft parse"?

2) they will perform soft parses and they could be softer soft parses, yes
</quote>

My question should refer to calling the same package in SUBSEQUENT web request. In other words, the first web request calls the package with static SQLs. At this time, PLSQL engine caches the cursors. Then, DB or app server calls the dbms_session.reset_pkg, which closes the PLSQL cursors.

1. After calling the dbms_session.reset_pkg, does PLSQL really close the cursors? Or, will the cursor still be in the cached (ala normal PLSQL call to close an explicit cursors)?

I am trying to get more "softer soft parse".

<quote>
3) Can the Oracle9iAS (for Java servlet) connection pooling share the same DATABASE sessions or can it only share the PHYSICAL DATABASE connections (i.e. not reusing the DB session)?

3) you can do either
</qutoe>

2. If I use only Oracle9iAS OCJ4 for Java serlvet (not mod_plsql), can I still use DB sessions sharing? If yes, can you refer me to some manuals? It seems to me that DB session sharing has more benefits than connection sharing. Why would anyone want to use connection sharing?


I have started to evaluate HTML DB. I need to think more of the advantages and disadvantages of not able to to use dreamweaver, flash, etc. This is because I really need find out how fast and flexible can HTML DB build the UI vs. these tools. These commerical tools do serve their proposes for many web designers.

I appreciate all your helps.

Peace,
Jon

Tom Kyte
April 29, 2004 - 2:46 pm UTC

If in a single session (forget the web not relevant) you call a package after a reset - it'll be AT LEAST a soft parse and could well be a "softer" soft parse (via session cached cursors)

the cursors really are closed (even with session cached cursors)


I'm not a java dude *at all*, I don't really know my way around those docs even a little bit.


You can share sessions (the normal, the default)
You can share a physical connection -- ntier proxy authentication does that.


flash is perhaps the most annoying thing I've seen on the web. I love Firefox (my browser) with Adblock extensions. Flash be gone (most flash is advertising!). Web pages that move -- ugh.


Htmldb builds *database applications* -- functional, lean, fast, easy.

A reader, May 19, 2004 - 1:31 pm UTC

Tom, I was going though this interesting thread and one basic question came to my mind. Or else I couldn't get it from the reviews.

Using a connection pool like mod_plsql or ODBC do, what happens to cursors regarding reuse ?

I mean, after a stateless Oracle session closes, don't I lose my cursors that could be shared using bind variables ? Or they keep retained ? How ?

Tom Kyte
May 19, 2004 - 2:35 pm UTC

mod_plsql does a "reset session state" to wipe it all out.

odbc is an api, not a connection pool.


the sharing happens in the shared pool -- in the sga -- on the server, not in the client.

A reader, May 19, 2004 - 2:45 pm UTC

I have just downloaded mod_plsql to try to resolve a the connection pool problem I am having with PHP and Apache. I don't know if it applies, just trying.

I have downloaded and opened the tar.gz file from total-knowledge, lastest version from the link above.

1) I don't find any documentation as to how to use it. Where is it ?

2) In the disign readme file, they say:

<q>
First and most importand: unlike in owa, database connections are CACHED.
It means that you do not have state of your packages reinitialized with
every request. Commit still happens after each request, though.
<\q>

That is... would we have only: Stateless (Preserve package state), which you said is bad ?

3) I myself would have to reinitilize the package state, it seems. You told us to use ALTER SESSION above. How, exactly ?

Thanks

Tom Kyte
May 19, 2004 - 2:51 pm UTC

1) otn.oracle.com has all documentation

but if you have a 8i/9i database, you already had/have mod_plsql -- its in the server install.

2) that would be true only for stateful connections which no one should use (thats me saying that). otherwise your packages are reset

3) will be done for you.

A reader, May 19, 2004 - 2:51 pm UTC

You mean sql cursors remain in the shared pool even if the session relative to them is closed ?

Does that "reset" invalidate cursors ?

Tom Kyte
May 19, 2004 - 2:56 pm UTC

sql is in the shared pool

cursors point to sql in the shared pool

cursor is a client thing, sql is a shared pool thing.

you close a cursor, parsed sql stays in the shared pool until it ages out.

A reader, May 19, 2004 - 4:20 pm UTC

Isn't that piece of the shared pool which holds the sql plus execution plan also called a cursor, like in cursor_sharing ? It seems to me there are both a "client" cursor and a "server" cursor.

I remember there is something called "cursor state" that is in the pga. Is that what you call a "client thing" -- not shared ?

On the other hand, the "sql in the shared pool" is the cursor that can be shared, hence cursor_sharing ?

Can you explain ?

Tom Kyte
May 19, 2004 - 5:50 pm UTC

no, cursor_sharing is a technique to make sure that statements your prepare are more "shareable" -- have bind variables.

there are no server "cursors" really. cursor_sharing is just a convienent term.

the sql in the shared pool is sql, plans, stuff. it is not a cursor.

How to count within "partitions" without using "Analytics"

A reader, October 29, 2004 - 2:08 pm UTC

hi tom,

we are trying to achieve something like this:

select row_number() over(partition by o.owner order by object_name) rowno,
o.*
from all_objects o

with 7.3.x ...

using a packaged function taking the partition criteria as input and incrementing a counter according to that would work - but one would have to *RESET* package state before each statement (whereas package state remains between different calls to the function within the same statement).

(how) can one solve this problem without the need for a reset-call before each new statement?




Tom Kyte
October 29, 2004 - 5:02 pm UTC

one cannot do that reliably.

application is going to have to number the rows.

Is it a valid approach ...

A reader, October 31, 2004 - 10:08 am UTC

... to call that packaged counting function with an additional parameter 'RowNum' feed by the value of that pseudo column taken from the set?

every first row produced by a query would have value 1 for that, and one could reset package state in that case.

is there anything wrong with that?

Tom Kyte
October 31, 2004 - 10:22 am UTC

you cannot do it reliably in the server in 7.x, you have to do it in the client, as the rows are fetched.

You are assuming some fixed order of operation, which is not promised by SQL.

sorry - i didn't mention ...

j., November 02, 2004 - 7:02 am UTC

... that i'm not interested in a certain order of rows within "partitions". i just want to assign row numbers in a "groupwise" manner - starting with 1 for each group.

for that my approach would be OK, right?

Tom Kyte
November 02, 2004 - 8:46 am UTC

no, you are counting on rows being processed in a specific order -- you cannot count on that.

hmmm - i thought something like that would work ...

j, November 02, 2004 - 11:33 am UTC

create or replace package Counter
is
subtype t_cGroup is varchar2( 100 ) ;

c_nStartIdx constant pls_integer := 1 ;
c_nMaxRange constant pls_integer := 100000 ;
c_nIdxInc constant pls_integer := 3 ;

function GetValue( p_cGroup t_cGroup, p_nRowNum number )
return number ;
end Counter ;
/

create or replace package body Counter
is
type t_recHashRec is record( cGroup t_cGroup, nCntVal number ) ;
type t_tabHashRec is table of t_recHashRec index by binary_integer ;

v_tabHashRec t_tabHashRec ;


procedure GetNextFreeIdx( p_nIdx in out pls_integer )
is
begin
if p_nIdx >= c_nMaxRange - c_nIdxInc then
p_nIdx := 1 ;
else
p_nIdx := p_nIdx + c_nIdxInc ;
end if ;
end GetNextFreeIdx ;


function GetHash( p_cStr2Hash varchar2 )
return pls_integer
is
begin
return DBMS_Utility.Get_Hash_Value( p_cStr2Hash, c_nStartIdx, c_nMaxRange ) ;
end GetHash ;


function GetValue( p_cGroup t_cGroup, p_nRowNum number )
return number
is
v_bDone boolean := FALSE ;
v_nIdx pls_integer := GetHash( p_cGroup ) ;
begin
if p_nRowNum = 1 then v_tabHashRec.Delete ; end if ;

loop
if v_tabHashRec.Exists( v_nIdx ) then
if v_tabHashRec( v_nIdx ).cGroup = p_cGroup then
v_bDone := TRUE ;
else
GetNextFreeIdx( v_nIdx ) ;
end if ;
else
v_tabHashRec( v_nIdx ).cGroup := p_cGroup ;
v_tabHashRec( v_nIdx ).nCntVal := 0 ;
v_bDone := TRUE ;
end if;

exit when v_bDone ;
end loop ;

v_tabHashRec( v_nIdx ).nCntVal := v_tabHashRec( v_nIdx ).nCntVal + 1 ;
return v_tabHashRec( v_nIdx ).nCntVal ;
end GetValue ;
end Counter ;
/

select Counter.GetValue( Owner, RowNum ) RowNo, o.* from DBA_Objects o where Object_Type = 'FUNCTION' order by Created ;

could you please point me to the point i don't get?

Tom Kyte
November 03, 2004 - 5:19 am UTC

you are making the assumption the that function is always called once per each row in the output of the query and that it is called before the order by and other stuff. different plans result in different numbering scheme (same query on two days could return different "rowno"s

Looks like a very slow way to achieve what the client should be doing (in 7.3 before this ability was added to the database itself). It might work, but there is no promise that it will. You are ascribing procedural concepts to a non-procedural language.

j., November 03, 2004 - 7:10 am UTC

now i see what i've forgotten to mention: the purpose of the query is not to number ordered rows but to assign numbers to rows that can't be ordered at all --- in order to select a certain number of rows per group then ("show me all groups and up to x rows per group").

i do assume that the function is called (at least) once per row (since values from that row are input parameters).

but can - on the other hand - one think of any reason why the function should be invoked *MORE* then once per row although it appears only once in the query?

Tom Kyte
November 03, 2004 - 7:23 am UTC

you are assuming

a) rownum=1 will always get sent to the function first
b) that the function is called ONCE and only ONCE per row produced (called twice with rownum=1 could be fatal)

I cannot think of a reason why it could not be -- that is the problem :)

(i still don't see no "chance")

j., November 05, 2004 - 11:44 am UTC

as for assumption a:
wouldn't in fact (and in general) a value of 1 for rownum get sent *first* to the function in case rownum appears only once within the query, as parameter sent to the function?

i've tried to disprove this by:

create table Test
as
select Owner, RowNo
from (
select Counter.GetValue( Owner, RowNum ) RowNo, o.*
from DBA_Objects o
where Object_Type = 'FUNCTION'
order by Created /*wouldn't work with 7.3*/
)

alter table Test add constraint TestPK primary key( Owner, RowNo ) ;

... but failed with success ...

but this in turn doesn't proof the opposite of course :o(
can you please show me the only case needed to achieve this?

as for assumption b:
to avoid problems with that one could provide several additional parameters to the function in order to increment the counter only if their values *change*.

would calling the function twice with rownum value of 1 be a problem at all? couldn't that happen to *one* row only - the only one with that particular rownum - given that the abovementioned constraints on how to apply rownum and function call to the query are not violated?

Tom Kyte
November 05, 2004 - 5:26 pm UTC

this is one of those cases where "there is no obvious reason why it shouldn't work". probably will.

Just cannot prove it.

calling the function twice with rownum=1 would be bad depending on when rownum=1 was called.

j., November 06, 2004 - 10:07 am UTC

seems to be the very first time there is neither a sample that shows how it fails nor any link to documentation supporting this. but the slight move from "it won't work" to "it probably will" (which is what i "believe") is not sufficient to apply the "solution" to the real life case ...

tom, thank you very much for the interesting points you gave me.

Tom Kyte
November 06, 2004 - 11:04 am UTC

it probably will falls into the same category as "no reason it shouldn't work" meaning "i would not rely on it"

in 7.3 the only reliable way I know is to do it in the CLIENT software, period.


It might well work in your specific case, but a simple change in the plan may well make it "not work". You asked -- I answered.

go back and re-read the thread here -- i never said "it won't work", in fact days ago I said "It might work, but there is no promise that it will."

I have not made a slight move either direction here, I've been pretty consistent.

j., November 06, 2004 - 6:16 pm UTC

let me just thank you once again for your very valuable advice.

webdb to htmldb

reader, November 09, 2004 - 4:26 pm UTC

Tom,

To settle a dispute with a co-worker, is it fair to say that the application development functionality of webDB was included in or merged into HTMLDB? A simple "Yes" will suffice.





Tom Kyte
November 09, 2004 - 7:24 pm UTC

it is not so simple :)

it is safe to say that if you could it in webdb - you can certainly do it and a lot more in htmldb.


It would be fair to say that webdb and htmldb are "step brothers" of eachother.




owarepl: Database Authentication to protect your regular OS files

Vitaliy, March 15, 2006 - 2:39 am UTC

Is this still possible with current mod_plsql design?

"Use the Database Authentication to protect your regular OS files as well as database data?"

REFERENCE:
</code> http://asktom.oracle.com/~tkyte/owarepl/doc/ <code>

Thank you,
-Vitaliy

Tom Kyte
March 15, 2006 - 4:37 pm UTC

Yes, as you can return lob locators from your plsql routine and mod_plsql will stream them back to the client.

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



lob locators

Vitaliy, April 28, 2006 - 12:22 am UTC

Tom,

But this implies that the files will have to located on a file-system accessible by the database ... not exactly the same as the original solution that you had developed.

Regards,
-Vitaliy

Tom Kyte
April 28, 2006 - 2:00 am UTC

correct, this one is far superior to what owarepl did (that code is 11 years old now)

Stateless and legacy code

Andrew Markiewicz, August 28, 2006 - 6:12 pm UTC

Tom,
(A bit of context before the questions.)
Currently, our shop is in the process of moving from Oracle 9.2 db running Oracle Forms 6i in character mode (hey, they worked) to GUI forms running on OAS 10. The buzz since ODTUG in June is "thick database" aka database-centric design. Our intention is to continue running on Forms since we require a rich UI and create some web applications where applicable. However, for new designs we would like to employ the database-centric design so that the UI can be changed if needed (if that's even feasible).

Since we are running Forms with a dedicated server, we can keep state. In fact, many legacy packages we have require a stateful environment since they populate plsql tables with session info or use them as lookups, etc.

My questions arise about how to organize and write the plsql packages with the database-centric web approach, but also use legacy plsql package code that may require state.

1) If we start to write new plsql packages for Forms apps that put the BL (and sometimes even app logic) in the db, so that the form is really just a shell, how reasonable would it be to convert that to a web app if necessary? (The new code would not rely on state.)

2) Is the database-centric methodology applicable to Forms? We have code that bases blocks on package procedures and that works well, but those packages are geared toward a direct mapping of Forms block functionality (slct, ins, upd, del, lock). When I read about db-centric designs, the packages seem to be more organized by business function (OO like in philosophy).

3) Should plsql packages be coded that require state? Suppose I am writing packages A,B, and C today that rely on package state. If other programmers create packages that call my package not knowing it relies on state and then somewhere down the line it is used in a web application, wouldn't the application potentially break or functionality be compromised? In short, can we safely design packages to use state in plsql if there is a chance our code might be called by a stateless web app?

4) Can legacy stateful plsql code be used in web apps or would we need to rewrite as stateless versions?

Thanks for your time.


Tom Kyte
August 29, 2006 - 6:45 am UTC

1) pretty easy, you just described what I had with version 1 of asktom. The htmldb developers "re-skinned it" very quickly because I had APIs to do the business and another set of packages to interact with the end users

2) It is not "natural" for forms, forms is very much table/block driven.

3) if you want them to be "web-able", you won't maintain a state in plsql. You would/could use global application context's for small bits of state, or real database tables for a session state.

4) probably would need in depth review to see what would and what would not work on a case by case basis.

Passing Session Id

Chris, September 28, 2006 - 10:57 am UTC

Tom,
You say you genarate a unique session id using a sequence, etc. for each session then use that to basically index into a table that holds all "state" information. All of that is understandable.

My question is how do you pass that id from the client regardless of what is selected? Are you generating that id the first time it is needed and then embedding it in every possible link the se could click on? Hence every hyperlink, form submission, etc. would have that session id already in it from the code that was generated in the database?

If so, this sounds like a sound approach but I don't like the sequence number as the session id as it would appear in the URL and some wiseguy could change his URL by adding one to that number and basically grab someone else's session. I hought I recalled a built-in that gave you a unique session id but don't remember what it was.

Tom Kyte
September 29, 2006 - 7:44 am UTC

or, cookie....


but you need not use anything as guessable as a sequence. I don't.

Session ending

Chris, October 16, 2006 - 1:44 pm UTC

Just curious. How do you handle getting rid of sessions? Is there any sort of event that can be captured to know when the user has ended a session by closing the browser? Right now I have a database job set up that runs every 20 minutes end expires sessions that have had no activity for more than 20 minutes. It would be nice if I could expire the sessions immediately instead of waiting for the job to come along without putting any client side code (javascript) in the page to try to handle that.

ASP handled this with it's Session events, and I was wondering if there was a similar concept in PSP. I can't even imagine how it would be implemented so I wasn't sure where to look in the docs to try to find it.

Tom Kyte
October 16, 2006 - 5:44 pm UTC

we handle it with database events :) - eg: dbms_job

If you use a tool like APEX (apex.oracle.com), the concept of a 'session' does exist (in a psp, you are inventing the session concept) and APEX deals with it (with its own jobs...)

mod_plsql and Apache 2.X

Michael, July 25, 2007 - 10:36 am UTC

Do you have insight on when if ever mod_plsql will be available on Apache 2.X? I cannot find any Oracle directives on when it will happen. I realize that in 11G the HTTP Server will be inside Oracle and APEX 3.X will be utilized to use that instance rather than a external HTTP Server if my information is correct. But for instance if Oracle Application Server is installed with Apache 2.X, the customer should be able to use their features without installing Apache 1.3.X to use mod_plsql. Any insight or links would be helpful.

session

A reader, July 10, 2009 - 1:59 am UTC


mod_plsql

A reader, September 30, 2009 - 4:34 pm UTC

Tom:

When you run mod_plsql in stateful mode, does this mean that when user A requests a page you have allocaetd a dedicated connection from the connection pool available between mod_plsql and database>

I thought stateless meant the HTTP rotocol between brwoser and http server but it seems it also applies to connection between mod_plsql and database.
Tom Kyte
October 07, 2009 - 9:04 am UTC

http://docs.oracle.com/docs/cd/A97338_01/doc/apache.13/a83590/configur.htm


...
Create a Stateful Session?


Choose Yes to preserve the database package/session state for each database request. Choose No to reset it after each request. For mod_plsql, this parameter must be set to No.
.........

mod_plsql

A reader, October 01, 2009 - 7:42 am UTC

Tom:

When you run mod_plsql in stateful mode, does this mean that when user A requests a page you have allocated a dedicated connection from the connection pool available between mod_plsql and database and you use that oracle session id to identify the user.

I thought stateless meant the HTTP rotocol between browser and http server but it seems it also applies to connection between mod_plsql and database.

Also, even if you run in stateful mode, does it hae to be a unique oracle DB login account per user or you can still use with one shared account for eveyone.

Single User Multiple Sessions

Derek (another Aussie), February 26, 2010 - 12:22 am UTC

This is about stateless sessions and the unique session_id with reference to user_id.

Emanating from the unique session_id is the inability to know if a user is logged on more than once at the same time. I can see that from a security point of view session_id should not be able to be found.

I have a custom authentication (user_id, password) returning booleannbut let APEX handle all the rest.

Q1) Is there a package,function, view or table in APEX that can be checked to see if a user_id already has an active session (one that hasn't been purged yet).

Thanks
Derek

Shifting Business Logic to database

Gaurav, March 21, 2011 - 3:17 am UTC

Tom
We have here an order processing system for major European client. The OLTP database size is of the order of 10 TB, with some billion-rows tables.

The system is accessed around the world from more than 300 locations and is accessed by more than 15000 end-users (8000-10000 concurrent). Some features are
* End user response times between 2.5 and 5 seconds, depending on transaction complexity
* Peak transaction rates of up to 100 transactions per second (736.000.000 transactions / year)
* The application front end is written in VC++. The application logic resides in the back-end using C++/Pro*C++ code along with Tuxedo, MQ Series Middleware as well as Oracle 11g R1 database.
*All database access use static embedded SQL only; all database updates through auto-generated CRUD objects.
* All back-end application components are controlled by Tuxedo.
* Back-end application COMPLETELY STATELESS; all data are in the Oracle database and only there
* Front-end application is simple and contains no business logic, only syntactical validation - all business/data validation takes place on the back-end.
* A set of about 3000 highly-tuned SQL statements are used for the entire application.These statements, accessing more than 400 database tables, are explained daily against production-like database.

With all these, the system has acquired immense complexity, which increases with every release. Performance issues are becoming more and more difficult to catch.

One feature of the database is that .. there are NO stored objects. No packages, no procedures. All the database access happens through Pro*C++ programs using static SQL. The application logic is stored in C++ programs.

Looking at the complexity, I was wondering if it'd be worthwhile (from the point of view of manageability etc) to shift to a database-centric model with the application logic (or part of it) being stored in the back-end in highly-tuned packages/procedures. I quoted from your book "Effective Oracle by design" where you mention
<Quote>
In my answer, I explained the overwhelming benefits of stored procedures. I pointed out how they can help the DBA tune the applications (without having to dive into the application). I emphasized that stored procedures are a great security mechanism, a performance enhancement tool, and an awesome programming device. I advised the DBA to permit the use of stored procedures. I suggested that developers could do as much, if not more, damage by putting bad SQL in their client applications. Furthermore, I pointed out that if the SQL were in stored procedures, at least the DBA could reasonably inspect it and help to tune it and manage it.
<Unquote>

However, people here say that since the present environment is based on a stateless architecture, and it has been performing well so far, such a change is unwarranted and it's likely, in fact, to have an adverse performance impact.

I'd like to know your opinions on this, especially as far as performance is concerned.

Tom Kyte
March 21, 2011 - 10:02 am UTC

can you explain to me why this is relevant:

people here say that since the present environment is based on a
stateless architecture


why does that matter? Your packages do not have to maintain a state (and probably shouldn't unless you are a client server application)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library