The improvement of a SQL is depending on the complexity of the original SQL syntax, for simple and quick SQL statements, most database optimizers can handle it very well, so the chance to get improvement is low, unless there is an obvious problem in your SQL.
You can also increase the Optimize Intelligence Level to investagate more alternatives.
NO_CPU_Costing hint is used to tell Oracle to use IO costing only, since some SQL statements query plan will be changed by 10g new CPU costing method, if it caused problem to your SQL statements, it is better to use no CPU costing method. Oracle provide Hints for experience users to influence their SQL to solve performance problem, if it is problem, you should use it, if it is not a problem or the improvement is little, you should consider not to use it. But there is another good side effect of using Hints in your SQL, since a hint to a SQL will normally fix the query plan of the SQL; it makes your SQL run more stable from time to time.