Re: Oracle Apache Session

mokwilson_at_yahoo.com (Wilson) wrote in message news:<bc7d0514.0402091240.5277f0c2_at_posting.google.com>...
> I am currently running Oracle 8i with Apache setup in a 4-CPUs Windows
> 2000 Server. Users on their workstations connect to Oracle through
> Internet Explorer which accesses Apache's DAD object and eventually
> the PL/SQL packages in Oracle.
>
> My application is mainly built by differenct PL/SQL packages in
> Oracle. The packages build the html page based on the contents in the
> database. In one part of my application, the users simply click a
> search button and the application will return a list of records and
> displays through Internet Explorer. The results are displayed in a
> new Popup windows using basic html and jsp scripts.
>
> The problem is that for each login through Apache to Oracle in
> Internet Explorer, I can only trigger two of the four CPUs to work
> when I click on the serach button. When I click the button for third
> time, the new popup window is displayed but it waits until one of the
> two previous windows finishes the SQL. Thus, I always cannot get all
> CPUs to utilize at the same time.
>
> I already checked CPU_COUNT in V$PARAMETER table, and the value is
> four. If I run the query (the SQL statement behind the search button)
> in sqlplus using four sessions at the same time, all four CPUs are
> utilized.
>
> Also, I checked the profiles of my user accounts and there is no limit
> on CPU_PER_SESSION...etc.
>
> If I open two Internet Explorers and login for two sessions, I can
> open up to four windows to process the Search SQL at the same time (4
> CPUs working at the same time).
>
> Based on my find, Oracle is actually able to utilize all CPUs in the
> system. However, when I work from the Internet Explorer and Apache, I
> will get the utilizatoin limit problem. I wonder if there are any
> parameters that limit my Oracle Apache session.

That parameter would be a fundamental lack of understanding of how Oracle works.
There is no guarantee at all each new session will get a different CPU.
Most likely your queries are unscalable and hog Oracle, so that would be the reason why you have to wait for a query to complete. Check out whether your queries executes full table scans, my bet is they all do.
Now try to learn Oracle and rebuild your application from scratch.

Sybrand Bakker
Senior Oracle DBA Received on Tue Feb 10 2004 - 09:38:29 CET