You can measure hard vs. soft parses by checking the "parse count (hard)" and "parse count (total)" metrics:
SQL> create table t (
2 x int
3 );
Table created.
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select s.name, m.value
2 from v$statname s, v$mystat m
3 where s.statistic# = m.statistic#
4 and s.name like 'parse%(%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 616
parse count (hard) 318
parse count (failures) 0
parse count (describe) 0
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL> select s.name, m.value
2 from v$statname s, v$mystat m
3 where s.statistic# = m.statistic#
4 and s.name like 'parse%(%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 622
parse count (hard) 320
parse count (failures) 0
parse count (describe) 0
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL> select s.name, m.value
2 from v$statname s, v$mystat m
3 where s.statistic# = m.statistic#
4 and s.name like 'parse%(%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 626
parse count (hard) 320
parse count (failures) 0
parse count (describe) 0
Note "parse count (hard)" stays the same (320) on the second execution. But the total increases!
For more about this, see:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082