Home » SQL & PL/SQL » SQL & PL/SQL » convert xml column value into columns (Oracle 19c)
| convert xml column value into columns [message #687494] |
Mon, 20 March 2023 21:53  |
 |
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
hi i have a question. when checking execution plan notes from v$sql_plan.other_xml column i want to convert each value into one column forexample if i run below query
SELECT
'sql_profile ' || extractvalue(xmlval, '/*/info[@type = "sql_profile"]')||'
sql_patch ' || extractvalue(xmlval, '/*/info[@type = "sql_patch"]')||'
baseline ' || extractvalue(xmlval, '/*/info[@type = "baseline"]')||'
outline ' || extractvalue(xmlval, '/*/info[@type = "outline"]')||'
dyn_sampling ' || extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]')||'
dop ' || extractvalue(xmlval, '/*/info[@type = "dop"]')||'
dop_reason ' || extractvalue(xmlval, '/*/info[@type = "dop_reason"]')||'
pdml_reason ' || extractvalue(xmlval, '/*/info[@type = "pdml_reason"]')||'
idl_reason ' || extractvalue(xmlval, '/*/info[@type = "idl_reason"]')||'
queuing_reason ' || extractvalue(xmlval, '/*/info[@type = "queuing_reason"]')||'
px_in_memory ' || extractvalue(xmlval, '/*/info[@type = "px_in_memory"]')||'
px_in_memory_imc ' || extractvalue(xmlval, '/*/info[@type = "px_in_memory_imc"]')||'
row_shipping ' || extractvalue(xmlval, '/*/info[@type = "row_shipping"]')||'
index_size ' || extractvalue(xmlval, '/*/info[@type = "index_size"]')||'
result_checksum ' || extractvalue(xmlval, '/*/info[@type = "result_checksum"]')||'
card_feedback ' || extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]')||'
perf_feedback ' || extractvalue(xmlval, '/*/info[@type = "performance_feedback"]')||'
xml_suboptimal ' || extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]')||'
adaptive_plan ' || extractvalue(xmlval, '/*/info[@type = "adaptive_plan"]')||'
spd_used ' || extractvalue(xmlval, '/*/spd/cu')||'
spd_valid ' || extractvalue(xmlval, '/*/spd/cv')||'
gtt_sess_stat ' || extractvalue(xmlval, '/*/info[@type = "gtt_session_st"]')||'
db_version ' || extractvalue(xmlval, '/*/info[@type = "db_version"]')||'
plan_hash_full ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]')||'
plan_hash ' || extractvalue(xmlval, '/*/info[@type = "plan_hash"]')||'
plan_hash_2 ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]') as Full_Notes
from
(select xmltype(other_xml) xmlval from
(select other_xml
from dba_hist_sql_plan
where sql_id = '&&sql_id'
-- and plan_hash_value = nvl('&&plan_hash',0)
and other_xml is not null
and not exists (select 1 from gv$sql_plan where
sql_id = '&&sql_id'
-- and plan_hash_value = nvl('&&plan_hash',0)
and other_xml is not null)
union all
select other_xml
from gv$sql_plan
where sql_id = '&&sql_id'
-- and plan_hash_value = nvl('&&plan_hash',0)
and other_xml is not null
and (inst_id, child_number) in (select inst_id, child_number from gv$sql_plan where sql_id = '&&sql_id'
--and plan_hash_value = nvl('&&plan_hash', 0)
and rownum <= 1)
)
)
/
it generates me below output in one row (multiple lines).
sql_profile SYS_SQLPROF_01687c32214f0026
sql_patch
baseline
outline
dyn_sampling
dop
dop_reason
pdml_reason
idl_reason
queuing_reason
px_in_memory
px_in_memory_imc
row_shipping
index_size
result_checksum
card_feedback
perf_feedback
xml_suboptimal
adaptive_plan
spd_used
spd_valid
gtt_sess_stat
db_version 19.0.0.0
plan_hash_full 1342132429
plan_hash 1184223408
plan_hash_2 1342132429
basically i need to convert each line into column some kind of below not putting all the column_names here thanks...
plan_hash_full plan_hash plan_hash_2 db_version sql_profile sql_patch
1342132429 1184223408 1342132429 19.0.0.0 SYS_SQLPROF_01687c32214f0026
|
|
|
|
|
|
| Re: convert xml column value into columns [message #687496 is a reply to message #687494] |
Tue, 21 March 2023 03:38  |
 |
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You are getting everything all in one column because you are concatenating line feeds. If you want separate columns, then you need to select them as columns followed by any aliases and separated by commas. You also need to use some method such as column definitions or to_char and set your linesize so that the columns are not so wide and the linesize so narrow that the columns wrap around. A better newer method is to use xmltable. I have demonstrated both below.
get sql_id and plan_hash for testing:
SCOTT@orcl_12.1.0.2.0> column sql_id new_value sql_id
SCOTT@orcl_12.1.0.2.0> column plan_hash new_value plan_hash
SCOTT@orcl_12.1.0.2.0> select sql_id, '1' plan_hash
2 from dba_hist_sql_plan
3 where sql_id is not null
4 and rownum = 1
5 /
SQL_ID PLAN_HASH
------------- ---------
0gx3b09qrx9f5 1
1 row selected.
SCOTT@orcl_12.1.0.2.0> COLUMN plan_hash_full FORMAT A14
SCOTT@orcl_12.1.0.2.0> COLUMN plan_hash FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN plan_hash_2 FORMAT A11
SCOTT@orcl_12.1.0.2.0> COLUMN db_version FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN sql_profile FORMAT A11
SCOTT@orcl_12.1.0.2.0> COLUMN sql_patch FORMAT A10
SCOTT@orcl_12.1.0.2.0> SELECT extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]') plan_hash_full,
2 extractvalue(xmlval, '/*/info[@type = "plan_hash"]') plan_hash,
3 extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]') plan_hash_2,
4 extractvalue(xmlval, '/*/info[@type = "db_version"]') db_version,
5 extractvalue(xmlval, '/*/info[@type = "sql_profile"]') sql_profile,
6 extractvalue(xmlval, '/*/info[@type = "sql_patch"]') sql_patch
7 from (select xmltype (other_xml) xmlval from
8 (select other_xml
9 from dba_hist_sql_plan
10 where sql_id = '&&sql_id'
11 -- and plan_hash_value = nvl('&&plan_hash',0)
12 and other_xml is not null
13 and not exists
14 (select 1
15 from gv$sql_plan
16 where sql_id = '&&sql_id'
17 -- and plan_hash_value = nvl('&&plan_hash',0)
18 and other_xml is not null)
19 union all
20 select other_xml
21 from gv$sql_plan
22 where sql_id = '&&sql_id'
23 -- and plan_hash_value = nvl('&&plan_hash',0)
24 and other_xml is not null
25 and (inst_id, child_number) in
26 (select inst_id, child_number
27 from gv$sql_plan
28 where sql_id = '&&sql_id'
29 --and plan_hash_value = nvl('&&plan_hash', 0)
30 )))
31 /
PLAN_HASH_FULL PLAN_HASH PLAN_HASH_2 DB_VERSION SQL_PROFILE SQL_PATCH
-------------- ---------- ----------- ---------- ----------- ----------
1254914732 1964104430 1254914732 12.1.0.2
1254914732 1964104430 1254914732 12.1.0.2
2 rows selected.
SCOTT@orcl_12.1.0.2.0> select x.*
2 from (select other_xml
3 from dba_hist_sql_plan
4 where sql_id = '&&sql_id'
5 -- and plan_hash_value = nvl('&&plan_hash',0)
6 and other_xml is not null
7 and not exists
8 (select 1
9 from gv$sql_plan
10 where sql_id = '&&sql_id'
11 -- and plan_hash_value = nvl('&&plan_hash',0)
12 and other_xml is not null)
13 union all
14 select other_xml
15 from gv$sql_plan
16 where sql_id = '&&sql_id'
17 -- and plan_hash_value = nvl('&&plan_hash',0)
18 and other_xml is not null
19 and (inst_id, child_number) in
20 (select inst_id, child_number
21 from gv$sql_plan
22 where sql_id = '&&sql_id'
23 --and plan_hash_value = nvl('&&plan_hash', 0)
24 )) t,
25 xmltable
26 ('/other_xml'
27 passing xmltype (t.other_xml)
28 columns
29 plan_hash_full varchar2(14) path '/*/info[@type = "plan_hash_full"]',
30 plan_hash varchar2( 9) path '/*/info[@type = "plan_hash"]',
31 plan_hash_2 varchar2(14) path '/*/info[@type = "plan_hash_2"]',
32 db_version varchar2(10) path '/*/info[@type = "db_version"]',
33 sql_profile varchar2(11) path '/*/info[@type = "sql_profile"]',
34 sql_patch varchar2(10) path '/*/info[@type = "plan_hash_2"]') x
35 /
PLAN_HASH_FULL PLAN_HASH PLAN_HASH_2 DB_VERSION SQL_PROFILE SQL_PATCH
-------------- ---------- ----------- ---------- ----------- ----------
1254914732 196410443 1254914732 12.1.0.2 1254914732
1254914732 196410443 1254914732 12.1.0.2 1254914732
2 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Oct 04 22:44:33 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
