Solution: By Cauchy-Schwarz inequality, we can obtain
$$\begin{array}{l}
(x+y)\left(\frac{1}{x}+\frac{a}{y}\right) \geqslant\left(\sqrt{x} \cdot \frac{1}{\sqrt{x}}+\sqrt{y} \cdot \frac{\sqrt{a}}{\sqrt{y}}\right)^{2} \\
=(1+\sqrt{a})^{2}
\end{array}$$

When $x=1, y=\sqrt{a}$, $(x+y)\left(\frac{1}{x}+\frac{a}{y}\right)$ can achieve its minimum value.

Since the inequality $(x+y)\left(\frac{1}{x}+\frac{a}{y}\right) \geqslant 9$ holds for any positive real numbers $x, y$, it also holds for the minimum value of $(x+y)\left(\frac{1}{x}+\frac{a}{y}\right)$, thus we have
$$(1+\sqrt{a})^{2} \geqslant 9$$

Solving this, we get $a \geqslant 4$.
When $x=1, y=2$, $a$ achieves its minimum value of 4.
Example 4 Given the inequality $(x+y)\left(\frac{1}{x}+\frac{a}{y}\right) \geqslant 9$ holds for any positive real numbers $x, y$. Then the minimum value of the positive real number $a$ is ( ).
(A)2
(B) 4
(C)6
(D) 8