The second of the Sacraments of Healing (Confession being the other), Anointing of the Sick used to be called "Extreme Unction", or, more commonly in TV/movie world, "last rites." But recently its applications have been expanded beyond those who are in immediate danger of death. 

The Sacrament can be used in a variety of applications: 

1. For people who, indeed, are in danger of death. 
2.  Before major surgery (major being the operative word.) 
3. People who are older/infirm/have chronic illnesses that put them in danger of death  (for example, I used to get anointed a lot, due to epilespy/CF...my parents were big on me receiving the sacrament whenever it was offered. I wasn't quite so big on it. But I digress.) 

Anointing, like Confession, is used a lot in the media because it's a rather dramatic sacrament. Or, at least, it tends to happen in dramatic circumstances. For example, Outlander used it in season 2, after Claire miscarried Baby Faith. 

The benefits of anointing, according to the Catechism, are: 

1520 A particular gift of the Holy Spirit. the first grace of this sacrament is one of strengthening, peace and courage to overcome the difficulties that go with the condition of serious illness or the frailty of old age. This grace is a gift of the Holy Spirit, who renews trust and faith in God and strengthens against the temptations of the evil one, the temptation to discouragement and anguish in the face of death. This assistance from the Lord by the power of his Spirit is meant to lead the sick person to healing of the soul, but also of the body if such is God's will. Furthermore, "if he has committed sins, he will be forgiven."

1521 Union with the passion of Christ. By the grace of this sacrament the sick person receives the strength and the gift of uniting himself more closely to Christ's Passion: in a certain way he is consecrated to bear fruit by configuration to the Savior's redemptive Passion. Suffering, a consequence of original sin, acquires a new meaning; it becomes a participation in the saving work of Jesus.

1522 An ecclesial grace. the sick who receive this sacrament, "by freely uniting themselves to the passion and death of Christ," "contribute to the good of the People of God." By celebrating this sacrament the Church, in the communion of saints, intercedes for the benefit of the sick person, and he, for his part, though the grace of this sacrament, contributes to the sanctification of the Church and to the good of all men for whom the Church suffers and offers herself through Christ to God the Father.

1523 A preparation for the final journey. If the sacrament of anointing of the sick is given to all who suffer from serious illness and infirmity, even more rightly is it given to those at the point of departing this life; so it is also called sacramentum exeuntium (the sacrament of those departing).The Anointing of the Sick completes our conformity to the death and Resurrection of Christ, just as Baptism began it. It completes the holy anointings that mark the whole Christian life: that of Baptism which sealed the new life in us, and that of Confirmation which strengthened us for the combat of this life. This last anointing fortifies the end of our earthly life like a solid rampart for the final struggles before entering the Father's house. 

It's sort of a Grand Slam of Sacraments: Confession, Anointing, and, if possible, the Eucharist is given. I received anointing before my transplant surgery, but I couldn't receive the Eucharist because I wasn't allowed to eat anything. (I was sort of irritated by this.) The person is anointed with the Oil of the Sick while the priest says certain prayers. The oil is the matter of the sacrament, and the prayer is the form. 

Biblical support for this sacrament can be found in the book of James, chapter 5, verse 14: "Is anyone among you sick? He should summon the presbyters of the church, and they should pray over him and anoint [him] with oil in the name of the Lord." 

This is one of the reasons it's important to put your religious affiliation on any hospital admission forms; that way, if worst comes to worst, medical people know who to call. Every hospital I've been in has had a Catholic chaplain around to give the sacraments and provide spiritual counsel , etc. 

In my experience it's a rather peaceful sacrament. Many churches offer Anointing at least once a year for people with chronic illness, for older members of the congregation, etc.