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()

Export / Import Oracle Table/Index Stats

Source - Why Export Import optimizer statistics: 

Importing and exporting statistics for the CBO and the systems stats (external system statistics for CPU, I/O. etc) and useful in a variety of areas:

• Export production into test to make test systems "look like" large systems for execution plan generation".
• Export/imports can be used to control execution plans by "freezing execution plans".
• Statistics are used as a backup before re-analyzing a schema.
• System stats can be moved to a smaller server to make it appear as if Oracle is executing on a large fast server.         

- System stats:  When migrating to a new server, you can export the old system statistics to ensure consistent execution plans until you are ready to use the "real" system stats.

- Systems reverse:  Conversely, you can migrate system stats from production to test to make a tiny server appear to be a larger server.  This will not improve SQL execution speed, but developers will see the same execution plans that they would see in production

- Backup stats:  Before making any production change to the CBO stats with dbms_stats, take a full schema backup and an backup of your dbms_stats system stats.  Remember, the primary reason for re-analyzing stats is to change SQL execution plans.

For example, here we export production table stats and backport them to the test database to make it appear to be a larger table

Source - Steps



Source - STATs stale?

EX: 
* when table was last analyzed: select table_name, to_char(last_analyzed,'MM-DD-YYYY HH24:MI'),stale_stats from all_tab_statistics where STALE_STATS='YES'

* what tables got modified recently(MONITORING attribute must be Yes): 
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, to_char(timestamp,'MM-DD-YYYY HH24:MI') from dba_tab_modifications where timestamp > trunc(sysdate) and TABLE_OWNER='SYSADM'  
(delayed flushing; for immediate flush:  
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;)

During the gather_*_stats, FLUSH_DATABASE_MONITORING_INFO would have flushed the information ; it could wipe out the entry from dba_tab_modifications  instead

Source - Rebuild TABLE stats include INDEX stats?

1. create INDEX....
2. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'

3. exec dbms_stats.gather_table_stats(....)

4. select index_name , num_rows, last_analyzed from user_indexes where table_name ='A'
union
select table_name,  num_rows, last_analyzed from user_tables where table_name ='A'

Source - Restore Previous Stats
dba_histograms  
dba_tab_stats_history

exec DBMS_STATS.DELETE_TABLE_STATS
exec dbms_stats.restore_table_stats(...)
exec dbms_stats.import_table_stats

PS Online Trace Analyzer

YATA - yet another Trace analyzer... this one for Online SQL trace


based on "COM Stmt" - 

* Elapsed Time : awk '{print $5}'  xxx.tracesql  |  awk 'BEGIN {tot=0}; {tot=tot+$1; next}; END{printf "%f / %d = %f\n", tot, NR,tot/NR}'

* Dur Time: awk -F' |Dur=' xxx | cut -d' ' -f1 | awk 'BEGIN {tot=0}; {tot=tot+$3; next}; END{printf "%f / %d = %f\n", tot, NR,tot/NR*100}'

Scan SQL history for reference to Object

Quick scan on DB for sqls used a certain objects:

set serveroutput on linesize 4000

declare 

   sqltxt clob;
   cnt integer;
   
begin

      cnt:=0;
      
      for x in (select sql_id, sql_fulltext from gv$sql where regexp_like (sql_fulltext ,'OBJECT_NAME','i') 
                and not regexp_like (sql_fulltext ,'^declare|sql_fulltext|sys.col','i')) 
      loop
      
      cnt:=cnt+1;
      
      DBMS_OUTPUT.PUT_LINE(lpad(cnt,5,0)||': '|| x.sql_id ||'-'||x.sql_fulltext );

      end loop;
   
exception
   
      WHEN OTHERS then
        declare
           errcd  NUMBER := SQLCODE;
           errmsg VARCHAR2(300) := SQLERRM;
        begin
           DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);
        end;
   
end;
/

Combine 2 lines into 1 line

paste -d " " - -
sed 'N;s/\n/ /' yourFile
xargs -l2 
xargs -n2 -d'\n'
awk '{key=$0; getline; print key ", " $0;}'