process become slower

hi all,

we are using oracle 8.1.7.
front end developer 2000
windows 2K server

we are using commercial software developed on above
plateform.

our backend process are executing very slow and system becomes
very slow when this process are run.

we noticed, when we install oracle fresh and import data then
these process works very fast. A process which take 30-60 minutes normally,
takes only 2-5 minutes after fresh new install.
after few months it becomes again slow.

where/what is the problem kindly guide me



regards

trace it, see where your time is being taken

Faster after importing data?

I'm wondering if you do any maintenance on your tables/indexes like reorg fragmented tables and keep fresh stats

Import is probably doing that for you

try analyze tables compute statistics for the optimizer to work properly:)

Quote:

---

Originally Posted by jennifer2007

try analyze tables compute statistics for the optimizer to work properly:)

---

we are doing analyze tables/indexes,,
import ,
but it does not work.

it work faster only when oracle is fresh installed

did u analyze it before the imports:confused:
u better follow what davey said:) runs the statspack....i myself doesnt know
how to run and interpret its result....so i run the following scripts courtesy
of papa rey :D

monitoring performance scripts
========================
Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",
hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;

Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;

Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;

Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;

Top 10 by Sharable Memory:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;

Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10
;

col "Machine Name" format a15
col Program format a20
col Username format a15
col "OS User" format a15
prompt SESSION and PROCESS information
select a.sid "SID" , a.serial# "Serial #",a.status "Status" ,a.username "Username", a.osuser "OS User",b.spid "OS Process ID",a.machine "Machine Name", a.program "Program",a.logon_time "Logon Time" from v$session a, v$process b where a.paddr=b.addr order by a.sid
/

prompt SESSION INFO - HIGH I/O AND CPU CONSUMPTION
select cpu.sid "SID", cpu.serial# "Serial #", cpu.username "User Name",reads.value "Physical reads", writes.value "Physical writes",decode(connection_time.value,0,0,cpu.value/connection_time.value) "CPU Utilization %" from (select a.sid sid, a.username username, b.name, c.value value,a.serial# serial# from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='CPU used by this session' ) cpu,(select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='session connect time' )connection_time,(select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='physical reads' ) reads,(select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='physical writes' ) writes where cpu.sid = connection_time.sid and connection_time.sid=reads.sid and reads.sid = writes.sid
/


prompt TOP 10 SQL -RESOURCE EXTENSIVE
select 0 - GETM "Gets", ROWC "Rows" , EXES "Number of Executions" , STMT "SQL Statement" from (select distinct GETM, ROWC, EXES, STMT from ( select substr(SQL_TEXT, 1, 512) STMT,sum(ROWS_PROCESSED) ROWC , sum(EXECUTIONS) EXES, sum(0 - BUFFER_GETS) GETM from V$SQL group by substr(SQL_TEXT, 1, 512) ) ) where ROWNUM <= 10 ORDER BY EXES DESC
/

col Name format a15
prompt TOP 10 TABLE -RESOURCE EXTENSIVE
select CTYP "Command Type", OBJ "Name", 0 - EXEM "Number of Executions" , GETS "Buffer Gets" , ROWP "Rows Processed" from (select distinct EXEM, CTYP, OBJ, GETS, ROWP from ( select decode (S.COMMAND_TYPE , 2, 'Insert into ' , 3,'Select from ', 6, 'Update of ' , 7, 'Delete from ' , 26,'Lock of ') CTYP , O.OWNER || '.' || O.NAME OBJ , sum(0 - S.EXECUTIONS) EXEM , sum(S.BUFFER_GETS) GETS , sum(S.ROWS_PROCESSED) ROWP from V$SQL S , V$OBJECT_DEPENDENCY D , V$DB_OBJECT_CACHE O where S.COMMAND_TYPE in (2,3,6,7,26)and D.FROM_ADDRESS = S.ADDRESS and D.TO_OWNER = O.OWNER and D.TO_NAME= O.NAME and O.TYPE = 'TABLE' group by S.COMMAND_TYPE , O.OWNER , O.NAME ) ) where ROWNUM <= 10
/

