Monthly Archives: July 2019

Frank 11:45 pm on July 13, 2019  

find best plan using sql execution stats and reports

Optimizer generates execution plans based on the available information at the time the query gets processed, and the plan is not always optimal. In some cases, when the bad plan is used, query execution time is increased significantly, users start to complain that application stops responding. We call it plan regression.

How do you respond when plan regression happens? There are several options available, one of the options is to find the best plan and create a baseline using that plan. In order to find a better plan, you need to find all the plans that are available, and compare the average duration per execution, the best plan has the smallest duration.

How to find the best plan? You can query either DBA_HIST_REPORTS or DBA_HIST_SQLSTAT to get the answer. 

col begin_interval_time for a30
col avg for 99999.9
col elapsed for 9999999.9
col sql_profile for a30
select s.snap_id, 
       h.begin_interval_time,
       s.sql_id,              
       s.plan_hash_value,
       s.instance_number,
       round(s.elapsed_time_delta/1000000, 1) elapsed,
       s.executions_delta,
       round(s.elapsed_time_delta/s.executions_delta/1000000, 1) avg,
       s.sql_profile
from dba_hist_sqlstat s 
join dba_hist_snapshot h on s.snap_id = h.snap_id 
and s.instance_number = h.instance_number
where s.sql_id = '&&sql_id'
order by 1
;

Here is a sample output,

"SNAP_ID","BEGIN_INTERVAL_TIME","SQL_ID","PLAN_HASH_VALUE","INSTANCE_NUMBER","ELAPSED","EXECUTIONS_DELTA","AVG","SQL_PROFILE"
12093,"11-JUL-19 11.00.16.124 AM","55f86bjpx6d1z",3685618265,1,"69.3",14,"4.9",
12094,"11-JUL-19 12.00.27.540 PM","55f86bjpx6d1z",3685618265,1,"34.6",9,"3.8",
12095,"11-JUL-19 01.00.09.192 PM","55f86bjpx6d1z",3685618265,1,"96.3",16,"6.0",
12097,"11-JUL-19 03.00.28.744 PM","55f86bjpx6d1z",1492133555,2,"46.8",11,"4.3",
12113,"12-JUL-19 07.00.05.634 AM","55f86bjpx6d1z",536527346,1,"280.4",1,"280.4",
12113,"12-JUL-19 07.00.05.537 AM","55f86bjpx6d1z",536527346,2,"581.3",3,"193.8",
12114,"12-JUL-19 08.00.15.108 AM","55f86bjpx6d1z",536527346,2,"13341.2",6,"2223.5",
12114,"12-JUL-19 08.00.15.221 AM","55f86bjpx6d1z",536527346,1,"10046.4",7,"1435.2",
12115,"12-JUL-19 09.00.25.884 AM","55f86bjpx6d1z",536527346,1,"12495.6",4,"3123.9",
12115,"12-JUL-19 09.00.25.751 AM","55f86bjpx6d1z",536527346,2,"10089.6",4,"2522.4",

Or, you can query DBA_HIST_REPORTS,

set pages 9999
set lines 200
col plan_hash for 99999999999999999
col sql_id for a30
col duration for 9999999999
alter session set nls_date_format = "yyyymmdd-hh24:mi:ss";

select
  r.period_start_time,
  r.period_end_time,
  r.report_id,
  key1 SQL_ID,
  EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/stats/stat[@name="duration"]') duration,
  EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/plan_hash') plan_hash
from dba_hist_reports r
where component_name= 'sqlmonitor' and key1 = '&&sql_id'
order by 1
;

The output is shown here,

"PERIOD_START_TIME","PERIOD_END_TIME","REPORT_ID","SQL_ID","DURATION","PLAN_HASH"
"20190711-11:56:51","20190711-11:56:55",1152441,"55f86bjpx6d1z","4","3685618265"
"20190711-12:48:43","20190711-12:48:54",1152524,"55f86bjpx6d1z","11","3685618265"
"20190711-13:17:04","20190711-13:17:09",1152574,"55f86bjpx6d1z","5","3685618265"
"20190711-13:17:04","20190711-13:17:09",1152666,"55f86bjpx6d1z","5","1492133555"
"20190711-13:38:56","20190711-13:39:00",1152634,"55f86bjpx6d1z","4","3685618265"
"20190711-15:06:07","20190711-15:06:12",1152851,"55f86bjpx6d1z","5","1492133555"
"20190712-07:54:13","20190712-08:30:30",1154708,"55f86bjpx6d1z","2177","536527346"
"20190712-07:57:07","20190712-08:30:28",1154709,"55f86bjpx6d1z","2001","536527346"
"20190712-07:59:57","20190712-08:33:34",1154737,"55f86bjpx6d1z","2017","536527346"

Optionally, you can generate sql monitor report using the report id, here is the query

set trimspool on 
set trim on 
set verify off
set feedback off
set pages 0 
set linesize 1000 
set long 1000000 
set longchunksize 1000000 

accept report_id prompt 'enter report_id:'

variable b1 VARCHAR2(30);

begin
    :b1 := '&report_id';
end;
/

column filename new_val filename

select 'sqlmon_' || to_char(sysdate, 'dd_hh24miss_') || :b1 || '.html' filename from dual;
SPOOL &filename

SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => :b1, TYPE => 'active') FROM dual
;

spool off

It is obvious that plan_hash_value 536527346 is the plan that is having performance issue, and the other plans have similar execution time. All you need to do is to load the plan that has the smallest duration as baseline.

Frank 7:47 am on July 6, 2019  

load text file into CLOB column

The problem to solve

When you need to load a large text file into a clob column, or you need to load many files, and the files are on the client host, there is no simple insert or update statement you can write to accomplish the task.

To programmatically load text files, I created a python script that generates sql script from text file. Once the sql script is generated, I can run the script from sqlplus to insert or update the CLOB column. You can download the script from this link,

load_clob_from_file

How the program works

The script creates a LOB object, opens the object, reads the text file line by line, converts each line into a procedure call that appends the line into the LOB object, close the object, and updates or inserts the object into a table.

The output of the script is a sql file that can be run any time.

A demo

Here is a text file for demo purposes,

<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
</catalog>

To generate the sql script, I will run the command

load_clob_from_file.py demo.xml > demo.sql

The output of the script is shown here. You will need to replace the insert or update statement with the actual table and column name.

SPO load_xml.log
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
DECLARE
  l_text clob;
  l_line varchar2(32000);
BEGIN
DBMS_LOB.CREATETEMPORARY(l_text, TRUE);
DBMS_LOB.OPEN(l_text, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITEAPPEND(l_text, 22, q'~<?xml version="1.0"?>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 10, q'~<catalog>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 21, q'~   <book id="bk101">~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 30, q'~      <genre>Computer</genre>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 27, q'~      <price>44.95</price>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 46, q'~      <publish_date>2000-10-01</publish_date>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 11, q'~   </book>~'|| CHR(10));
DBMS_LOB.WRITEAPPEND(l_text, 11, q'~</catalog>~'|| CHR(10));
DBMS_LOB.CLOSE(l_text);
-- insert into sales.customer values (4, 'test', sysdate, l_text);
-- update sales.customer set bio = l_text where customer_id = 31;
commit;

END;
/
SPOOL OFF