Section 9 of the Birth of Tragedy is mainly about the value of a certain kind of sacrilege, the sacrificial sacrilege needed to achieve Dionysian wisdom, or any sort of deeper wisdom. It is all described in terms of the ancient Greeks and their myths, in terms of battles between various groups of Gods, particularly between the Titans and the Olympians, and yet, as always, N. has in mind something more contemporary. The significance of this section for the project of the book as a whole can be seen in the fact that the title page of the book features Prometheus unchained. So part of the question here is the significance of Prometheus for N. It gets pretty complicated since N. does not just identify Prometheus with the Dionysian, although the Titan is closer to that than to the Apollonian. Ultimately Prometheus is seen as having both sides.
The significance of this for today, in N.s view, would be in terms of the price paid by the genius (whether artistic or philosophical). Such a person must violate "natural law" (or what is conceived as such) in order to achieve something real. In this section the natural law is symbolized by, in Oedipus Rex, the rules against incest and patricide, and the violation of those rules. Quite possibly (well, quite probably) N. identifies himself both with Prometheus, as hero of Aeschylean tragedy, and Oedipus, as hero of Sophoclean tragedy. We get here the beginnings of a critique of morality not simply of the Christian sort, but of morality itself (by which he mainly means, the conventional and traditional, especially the Alexandrian, which is really the Appolinian without the Dionysian).
But the best way to think of this is to ponder N. the rebel academic, the violator of his philological scholarly parents. Then you have this idea that in order to really do something, in philosophy for example, or in German culture as a whole, or perhaps for humanity itself, one must commit sacrilege (i.e. break the idols, which means, for example, taking on Kant and even Socrates), and that the consequent suffering is just part of it.
His main thought is to get behind the dialogue and the character of the hero in tragedy, all of which is clear and precise in an Apollinian way, and get to the myth which is projected as the bright image of the hero, an image which simply stands out on the wall of the cave (read Plato's cave, but with reality not outside in the sun and the world of the Forms, but deeper inside the cave in the Schopenhauerian depths of the wisdom of Silenus) as a healing image against the background of the abyss. The mask of the hero (on stage) has two aspects: the Apollinian and the Dionysian, the second related to the gruesome terrors of nature. And what of the much vaunted Greek cheerfulness? Well, if there is any it is due to this contrast, the same one that was described in terms of the Greek response to the wisdom of Silenus mentioned earlier.
It is odd that, for N., Oedipus not only suffers due to his errors and wisdom but also "spreads a magical power of blessing" that continues beyond his death, since even if the entire moral world is destroyed by his actions, he magically founds a new world on the ruins of the old. This is a two stage process, following the two great Sophoclean plays. The cheerfulness evident in Oedipus Rex through our pleasure in the hero's solving of the mystery of the Theban plague, a cheerfulness that softens the edges of the king's personal tragedy, is heightened, infinitely says N., in Oedipus at Colonus, where the king's suffering faces an unearthly cheerfulness in which the hero's passiveness is itself the highest form of activity, and we are overcome by a "profound human joy" as a counterpart to dialectic (a clear reference again to Plato's methodology and how it does not work in regard to these deepest of questions).
Yet, the play itself, "the poet's whole conception," is nothing but a bright healing image projected to save us from our perception of the abyss. The question N. then raises is how Oedipus can be both the violator of custom and also solver of the riddle of the Sphinx, a riddle which represents the deepest wisdom about man. The clue is to be found in the "popular belief" that the wise man is born from incest, i.e. he gains "prophetic and magical powers" that "break the spell of present and future," i.e. the spell of convention by way of an act of sacrilege. He must "compel nature to surrender her secrets" by means of something unnatural. Dionysian wisdom is the result of an "unnatural abomination" by which the hero plunges nature into destruction and then suffers his own dissolution as a result. Wisdom of this sort, is "a crime against nature."
And no one before N. has ever said something like that, although perhaps he is right that the Greek tragedians implied it. No one has ever associated wisdom with sacrilege. And think about the cost N. himself will pay life for his radical questioning, for his own sacrilege.
In short, we do not have here the death of God but rather a joyous defiance of God, something shared by Prometheus and the philosophical or artistic genius: "Man, rising to Titanic stature [Prometheus was a Titan], gains culture by his own efforts and forces the gods into an alliance with him because in his very own wisdom he holds their existence and their limitations in his hands." Man can kill the gods, or bring them alive again.
N., as always, sees this in terms of stages. In this case the Aeschylean tragedy poses the bold individual against the gods in decline, the two reconciled by the mediation of Moira, Fate, who stands above both men and gods. So Aeschylus, himself audacious like Prometheus, places both God and men on the same scales (i.e. of justice), this based on the Greek capacity to use "mysteries" (generally associated with the Dionysian) to ground metaphysical thought and to counter the dominance of the Olympians.
The Greek poet recognizes that the gods depend on him as much as he on them. For N., the death of God is the destruction of God by the individual foretold by the myth of Prometheus creating men to overcome the gods and then suffering eternally as a result. But, where Aeschylus ends with a story of eternal suffering, Sophocles tells a higher story that ends with "the holy man's song of triumph" a Nietzschean yes to life. Aeschylus is Schopenhauer to Nietzsche's Sophocles. Thus, Aeschylus's "interpretation of the myth" is one-upped by that of Sophocles.
The artist's image again is just "a bright image of clouds and sky mirrored in a black lake of sadness" -- the abyss. Prometheus brings fire to man and this is the crucible for the ascent of culture insofar as man uses it, not as a present from heaven, but as something he has robbed from the gods. This battle, this "irresolvable contradiction between man and god" is the origins of culture. Myth, one might say, hides, but also reveals, this contradiction.
N. compares this idea to the idea of the Jews and Christians (he calls it Semitic myth), the idea of sin whereby, in his view, various feminine qualities give rise to evil. The Aryan (by which he means Greek and German) idea of "active sin" as Promethean virtue provides the "ethical basis" for Greek tragedy: in this case human evil is justified, along with guilt and resultant suffering. (I think "evil" for N. mainly means not cruel acts against others but violations of conventional limits based on systematic application of a skeptical methodology.)
So, at the heart of things, there is a contradiction between the divine and the human, each having right on its side as an individual, but suffering nonetheless, the individual hero trying to transcend his individuation to become, in a Schopenhauerian way, "one world being."
Not important to me is N.s identification of sacrilege with the Aryan and the masculine and sin with the Semitic and feminine, but simply the idea that the "titanically striving individual" is no follower of Apollo since Apollo draws boundaries by way of his Socratic, and ultimately Alexandrian, demand for self-knowledge and moderation, i.e. self-knowledge only by way of moderation and limitation, i.e. logical tidiness. This is the scholarly ideal too: philosopher as definer in terms of necessary and sufficient conditions, for example.
Nietzsche then combines the Titanic and the Dionysian: "The Titanic impulse to become, as it were, the Atlas [brother of Prometheus] for all individuals, carrying them on a broad back, higher and higher [as Zarathustra does for his friends?], farther and farther, is what the Promethean and the Dionysian have in common." So, Prometheus "is a Dionysian mask" and the demand for justice is just his Apollinian side.
The end of the section is a riddle that sums it up: "All that exists is just and unjust and equally justified in both." i.e. we have moved "beyond good and evil" as N. later puts it. N. follows this with "This is your world! A world indeed! ---" a quote from Faust. To say yes to your world, a wondrous world, one needs to see just and unjust as equally justified, something I cannot personally go along with. But the idea of the necessity of sacrilege and a battle between men, allied with Titan gods, and the Olympian gods (representing what N. called the Socratic but also the tradition of Plato's Forms and all attempts as self-knowledge through logic, boxing in, limitation of sort) haunts.