What was the most effective cannon ball in the civil war?

It depends on what the target is, and what type of gun is being used.

A "cannon ball" is what's called a "solid shot", useful for battering down brick fort walls, or smashing in the side of a wooden ship. In navy use, sometimes they had little ovens near the gun for heating the shot red hot, hoping to cause fires aboard the target with this "hot shot".

A better type of ammunition against troops in the field is "spherical shell". This is a round, hollow "cannon ball", filled with gunpowder through a round hole in one side. Once filled this hole is plugged by a wooden screw-in stopper, which has holes drilled through it filled with fuse. Depending on how far away the target is, the fuse can be set for various times of flight by trimming the fuse material. The idea is that the fuse will be ignited during the discharge of the gun, and the burning fuse will reach the powder-packed interior and explode the shell over the head of the enemy troops. The shell will break into fragments and spray the troops. Very effective, if it works. Confederate fuses were notoriously unreliable, exploding shells as soon as they left the gun muzzle, or never, but very rarely over the enemy's head as intended.

At close range the best ammunition against charging enemy troops was "canister". This looked something like a big #10 can of vegetables, but was usually packed with 48 iron balls about the size of the huge rifle ammunition of the day. This would be blasted out as the gun was fired, the can would disintegrate and the balls would spread out and rip through the enemy running up in front, like a giant shotgun blast. If the situation was really serious the cannon crew might load "double canister" - put two of these in the gun each time they fired it, doubling the death-dealing balls. This was extremely effective at stopping enemy charges.

There were a few rifled pieces of artillery available by the time of the Civil War, such as Armstrong guns, and Whitworth guns. Like the rifled muskets of the foot soldiers, these were a vast improvement as far as accuracy over the smooth-bore type of cannon up to that time. These could accurately hit targets up to three miles away. All Civil War artillery was basically "line of sight" artillery - you really only had a hope of hitting a target you could see. But rifled pieces could hit targets twice as far away as the standard 12-pound smoothbore "Napoleon" that was the standard gun of both sides.