Saturday, July 28, 2018

plshprof command-line utility in Oracle 12.2.0.1.0

The plshprof command-line utility generates HTML reports from raw profiler output files.

In Oracle 12.2.0.1.0 (12cR2), plshprof is changed in three aspects:
    (1). only one single HTML file is generated. However, in 12.1.0.2.0 and lower versions, 16 HTML files are generated.
    (2). new Section: SQL ID Elapsed Time (microsecs) Data sorted by SQL ID.
    (3). throws Memory fault when there exist new line characters in raw profiler output files.


1. 16 HTML files assemble in 12.1.0.2.0


In 12.1.0.2.0, plshprof generates 16 HTML files. To faciliate deploying and displaying, they can be congregated into one single file by following shell script (publicly presented in Oracle Open World 2015). 


#!/bin/ksh
# USAGE: plshprof.ksh <directory-of-raw-profile> <profile-name>
# Basic script without input checking / exception handling. Use at your own risk. Environment variable ORACLE_BIN must be defined and user must have necessary OS privileges
# Produces even less compliant HTML than plshprof, but all browsers display it nicely.
HPROF_PATH_NAME=$1
HPROF_ROOT_FILE=$2
HPROF_ROOT_FILE_PURE=${HPROF_ROOT_FILE%.*}

cd ${HPROF_PATH_NAME}

${ORACLE_BIN}/plshprof -output ${HPROF_ROOT_FILE_PURE} ${HPROF_ROOT_FILE}

HPROF_INTM_FILE="${HPROF_ROOT_FILE_PURE}"_intm
HPROF_AGGR_FILE="${HPROF_ROOT_FILE_PURE}"_aggr.html

BACK_TOP="<H2 align=\"center\"> <A HREF = ${HPROF_ROOT_FILE_PURE}.html> ↑ Back To Top ↑ </A></H2>"

echo "<P ID=${HPROF_ROOT_FILE_PURE}.html>" > "${HPROF_INTM_FILE}"
cat "${HPROF_ROOT_FILE_PURE}".html >> "${HPROF_INTM_FILE}"
echo "${BACK_TOP}" >> "${HPROF_INTM_FILE}"

keys="ts tf fn td tc ms mf md 2f 2n 2c nsf nsp nsc pc"
for key in $keys
do
    echo "<P ID=${HPROF_ROOT_FILE_PURE}_${key}.html>" >> "${HPROF_INTM_FILE}"
    cat "${HPROF_ROOT_FILE_PURE}"_${key}.html >> "${HPROF_INTM_FILE}"
    echo "${BACK_TOP}" >> "${HPROF_INTM_FILE}"
done

rm "${HPROF_ROOT_FILE_PURE}"_*.html
rm "${HPROF_ROOT_FILE_PURE}".html

sed -e "s/<A HREF = \([^#]\)/<A HREF = #\1/g" \
    -e "s/#"${HPROF_ROOT_FILE_PURE}"_pc.html#/#/g" \
    "${HPROF_INTM_FILE}" > "${HPROF_AGGR_FILE}"

rm "${HPROF_INTM_FILE}"

mv ${HPROF_AGGR_FILE} ${HPROF_ROOT_FILE_PURE}.html
It seems that Oracle 12.2.0.1.0 is trying to incorporate my idea/script into its new plshprof.


2. SQL ID Elapsed Time (microsecs) Data sorted by SQL ID


A new Section:
     SQL ID Elapsed Time (microsecs) Data sorted by SQL ID
is added, which contains:
     SQL ID      SQL TEXT
for SQL statements and dynamic_string in EXECUTE IMMEDIATE Statement (SQL TEXT is cut off to a length of 50).


3. Memory fault in 12.2.0.1.0


Oracle MOS:
     Bug 26789742 : NEW LINE IN DYNAMIC STRING MAKES DBMS_HPROF TO CRASH WITH ORA 3113/7445
reveals Memory fault/SIGSEGV exceptions when there exist new line characters in raw profiler output files.

This BUG is probably related to the above 12cR2 new introduced Section:
     SQL ID Elapsed Time (microsecs) Data sorted by SQL ID
in which "SQL TEXT" could be a dynamic_string containing new line characters.

As documented in Bug 26789742, the Bug can be reproduced with following test: 

declare
  l_plshprof_dir     varchar2(20) := 'PLSHPROF_DIR';
  l_filename         varchar2(50) := 'hprof_Bug_26789742.trc';
  l_txt              varchar2(50);
begin
  dbms_hprof.start_profiling(l_plshprof_dir, l_filename, null, true, true);
  
  --put a New Line in the start of dynamic_string.
  execute immediate 
q'[

select 'New Line Bug 26789742 Demo' from dual

]' into l_txt;
  dbms_hprof.stop_profiling;
  
  dbms_output.put_line(l_txt);
 
  --analyzed HTML report in location/filename.html
  --Bug 26789742
  dbms_hprof.analyze(location => l_plshprof_dir, filename => l_filename);
end;
/
Process (Session) abnormally terminated, and trace/incident file showed: 

ORA-07445: exception encountered: 
   core dump [PrintSymbolEntriesSymbol1P()+82] [SIGSEGV] 
   [ADDR:0x0] [PC:0xF4405A2] [Address not mapped to object] []
12.2.0.1.0 Test 

$ORACLE_HOME/bin/plshprof -output test_4mb test_4mb.trc

PLSHPROF: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[7223 symbols processed]
Memory fault
12.1.0.2.0 Test 

$ORACLE_HOME/bin/plshprof -output test_4mb test_4mb.trc

PLSHPROF: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
[7223 symbols processed]
[Report written to 'test_4mb.html']


4. New APIs


dbms_hprof in 12cR2 is enhanced with 3 new APIs, demonstrated as follows: 

declare
  l_plshprof_dir     varchar2(20) := 'PLSHPROF_DIR';
  l_filename         varchar2(20) := 'hprof_ksun_1.trc';
  l_cnt              number;
  l_sqlmonitor_repot clob;
  l_report_clob      clob;
begin
  dbms_hprof.start_profiling(l_plshprof_dir, l_filename, null, true, true);
  select 123 into l_cnt from dual;
  l_sqlmonitor_repot := dbms_hprof.stop_profiling;
  
  --Real-Time Monitoring report for the profiler run.
  dbms_output.put_line('<!--sqlmonitor_report-->' || l_sqlmonitor_repot);
  
  --analyzed HTML report in location/filename.html
  dbms_hprof.analyze(location => l_plshprof_dir, filename => l_filename);
  
  --analyzed HTML report in report_clob.
  dbms_hprof.analyze(location => l_plshprof_dir, filename => l_filename, report_clob => l_report_clob);
  dbms_output.put_line('<!--hprof_report-->' || l_report_clob);
end;
/
Posted by at