Export Process Instance into test environment

-----------------------------------------------reset PI 

insert into psprcsrqst  select * from psprcsrqst@db where PRCSINSTANCE = 88888888

 

insert into PSPRCSPARMS select * from PSPRCSPARMS@db WHERE    PRCSINSTANCE  = 88888888

 

update PSPRCSPARMS set PARMLIST='...', ORIGPARMLIST='...' WHERE    PRCSINSTANCE  = 88888888

 

UPDATE PS_AERUNCONTROL SET ae_run_data = to_clob('INTFAPAM    MAIN    Notify  INTFAPAM    PrcNtfy Step05  ') where PROCESS_INSTANCE=88888888

 

UPDATE PS_AERUNCONTROL SET ae_run_data = to_clob('INTFAPAM    MAIN    Notify  INTFAPAM    PrcNtfy Step06  ') where PROCESS_INSTANCE=88888888

 

insert into PSPRCSQUE select * from PSPRCSQUE@db where PRCSINSTANCE =88888888

 

insert into PS_CDM_file_LIST select * from PS_CDM_file_LIST@db where PRCSINSTANCE =88888888

 

insert into ps_cdm_list select * from ps_cdm_list@db where PRCSINSTANCE =88888888

 

------------------------------------------------- reassign output folder/content ID

update PS_CDM_file_LIST set CONTENTID=4071614, filename='AP_CRDT_MEMO.pdf',  file_size=3570, CDM_FILE_TYPE='PDF'

where PRCSINSTANCE =88888888  and CDM_FILE_TYPE='LOG'

 

update ps_cdm_list set PRCSOUTPUTDIR='...',OUTPUTDIR='...' where PRCSINSTANCE =88888888

 

update psprcsrqst set CONTENTID=4071614 where PRCSINSTANCE =88888888

------------------------------------------------- stuck in Posting

update psprcsrqst set DISTSTATUS=4, CONTENTID=4144646 where PRCSINSTANCE = 88888888                                   

update PSPRCSQUE set DISTSTATUS=4 where PRCSINSTANCE = 88888888                                                        

insert into PS_CDM_file_list                                                                                          

select 88888888,CONTENTID,'AMPS1000_4648811.log',CDM_FILE_TYPE, 166249,sysdate-1                                      

from PS_CDM_file_list where PRCSINSTANCE =88888888                                                     

insert into PS_CDM_list                                                                                              

select 88888888,CONTENTID,PRCSNAME,PRCSTYPE,'XXXXXXXX/log_output/AE_AMPS1000_88888888',

CONTENT_DESCR,OUTDESTFORMAT,RQSTDTTM,ENDDTTM,EXPIRATION_DATE,4,DISTNODENAME,OUTPUTDIR,0,ADMIN_FILENAME,GENPRCSTYPE,  

0,FILENAME,PSRF_FOLDER_NAME,PRCSBURSTRPT,MSGNODENAME,CDM_APPROVAL_FLAG,QRYXFORMFILETYPE                               

from PS_CDM_list where PRCSINSTANCE = 4687995    

J



 

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;}'

Find session record locks

select d.module, d.action, a.SID, USERNAME,OSUSER, machine, LOCK_TYPE,MODE_HELD "Lock Mode", OBJECT_TYPE "Object",OBJECT_NAME "Name",a.PROCESS,c.process||'-'||a.blocking_session "Blocked By", to_char(to_date(SECONDS_IN_WAIT,'sssss'),'hh24:mi:ss') "Wait Time" from ( 
        Select se.sql_id, lk.SID, se.username, to_single_byte(se.OSUser) osuser, to_single_byte(se.Machine) as machine, 
               DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
               DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
               DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
               ob.object_id, ob.object_type, ob.object_name, decode(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block, se.lockwait, se.process, se.blocking_session
        FROM   gv$lock lk, dba_objects ob, gv$session se
        WHERE  lk.TYPE IN ('TX', 'TM', 'UL') AND    lk.SID = se.SID AND    lk.id1 = ob.object_id (+)) a, gv$session_wait b, gV$LOCKED_OBJECT c, gv$sql d  
        where a.lockwait is not null and a.sid = b.sid and SECONDS_IN_WAIT>0
        and a.object_id=c.object_id and blocking_session is not null and a.blocking_session=c.session_id
        and LOCK_TYPE<>'Transaction'
        and a.sql_id=d.sql_id(+)
        

Oracle Table Time Stamp

Original Link

CREATED = date of creation of the object

LAST_DDL_TIME = last ddl on object, would include CREATE OR REPLACE (example below)

TIMESTAMP = last time the external "view" or "specification" of the object changed -- will be between created and last_ddl_time.

it starts with the timestamp = created = last_ddl_time. 

Windows - Search file modified last hour

source - https://answers.microsoft.com/en-us/windows/forum/all/can-i-search-for-files-modified-in-the-last-hour/d7ae54c2-e271-418f-ad0b-f1b867ea5f81

Window Explorer: modified:2020-02-24 11:30..11:45

source - https://www.windows-commandline.com/find-files-based-on-modified-time/

forfiles /P directory /S /D +01/04/2015

forfiles /S /D -3 /C "cmd /c move @file D;\archiveDir"

Linux Date Snippets

source: https://unix.stackexchange.com/questions/24626/quickly-calculate-date-differences

* date --utc -d 'mar 28 1867 +72years +11months +2days'
* expr $(date '+%s' -d 'Aug 02, 2020') - $(date '+%s' -d 'Aug 01, 2020')

get recipient from maillog

source: https://superuser.com/questions/178171/gnu-sort-by-case-sensitive

awk -F"[<>]" '{print $2}' maillog | grep -i $recipient |  LC_COLLATE=C sort --ignore-case | uniq -i