process become slower

[jennifer2007]

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';