Hello all,
I am on 11.2.0.3 DB(hpux ia64)....I had a question with regards to "Backup: MML create a backup piece"....from what i understand currently one of the session(1666) is waiting on my MML layer to start writing to tape...its waiting to create a backup piece...i totaly understand this is nothing to do with oracle being slow or anything(as its waiting on MML)...but my question is....have such long wait...would it effect my DB performance/server performance??
I looked at the OEM page when my backup runs...while under the main performance page i do not see any graphs related to Administrative wait_class, its only when i look under the top activity page that i see graph that pops up the Administrative wait_class....is it bad that if this happens for a long time...is my server process in sleep state(but v$session shows active)...just wanted to understand a little more about "Backup: MML create a backup piece wait"....
after even talking to the tape backup administrator we know we have some tape contention(as others backups are going on) this backup sits in the queue...some time it sits there for about 3-4 hours before it can start writing anything....
SQL> SELECT SID, event, total_waits, total_timeouts, time_waited, average_wait,
2 max_wait, time_waited_micro/1000000
3 FROM v$session_event
4 WHERE SID = 1666
5 ORDER BY SID, time_waited DESC
6 ;
SID|EVENT |TOTAL_WAITS|TOTAL_TIMEOUTS|TIME_WAITED|AVERAGE_WAIT| MAX_WAIT|TIME_WAITED_MICRO/1000000
------|----------------------------------------------------------------|-----------|--------------|-----------|------------|----------|-------------------------
1666|Backup: MML create a backup piece | 2| 0| 94974| 47487.05| 60510| 949.740924
1666|Backup: MML write backup piece | 82126| 0| 82815| 1.01| 28| 828.151483
1666|RMAN backup & recovery I/O | 36564| 0| 42769| 1.17| 28| 427.687802
1666|Backup: MML commit backup piece | 1| 0| 714| 714.16| 714| 7.141612
1666|SQL*Net message from client | 73| 0| 658| 9.02| 319| 6.582584
1666|Backup: MML query backup piece | 1| 0| 550| 550.02| 550| 5.500196
1666|events in waitclass Other | 317| 2| 386| 1.22| 85| 3.859556
1666|control file sequential read | 387| 0| 18| .05| 1| .180209
1666|Disk file operations I/O | 71| 0| 13| .18| 2| .125252
1666|control file parallel write | 21| 0| 6| .26| 1| .055472
1666|db file sequential read | 27| 0| 3| .11| 1| .029231
1666|Backup: MML initialization | 1| 0| 2| 1.57| 2| .015683
1666|db file single write | 9| 0| 1| .09| 0| .007805
1666|SQL*Net message to client | 73| 0| 0| 0| 0| .000089
1666|Backup: MML command to channel | 1| 0| 0| .01| 0| .000055
1666|Backup: MML extended initialization | 1| 0| 0| 0| 0| .000003
16 rows selected.