Re: PUBLIC:Re: database slowing down after few hours

From: Grinalds <grinalds_at_konts.lv>
Date: 1998/02/09
Message-ID: <34DF3DF9.D9F00AB6@konts.lv>

May be you need to look at Revealnet- December's Tip of the Month

 On a recent project we had a problem where performance would start acceptable at the beginning of the day and by mid-day would be totally unacceptable. Investigation showed that the third party application that ran on top of the Oracle 7.2.3 database was generating ad hoc SQL with out using bind variables. This generation of ad hoc SQL and non-use of bind variables was resulting in proliferation of non-reusable code fragments in the shared pool, one user had over 90 shared pool segments assigned for queries that differed only by the selection parameter (for example "where last_name='SMITH'" instead of "where last_name='JONES'"). This proliferation of multiple nearly identical SQL statements meant that for each query issued the time to scan the shared pool for identical statements was increasing for each non-reusable statement generated.

A purely empirical perfromance test ( the one used by the Q diagnostic tool from Savant, Corporation) showed a performance indicator of 600+ when 30-40 concurrent users where using the system. This corresponded to a query time for a simple select taking nearly 30 seconds. A flush of the shared pool resulting in a more than three-fold drop in the performance indicator to around 150 and the 30 second query returned in less than a second.

It was determined that an automatic procedure was needed to monitor the shared pool and flush it when it reached 60-70% of capacity. The following procedue was created:

create or replace procedure flush_it as
cursor get_share is
 select sum(sharable_mem) from
  system.sql_summary;
cursor get_var is
 select value from v$sga where name like 'Var%'; cursor get_time is
 select sysdate from dual;
  todays_date date;
  mem_ratio number;
share_mem number;
variable_mem number;
cur integer;
sql_com varchar2(60);
row_proc number;
begin
 open get_share;
 open get_var;
 fetch get_share into share_mem;
dbms_output.put_line('share_mem: '||to_char(share_mem));  fetch get_var into variable_mem;

dbms_output.put_line('variable_mem: '||to_char(variable_mem));
 mem_ratio:=share_mem/variable_mem;
dbms_output.put_line(to_char(mem_ratio));

 if mem_ratio>0.3 Then
  cur:=dbms_sql.open_cursor;
  sql_com:='alter system flush shared_pool';   dbms_sql.parse(cur,sql_com,dbms_sql.v7);   row_proc:=dbms_sql.execute(cur);
  dbms_sql.close_cursor(cur);
  open get_time;
  fetch get_time into todays_date;
  insert into system.dba_running_stats values

      ('Flush of Shared Pool',1,35,todays_date,0);   commit;
 end if;
end;

This procedure was then loaded into the job queue and scheduled to run every hour using the following commands:

variable x number;
execute dbms_job.submit(x,'flush_it;',sysdate,'sysdate+1/24');

Since instituting the automated flush our perfromance indicator has never peaked above 250 and rarely varies above 200 event with 90+ concurrent users

Grinalds

Martin J. Malley wrote:

> > >
> > > We have a lan with one NT4 Server and a few Win95 workstations. We use
> > > TCP/IP and Netbeui, CentreCOM 3624TR 10Base-T HUB/repeater and UTP
 cable.
> > > The server is running : Oracle7 Workgroup Server 7.3.2.2.1 and a
 database
> > > written in Visual Basic.
> > > The Win95 workstation is running : database clients software, Oracle7
 16bit
> > > ODBC Driver 2.0.3.1.1 and Oracle SQL*Net Client 2.3.2.1.4.
> > > First, the database works fine but after a few hours the responses are
> > > getting very slow. When using TNGPing : every 7th ping i receive
 something
> > > of 3400 or 9700 ms ! (regular Ping gives 1-10ms tho...)
> > > The server has enough memory and the processor activity is normal.
> > > The strange thing is : when i REBOOT the server, everything is back to
> > > normal ! TNGPing gives 0 - 60ms. The database is working fine. After a
 few
> > > hours ... everything slows down again!
>
> You might be running into the 7.3 EPC problem. Ensure EPC_DISABLED is TRUE
> for the instance and for SQLNET. Oracle has a bulleting on this topic.

--

Received on Mon Feb 09 1998 - 00:00:00 CST