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  |
 |
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 #562846 is a reply to message #562844] |
Mon, 06 August 2012 05:34  |
John Watson
Messages: 8960
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 |
|
RedFux
Messages: 5
Registered: August 2012
|
Junior Member |
|
|
John Watson wrote on Mon, 06 August 2012 03:34Given 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 
|
|
|
|
| Re: Increased LOGFILE did not increase performance [message #562850 is a reply to message #562848] |
Mon, 06 August 2012 05:47 |
John Watson
Messages: 8960
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 #563016 is a reply to message #563014] |
Tue, 07 August 2012 21:56 |
 |
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 |
|
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 |
John Watson
Messages: 8960
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 16:08:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
