What is an effective CIWS? Anything, if the ship has nothing when it is suddenly needed! We have billions of dollars of taxpayers money invested in these ship, they need to be well armed and defended.
I agree about the need for warships to be well armed and defended. The statement about what is an effective CIWS however is not really accurate, in part because there are variables to what will make a specific CIWS effective in different scenarios.
Looking at the RAN inventory, there is currently one weapon system designated as a CIWS which is the Mk 15 Phalanx. That system has already been identified as having limitations or limited effectiveness against certain types of aerial threats, even though it was originally designed to provide protection against such threats.
Using the P-270 Moskit as an example, it is a ~4,500 kg missile which will close at Mach 2+ and leaving a Mk 15 Phalanx only about one or two sec of firing between the missile entering the effective range of the Mk 15 and target on the target impact. With the upgrade to the Mk 15, that means it is possible for the gun to fire 75 to 150 rounds whilst the inbound missile is within the effective range of the gun. This is also assuming that an inbound missile is closing on a vector within the firing arc of a Mk 15 which might very well not be true.
Going further along with this, even if all 150 20 mm rounds were to successfully impact an inbound P-270, would they be able to inflict sufficient damage to the inbound missile that either the missile detonated safely away from the targeted ship, or the control surfaces and/or guidance systems were damage enough so that the missile went off course and safely impacted without damaging the ship.
It is unfortunate, but a 4,500 kg object moving at Mach 2+ is going to have a significant amount of KE and it, or even just fragments if the missile was damaged and started to break up before impact, keep going for quite a distance, with the potential for a ship kill (mission or outright) due to debris impacts. Again, this concern is largely why the RIM-116 RAM was developed as a point-defence weapon vs. AShM.
Now missile-based CIWS do exist, like the RIM-116 RAM and SeaRAM but again, trade off exists with those too. A Mk 15 Phalanx mounting has ~1,500 rounds loaded, whilst a RIM-116 RAM holds up to 21 missiles and a SEARAM mounting can only hold 11. Where this potentially limited munitions loadout comes up is if surface targets are engaged like hostile FAC, with something like a Phalanx likely being a more efficient and cost effective way of dealing with potential FAC swarms.