Consider a flat morphism $f:X\to Y$ of smooth connected varieties. For instance let $X=Y\times F$ with $X,Y,F$ all smooth. Further let $Z\subset X$ be a generically reduced subvariety such that $f|_Z:Z\to Y$ is a finite morphism, which is not flat. For any $y\in Y$ let $X_y\subset X$ denote the fiber of the original $f$.
As any two points on $Y$ are equivalent, the intersection product $Z\cdot_X X_y$. is independent of $y\in Y$.
Since $Y$ is smooth and $Z$ is generically reduced, most fibers of $f|_Z$ are smooth, so the intersection product $Z\cdot_XX_{y}$ for a general $y\in Y$ is given by the length of $\mathscr O_{X_{y}}$, in other words, there is no $\mathrm{Tor}$ contribution there.
Now take a point $y\in Y$ such that $f_Z$ is not flat in any neighbourhood of $y$ in $Y$.
It follows that the length of $\mathscr O_{X_y}$ (which is also the Hilbert polynomial of the fiber) has to be different from the value that we get from the smooth fibers. Therefore there has to be $\mathrm{Tor}$ contribution there.
Perhaps this example shows why it is $\mathrm{Tor}$ that comes in: $\mathscr O_{Z_y}$ is not flat on $Z$, so it does not give the right length and thus has to be corrected. It is arguably intuitive that the alternating sum of the length of the $\mathrm{Tor}$'s will be constant as $y$ runs through the (closed) points of $Y$.
I don't know if you find this geometrically satisfying. I would paraphrase what Hailong said and say that anything involving $\mathrm{Tor}$ should not be geometrically obvious, on the other hand $\mathrm{Tor}$ measures the failure of exactness of the tensor product so the failure of flatness is an obvious condition to look at. Then again, all of this just reiterates the fact that flatness is a difficult notion to truly comprehend (and I don't claim I do).
The examples/comments given by JC and Hailong fit into this example:
Since $Y$ is smooth and $f|_Z$ is finite, it is flat if and only if $Z$ is Cohen-Macaulay, which gives us Hailong's comment, and
a concrete example is given by $X=\mathbb A^4$, $Y=\mathbb A^2$, $f$ the obvious projection, and $Z$ the union of two planes meeting in a single point both of which project onto $Y$ isomorphically, which gives us JC's example.
