-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
try analyze tables compute statistics for the optimizer to work properly
-
 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
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
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';
Last edited by jennifer2007; 04-18-2007 at 07:45 AM.
-
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.
-
 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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
 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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|