Hi
trying to use P Profiler in Oracle D
I tried query below
SELECT level,RPAD(' ', (level-1)*2, ' ') || a.name AS name,
a.subtree_elapsed_time,
a.function_elapsed_time,
a.calls
FROM (SELECT fi.symbolid,
pci.parentsymid,
RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
NVL(pci.calls, fi.calls) AS calls
FROM dbmshp_function_info fi
LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
WHERE fi.runid = 16
AND fi.module != 'DBMS_HPROF' ) a
CONNECT BY a.parentsymid = PRIOR a.symbolid
START WITH a.parentsymid IS NULL
order by a.subtree_elapsed_time desc
but not show correct order calls
Then read
http://aprogrammerwrites.eu/?p=703#.Xk1FIEp7mM8
But when execute the last query
WITH last_run AS (
SELECT Max (runid) runid FROM dbmshp_runs
), full_tree (runid, lev, node_id, sub_t, fun_t, calls, link_id) AS (
SELECT fni.runid, 0, fni.symbolid, fni.subtree_elapsed_time, fni.function_elapsed_time, fni.calls, 'root' || ROWNUM
FROM dbmshp_function_info fni
JOIN last_run lrn
ON lrn.runid = fni.runid
WHERE NOT EXISTS (SELECT 1 FROM dbmshp_parent_child_info pci WHERE pci.childsymid = fni.symbolid AND pci.runid = fni.runid)
UNION ALL
SELECT ftr.runid,
ftr.lev + 1,
pci.childsymid,
pci.subtree_elapsed_time,
pci.function_elapsed_time,
pci.calls,
pci.parentsymid || '-' || pci.childsymid
FROM full_tree ftr--
JOIN dbmshp_parent_child_info pci
ON pci.parentsymid = ftr.node_id
AND pci.runid = ftr.runid
) SEARCH DEPTH FIRST BY sub_t DESC, fun_t DESC, calls DESC, node_id SET rn
--select * from full_tree
, tree_ranked AS (
SELECT runid, node_id, lev, rn,
sub_t, fun_t, calls,
Row_Number () OVER (PARTITION BY node_id ORDER BY rn) node_rn,
Count (*) OVER (PARTITION BY node_id) node_cnt,
Row_Number () OVER (PARTITION BY link_id ORDER BY rn) link_rn
FROM full_tree
)
SELECT RPad (' ', trr.lev*2, ' ') || fni.function "Function tree",
fni.symbolid sy, fni.owner, fni.module,
CASE WHEN trr.node_cnt > 1 THEN trr.node_rn || ' of ' || trr.node_cnt END "Inst.",
trr.sub_t, trr.fun_t, trr.calls,
trr.rn "Row"
FROM tree_ranked trr
JOIN dbmshp_function_info fni
ON fni.symbolid = trr.node_id
AND fni.runid = trr.runid
WHERE trr.link_rn = 1
ORDER BY trr.rn
return me error
ORA-32044: cycle detected while executing recursive WITH query
How can I do query without to cause CONNECT BY loop in user data
I have Invoice with many items
The main procedure call others procedures that calculate each subitems
main_calculate
calc_A (subitiem(1));
calc_B (subitiem(1));
calc_C (subitiem(1));
calc_D (subitiem(1));
....
....
calc_P (subitiem(1));
---
calc_A (subitiem(2));
calc_B (subitiem(2));
calc_C (subitiem(2));
calc_D (subitiem(2));
....
....
calc_P (subitiem(2));
-----
....
calc_A (subitiem(100));
calc_B (subitiem(100));
calc_C (subitiem(100));
calc_D (subitiem(100));
....
....
calc_P (subitiem(100));
how can i call to display calls in the correct order
Each Call_A(B, C....P) call others procedures
using
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production