/* First, select the bind variables used in the SQL statement you're interested in, into the variable value_string. Record them in a text file. You will be using them later */ SELECT DECODE (value_string, 'NULL,', ''''',', value_string) FROM ( SELECT DECODE (datatype_string, 'VARCHAR2(32)', '''' || value_string || ''',', value_string || ',') value_string FROM v$sql_bind_capture WHERE sql_id = '4d6sdzm63st1u' AND child_number = 1 ORDER BY position); /* Get the SQL text including the bind usage (but not the bind variable values) */ SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2 (32000); l_sql_id VARCHAR2 (30) := '4d6sdzm63st1u'; BEGIN BEGIN SELECT sql_fulltext INTO l_sql FROM v$sql WHERE sql_id = l_sql_id AND ROWNUM < 2; EXCEPTION WHEN NO_DATA_FOUND THEN SELECT sql_text INTO l_sql FROM dba_hist_sqltext WHERE sql_id = l_sql_id AND ROWNUM < 2; END; DBMS_OUTPUT.put_line (l_sql); /* Capture the baseline for this statement */ EXECUTE IMMEDIATE 'alter session set optimizer_capture_sql_plan_baselines=true'; /* Send the SQL including the bind variable values to the database */ EXECUTE IMMEDIATE l_sql USING 5022000, 9834, 9822, 9, 4, 6; /* Turn the capture off again */ EXECUTE IMMEDIATE 'alter session set optimizer_capture_sql_plan_baselines=false'; END; /
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Tuesday, December 5, 2017
How to execute and capture an SQL statement in the SMB at the same time
An experienced co-worker showed me this neat trick to execute an SQL statement and capture its execution plan in the SQL Plan Management Base at the same time.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment