I used a fee only advisor twice. First to create a financial plan for a flat fee, about 5 years before retirement. Then again for an update a year before retirement, paid for on an hourly basis. I used the advisor for financial planning only, not investment advice. My perspective on investing is just use a 3-fund Boglehead or Vanguard portfolio.
I think fee-only advice is rare for 2 reasons. 1) It's much more lucrative for the advisors to have ongoing clients they charge a % of assets to manage investments, or to work on commissions from the investment companies. Finding enough clients to make a living from doing one-time plans is a tough gig. 2) Most people don't care or are afraid to manage their own investments. People look at me like a skunk at a wedding when I tell them I pay hourly for financial planning, but those same people are unaware how high their MERs and trailing commissions are and how much it reduces their long-term financial assets.
The benefit to me is having someone experienced to create a very detailed plan that takes into account tax rates, which accounts (taxable, tax-deferred or tax-exempt) to save in and which to withdraw from first to minimize taxes, when to take CPP (one component of Canada's social security system), avoiding OAS clawback (another component of Canada's social security system), understanding tax rates, recommendations on insurance (critical illness, long-term care etc), and generally ensuring I have not made erroneous assumptions in my own planning.
TL;DR: I used a fee only planner for planning but not investment advice. Well worthwhile for me.