estimating time for longops SQL:
SQL> select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING,
2 ELAPSED_SECONDS from v$session_longops where username='SCOTT';

Sorry, I maybe missed something but what you mean by "backend process"?
C program? PL/SQL? .... give some more details
I support Davey you should trace and/or profile it somehow and find our what's slow. Is it SQL or the algorythm itself etc.
Just changing "something" will not help, or if it helps, then berter start paying lotto :-)

Regards

Install Statspacks ... monitor for 15 minutes ... check top SQL and top wait events etc.

Quote:

---

Originally Posted by PSoni

...it work faster only when oracle is fresh installed

---

Just double-checking... are you saying that just by reinstalling Oracle binaries -pointing to the same old database, you are getting amazingly better performance?

Quote:

---

Originally Posted by PAVB

Just double-checking... are you saying that just by reinstalling Oracle binaries -pointing to the same old database, you are getting amazingly better performance?

---

1. we exported our erp database
2. we uninstall oracle 8.1.7 (on w2k plateform)
3. we install oracle 8.1.7 again
4. we import our erp database


we have developed some procedure like salary_process,
vendor_analysis,excis_rg1 process
we are saying above procedure as backend process.

If these are PL/SQL procedures, you maigh want to check what dbms_profiler can do for you

Regards
Boris

As said by SlimDave, Statspack is the good tool to analyze the databases whose performance degrades over the time period.

Quote:

---

Originally Posted by PSoni

1. we exported our erp database
2. we uninstall oracle 8.1.7 (on w2k plateform)
3. we install oracle 8.1.7 again
4. we import our erp database

---

Don't take it wrong, I trust what you say but it's really hard to believe reinstallation of Oracle binaries has something to do with getting better performance.

As per previous postings who have said you already tried exp/imp with no success.

Lets try to take Oracle binaries out of the equation. Could you please do the following two tests?

++ Test #1

1-- Shutdown your database
2-- Reboot your server
3-- Startup your database
4-- Check performance... how is it now?
IF performance is better either Oracle services recycle or O/S
recycling fixed it.

If Test #1 does not does the trick...try test #2...

++ Test #2

1-- Export your erp database (just in case)
2-- Uninstall Oracle software
3-- Reinstall Oracle software
4-- Create your erp database pointing to the old datafiles
5-- DO NOT IMPORT the data, lets Oracle work with the old data
6-- Startup your database and check performance... how is it now?

Quote:

---

Originally Posted by PAVB

...
4-- Create your erp database pointing to the old datafiles
5-- DO NOT IMPORT the data, lets Oracle work with the old data
...

---

I think you'll find that you can't do this.

Anyway, run statspack.

Why not Dave? You mean like that cannot be done, or you doubt the poster can do it

I agree Dave also.

Quote:

---

Originally Posted by Bore

Why not Dave? You mean like that cannot be done, or you doubt the poster can do it

---

I agree with Dave....it cannot be done..ie creating a new database with datafiles from another database...
There is something like datafile header markers that are only recognized by that database that they come from. In other words you cannot take datafiles out of database A and plug into database B.

Strange that someone has to uninstall Oracle each time to do a refresh....whoaa!!!
I think what is is happening is, they are not running stats.
On exporting/importing Oracle implicitly does an analyze for them..that's why is good immediately after each new import. Now after months of deletes,updates,inserts the stats fall behind and it begins to crawl.
Check that your stats are updated?

Quote:

---

Originally Posted by slimdave

I think you'll find that you can't do this.

---

Sorry Dave but you are wrong, I do this all the time, at least once a week.

This is our preferred method to refresh UAT environments.

:cool:

Quote:

---

Originally Posted by PAVB

Sorry Dave but you are wrong, I do this all the time, at least once a week.

This is our preferred method to refresh UAT environments.

:cool:

---

Oh OK, you're not just talking about the data files but the control files etc. as well .. ie the whole database. Gotcha.

Still, it sounds more like a benefit doe to a reorganisation based on the import than anything else. Maybe a row migration problem, but statspack ought to identify that.

