Home » RDBMS Server » Performance Tuning » Increased LOGFILE did not increase performance ( Toad 9.5, Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production, Windows Server 2003 R2)
Increased LOGFILE did not increase performance [message #562838] Mon, 06 August 2012 05:07 Go to next message
RedFux
Messages: 5
Registered: August 2012
Junior Member
Hello, I'm new to oracle and I'm planning to decrease the time taken to execute data by managing the redo log file but I'm kinda stuck in some aspect :
> Why is my OPTIMAL_LOGFILE_SIZE is showing NULL ?
> I'm trying to resize the LOGFILE capacity from 100M to 200M and I'm also adding 1 more LOG GROUP with 200M capacity too but turned out that didn't decrease my execution time. How's that possible?

Thank you very much for the attention guys.
Re: Increased LOGFILE did not increase performance [message #562842 is a reply to message #562838] Mon, 06 August 2012 05:17 Go to previous messageGo to next message
John Watson
Messages: 8207
Registered: January 2010
Location: Global Village
Senior Member
First, why do you think that the performance issue is related to the size of you online logfile groups?
Re: Increased LOGFILE did not increase performance [message #562844 is a reply to message #562842] Mon, 06 August 2012 05:28 Go to previous messageGo to next message
RedFux
Messages: 5
Registered: August 2012
Junior Member
John Watson wrote on Mon, 06 August 2012 03:17
First, why do you think that the performance issue is related to the size of you online logfile groups?

Hmm I'm just following the Oracle Performance Tuning Guide suggestion in the section "Sizing Redo Files" which it told that "Generally, larger
redo log files provide better performance" --> I quote it from the Guide

To be honest, I'm a newcomer in the Database Configuration Tuning and I'm an Intern in my current company.
If turns out I made a wrong move, then I'd would be happy if you could give me the right direction. Very Happy
Re: Increased LOGFILE did not increase performance [message #562846 is a reply to message #562844] Mon, 06 August 2012 05:34 Go to previous messageGo to next message
John Watson
Messages: 8207
Registered: January 2010
Location: Global Village
Senior Member
Given your circumstances, the best advice I can give is on the tuning methodology. The only thing end users care about is "wall clock time". How long it takes to get a response back when they do somethnig; how long a report takes to generate; how long a batch job takes to complete. No user ever telephoned the DBA to say "the online logfiles are too small". You need to identify a problem, then apply the scientific method (Google that, if you are not familiar with it) to identify possible solutions.
All the best with your studies. Start here, http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm
Re: Increased LOGFILE did not increase performance [message #562848 is a reply to message #562846] Mon, 06 August 2012 05:41 Go to previous messageGo to next message
RedFux
Messages: 5
Registered: August 2012
Junior Member
John Watson wrote on Mon, 06 August 2012 03:34
Given your circumstances, the best advice I can give is on the tuning methodology. The only thing end users care about is "wall clock time". How long it takes to get a response back when they do somethnig; how long a report takes to generate; how long a batch job takes to complete. No user ever telephoned the DBA to say "the online logfiles are too small". You need to identify a problem, then apply the scientific method (Google that, if you are not familiar with it) to identify possible solutions.
All the best with your studies.


Yes, that's true sir,, my company only cared about the execution time.
But, actually this Intern is supposed to be the contents of my Thesis too so I've to show them why my method is worked.
So sir, is sizing the redo logs does or doesn't affect the performance? I'm going to try any techniques that considered to increase performance and I'm starting from this one.
Thank you btw for the quick reply Very Happy
Re: Increased LOGFILE did not increase performance [message #562850 is a reply to message #562848] Mon, 06 August 2012 05:47 Go to previous messageGo to next message
John Watson
Messages: 8207
Registered: January 2010
Location: Global Village
Senior Member
Quote:
So sir, is sizing the redo logs does or doesn't affect the performance?
I have no idea. It would depend on the problem. As I said, you need to follow the scientific method: observe some facts; construct a hypothesis that might explain them; test the hypothesis to see if it is correct; repeat as necessary. You appear to have picked one course of action without thinking about whether it is relevant. You might want to consider what your thesis supervisor will think of such an unscientific approach. Seriously.
I would think that your best course of action right now is to start a proper investigation into the problem (about which you have said nothing) and then open a new topic, with full details of your observations. Then perhaps someone will suggest possible solutions for you to test.
Be a scientist, man!
Re: Increased LOGFILE did not increase performance [message #562851 is a reply to message #562844] Mon, 06 August 2012 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66940
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should not start by the end.
The first point is: "Tune the business rules", did you do it?

Regards
Michel
Re: Increased LOGFILE did not increase performance [message #563012 is a reply to message #562838] Tue, 07 August 2012 16:49 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
You can see the time waited by the log switches with the following query.
ENWEBP1P > select
  2     event                         c1,
  3     total_waits                   c2,
  4     time_waited / 1000             c3,
  5     total_timeouts                c4,
  6     average_wait    /1000          c5
  7  from
  8     sys.v_$system_event
  9  where
 10     upper(event) like '%LOG%SWITCH%'
 11  order by c3 desc;

                                                                          Average
Event                                   Total                     Total      Wait
Name                                    Waits Seconds_Wait     Timeouts (in secs)
------------------------------ -------------- ------------ ------------ ---------
log switch/archive                        255          174          150      .681
log file switch completion                103            0            0      .005
Re: Increased LOGFILE did not increase performance [message #563014 is a reply to message #563012] Tue, 07 August 2012 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
>Yes, that's true sir, my company only cared about the execution time.
If you succeeded in reducing REDO log file switch time to ZERO,
by how many seconds would elapsed time be reduced over a 24 hour period? PLEASE quantify.
Re: Increased LOGFILE did not increase performance [message #563016 is a reply to message #563014] Tue, 07 August 2012 21:56 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
We had 59 log swaps today when I inserted several million rows into the database.
There were 59 log switch completions that averaged .016 seconds each for a total of 1 second
of wait time. If I had doubled the redo logs from 100 megabytes to 200 megabytes each, there
would have 29 or 30 log switch completions averaging .016 seconds each for a total of 1/2 second
wait. So increasing the redo logs would only gain me a total of 1/2 second of response time per day.
ENWEBPD > @average_wait_of_log_switches_swaps.sql

                                                                          Average
Event                                   Total                     Total      Wait
Name                                    Waits Seconds_Wait     Timeouts (in secs)
------------------------------ -------------- ------------ ------------ ---------
log file switch (checkpoint in             31            1            0      .041
log file switch completion                 59            1            0      .016
log file switch (private stran              1            0            0      .002

ENWEBPD > get average_wait_of_log_switches_swaps.sql
  1  set pages 50
  2  set lines 100
  3  column c1 heading 'Event|Name'             format a30
  4  column c2 heading 'Total|Waits'            format 9,999,999,999
  5  column c3 heading 'Seconds_Wait'        format 999,999,999
  6  column c4 heading 'Total|Timeouts'         format 999,999,999
  7  column c5 heading 'Average|Wait|(in secs)' format 99.999
  8  select
  9     event                         c1,
 10     total_waits                   c2,
 11     time_waited / 1000             c3,
 12     total_timeouts                c4,
 13     average_wait    /1000          c5
 14  from
 15     sys.v_$system_event
 16  where
 17     upper(event) like '%LOG%SWITCH%'
 18* order by c3 desc
ENWEBPD > select to_char(first_time,'MM-DD-RRRR HH24:MI:SS') "log_swaps_today"
  2  from v$loghist where first_time > sysdate-1;

log_swaps_today
-------------------
08-07-2012 12:40:30
08-07-2012 12:41:04
08-07-2012 12:41:41
08-07-2012 12:42:11
08-07-2012 12:42:41
08-07-2012 12:47:59
08-07-2012 12:49:11
08-07-2012 12:49:41
08-07-2012 12:53:42
08-07-2012 12:54:48
08-07-2012 12:55:19
08-07-2012 12:56:00
08-07-2012 12:56:27
08-07-2012 12:56:44
08-07-2012 12:57:00
08-07-2012 12:57:19
08-07-2012 12:57:31
08-07-2012 12:57:45
08-07-2012 12:57:57
08-07-2012 12:58:10
08-07-2012 12:58:20
08-07-2012 12:58:33
08-07-2012 12:59:21
08-07-2012 12:59:38
08-07-2012 12:59:52
08-07-2012 13:00:09
08-07-2012 13:00:26
08-07-2012 13:00:40
08-07-2012 13:00:55
08-07-2012 13:01:15
08-07-2012 13:01:38
08-07-2012 13:02:04
08-07-2012 13:02:30
08-07-2012 13:02:52
08-07-2012 14:53:43
08-07-2012 14:53:52
08-07-2012 14:54:06
08-07-2012 14:54:18
08-07-2012 14:54:31
08-07-2012 14:54:42
08-07-2012 14:54:54
08-07-2012 14:55:07
08-07-2012 14:55:19
08-07-2012 14:55:35
08-07-2012 14:55:48
08-07-2012 14:56:00
08-07-2012 15:08:05
08-07-2012 15:08:21
08-07-2012 15:08:37
08-07-2012 15:08:56
08-07-2012 15:09:10
08-07-2012 15:09:26
08-07-2012 15:09:40
08-07-2012 15:09:55
08-07-2012 15:10:10
08-07-2012 15:10:25
08-07-2012 15:10:40
08-07-2012 15:10:55
08-07-2012 17:24:08

59 rows selected.
Re: Increased LOGFILE did not increase performance [message #563017 is a reply to message #563016] Tue, 07 August 2012 22:10 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
In the following query you can see where all the time is being spent
in the your database. In the following database, the log switches take
up only .15% of the time for "log file switch completion" and .21% of the
time for "log file switch (checkpoint incomplete)". "log buffer space"
takes 3.05% of the time and this can be reduced by increasing the log_buffer
size. Since the majority of time is taken up by cpu and disk I/O, this
database can see an increase in performace by better caching, better
indexing and application tuning like Michel said. Also 8.17% of the
time is being used by "enq: RO - fast object reuse" and that can be
avoided by not creating so many temporary tables in the applications.
ENWEBPD > SELECT
  2     wait_class,
  3     NAME,
  4     ROUND (time_secs, 2) time_secs,
  5     ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
  6  FROM
  7     (SELECT
  8        n.wait_class,
  9        e.event NAME,
 10        e.time_waited / 100 time_secs
 11      FROM
 12        v$system_event e,
 13        v$event_name n
 14      WHERE
 15         n.NAME = e.event AND n.wait_class <> 'Idle'
 16      AND
 17         time_waited > 0
 18      UNION
 19      SELECT
 20        'CPU',
 21        'server CPU',
 22        SUM (VALUE / 1000000) time_secs
 23      FROM
 24        v$sys_time_model
 25      WHERE
 26        stat_name IN ('background cpu time', 'DB CPU'))
 27  ORDER BY
 28     time_secs;

WAIT_CLASS           NAME                                     TIME_SECS    PCT
-------------------- ------------------------------ ------------------- ------
User I/O             db file single write                           .01    .00
Other                latch: call allocation                         .01    .00
Other                latch: cache buffer handles                    .01    .00
Concurrency          buffer busy waits                              .01    .00
Network              SQL*Net more data from client                  .01    .00
Other                Streams AQ: qmn coordinator wa                 .01    .00
                     iting for slave to start

Network              SQL*Net message to client                      .02    .00
Configuration        log file switch (private stran                 .02    .00
                     d flush incomplete)

User I/O             direct path write temp                         .02    .00
Concurrency          latch: cache buffers chains                    .03    .00
Concurrency          cursor: pin S                                  .03    .00
Other                latch: enqueue hash chains                     .03    .00
User I/O             Parameter File I/O                             .03    .00
Other                LGWR wait for redo copy                        .03    .00
Configuration        checkpoint completed                           .04    .00
User I/O             direct path read temp                          .04    .00
Other                asynch descriptor resize                       .05    .00
System I/O           control file single write                      .11    .00
Concurrency          latch: row cache objects                       .11    .00
Concurrency          cursor: pin S wait on X                        .12    .00
Concurrency          library cache pin                              .13    .00
Concurrency          row cache lock                                 .14    .00
Other                latch free                                     .14    .00
Concurrency          library cache: mutex X                         .15    .00
Other                parallel recovery coord wait f                 .17    .00
                     or reply

Other                PX Deq: Signal ACK EXT                         .20    .00
Other                PX Deq: Test for credit                        .20    .00
Administrative       switch logfile command                         .21    .00
User I/O             direct path sync                               .24    .00
Other                ADR block file write                           .27    .00
Configuration        undo segment extension                         .29    .00
User I/O             direct path write                              .35    .01
Concurrency          latch: shared pool                             .36    .01
System I/O           log file sequential read                       .40    .01
Application          SQL*Net break/reset to client                  .47    .01
Other                ADR file lock                                  .49    .01
Other                parallel recovery slave wait f                 .55    .01
                     or change

Other                enq: PR - contention                           .58    .01
Other                reliable message                               .62    .01
Other                PX Deq: Slave Session Stats                    .67    .01
System I/O           RMAN backup & recovery I/O                     .70    .01
Other                parallel recovery coord send b                 .83    .01
                     locked

Administrative       JS coord start wait                           1.01    .02
System I/O           log file single write                         1.08    .02
User I/O             read by other session                         2.50    .04
Other                control file heartbeat                        4.01    .06
Other                ADR block file read                           4.07    .07
System I/O           recovery read                                 5.79    .09
Other                enq: CF - contention                          6.38    .10
Concurrency          library cache load lock                       6.66    .11
Other                enq: CR - block range reuse ck                8.67    .14
                     pt

Configuration        log file switch completion                    9.48    .15
Configuration        log file switch (checkpoint in               12.82    .21
                     complete)

User I/O             db file parallel read                        15.68    .25
Other                rdbms ipc reply                              18.37    .29
User I/O             Disk file operations I/O                     20.36    .33
Commit               log file sync                                37.45    .60
System I/O           control file sequential read                 37.69    .60
User I/O             Log file init write                          53.91    .86
User I/O             direct path read                             54.11    .87
System I/O           control file parallel write                 117.15   1.88
User I/O             db file scattered read                      162.71   2.61
Configuration        log buffer space                            189.97   3.05
Concurrency          os thread startup                           194.21   3.11
User I/O             db file sequential read                     275.78   4.42
Application          enq: RO - fast object reuse                 509.08   8.17
System I/O           log file parallel write                     531.20   8.52
System I/O           db file parallel write                      637.61  10.23
CPU                  server CPU                                3,308.16  53.06
Re: Increased LOGFILE did not increase performance [message #563048 is a reply to message #563017] Wed, 08 August 2012 01:49 Go to previous messageGo to next message
John Watson
Messages: 8207
Registered: January 2010
Location: Global Village
Senior Member
Alan, your query could be made a bit more accurate. You are looking at V$SYSTEM_EVENT.TIME_WAITED, which is waiting time for background processes and for user sessions. Who cares if a background process hangs? Querying TIME_WAITED_FG will specifically pull out the time lost by sessions, which is what matters. Would that help with the analysis?
Re: Increased LOGFILE did not increase performance [message #563117 is a reply to message #563048] Wed, 08 August 2012 15:46 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Thanks John,

Definite improvement,
ENDOCP1P > @v$sys_time_model_top_Waits.sql

WAIT_CLASS           NAME                                     TIME_SECS    PCT
-------------------- ------------------------------ ------------------- ------
Other                log switch/archive                           50.35    .72
Other                enq: WF - contention                         52.31    .74
System I/O           log file parallel write                      68.07    .97
User I/O             db file scattered read                       78.82   1.12
User I/O             direct path read                             82.38   1.17
Other                reliable message                             92.55   1.32
System I/O           control file sequential read                106.26   1.51
User I/O             db file sequential read                     277.91   3.95
System I/O           RMAN backup & recovery I/O                2,672.65  38.00
CPU                  server CPU                                3,019.11  42.92

ENDOCP1P > list
  1  SELECT
  2     wait_class,
  3     NAME,
  4     ROUND (time_secs, 2) time_secs,
  5     ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
  6  FROM
  7     (SELECT
  8        n.wait_class,
  9        e.event NAME,
 10        e.time_waited / 100 time_secs
 11      FROM
 12        v$system_event e,
 13        v$event_name n
 14      WHERE
 15         n.NAME = e.event AND n.wait_class <> 'Idle'
 16      AND
 17         time_waited > 0
 18      UNION
 19      SELECT
 20        'CPU',
 21        'server CPU',
 22        SUM (VALUE / 1000000) time_secs
 23      FROM
 24        v$sys_time_model
 25      WHERE
 26        stat_name IN ('background cpu time', 'DB CPU'))
 27  ORDER BY
 28*    time_secs

ENDOCP1P > @v$sys_time_model_top_Waits_fore_ground.sql

WAIT_CLASS           NAME                                     TIME_SECS    PCT
-------------------- ------------------------------ ------------------- ------
User I/O             db file scattered read                       26.23    .75
System I/O           control file sequential read                 31.14    .89
Other                log switch/archive                           50.35   1.44
User I/O             direct path read                             82.38   2.35
User I/O             db file sequential read                      91.68   2.62
CPU                  server CPU                                3,019.97  86.20

ENDOCP1P > list
  1  SELECT
  2     wait_class,
  3     NAME,
  4     ROUND (time_secs, 2) time_secs,
  5     ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
  6  FROM
  7     (SELECT
  8        n.wait_class,
  9        e.event NAME,
 10        e.time_waited_fg / 100 time_secs
 11      FROM
 12        v$system_event e,
 13        v$event_name n
 14      WHERE
 15         n.NAME = e.event AND n.wait_class <> 'Idle'
 16      AND
 17         time_waited_fg > 0
 18      UNION
 19      SELECT
 20        'CPU',
 21        'server CPU',
 22        SUM (VALUE / 1000000) time_secs
 23      FROM
 24        v$sys_time_model
 25      WHERE
 26        stat_name IN ('background cpu time', 'DB CPU'))
 27  ORDER BY
 28*    time_secs

Previous Topic: Cannot find TraceFile for SESSION
Next Topic: get AWR report data
Goto Forum:
  


Current Time: Thu Apr 09 03:29:46 CDT 2020