How many times a sql gets run?

select SQL_ID, CHILD_NUMBER, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME,module, action
from gv$sql where sql_text like 'select * from %'

CHILD_NUMBER: 0,1,2,...
EXECUTIONS: # of time a sql runs
ROWS_PROCESSED: # of rows fetched, can increase with more fetching
MODULE: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_MODULE
ACTION: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_ACTION

Source: V$SQL reference

Source: Why some sqls show up multiple times?  CHILD_NUMBER (unique, 0 ~...)

• caused by schema difference(owner), optimizer mode(ALL_ROWS,FIRST_ROWS), bind var(implicit conversion)...
• Why cursor IS_SHAREABLE = 'No' (reason: select * from v$SQL_SHARED_CURSOR where sql_id=...) 

Source: Different v$sql views

• v$sql the details -- if you have multiple copies of the query: "select * from T" in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.
• v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
• v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
• v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
• 
  

Compare:

select sql_id, CHILD_NUMBER,EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME, module, action, sql_text from gv$sql where sql_text  ='....'

with:
select sql_id, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED,  LAST_LOAD_TIME, LAST_ACTIVE_TIME , module, action, sql_text  

from gv$sqlarea where sql_text ='..'

2nd cursor will aggregate info on all columns from 1st  cursor and sum up totals from  EXECUTIONS, ROWS_PROCESSED

Source: PS 8.50 uses DBMS_APPLICATION_INFO to Identify DB Sessions

For some components the action is set to ‘xyzzy’. This seems to be a default value set when the component is opened, but before any of the pages are processed.  Therefore, it refers to activity in the search dialogue, including processing of :

• look ups to obtain values for search criteria
• SQL issued during SearchSave PeopleCode to validate the search criteria.
• the query on the Component Search record

MODULE & ACTION are different in v$sql vs v$session.

Source: Histogram vs Stats

• improve skew condition - histograms are useful for improving cardinality estimates
• Histograms are created automatically when statistics are gathered using the SKEWONLY and AUTO options in METHOD_OPT: EXEC DBMS_STATS.GATHER_TABLE_STATS( … METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO' …)
• How does the Oracle Database know that a particular column is used in a query predicate or join? This information is gathered by the Oracle Optimizer at parse time and ultimately stored in the Oracle data dictionary in a table called SYS.COL_USAGE$. 

To manually flush monitoring info: dbms_stats.flush_database_monitoring_info()