Memory optimization, help!

Memory optimization, help!

Post by Terry Maguir » Wed, 16 Sep 1998 04:00:00



Hi,

This question is from a newbie so please be patient. I'm using Oracle 7.3.3
and VB5 to run numerous PL/SQL procedures for a financial modeler. The SQL
includes selects, inserts, updates and creates. I 'commit' after each proc.
Here is the problem I'm having:

The full execution time of one of the models is 2 hrs. A scheduler has been
setup in VB5 so that we can run many of these over night. I arrived in one
morning to find my machine nearly smoking! The spec of the machine is:

Dual Pentium Pro 200Mhz
128MB
18 Gigs Disk Space
Windows NT 4.0 Workstation

The virtual memory warning was up saying I was low on resources. I looked
at the mem usage on the task manager and it said 386MB. Looking at the
amount of memory each process was taking up, Oracle took up nearly 200MB!

It seems the memory footprint of Oracle grows during execution. I tried
disconnecting and reconnecting during model runs but this doesn't seem to
work. When I exit the full VB app then memory is handed back.

Is there some command that can be used so that Oracle releases some of this
memory, or is this a problem  with VB?

Regards

Terry Maguire

 
 
 

Memory optimization, help!

Post by Jeroen van Sluisda » Wed, 16 Sep 1998 04:00:00


Hi,

First of all check the SQL-code. If you have it make tracefiles either by
running them through sqlplus and before issue alter session set sql_trace=TRUE
or put the whole db in trace mode or use a tool like SQLLab to search
for extensive SQL. Most of times bad SQL is doing stuff like that.

In the meantime check out diskperformance during execution. Don't know much
about
NT meaning whether it's easy to spot that.

Good luck,

Jeroen


> Hi,

> This question is from a newbie so please be patient. I'm using Oracle 7.3.3
> and VB5 to run numerous PL/SQL procedures for a financial modeler. The SQL
> includes selects, inserts, updates and creates. I 'commit' after each proc.
> Here is the problem I'm having:

> The full execution time of one of the models is 2 hrs. A scheduler has been
> setup in VB5 so that we can run many of these over night. I arrived in one
> morning to find my machine nearly smoking! The spec of the machine is:

> Dual Pentium Pro 200Mhz
> 128MB
> 18 Gigs Disk Space
> Windows NT 4.0 Workstation

> The virtual memory warning was up saying I was low on resources. I looked
> at the mem usage on the task manager and it said 386MB. Looking at the
> amount of memory each process was taking up, Oracle took up nearly 200MB!

> It seems the memory footprint of Oracle grows during execution. I tried
> disconnecting and reconnecting during model runs but this doesn't seem to
> work. When I exit the full VB app then memory is handed back.

> Is there some command that can be used so that Oracle releases some of this
> memory, or is this a problem  with VB?

> Regards

> Terry Maguire



 
 
 

Memory optimization, help!

Post by Reid La » Fri, 18 Sep 1998 04:00:00


Your problem mainly caused by network traffic.  You can try to implement your
code in VB5 to Oracle stored procedure.  I think it may be faster.

> Hi,

> This question is from a newbie so please be patient. I'm using Oracle 7.3.3
> and VB5 to run numerous PL/SQL procedures for a financial modeler. The SQL
> includes selects, inserts, updates and creates. I 'commit' after each proc.
> Here is the problem I'm having:

> The full execution time of one of the models is 2 hrs. A scheduler has been
> setup in VB5 so that we can run many of these over night. I arrived in one
> morning to find my machine nearly smoking! The spec of the machine is:

> Dual Pentium Pro 200Mhz
> 128MB
> 18 Gigs Disk Space
> Windows NT 4.0 Workstation

> The virtual memory warning was up saying I was low on resources. I looked
> at the mem usage on the task manager and it said 386MB. Looking at the
> amount of memory each process was taking up, Oracle took up nearly 200MB!

> It seems the memory footprint of Oracle grows during execution. I tried
> disconnecting and reconnecting during model runs but this doesn't seem to
> work. When I exit the full VB app then memory is handed back.

> Is there some command that can be used so that Oracle releases some of this
> memory, or is this a problem  with VB?

> Regards

> Terry Maguire


 
 
 

1. Help Help Help Help Help Help Help Help Help Help Help Paleeeeeezzzzzzz!!!!!!!!

I have a problem that I need to solve at work, and thinking that
there is a good chance that someone in this conference/news group
would know how to tackle it, I decided to give this avenue a try...

Here it is;
1. I need to update a table with information from another table.
2. These two tables are 'linked' by their equipment number (eqnum).
3. Table names are PM and EQUIPMENT.
4. The two rows (fields) are of the same type/size and match.
5. The row to update is from the PM table and is called PM1
   (NUM 15.2 NOT NULL)
6. The row the information is from the EQUIPMENT table and is
   called METERREADING (NUM 15.2 NOT NULL)

The way I was thinking about doing this would be using the logic
listed below.  If someone could help me in the syntax and correct
pl/sql-or just sql statments... I would be in your dept... (and
yes I do payoff!!!)

open the EQUIPMENT table
open the PM table
get record 1 from EQUIPMENT
get record 1 from PM
loop
  loop
    if pm.eqnum=equipment.eqnum then
      update pm.pm1 with equipment.meterreading
      goto top of pm table
      exit loop
    else
      if not eof then incr PM table by one
    end if
    if eof then exit loop
  end loop
  if not eof then incr EQUIPMENT table by one
end loop
This might have holes in it (and probably does),
so feel free to suggest away.

Other usefull information that might be needed...
* These tables are insalled on a Oracle v7.1 rdbms.
* This has to 'batch run' nightly.

If there are any other questions that you need to ask
(if there is someone nice enough to help) you can

Thanx alot in advance...

2. Need to synch Daytimer to Outlook

3. ORA-04030: out of process memory when trying to allocate 99232 bytes (callheap,temporary memory)

4. Central Offices

5. HELP: Shared memory and Oracle

6. Connection Refused???

7. Oracle 7 and Powerbuilder Dos Memory Grab Help!

8. Help: cannot retreive the server name

9. Oracle instances sharing shared memory segments - Help!

10. Shared Memory Error when installing Designer 2000, Help!

11. HELP: Forms 4.0 memory problem

12. HELP: MS-ACCESS with ODBC eats up memory

13. Help - Memory Fault(Coredump) when use r20run w/Oracle 7