| Join Our List |
 |
|
|
| Ask DBSpecialists: Log File Switch Completion wait event |
|
You're invited to submit your Oracle-related questions to us at askdbspecialists11@dbspecialists.com. Please include your name and telephone number in your email.
This month's question comes to us from Lindy from Louisville, KY:I often see the log file switch completion wait
event during active times of the day. Do I need to worry about this wait event?
James F. Koopmann of the Database Specialists team responds:
The definition of log file switch completion is the amount
of time users and applications must wait for a log switch to complete. As users
make changes to data, Oracle keeps a running log of these changes within a set
of structures that make up what Oracle calls "redo". These Redo Logs within
Oracle are the critical link in the recovery process as they record the hanges
to data and provide the input to produce sets of archived logs that are used
for point in time recovery. As redo logs fill to capacity and are readied for
archival a log switch occurs that relinquishes control from the current redo
log to the next available. A single log switch entails stopping further
acceptance of generated of redo, reading & writing to control files for
redo log status and SCN, (System Change Number), information, flushing memory,
and the closing & opening of individual redo log files. Since there are
many other resources in use when log switching occurs it is imperative that we
are not fooled by high resource usage on these erroneous resources.
There
is no record in Oracle that pinpoints the amount of time it takes for a log
file switch. It is only experienced by users or applications and is recorded as
a wait event at a session or system level. Other than individual sessions
generating more redo through improper SQL applications this wait event can only
be reduced by attacking it from a full system perspective. Often the
performance impact of switching too many redo logs is the culprit. A good rule
of thumb is to switch log files every 10-20 minutes. The following SQL
statement will show how often log switching is occurring every hour. You need
only increase your redo log sizes to hold more redo and this will reduce the
amount of log switching.
set linesize 132
col day for a10
select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999')
"00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999')
"01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999')
"02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999')
"03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999')
"04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999')
"05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999')
"06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999')
"07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999')
"08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999')
"09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999')
"10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999')
"11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999')
"12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999')
"13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999')
"14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999')
"15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999')
"16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999')
"17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999')
"18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999')
"19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999')
"20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999')
"21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999')
"22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999')
"23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD'
As
data is manipulated and altered within an Oracle database, time must be spent
recording and archiving these changes through redo log generation. The time
spent recording and archiving can be reduced if proper consideration is given
to the number of log switches occurring, the size of the redo logs, how they
are cyclically used, and taking a look at supporting structures such as the
control file or init.ora settings. Remembering to check periodically and around
major application changes will safeguard your database from wasting time and
waiting for a log file switch.
|
Adding Knowledge: Getting a little help
|
|
|
James F. Koopmann
Everyone needs a little help now and then. If you have never used Oracle's help facility, venture with me and find new ways you can provide benefit to your users of SQL*Plus through this simple interface.
Invoking Help
The SQL*Plus facility has a very simple syntax.
HELP [topic]
In order to get the appropriate help information, you need only issue the HELP or '?' command on the command line within SQL*Plus followed by the command or subject matter you need help on. If you don't know what you want or just want to look what is available then for the subject matter supply the global 'TOPICS' or 'INDEX' keyword and get a listing of everything available for HELP on.
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER
COLUMN INPUT SAVE WHENEVER
COMPUTE LIST SET
CONNECT PASSWORD SHOW
Beefing Up HELP
I can't quite remember the version of Oracle, but it used to be that the HELP facility also contained quite a few topics that dealt with the DDL, DML, and other administration commands. Anyway, I really miss some of this simple text based help and have since decided to start porting some of the more widely used command syntax back into my HELP facility on the databases that I use. There is really nothing you can't put into this HELP facility and you should seriously start thinking of giving your customers or developers as others call them the ability to quickly access information that they need without having to page through manuals.
Adding HELP content is really just a four step process.
1. Load you new content through a SQL script.
2. Rebuild the 'TOPICS' information
3. Rebuild the 'INDEX' information
4. Validate your information
Follow along with the simple formula.
Load Help
The content I wish to add is for the CREATE TABLESPACE command for version 9.2. All that is required is the TOPIC ('CREATE TABLESPACE'), a unique line number, and the text for that line. After you build your topic I would suggest that you save it into it's own file, in my case I called it create_tablespace.sql and then if you ever need to rebuild or port this it is very simple.
Remember you must be connected as the SYSTEM user.
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 1, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 2, ' CREATE TABLESPACE (9.2)');
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 3, ' -----------------------');
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 4, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 5, 'CREATE [UNDO] TABLESPACE tablespace ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 6, '[DATAFILE datafile_tempfile_spec [, datafile_tempfile_spec]... ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 7, '[{ MINIMUM EXTENT integer [ K | M ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 8, ' | BLOCKSIZE integer [K] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE', 9, ' | logging_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',10, ' | FORCE LOGGING ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',11, ' | DEFAULT [data_segment_compression] storage_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',12, ' | { ONLINE | OFFLINE } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',13, ' | { PERMANENT | TEMPORARY } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',14, ' | extent_management_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',15, ' | segment_management_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',16, ' } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',17, ' [ MINIMUM EXTENT integer [ K | M ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',18, ' | BLOCKSIZE integer [K] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',19, ' | logging_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',20, ' | FORCE LOGGING ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',21, ' | DEFAULT [data_segment_compression] storage_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',22, ' | { ONLINE | OFFLINE } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',23, ' | { PERMANENT | TEMPORARY } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',24, ' | extent_management_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',25, ' | segment_management_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',26, ' ]... ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',27, '] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',28, '; ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',29, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',30, 'datafile_tempfile_spec ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',31, '[''filename''] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',32, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',33, 'autoextend_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',34, 'AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',35, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',36, 'logging_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',37, '{LOGGING | NOLOGGING} ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',38, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',39, 'data_segment_compression ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',40, '{ COMPRESS | NOCOMPRESS } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',41, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',42, 'storage_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',43, 'STORAGE ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',44, '( { INITIAL integer [ K | M ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',45, ' | NEXT integer [ K | M ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',46, ' | MINEXTENTS integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',47, ' | MAXEXTENTS { integer | UNLIMITED } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',48, ' | PCTINCREASE integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',49, ' | FREELISTS integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',50, ' | FREELIST GROUPS integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',51, ' | OPTIMAL [ integer [ K | M ] | NULL ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',52, ' | BUFFER_POOL { KEEP | RECYCLE | DEFAULT } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',53, ' } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',54, ' [ INITIAL integer [ K | M ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',55, ' | NEXT integer [ K | M ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',56, ' | MINEXTENTS integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',57, ' | MAXEXTENTS { integer | UNLIMITED } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',58, ' | PCTINCREASE integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',59, ' | FREELISTS integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',60, ' | FREELIST GROUPS integer ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',61, ' | OPTIMAL [ integer [ K | M ] | NULL ] ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',62, ' | BUFFER_POOL { KEEP | RECYCLE | DEFAULT } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',63, ' ]... ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',64, ') ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',65, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',66, 'extent_management_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',67, 'EXTENT MANAGEMENT ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',68, '{ DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM [SIZE integer [ K | M ]]] } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',69, NULL);
INSERT INTO HELP VALUES ('CREATE TABLESPACE',70, 'segment_management_clause ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',71, 'SEGMENT SPACE MANAGEMENT { MANUAL | AUTO } ');
INSERT INTO HELP VALUES ('CREATE TABLESPACE',72, NULL);
COMMIT;
Rebuild TOPICS
In order for the 'HELP TOPICS' command to work and show the new content you just added you must rebuild the whole TOPIC content. Here are the four simple steps.
1.Rebuild the HELP_TEMP_VIEW
CREATE OR REPLACE VIEW HELP_TEMP_VIEW (TOPIC) AS
SELECT DISTINCT UPPER(TOPIC) FROM HELP;
2.Delete the TOPICS content
DELETE FROM HELP WHERE TOPIC = 'TOPICS';
3.Rebuild the TOPICS content
INSERT INTO HELP
SELECT 'TOPICS', ROWNUM + 10, TOPIC FROM HELP_TEMP_VIEW;
4.Commit your work
COMMIT;
Rebuild INDEX
Unfortunately the rebuilding of the INDEX content is not quite as easy as the TOPICS and you may wish to not provide this since the same information is available through the TOPICS content which we just rebuilt. If you do want to keep up on changing the INDEX content you should first cut and paste the content from the helpus.sql script into a file called index_content.sql and maintain all your future changes there.
Here is my current index_content.sql that I have after I have added the 'CREATE TABLESPACE' content. Just notice that all I have added is and 'EXTRA' area at the bottom from lines 19 thru 22. You may call it anything you wish or rearrange the lines so that you can convey to your users in the best method for them.
INSERT INTO HELP VALUES ('INDEX', 1, NULL);
INSERT INTO HELP VALUES ('INDEX', 2, 'Enter Help [topic] for help.');
INSERT INTO HELP VALUES ('INDEX', 3, NULL);
INSERT INTO HELP VALUES ('INDEX', 4, ' @ COPY PAUSE SHUTDOWN');
INSERT INTO HELP VALUES ('INDEX', 5, ' @@ DEFINE PRINT SPOOL');
INSERT INTO HELP VALUES ('INDEX', 6, ' / DEL PROMPT SQLPLUS');
INSERT INTO HELP VALUES ('INDEX', 7, ' ACCEPT DESCRIBE QUIT START');
INSERT INTO HELP VALUES ('INDEX', 8, ' APPEND DISCONNECT RECOVER STARTUP');
INSERT INTO HELP VALUES ('INDEX', 9, ' ARCHIVE LOG EDIT REMARK STORE');
INSERT INTO HELP VALUES ('INDEX', 10, ' ATTRIBUTE EXECUTE REPFOOTER TIMING');
INSERT INTO HELP VALUES ('INDEX', 11, ' BREAK EXIT REPHEADER TTITLE');
INSERT INTO HELP VALUES ('INDEX', 12, ' BTITLE GET RESERVED WORDS (SQL) UNDEFINE');
INSERT INTO HELP VALUES ('INDEX', 13, ' CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE');
INSERT INTO HELP VALUES ('INDEX', 14, ' CLEAR HOST RUN WHENEVER OSERROR');
INSERT INTO HELP VALUES ('INDEX', 15, ' COLUMN INPUT SAVE WHENEVER SQLERROR');
INSERT INTO HELP VALUES ('INDEX', 16, ' COMPUTE LIST SET');
INSERT INTO HELP VALUES ('INDEX', 17, ' CONNECT PASSWORD SHOW');
INSERT INTO HELP VALUES ('INDEX', 18, NULL);
INSERT INTO HELP VALUES ('INDEX', 19, ' EXTRA');
INSERT INTO HELP VALUES ('INDEX', 20, ' -----');
INSERT INTO HELP VALUES ('INDEX', 21, ' CREATE TABLESPACE' (9.2)');
INSERT INTO HELP VALUES ('INDEX', 22, NULL);
COMMIT;
After you have built your index_content.sql script you are now ready to install it. This is a simple two step process.
1.Delete the old INDEX content
DELETE FROM HELP WHERE TOPIC = 'INDEX';
2.Rebuild the INDEX content through index_content.sql script.
@index_content.sql
3.Commit Your work
COMMIT:
Validate Help
After everything is loaded and rebuilt, it is time to make sure it all works. Here is our newly built content.
SQL> HELP create tablespace
CREATE TABLESPACE (9.2)
---------------------------------------
CREATE [UNDO] TABLESPACE tablespace
[DATAFILE datafile_tempfile_spec [, datafile_tempfile_spec]... ]
[{ MINIMUM EXTENT integer [ K | M ]
| BLOCKSIZE integer [K]
| logging_clause
| FORCE LOGGING
| DEFAULT [data_segment_compression] storage_clause
| { ONLINE | OFFLINE }
| { PERMANENT | TEMPORARY }
| extent_management_clause
| segment_management_clause
}
[ MINIMUM EXTENT integer [ K | M ]
| BLOCKSIZE integer [K]
| logging_clause
| FORCE LOGGING
| DEFAULT [data_segment_compression] storage_clause
| { ONLINE | OFFLINE }
| { PERMANENT | TEMPORARY }
| extent_management_clause
| segment_management_clause
]...
]
;
datafile_tempfile_spec
['filename'] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause]
autoextend_clause
AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] }
logging_clause
{LOGGING | NOLOGGING}
data_segment_compression
{ COMPRESS | NOCOMPRESS }
storage_clause
STORAGE
( { INITIAL integer [ K | M ]
| NEXT integer [ K | M ]
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
}
[ INITIAL integer [ K | M ]
| NEXT integer [ K | M ]
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
]...
)
extent_management_clause
EXTENT MANAGEMENT
{ DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM [SIZE integer [ K | M ]]] }
segment_management_clause
SEGMENT SPACE MANAGEMENT { MANUAL | AUTO }
Having to work in command line all day and not having access to any manuals is sometimes the way we must work. If you are like me and forget the exact syntax to some of the commands the HELP facility is a very quick and easy way to store the information you need so that it is readily available. Not only that, you can also supply you development staff with other insightful information about the use of the Oracle database that they would not normally have documentation on. For instance just think of the possibilities for you to develop a HELP ENV command that described the current machine or settings needed for a particular process. The possibilities are endless. |
|
|
| Conference Roundup |
|
RMOUG Training Days The
Rocky Mountain Oracle Users Group puts on a wonderful two-day conference every year
in downtown Denver. Most of the Database Specialists, Inc. team will be
in attendance and would enjoy seeing you there. Save the dates of
February 13 & 14, 2008 and come say hello. Visit the RMOUG site for
more details at http://www.rmoug.org. NoCOUG Winter Conference The
Northern California Oracle Users Group will hold its Winter Conference
on February 19, 2008. The conference will be at the Oracle Conference
Center in Redwood Shores, and there are still opportunities for you to
speak. Please share the knowledge. Get updates at http://www.nocoug.org. |
|
|
Call Database Specialists, Inc. when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Visit our website for no-cost resources, white papers, conference presentations and handy scripts. We can help increase your uptime, improve performance, minimize risk, and reduce
costs.
Sincerely,
David Wollf
Database Specialists, Inc. |
|
|
