Should I use urethane spray foam or foam rope for caulking wide gaps

Polyurethane foam is an excellent insulator, and it needs no added vapor barrier. Depending on your climate and local building authority requirements, you will need anywhere from 4 to 7 inches of foam. The remaining airspace does not need to be filled. It will help prevent your new ceiling from getting wet from inevitable condensation. Alternately, you could install baffles against which to spray the foam, creating an air gap below the roof sheathing and creating a sort of cold roof, if that has any benefit in your climate.

The foam is such a good vapor barrier, you will need a plan to remove excess moisture from your house. Frequent use of bath and kitchen fans may be adequate, depending on climate and remaining construction. You might consider whole house ventilation through an air to air heat exchanger or similar.

If you are removing the ceiling, you can pretty much use any type of insulation that makes economic sense in your area, but in very cold climates, 9.5 in of fiberglass is not really adequate. In warmer climates, you could avoid stripping the ceiling off by blowing in fiberglass through holes in the ceiling, then patching or covering the holes. You can also apply foam through holes, but you may have to fill the whole cavity, which would be expensive overkill.

I don't think it's possible to accurately place foam through holes such that the cavity is not totally filled, but it might be worth questioning an experienced applicator. If you blow fiberglass, then you will need to add a vapor barrier somehow. Some heavy solids paints meet the vapor barrier spec of 1 perm, so could be considered to be a leaky but adequate barrier. Or you could add a thin layer of foil backed sheetrock. Or just sandwich polyethylene sheeting between layers.

Without knowing your climate or local prices, I couldn't say what the best option might be, but I hope I've given you enough ideas so you can decide on your own. Cheers.

Short answer : money.

Longer answer:

Inquiring at Google Contracting Services and doing some back of the envelope math, I came up with the following price ranges for the mentioned insulation types.

Spray foam is about $0.15-0.21/square foot/R

Fibreglass batts are about $0.03-0.07/square foot/R

Rigid foam panels are about $0.10-0.15/square foot/R

As we can see, in terms of insulation value for your money, the batts take a bat to all challengers.

Now someone will look at an insulation value chart and says "But foam has a higher R-value per inch, so it's better insulation since you have non-infinite space to put it in".

Comparing insulation value per thickness, they are correct as shown below:

Spray foam - ~R-6.3-7/inch

Fibreglass batts - ~R-3.1-3.3/inch

Rigid foam - ~R-5-6/inch

Both of the more expensive options achieve more with less thickness, so those options would allow more insulation value to be packed into the same volume.

But, you're going to bang your head into a point of diminishing returns where you'd achieve more energy savings putting the money towards improving things somewhere else, and you'll hit that point sooner than you think, given the way R-values work*, so that increased insulation density doesn't really get you much gain.

Additionally, installing batts (and rigid foam, for that matter) is a pretty simple DIY job and requires minimal PPE (gloves, goggles, dust mask). Spraying in foam indoors means you're going to need a full-body suit, gloves, goggles, respirator, etc. which makes DIYing it outside the skill/comfort level of many, meaning needing to hire a professional to do it for you (and the hassle/expense/loss-of-pride that entails), leading people to just go with the simpler batts.

*expanding, R-value basically means that 1/Rth of the heat (or cold, as the case may be) will escape compared to no insulation. R-6 means 16.6% of the heat is getting out. adding R-24 of insulation and going to R-30 results in only 3.3% getting out, a saving of 13.3%. But add another 24 to get to R-54 only gets us down to 1.85%, a saving of 1.45% for the same investment, and as I said above, you can likely find somewhere else to improve things that will get you more than that small savings for the same money, or at an endpoint, that money will buy you more gas/electricity than the extra insulation will save you over any realistic timeframe.