What is slow RMAN or Media Management Library?
Posted: 09/08/2012 | Author: Levi Pereira | Filed under: 10g R1, 10g R2, 11g R1, 11g R2, AIX, Database, Linux, Operating System, Real Application Clusters, RMAN, Windows |6 CommentsBackup execution time is slow.. where is the problem?
When we perform backup using third party software and backup of database is slow, there always is some uncertain who is causing the slowness.
The Database Administrator (DBA) says: is Media Management Library (aka MML) and Backup Operator says: is RMAN.
To end this conflict I will show how to identify where is the problem.
I'll use the term RMAN (like RMAN spend time), but actually means Database spend time. Because RMAN is only a client. So typically the database is slow and not RMAN Client is slow.
Note: I will not diagnose what is causing it, I will only help you identify whether the problem is, in MML or RMAN.
Media Management
The Oracle Media Management Layer (MML) API lets third-party vendors build a media manager, software that works with RMAN and the vendor's hardware to allow backups to sequential media devices such as tape drives. A media manager handles loading, unloading, and labeling of sequential media such as tapes.
RMAN Interaction with a Media Manager
RMAN does not issue specific commands to load, label, or unload tapes. When backing up, RMAN gives the media manager a stream of bytes and associates a unique name with this stream. When RMAN must restore the backup, it asks the media manager to retrieve the byte stream. All details of how and where that stream is stored are handled entirely by the media manager. For example, the media manager labels and keeps track of the tape and names of files on each tape, and automatically loads and unloads tapes, or signals an operator to do so.
RMAN provides a list of files requiring backup or restore to the media manager, which in turn makes all decisions regarding how and when to move the data.
Before RMAN making a call to any of functions in the media management API, the server create a EVENT WAIT. So, These with EVENT WAIT is possible to get the number of wait (in seconds or number) that the server has been waiting for this call to return.
So, we can calculate it and find out how much time RMAN spend waiting MML (e.g writing or querying a backup piece filename) processing request and return to RMAN.
Complete list EVENT of MML
Oracle 11.2 or above:
SELECT NAME FROM V$EVENT_NAME WHERE NAME LIKE '%MML%'; NAME ---------------------------------------- Backup: MML initialization Backup: MML v1 open backup piece Backup: MML v1 read backup piece Backup: MML v1 write backup piece Backup: MML v1 close backup piece Backup: MML v1 query backup piece Backup: MML v1 delete backup piece Backup: MML create a backup piece Backup: MML commit backup piece Backup: MML command to channel Backup: MML shutdown Backup: MML obtain textual error Backup: MML query backup piece Backup: MML extended initialization Backup: MML read backup piece Backup: MML delete backup piece Backup: MML restore backup piece Backup: MML write backup piece Backup: MML proxy initialize backup Backup: MML proxy cancel Backup: MML proxy commit backup piece Backup: MML proxy session end Backup: MML datafile proxy backup? Backup: MML datafile proxy restore? Backup: MML proxy initialize restore Backup: MML proxy start data movement Backup: MML data movement done? Backup: MML proxy prepare to start Backup: MML obtain a direct buffer Backup: MML release a direct buffer Backup: MML get base address Backup: MML query for direct buffers
Previous version of Oracle Database 11.2 the Event name MML does not exists because it’s was changed on version 11.2 from %STB% to %MML%.
So, If you are using Oracle 11.1 or previous you can query V$EVENT_NAME where NAME like '%sbt%'.
SELECT NAME FROM V$EVENT_NAME WHERE NAME LIKE '%sbt%'; Backup: sbtinit Backup: ssbtopen Backup: ssbtread Backup: ssbtwrite Backup: ssbtbackup . . .
So, lets start...
Oracle store statistics (cumulative, since database was started) of these wait on v$system_event. I always use GV$ because is very common we admin RAC env on this days.
Before start backup I'll take a snapshot intial of gv$system_event...by creating a table RMAN_MML_EVENT_T1.
Click on icon "View Source" to see formated text.
CREATE TABLE RMAN_MML_EVENT_T1 AS SELECT inst_id, event, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT, TIME_WAITED_MICRO, sysdate as SNAPSHOT_TIME FROM gv$system_event WHERE event LIKE 'Backup%'; SQL> select * from RMAN_MML_EVENT_T1; INST_ID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO SNAPSHOT_TIME ---------- --------------------------------------- ----------- -------------- ----------- ------------ ----------------- ----------------- 1 Backup: MML initialization 371 0 54365 146.54 543651136 08-08-12 17:11:05 1 Backup: MML create a backup piece 450 0 4827 10.73 48270960 08-08-12 17:11:05 1 Backup: MML commit backup piece 450 0 7417 16.48 74172281 08-08-12 17:11:05 1 Backup: MML shutdown 371 0 47 .13 469267 08-08-12 17:11:05 1 Backup: MML query backup piece 894 0 11222 12.55 112222166 08-08-12 17:11:05 1 Backup: MML extended initialization 371 0 0 0 3655 08-08-12 17:11:05 1 Backup: MML delete backup piece 444 0 5348 12.05 53480530 08-08-12 17:11:05 1 Backup: MML write backup piece 1378078 0 3053683 2.22 3.0537E+10 08-08-12 17:11:05 8 rows selected.
I started backup using RMAN and MML (Tivoli Storage Manager). When backup finished you can query V$RMAN_BACKUP_JOB_DETAILS to get accurate time of backup
SELECT START_TIME, END_TIME, ROUND(INPUT_BYTES /1024/1024/1024,2) IMPUT_GBYTES , ROUND(OUTPUT_BYTES /1024/1024/1024,2) OUTPUT_GBYTES, INPUT_TYPE, ELAPSED_SECONDS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE TRUNC(START_TIME) = TRUNC(SYSDATE) AND INPUT_TYPE LIKE 'DB%'; START_TIME END_TIME IMPUT_GBYTES OUTPUT_GBYTES INPUT_TYPE ELAPSED_SECONDS ----------------- ----------------- ------------ ------------- ------------- --------------- 08-08-12 17:23:44 08-08-12 17:26:38 12.85 10.06 DB FULL 174
In my case the backup full take 174 seconds to backup read 12.85GB and Write on MML 10.06GB of data
So, after backup finish I take the 2nd snapshot by creating the table RMAN_SNAPSHOT_T2.
CREATE TABLE RMAN_SNAPSHOT_T2 AS SELECT inst_id, event, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT, TIME_WAITED_MICRO, sysdate as SNAPSHOT_TIME FROM gv$system_event WHERE event LIKE 'Backup%'; SQL> select * from RMAN_MML_EVENT_T2; INST_ID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO SNAPSHOT_TIME ---------- --------------------------------------- ----------- -------------- ----------- ------------ ----------------- ----------------- 1 Backup: MML initialization 373 0 54665 146.56 546652333 08-08-12 17:27:45 1 Backup: MML create a backup piece 454 0 4860 10.71 48604759 08-08-12 17:27:45 1 Backup: MML commit backup piece 454 0 7482 16.48 74820999 08-08-12 17:27:45 1 Backup: MML shutdown 373 0 47 .13 471590 08-08-12 17:27:45 1 Backup: MML query backup piece 900 0 11281 12.53 112808077 08-08-12 17:27:45 1 Backup: MML extended initialization 373 0 0 0 3665 08-08-12 17:27:45 1 Backup: MML delete backup piece 446 0 5373 12.05 53727006 08-08-12 17:27:45 1 Backup: MML write backup piece 1419274 0 3067298 2.16 3.0673E+10 08-08-12 17:27:45 8 rows selected.
Now I can calculate the values from RMAN_MML_EVENT_T2 minus RMAN_MML_EVENT_T1 to get the real time spend on MML.
Note:
EVENT: Name of the wait event
TOTAL_WAITS: Total number of waits for the event
TOTAL_TIMEOUTS: Total number of timeouts for the event
TIME_WAITED: Total amount of time waited for the event (in hundredths of a second)
AVERAGE_WAIT: Average amount of time waited for the event (in hundredths of a second)
TIME_WAITED_MICRO: Total amount of time waited for the event (in microseconds)
SELECT t1.inst_id, t1.event, t2.total_waits - t1.total_waits total_waits, t2.total_timeouts -t1.total_timeouts total_timeouts, t2.time_waited - t1.time_waited time_waited, t2.time_waited_micro - t1.time_waited_micro time_waited_micro FROM RMAN_MML_EVENT_T1 T1, RMAN_MML_EVENT_T2 T2 WHERE t1.inst_id = t2.inst_id AND t1.event = t2.event; INST_ID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ---------- --------------------------------------- ----------- -------------- ----------- ----------------- 1 Backup: MML initialization 2 0 300 3001197 1 Backup: MML create a backup piece 4 0 33 333799 1 Backup: MML commit backup piece 4 0 65 648718 1 Backup: MML shutdown 2 0 0 2323 1 Backup: MML query backup piece 6 0 59 585911 1 Backup: MML extended initialization 2 0 0 10 1 Backup: MML delete backup piece 2 0 25 246476 1 Backup: MML write backup piece 41196 0 13615 136141912 8 rows selected.
As I can see above the MML spend more time writing backup piece.
So, I'll sum the time to get total time spend on MML.
SELECT SUM (total_waits) total_waits, SUM(total_timeouts) total_timeouts , SUM (time_waited)/100 time_waited_in_second, SUM (time_waited_micro) time_waited_micro FROM (SELECT t1.inst_id, t1.event, t2.total_waits - t1.total_waits total_waits, t2.total_timeouts -t1.total_timeouts total_timeouts, t2.time_waited - t1.time_waited time_waited, t2.time_waited_micro - t1.time_waited_micro time_waited_micro FROM RMAN_MML_EVENT_T1 T1, RMAN_MML_EVENT_T2 T2 WHERE t1.inst_id = t2.inst_id AND t1.event = t2.event ) TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_IN_SECOND TIME_WAITED_MICRO ----------- -------------- --------------------- ----------------- 41218 0 140.97 140960346
Calculating time total of backup window, time spend on MML and time spend of RMAN.
Note: TIME_SPEND_BY_RMAN = (ELAPSED_SECOND_BACKUP-TIME_SPEND_BY_MML_SECOND)
ELAPSED_SECONDS_BACKUP TIME_SPEND_BY_MML_SECOND TIME_SPEND_BY_RMAN_SECOND ------------------------------ ------------------------------ ------------------- 174 140.97 33.03
Summarizing:
Total time of backup : 174
Time spend by MML: 141
Time spend by RMAN : 33
If this backup is slow is because MML take (141*100/174) 81% of time spend of backup window.
Additional info:
As my backup was done over Lan:
(10.06GB * 1024 = 10301MB)
10301MB / 144 = 71Mbytes/second
As I'm using network interface of 1 Gbit I can consider a normal throughput.
Also you can monitoring in real time where is wait.
Just execute this script above:
Note : if you are using previous version of 11.2 change %MML% to %sbt%.
vi monitoring_mml.sh sqlplus -s sys/<password>@<db_name> as sysdba<<EOF set echo off COLUMN EVENT FORMAT a17 COLUMN SECONDS_IN_WAIT FORMAT 999 COLUMN STATE FORMAT a15 COLUMN CLIENT_INFO FORMAT a30 set linesize 200 select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') actual_date from dual; SELECT p.SPID, sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT, sw.STATE, CLIENT_INFO FROM gV\$SESSION_WAIT sw, gv\$SESSION s, gV\$PROCESS p WHERE sw.EVENT LIKE '%MML%' AND s.SID=sw.SID AND s.PADDR=p.ADDR; EOF exit
Using shell execute the command above, and you will see in real time the wait on MML.
while true do sh monitoring_mml.sh sleep 1 done
Rate this:
Share this:
- Click to share on Facebook (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on LinkedIn (Opens in new window)
- Click to email a link to a friend (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- Click to print (Opens in new window)
Great ….
LikeLike
nice;)
LikeLike
Nice article, thanks. Just I would like to ask you a question, does mean that MML isn’t capable to write in a local tape unit, so only requires a tape software (manager) to handle backups to tape in rman?
Thanks.
LikeLike
Yes.
RMAN does not write directly to tape.
You need to have third party Media Management Software installed.
Oracle has published an API specification which Media Management Vendor’s who are members of Oracle’s Backup Solutions Partner program have access to.
Media Management Vendors (MMVs) then write an interface library which the Oracle server uses to write and read to and from tape.
Oracle Backup Solutions Program (BSP)
http://www.oracle.com/technetwork/database/features/availability/bsp-088814.html
LikeLike
excellent article
many thanks for writing.
LikeLike
goooooooooooooooooooooooooood very helpfull..
LikeLike