Skip to content

Commit 692ebe6

Browse files
gvenzlgvenzl
authored
Merge pull request oracle-samples#85 from nigelbayliss/master
TCB Example
2 parents 14fd8bc + 813b4fe commit 692ebe6

File tree

3 files changed

+176
-0
lines changed

3 files changed

+176
-0
lines changed

‎optimizer/tcb/README.md

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
Export and import a TCB testcase.
2+
3+
This example uses a DBA account TCB_DBA to export and import the test case.
4+
5+
SQL plan baselines are exported and imported manually to avoid issues some DB versions.
6+

‎optimizer/tcb/do_exp.sql

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
connect tcb_dba/tcb_dba
2+
3+
whenever sqlerror exit
4+
5+
--
6+
-- Remove preexiting export
7+
--
8+
host rm -r /tmp/TCB
9+
host mkdir /tmp/TCB
10+
11+
create or replace directory TCB_EXPORT_LOCATION as '/tmp/TCB';
12+
13+
--
14+
-- The relevant SQL_ID here
15+
-- In this case, the statement must be in cursor cache
16+
--
17+
var sqlid varchar2(50)
18+
exec :sqlid := '7tycj7v5dzz5m';
19+
--
20+
-- Drop preexisting SPM staging table
21+
--
22+
begin
23+
execute immediate 'drop table my_spm_staging_tab';
24+
exception
25+
when others then null;
26+
end;
27+
/
28+
--
29+
-- TCB bug means we have to export SQL plan baselines manually
30+
-- The bug is identified by the followin error message in the TCB
31+
-- export log:
32+
-- ORA-39166: Object TCBSTAGE was not found or could not be exported or imported.
33+
--
34+
-- Note! This table is used to export SQL profiles too so they will
35+
-- not be included in the test case export.
36+
--
37+
-- A workaround for the bug is used the SQL statement's parsing
38+
-- schema to export the test case. I'm assuming that this will
39+
-- not be possible in a production system because the account will
40+
-- not have the required privs.
41+
--
42+
--
43+
set serveroutput on
44+
declare
45+
sig_exact number;
46+
handle varchar2(30);
47+
n number;
48+
begin
49+
select dbms_sqltune.sqltext_to_signature(sql_fulltext)
50+
into sig_exact
51+
from v$sqlarea
52+
where sql_id = :sqlid;
53+
54+
select sql_handle
55+
into handle
56+
from dba_sql_plan_baselines
57+
where signature = sig_exact
58+
and rownum < 2;
59+
60+
dbms_spm.create_stgtab_baseline('my_spm_staging_tab',null,'SYSAUX');
61+
n := dbms_spm.pack_stgtab_baseline('my_spm_staging_tab',sql_handle=>handle);
62+
dbms_output.put_line('Exported '||n||' SQL plan baselines');
63+
end;
64+
/
65+
set serveroutput off
66+
--
67+
-- Now export the test case
68+
--
69+
declare
70+
tc clob;
71+
begin
72+
-- Directory name must be in upper case
73+
dbms_sqldiag.export_sql_testcase(directory=>'TCB_EXPORT_LOCATION'
74+
,sql_id=>:sqlid
75+
,testcase=>tc
76+
,preserveSchemaMapping=>true
77+
,testcase_name=>'mytestcase');
78+
end;
79+
/
80+
--
81+
-- Manually export the SQL plan baseline data
82+
--
83+
declare
84+
h1 number;
85+
job_state varchar2(100);
86+
sts ku$_Status;
87+
begin
88+
h1 := DBMS_DATAPUMP.OPEN('EXPORT','TABLE');
89+
DBMS_DATAPUMP.ADD_FILE(h1,'spm_pack.dmp','TCB_EXPORT_LOCATION');
90+
DBMS_DATAPUMP.START_JOB(h1);
91+
92+
job_state := 'UNDEFINED';
93+
while (job_state != 'COMPLETED') and (job_state != 'STOPPED')
94+
loop
95+
dbms_datapump.get_status(h1,
96+
dbms_datapump.ku$_status_job_error +
97+
dbms_datapump.ku$_status_job_status +
98+
dbms_datapump.ku$_status_wip,-1,job_state,sts);
99+
end loop;
100+
end;
101+
/
102+
103+
prompt SPM staging table...
104+
select count(*) from my_spm_staging_tab;
105+

‎optimizer/tcb/do_imp.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
connect tcb_dba/tcb_dba
2+
3+
whenever sqlerror exit
4+
--
5+
-- Drop preexisting SPM staging table
6+
--
7+
begin
8+
execute immediate 'drop table my_spm_staging_tab';
9+
exception
10+
when others then null;
11+
end;
12+
/
13+
14+
create or replace directory TCB_EXPORT_LOCATION as '/tmp/TCB';
15+
16+
set serveroutput on
17+
--
18+
-- Import the test case
19+
--
20+
begin
21+
-- Make sure directory name is upper case
22+
dbms_sqldiag.import_sql_testcase (directory=>'TCB_EXPORT_LOCATION'
23+
,filename=>'mytestcasemain.xml'
24+
,preserveSchemaMapping=>true);
25+
end;
26+
/
27+
--
28+
-- Due to TCB bug, we have to import the SQL plan baselines
29+
-- manually.
30+
--
31+
declare
32+
h1 number;
33+
job_state varchar2(100);
34+
sts ku$_Status;
35+
begin
36+
h1 := DBMS_DATAPUMP.OPEN('IMPORT','FULL');
37+
DBMS_DATAPUMP.ADD_FILE(h1,'spm_pack.dmp','TCB_EXPORT_LOCATION');
38+
DBMS_DATAPUMP.SET_PARAMETER(h1,'TABLE_EXISTS_ACTION','REPLACE');
39+
DBMS_DATAPUMP.START_JOB(h1);
40+
job_state := 'UNDEFINED';
41+
while (job_state != 'COMPLETED') and (job_state != 'STOPPED')
42+
loop
43+
dbms_datapump.get_status(h1,
44+
dbms_datapump.ku$_status_job_error +
45+
dbms_datapump.ku$_status_job_status +
46+
dbms_datapump.ku$_status_wip,-1,job_state,sts);
47+
end loop;
48+
end;
49+
/
50+
51+
prompt SPM staging table...
52+
select count(*) from my_spm_staging_tab;
53+
--
54+
-- Unpack the SQL plan baselines
55+
--
56+
set serveroutput on
57+
declare
58+
n number;
59+
begin
60+
n := dbms_spm.unpack_stgtab_baseline('my_spm_staging_tab');
61+
dbms_output.put_line('Imported '||n||' SQL plan baselines');
62+
end;
63+
/
64+
set serveroutput off
65+

0 commit comments

Comments
 (0)