No problem at all Dave.

Original poster insists he has exp/imp the whole database with no performance gains therefore, I wanted to take "Oracle reinstall" out of the equation once and forever :) That's the reason I've asked for these two tests ;)

Quote:

---

Originally Posted by PAVB

Don't take it wrong, I trust what you say but it's really hard to believe reinstallation of Oracle binaries has something to do with getting better performance.

As per previous postings who have said you already tried exp/imp with no success.

Lets try to take Oracle binaries out of the equation. Could you please do the following two tests?

++ Test #1

1-- Shutdown your database
2-- Reboot your server
3-- Startup your database
4-- Check performance... how is it now?
IF performance is better either Oracle services recycle or O/S
recycling fixed it.

If Test #1 does not does the trick...try test #2...

++ Test #2

1-- Export your erp database (just in case)
2-- Uninstall Oracle software
3-- Reinstall Oracle software
4-- Create your erp database pointing to the old datafiles
5-- DO NOT IMPORT the data, lets Oracle work with the old data
6-- Startup your database and check performance... how is it now?

---

Test#1 we are doing this
but no gains

Test#2 i will do and let you update.

bye-the-way what will it indicate if it success?

I'll agree with the current analysis.

Just a question to the poster..
may I know what is the memory of your win2k server? and how much is allocated for your oracle db?

Quote:

---

Originally Posted by PSoni

Test#2 i will do and let you update.
bye-the-way what will it indicate if it success?

---

man... if reinstalling Oracle binaries solves performance issues by itself I'll have to seriously think in looking for a different kind of job :)

My bet is it won't work meaning, you are gonna experience the very same bad performance afterwards, then we take uninstall/reinstall out of the equation and we can focus on SGA/PGA and data reorganization.

Quote:

---

Originally Posted by reydp

I'll agree with the current analysis.

Just a question to the poster..
may I know what is the memory of your win2k server? and how much is allocated for your oracle db?

---

Total Memory of W2k server is 2GB

Memory for oracle is as under

Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB

Total SGA = 1346.76 MB

Sort Area Size = 2 MB
Concurrent users 150-2000

Quote:

---

Originally Posted by PSoni

bye-the-way what will it indicate if it success?

---

Then do the same thing but run statspack and find out what is making the difference.

Quote:

---

Originally Posted by PSoni

Total Memory of W2k server is 2GB

Memory for oracle is as under

Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB

Total SGA = 1346.76 MB

Sort Area Size = 2 MB
Concurrent users 150-2000

---

Hi,
This may be due to memory stack issue. Every thread occupies 1MB and uses upto 200kb initially. If you calculate 2000conns*1MB = No free memory. More over Windows default max mem setting for single process is 2GB. Due to this no new connection cud be established once all the 2GB occupied.
U can try changing the orastack of oracle binaries to 500kb. (2000 conn*500kb) more mem can be left to use.

Is that shared pool sized correctly?

Quote:

---

Originally Posted by Kamkan

U can try changing the orastack of oracle binaries to 500kb. (2000 conn*500kb) more mem can be left to use.

---

Yes, you could guess that it is some obscure memory issue and run another funky experiment, or you could run statspack and actually find out what is making the system slow.

Quote:

---

Originally Posted by slimdave

Yes, you could guess that it is some obscure memory issue and run another funky experiment, or you could run statspack and actually find out what is making the system slow.

---

Hi Dave,

With due respect to you Dave 'coz you're one the icon here in this forum.

Yes, running statspack will surely help. But will it shows how many client connection were rejected by the LISTENER? How much paging/swapping were O.S. utilized just to accomodate db server's requirements?

With 2G of Memory.
Minus O.S. process
Minus ORACLE SGA
Minus ORACLE PGA for 500-2000 concurrent connection
Minus etc..

2G is surely not enough obviously.

Guys,
That goes out of control. Memory is low, DB is fragmented, stats are not generated etc. there can be thousands of reasons for that. Statspack or trace or whatever, but the poster should analyze the situation, come to the root of the problem and solve it. Just playing around will hardly help

Regards
Boris

