This discussion is archived
1 2 3 4 Previous Next 46 Replies Latest reply: Jan 6, 2013 7:35 AM by yxes2013 Go to original post RSS
  • 45. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    My apologies again for not being clear.

    Actually the report above is on the DEV server. I just tried to replicate the issue using single test data of AutoInvoice process. I just want to know what is the process flow beeing taken. Well
    I think this is misleading. I do not even know if those sql comes from auto invoice program.

    The situation that I want to replicate is in the PROD server, If lots of AutoInvoice is being run like 20 programs which just contains 50 lines of transactions the server hangs.

    Maybe my gathering of statspack intervals is not good that is what the actual report I gave you is not clear. I will tell the users to try processing autoInvoice again in the PROD server, but
    I think the data is not so big as that of the month-end. But at least its bigger than that of test data I did in dev.

    So can you tell me how many minutes time interval should my statspack run would be? Right now I am scheduling it every hour. Do I schedule it every 30mins? or 20mins?


    Thanks,
  • 46. Re: Redo Logs Sizing
    yxes2013 Newbie
    Currently Being Moderated
    Hi all again,

    I am trying to run sqlplus manually to check queries in the database who have high disk scans, using the following command:
    SELECT * FROM
    (SELECT to_char(sysdate,'DD-MON-YYYY HH:MI')||'~'|| substr(sql_text,1,100)||'~'|| disk_reads||'~'|| executions||'~'|| address FROM V$SQLAREA WHERE disk_reads > 1000 and to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss')>= sysdate-1/24 ORDER BY disk_reads DESC)
    WHERE rownum <= 10 ;
    And I got this output:
    21-DEC-2012 07:09~SELECT * FROM JSCUS.VW_USR_OE_SALES_ORDER_REGISTER WHERE (( upper(trim(:1)) = 'ORDER' AND to_date(OR~937205~3~B4BC16BC                             
    21-DEC-2012 07:09~select HEADER_ID      ,      STATUS      ,      ORDER_NUMBER      ,      CUSTOMER_PO_NUMBER      ,  ~710585~3~A77625D0                             
    21-DEC-2012 07:09~select HEADER_ID      ,      STATUS      ,      ORDER_NUMBER      ,      CUSTOMER_PO_NUMBER      ,  ~708626~3~B3986068                             
    21-DEC-2012 07:09~select HEADER_ID      ,      STATUS      ,      ORDER_NUMBER      ,      CUSTOMER_PO_NUMBER      ,  ~701569~3~B35D1014                             
    21-DEC-2012 07:09~select  TRANSACTION_BRANCH , BRANCH_ADDRESS_1 , BRANCH_ADDRESS_2 , BRANCH_PHONES , BRANCH_FAX , BRAN~625155~1~B4E4EDA0                             
    21-DEC-2012 07:09~declare Errbuf VARCHAR2 ( 256 ) ; Retcode VARCHAR2 ( 256 ) ; BEGIN arp_file . write_log ( 'RAXTRX is~61861~1~AB835DFC                              
    21-DEC-2012 07:09~update usr_in_pick_confirmation set SHIPPING_TRANSACTION='Y' where header_id =' 475112'~32868~1~B2C48E48                                           
    21-DEC-2012 07:09~update usr_in_pick_confirmation set SHIPPING_TRANSACTION='Y' where header_id =' 471777'~32844~1~B0ED3A98                                           
    21-DEC-2012 07:09~update usr_in_pick_confirmation set PICK_REALEASE='Y' where header_id =' 475112'~32782~1~B4191538                                                  
    21-DEC-2012 07:09~update usr_in_pick_confirmation set SHIPPING_TRANSACTION='Y' where header_id =' 474303'~32713~1~A872DAFC                                           
    26-DEC-2012 06:00~ INSERT INTO RA_INTERFACE_ERRORS  (INTERFACE_LINE_ID,   MESSAGE_TEXT,   INVALID_VALUE) SELECT  INTER~48497210~4~B2ED5E78      
    I can see that in the top 1 query, there is (( upper(trim(:1)) which usually disables any index created in this column hence, causing performance issues.
    I can not tell which time this has occured, and I can not understand why is this not showing in statspack? Or do you know how to locate this program in statspack?

    Thanks a lot,
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points