process become slower
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: process become slower

  1. #1
    Join Date
    Feb 2001
    Posts
    125

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace it, see where your time is being taken

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Feb 2007
    Posts
    212
    try analyze tables compute statistics for the optimizer to work properly

  5. #5
    Join Date
    Feb 2001
    Posts
    125
    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

  6. #6
    Join Date
    Feb 2007
    Posts
    212
    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 08:45 AM.

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Install Statspacks ... monitor for 15 minutes ... check top SQL and top wait events etc.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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?
    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.

  10. #10
    Join Date
    Feb 2001
    Posts
    125
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width