If you look at the formula of a single DFT bin

$$X[k] = \sum_{n=0}^{N-1}x[n]e^{-j2\pi k\frac nN}\text,$$

you'll notice that his is essentially a correlation of $x$ with the complex sinusoid $e^{-j2\pi k\frac nN}$.

That means the DFT can just be understood as a filter bank of matched filters for single tones that fall in the DFT "raster".

Hence, you simply get FFT length-based processing gain: The length of the sum.

If you look at the formula of a single DFT bin

$$X[k] = \sum_{n=0}^{N-1}x[n]e^{-j2\pi k\frac nN}\text,$$

you'll notice that his is essentially a correlation of $x$ with the complex sinusoid $e^{-j2\pi k\frac nN}$.

That means the DFT can just be understood as a filter bank of matched filters for single tones that fall in the DFT "raster".

Hence, you simply get FFT length-based processing gain: The length of the sum.

But: you probably don't have perfect knowledge of the exact frequency of the signal you're trying to detect¹! So, you can't put things into that perfect DFT raster.

Now, the larger you choose the FFT length $N$, the finer that raster will get, but also, the longer your observation has to be, and the more compute power you'll need.

At some point, the DFT stops being the best possible tone detector, and superresolution techniques become relevant. In this case (weak tone, you're sure that you've only got exactly one tone in your signal), the ESPRIT algorithm with a long observation period leading to the autocovariance matrix estimate that it takes as input, would probably work very nicely.