This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 3, 2013 6:57 PM by Maran Viswarayar Go to original post RSS
  • 15. Re: Performance issue
    sb92075 Guru
    Currently Being Moderated
    966949 wrote:
    Hi,

    Many redo logs are generating here. How can I avoid this ?

    Please let me know.
    DML generates REDO.

    reduce DML & REDO is reduced.
  • 16. Re: Performance issue
    969952 Newbie
    Currently Being Moderated
    In these views I have only SELECT statement. Please let me know how it will generate REDO logs without using INSERT/UPDATE/DELETE/MERGE. The views are retrieving the data only.

    So in this case how can I reduce the Redo logs?
  • 17. Re: Performance issue
    969952 Newbie
    Currently Being Moderated
    Hi,

    Please find the below result.
    select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='REFRESH_1_JOB' order by actual_start_date desc; ( here you can see the column names and the O/P of that column)
    
    LOG_ID           ---          65405
    LOG_DATE      ---          01-JAN-13 08.31.34.077000000 PM -05:00         
    OWNER           --           SPD       
    JOB_NAME     ----         REFRESH_1_JOB
    JOB_SUBNAME   ---      (null) 
    STATUS               ----  SUCCEEDED
    ERROR#                 ---- 0
    REQ_START_DATE   --- 01-JAN-13 01.30.12.200000000 AM -04:00
    ACTUAL_START_DATE ---  01-JAN-13 01.30.12.215000000 AM -04:00    
    RUN_DURATION           ---  20:1:22.0
    INSTANCE_ID            ----- 1
    SESSION_ID             ---- 240,9885
    SLAVE_PID              ---- 3164
    CPU_USED               ---- 19:55:0.0
    DESTINATION          ---- (null)
    ADDITIONAL_INFO   ----- (null)
    Please have a look and suggest me for further process.
  • 18. Re: Performance issue
    jgarry Guru
    Currently Being Moderated
    Some ideas here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:616795500346622064
  • 19. Re: Performance issue
    EdStevens Guru
    Currently Being Moderated
    966949 wrote:
    In these views I have only SELECT statement. Please let me know how it will generate REDO logs without using INSERT/UPDATE/DELETE/MERGE. The views are retrieving the data only.
    MV's aren't just views, in the sense of being nothing but a pre-compiled SELECT. The SELECT in the MV definition drives very real DML.
    So in this case how can I reduce the Redo logs?
  • 20. Re: Performance issue
    969952 Newbie
    Currently Being Moderated
    I am just observing MV's here.

    I have 8 Scheduled jobs over here. I am not sure what are operations running for those scheduled jobs. please let me know how to reduce the redo log generation... because of this it's effecting performance.

    Please have a look into the below query for redo log information.
    SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
    TO_CHAR(FIRST_TIME,'HH24') HOUR,
    COUNT(*) TOTAL
    FROM V$LOG_HISTORY
    GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
    ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')  desc ;
    
    O/P :
    
          Day                HOUR          Total 
    2013-01-01     22            3
    2013-01-01     21            1
    2013-01-01     20          13
    2013-01-01     19                 1
    2013-01-01     17            1
    2013-01-01     14            1
    2013-01-01     12            1
    2013-01-01     10            1
    2013-01-01     08            1
    2013-01-01     07        100
    2013-01-01     06            1
    2013-01-01     05            1
    2013-01-01     03            1
    2013-01-01     02            7
    2013-01-01     00            6
    2013-01-02     11            1
    2013-01-02     08          32
    2013-01-02     07          70
    2013-01-02     06            1
    2013-01-02     04            1
    2013-01-02     02            7
    2013-01-02     01            1
    2013-01-02     00            6  
    Please have a look and give me some idea to improve the performance and not to generate those many log files as well

    Thanks.
  • 21. Re: Performance issue
    EdStevens Guru
    Currently Being Moderated
    966949 wrote:
    I am just observing MV's here.

    I have 8 Scheduled jobs over here. I am not sure what are operations running for those scheduled jobs. please let me know how to reduce the redo log generation... because of this it's effecting performance.

    Please have a look into the below query for redo log information.
    SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
    TO_CHAR(FIRST_TIME,'HH24') HOUR,
    COUNT(*) TOTAL
    FROM V$LOG_HISTORY
    GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
    ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')  desc ;
    
    O/P :
    
    Day                HOUR          Total 
    2013-01-01     22            3
    2013-01-01     21            1
    2013-01-01     20          13
    2013-01-01     19                 1
    2013-01-01     17            1
    2013-01-01     14            1
    2013-01-01     12            1
    2013-01-01     10            1
    2013-01-01     08            1
    2013-01-01     07        100
    2013-01-01     06            1
    2013-01-01     05            1
    2013-01-01     03            1
    2013-01-01     02            7
    2013-01-01     00            6
    2013-01-02     11            1
    2013-01-02     08          32
    2013-01-02     07          70
    2013-01-02     06            1
    2013-01-02     04            1
    2013-01-02     02            7
    2013-01-02     01            1
    2013-01-02     00            6  
    Please have a look and give me some idea to improve the performance and not to generate those many log files as well

    Thanks.
    I see only 3 to 5 one-hour periods where redo generation might be a concern, and still no proof that the redo generation IS the cause of unspecified performance issues. What is going on during those obvious peaks? When does your MV refresh occur?
  • 22. Re: Performance issue
    969952 Newbie
    Currently Being Moderated
    As mentioned in the earlier thread we have one scheduled job , that particular job is using MV( am not aware how can I see the the specified scheduled job " what are the operations it is doing? ")

    But after after executing the above command for all dates... got the highest value as 149 (total) per a day... like this every day it is generating the log file with more than 120 as total.

    So I would like to reduce that whole total. Please let me know the below two things.

    1. How can I see , specified Scheduled job, what are the operations it is doing ?

    2. If the scheduled job is generating more redo log files how can I reduce it ?

    3. or any other way to increase the performance?

    Thanks.
  • 23. Re: Performance issue
    EdStevens Guru
    Currently Being Moderated
    966949 wrote:
    As mentioned in the earlier thread we have one scheduled job , that particular job is using MV( am not aware how can I see the the specified scheduled job " what are the operations it is doing? ")

    But after after executing the above command for all dates... got the highest value as 149 (total) per a day... like this every day it is generating the log file with more than 120 as total.

    So I would like to reduce that whole total. Please let me know the below two things.

    1. How can I see , specified Scheduled job, what are the operations it is doing ?
    your search for what a job is doing begins with where that job is scheduled. DBA_SCHEDULER_JOBS is the first place I'd look.
    >
    2. If the scheduled job is generating more redo log files how can I reduce it ?
    There is no silver bullet. You have to analyze what the job is doing, and how it is doing it. Look for inefficiencies in the process - insert/update/delete activity that is unnecessary.
    3. or any other way to increase the performance?
    Get your head out of MV's and redo logs. This entire thread is built on an assumption that has yet to be validated. Your approach so far is "READY, FIRE, AIM". Exactly WHERE are you seeing a "performance issue"? Exactly what process is not completing in an acceptable amount of time? You need to start there with no assumptions as to root cause.
    >
    Thanks.
  • 24. Re: Performance issue
    969952 Newbie
    Currently Being Moderated
    Hi,

    I am sorry actually am not a DBA. I am just trying to find out the root cause. As per the discussion with the DBA team came to know that the Scheduled job is unnecessarily creating Redo log files. So want to reduce Redo log generations.

    For this I request you people to " Let me know the process to reduce the Redo Log file generation.

    Thanks.
  • 25. Re: Performance issue
    EdStevens Guru
    Currently Being Moderated
    966949 wrote:
    Hi,

    I am sorry actually am not a DBA. I am just trying to find out the root cause. As per the discussion with the DBA team came to know that the Scheduled job is unnecessarily creating Redo log files. So want to reduce Redo log generations.
    Ouch. I hate to say it, but if that's the situation, then your DBA team is ducking its responsibilities.


    >
    For this I request you people to " Let me know the process to reduce the Redo Log file generation.
    Ok, let's restate a fundamental: the amount of redo generated is a direct function of the amount of DML being issued. Period. The only thing that will reduce redo generation is to reduce dml. Redo generation in itself is NOT a cause of performance problems. It is merely an indicator of high dml volume that may *or may not* be contributing to a perceived performance problem. And even if you DO decide that it is necessary (for *whatever* reason) to reduce DML, that is going to require an analysis of your application.

    Beyond that, we are shooting in the dark. You have not demonstrated why you even think you have a performance problem to be solved. At the very least you need to be able to state the 'process x' is taking 'n-minutes to complete', and that in turn causes 'xyz business problem.' Even if a process takes longer than you think it should, that in itself *still* does not create a business problem. Suppose I have a job that takes 20 minutes to run, and I think it should take 2 minutes. But it runs in the middle of the night and there is no human and no other process that is ever waiting for it to complete. As far as I'm concerned, I still don't have a performance problem. All of my SLA's are being met.





    Thanks.
  • 26. Re: Performance issue
    Maran Viswarayar Pro
    Currently Being Moderated
    If the process need to be run it has to be run and if generates redo it has to be generated but it has to be archived..Your DBA should do his maintenance activities thats all..


    CPU consumption is different,if you need to reduce the CPU consumtption of you job then probably you need to revisit your procedure.
1 2 Previous Next

Legend

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