Boris,
I'm just wondering what is there for you not to understand?

The poster gives this config:
-----------------------------------------
Total Memory of W2k server is 2GB

Memory for oracle is as under

Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB

Total SGA = 1346.76 MB

Sort Area Size = 2 MB
Concurrent users 150-2000
---------------------------------------

WINDOWS 2000 SERVER O.S. = 100MB->150MB
SGA=1.35G
PGA=(150->2000)*2M(#2m here is minimum) = 300MB -> 4G at least
+ HASH_AREA_SIZE
+ JAVA_POOL_SIZE
+ etc
--------------------------------------
+ O.S. overheads
+ ORACLE overheads
==============================
Variables with figures alone assuming with minimum value will have a sum of:
100mb + 1.35G + 300mb = 1.75Gbytes
==============================
if you follow what WINDOWS recommendation(specially if it is not highmem) which is:
you should not allocate more than 50% of the physical memory for ORACLE SGA.
then, this is more than 80% contiguous memory already consumed with minimal concurrent connection.

I really don't believe that it will goes up to 2000 concurrent connection,
I mean this is simply not true and therefore impossible.

It is extremely remote that a binary re-install is the solution. The fact that the OP is doing an export/import is the giveaway that db is getting a nice clean re-org.

I think the memory allocation is lop sided:
the poster gives this config:
-----------------------------------------
Total Memory of W2k server is 2GB

Memory for oracle:
Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB
------------------------------
I'll probably be flamed for this recommendation:
1. Why is the Shared pool so large?
2. Are you using Java inside the DB? if no then remove the java pool..
You buffer cache is 1/10 you shared pool and 1/20th your overall system memory.
3. Why is the Large Pool so big?
---- My suggestions as a baseline for memory -----------
Buffer Cache: 800 MB
Shared Pool: 200 MB
Java Pool: 0 (but you may need some just to start up).
Large Pool: 80 MB

Totally concur with Davey that stats pack analysis is the only way to identify and solve the issue at hand. (stats pack will probably indicate a poor hit ratio and likely a large amount of lock contention due to the size of the shared pool).

You got the answer on the first person that posted. Trace it and see where the time is being spent. Or as Mr. XM says, run statspack and see what's going on.

I feel a little more secure in my job today because of this thread.

Hi Jeff,
I hope this is not for me...... :)... because if it's for me then I need to re-evaluate my career in oracle... since it's from Jeff's comment(the super-G) :)
----------------------------------------
I feel a little more secure in my job today because of this thread.
----------------------------------------

I based my answer from the posters db configuration info. And I even calculate using only the minimum values.
If he configured it correctly based on what its system's requirement, then I'm very positive that it will still points to MEMORY issue.

for ixion reply, just like me, you only give your assumption.

why have a large shared pool? Maybe the application is huge
why have a java pool and at that size? Maybe the application used Java with lots of java components
why have a small db buffer cache? Maybe the application only need minimal config for minimal dat/output requirements.
why is the large pool so big? Maybe the system is configured as SHARED so it needs large pool.

If the poster post the factual info then I still point it to Memory issues. Again all of this are also assumption but with higher chances.

BTW,
I refer the O.S. overheads, aside from Services running but not needed in the system, etc., are like /WINNT in drive C and VirtualMem in drive C also, or Oracle Binaries in drive C, and worst datafiles are in drive C also. inappropriate mirroring/raid configs.
Oracle overheads are like sessions that are abnormally terminated by end-users, being WINDOWZ-having memory leaks during releasing of resources.

-----------------
Aside from running statspack,
May I suggest to the user, to take a good look also the log for the LISTENER and SQLNET. And the Memory consumption thru Task manager during the time when he claim that the server is getting slower.


Regards.

Quote:

---

Hi Jeff,
I hope this is not for me...... .

---

Rey, why you think it's for you? ;-)

come on boris I'm sure you have picked this up.
I just want to make sure that at least I'm in the NORM of being a DBAwtzawatza. :)

Coz if this this guys(like Jeff) will give me something to think twice. Then I'll be very upset, and may made me to get off from this role...