Oracle_Performance

Explain Plan (Plan_Table)

explain plan SET STATEMENT_ID='DOCS_SIMPLE_SORT_01'

  for select * from DW_PS_EMPL_HOURS_FACT order by 1;

select 'Statement_id:'||statement_id||' (Plan:'||plan_id||') @ '||to_char(timestamp,'YYYYMMDD_HHMMSS')||' Op:'||OPERATION ||' CPU:'||cpu_cost||' IO:'||io_cost dah_plan

  from plan_table where statement_id='DOCS_SIMPLE_SORT_01'

   union

   SELECT * FROM TABLE(dbms_xplan.display) where  plan_table_output

     not like '%Plan hash value%'; --from PLAN_TABLE see /rdbms/admin/utlxplan.sql

TKPROF 


ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET tracefile_identifier = DW_PS_ACTIVE_DIR_FACT_TRC;

select * from table(SAVE_CLOBS_TO_FILE('DW_PS_ACTIVE_DIR_FACT.csv', 
  CURSOR(SELECT * from TABLE(GET_CSV_FROM_TABLE_PIPELINED('DW_PS_ACTIVE_DIR_FACT','~')))));


ALTER SESSION SET SQL_TRACE=FALSE;



C:\APP\PRODUCT\12.2\diag\rdbms\dockerydb\dockerydb\trace>

tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  pdbtrace=user/password   Connect to ORACLE to retrieve SQL trace records.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor


C:\APP\PRODUCT\12.2\diag\rdbms\dockerydb\dockerydb\trace>tkprof dockerydb_ora_5024_DW_GIS_CONTRACTS_MV_TRACE.trc
output = _out.log
TKPROF: Release 12.2.0.1.0 - Development on Fri Sep 21 15:37:04 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

C:\APP\PRODUCT\12.2\diag\rdbms\dockerydb\dockerydb\trace>tkprof dockerydb_ora_5024_DW_GIS_CONTRACTS_MV_TRACE.trc  dockerydb_ora_5024_DW_GIS_CONTRACTS_MV_TRACE.out aggregate=yes sort=exeela

tkprof dockerydb_ora_5024_DW_GIS_CONTRACTS_MV_TRACE.trc  dockerydb_ora_5024_DW_GIS_CONTRACTS_MV_TRACE.out aggregate=yes sort=exeela