Following report queries used to take 100% CPU when run by 70+ users during peak time.
ORIGINAL QUERY - 1
==================
SELECT distinct CSP_RPT_CTGRY.CTGRY_NAME Category
,CSP_RPT_CTGRY.CTGRY_ID Id
FROM qri.CSP_RPT_CTGRY
, qri.CSP_RPT_GRP
, qri.CSP_RPT_GRP_CTGRY
, qri.csp_Roles
WHERE (CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID)
AND (CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID)
AND (CSP_RPT_GRP.RPT_GRP_NAME = csp_Roles.Role_Name)
AND (csp_Roles.User_Id = 'SNEWTON') <= Ran with sample id for stats ORDER BY 1 ASC;
ORIGINAL QUERY - 1
==================
SELECT distinct CSP_RPT_CTGRY.CTGRY_NAME Category
,CSP_RPT_CTGRY.CTGRY_ID Id
FROM qri.CSP_RPT_CTGRY
, qri.CSP_RPT_GRP
, qri.CSP_RPT_GRP_CTGRY
, qri.csp_Roles
WHERE (CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID)
AND (CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID)
AND (CSP_RPT_GRP.RPT_GRP_NAME = csp_Roles.Role_Name)
AND (csp_Roles.User_Id = 'SNEWTON') <= Ran with sample id for stats ORDER BY 1 ASC;
where CSP_ROLES is a view
select usr.usr_name user_id,
r.role_name role_name
from user_admin.usr_role ur,
user_admin.role r,
user_admin.usr_summary_vw us,
user_admin.usr usr
where ur.request_id = us.request_id
and us.usr_id = usr.usr_idand ur.role_id = r.role_id
where USR_SUMMARY_VW is a view
Select case when grp.grp_type_id > 0 then 'ORG: ' else 'WORK GROUP: '
ENDgrp.description as Requested_access,
ug.usr_id ,
ug.request_id
from usr_grp ug,
grp
where grp.grp_id = ug.grp_id
and ug.status_cd not in (4,7)
union
select systems.system_name' 'app.app_name' 'role.role_name'
'di.detail_itemdecode(d.detail_value,null,null,': ')
d.detail_valuedecode(role.role_id, 119,' email: 'u.email,null) as
Requested_access,
ug.usr_id ,
ug.request_id
from usr_role ug,
usr_role_detail d,
detail_item di,
usr u,
role,
app,
systems
where role.role_id = ug.role_id
and role.app_id = app.app_id
and u.usr_id = ug.usr_id
and systems.system_id = app.system_id
and ug.status_cd not in (4,7)
and ug.request_id = d.request_id (+)
and d.detail_item_id = di.detail_item_id (+)
CATEGORY ID
-------------------------------------------------- ----------
Annual Payment Update Reports 7
HCAHPS Warehouse Feedback Reports 18
HCAHPS Warehouse Submission Reports 17
HQA Preview Reports 9
Hospital Validation Reports 6
NHIFT Warehouse Comparison Reports 12
NHIFT Warehouse Submission Feedback Reports 4
Physician Office Data Submission Reports 15
Physician Office Data Submission Vendor Report 16
Physician Office Practice Info Reports 14
Physician Office Quality Measure Reports 26
Premier HQI Validation Reports 13
Premier Reports 8
QIO Clinical Warehouse Feedback Reports 5
QIO Clinical Warehouse Submission Reports 3
15 rows selected.
Elapsed: 00:00:04.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11192 Card=1 Bytes=137)
1 0 SORT (UNIQUE) (Cost=11191 Card=1 Bytes=137)
2 1 NESTED LOOPS (Cost=11190 Card=1 Bytes=137)
3 2 NESTED LOOPS (Cost=11189 Card=1 Bytes=105)
4 3 HASH JOIN (Cost=11188 Card=1 Bytes=99)
5 4 HASH JOIN (Cost=11179 Card=8 Bytes=584)
6 5 NESTED LOOPS (Cost=11177 Card=8 Bytes=392)
7 6 NESTED LOOPS (Cost=11171 Card=8 Bytes=320)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'USR' (TABLE) (Cost=2 Card=1 Bytes=14)
9 8 INDEX (UNIQUE SCAN) OF 'USR_UK1' (INDEX (UNIQUE)) (Cost=1 Card=1)
10 7 VIEW OF 'USR_SUMMARY_VW' (VIEW) (Cost=11169 Card=8 Bytes=208)
11 10 SORT (UNIQUE) (Cost=11169 Card=324682 Bytes=41411762)
12 11 UNION-ALL
13 12 HASH JOIN (Cost=177 Card=21586 Bytes=1403090)
14 13 TABLE ACCESS (FULL) OF 'USR_GRP' (TABLE) (Cost=76 Card=28755 Bytes=517590)
15 13 TABLE ACCESS (FULL) OF 'GRP' (TABLE) (Cost=100 Card=30000 Bytes=1410000)
16 12 HASH JOIN (Cost=1695 Card=303096 Bytes=40008672)
17 16 TABLE ACCESS (FULL) OF 'USR' (TABLE) (Cost=367 Card=38417 Bytes=1114093)
18 16 HASH JOIN (Cost=1325 Card=303096 Bytes=31218888)
19 18 TABLE ACCESS (FULL) OF 'SYSTEMS' (TABLE) (Cost=2 Card=10 Bytes=140)
20 18 HASH JOIN (Cost=1320 Card=303096 Bytes=26975544)
21 20 TABLE ACCESS (FULL) OF 'APP' (TABLE) (Cost=2 Card=47 Bytes=799)
22 20 HASH JOIN (Cost=1316 Card=303096 Bytes=21822912)
23 22 TABLE ACCESS (FULL) OF 'ROLE' (TABLE) (Cost=2 Card=193 Bytes=5211)
24 22 HASH JOIN (RIGHT OUTER) (Cost=1311 Card=303096 Bytes=13639320)
25 24 TABLE ACCESS (FULL) OF 'DETAIL_ITEM' (TABLE) (Cost=2 Card=31 Bytes=496)
26 24 HASH JOIN (RIGHT OUTER) (Cost=1306 Card=303096 Bytes=8789784)
27 26 INDEX (FAST FULL SCAN) OF'USR_ROLE_DETAIL_PK' (INDEX (UNIQUE)) (Cost=81 Card=94615 By tes=1135380)
28 26 TABLE ACCESS (FULL) OF 'USR_ROLE' (TABLE) (Cost=695 Card=303096 Bytes=5152632)
29 6 TABLE ACCESS (BY INDEX ROWID) OF 'USR_ROLE' (TABLE) (Cost=1 Card=1 Bytes=9)
30 29 INDEX (UNIQUE SCAN) OF 'USR_ROLE_PK' (INDEX(UNIQUE)) (Cost=1 Card=1)
31 5 TABLE ACCESS (FULL) OF 'ROLE' (TABLE) (Cost=2 Card=193 Bytes=4632)
32 4 TABLE ACCESS (FULL) OF 'CSP_RPT_GRP' (TABLE) (Cost=8 Card=22 Bytes=572)
33 3 INDEX (RANGE SCAN) OF 'PK_CSP_RPT_GRP_CTGRY' (INDEX(UNIQUE)) (Cost=1 Card=1 Bytes=6)
34 2 TABLE ACCESS (BY INDEX ROWID) OF 'CSP_RPT_CTGRY' (TABLE) (Cost=1 Card=1 Bytes=32)
35 34 INDEX (UNIQUE SCAN) OF 'PK_CSP_RPT_CTGRY' (INDEX (UNIQUE)) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6391 consistent gets
0 physical reads
0 redo size
873 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
15 rows processed
MODIFIED QUERY - 1
==================
SELECT distinct CSP_RPT_CTGRY.CTGRY_NAME Category
,CSP_RPT_CTGRY.CTGRY_ID Id
FROM qri.CSP_RPT_CTGRY
, qri.CSP_RPT_GRP
, qri.CSP_RPT_GRP_CTGRY
, User_AdmIn.Usr
, User_AdmIn.ROLE
, user_admin.usr_role
WHERE (CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID)
AND (CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID)
AND (CSP_RPT_GRP.RPT_GRP_NAME = role.Role_Name)
AND (usr_role.usr_id = usr.usr_id)
AND (usr_role.role_id = role.role_id)
AND (Usr.usr_name = 'SNEWTON')
AND (usr_role.status_cd not in (4,7))
ORDER BY 1 ASC;
CATEGORY ID
-------------------------------------------------- ----------
Annual Payment Update Reports 7
HCAHPS Warehouse Feedback Reports 18
HCAHPS Warehouse Submission Reports 17
HQA Preview Reports 9
Hospital Validation Reports 6
NHIFT Warehouse Comparison Reports 12
NHIFT Warehouse Submission Feedback Reports 4
Physician Office Data Submission Reports 15
Physician Office Data Submission Vendor Report 16
Physician Office Practice Info Reports 14
Physician Office Quality Measure Reports 26
Premier HQI Validation Reports 13
Premier Reports 8
QIO Clinical Warehouse Feedback Reports 5
QIO Clinical Warehouse Submission Reports 3
15 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1 Bytes=114)
1 0 SORT (UNIQUE) (Cost=19 Card=1 Bytes=114)
2 1 NESTED LOOPS (Cost=18 Card=1 Bytes=114)
3 2 NESTED LOOPS (Cost=17 Card=1 Bytes=82)
4 3 HASH JOIN (Cost=16 Card=1 Bytes=76)
5 4 HASH JOIN (Cost=8 Card=8 Bytes=400)
6 5 NESTED LOOPS (Cost=5 Card=8 Bytes=208)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'USR' (TABLE) (Cost=2 Card=1 Bytes=14)
8 7 INDEX (UNIQUE SCAN) OF 'USR_UK1' (INDEX (UNIQUE)) (Cost=1 Card=1)
9 6 TABLE ACCESS (BY INDEX ROWID) OF 'USR_ROLE' (TABLE) (Cost=4 Card=8 Bytes=96)
10 9 INDEX (RANGE SCAN) OF 'USR_ROLE_UK1' (INDEX(UNIQUE)) (Cost=1 Card=12)
11 5 TABLE ACCESS (FULL) OF 'ROLE' (TABLE) (Cost=2 Card=193 Bytes=4632)
12 4 TABLE ACCESS (FULL) OF 'CSP_RPT_GRP' (TABLE) (Cost=8 Card=22 Bytes=572)
13 3 INDEX (RANGE SCAN) OF 'PK_CSP_RPT_GRP_CTGRY' (INDEX(UNIQUE)) (Cost=1 Card=1 Bytes=6)
14 2 TABLE ACCESS (BY INDEX ROWID) OF 'CSP_RPT_CTGRY' (TABLE) (Cost=1 Card=1 Bytes=32)
15 14 INDEX (UNIQUE SCAN) OF 'PK_CSP_RPT_CTGRY' (INDEX (UNIQUE)) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
122 consistent gets
0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
Modified Query – 2 (Vw defination is same in query 1)
=======================================================
SQL> SELECT distinct CSP_RPT_DEFN.RPT_ARGMNT_CMPNT_NAME,
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
4 CSP_RPT_DEFN.DBMS_TXT,
5 CSP_RPT_DEFN.CACHE_TXT,
6 CSP_RPT_DEFN.RPT_CRTR_TXT,
7 CSP_RPT_DEFN.CTGRY_ID,
8 CSP_RPT_DEFN.RPT_CMPNT_NAME,
9 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_WT_NUM
12 FROM qri.CSP_RPT_CTGRY,
13 qri.CSP_RPT_DEFN,
14 qri.CSP_RPT_GRP_CTGRY,
15 qri.CSP_RPT_GRP,
16 (select usr.usr_name user_id,
17 r.role_name role_name
18 from
19 user_admin.usr_role ur,
20 (
21 select ug.usr_id,ug.request_id from user_admin.usr_grp ug
22 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
23 union all
24 select ug.usr_id,ug.request_id from user_admin.usr_role ug
25 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
26 ) us,
27 user_admin.usr,
28 user_admin.role r
29 where ur.request_id = us.request_id
30 and us.usr_id = usr.usr_id
31 and ur.role_id = r.role_id
32 and usr.usr_name='SNEWTON') csp_roles
33 WHERE ( CSP_RPT_DEFN.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
34 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
35 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
36 ((CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) AND
37 (csp_roles.user_id = 'SNEWTON' ) AND
38 ( CSP_RPT_DEFN.RPT_DEFN_ID = 5 ) );
RPT_ARGMNT_CMPNT_NAME RPT_NAME
---------------------------------------- ----------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME DBMS_TXT CACHE_TXT
---------------------------------------- -------------------- --------------------
RPT_CRTR_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
CTGRY_ID RPT_CMPNT_NAME RPT_FNCTN_CALL_CD
---------- ---------------------------------------------------------------------------------------------------- -----------------
RPT_PARM_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
RPT_WT_NUM
----------
QIOHospsAuthToUploadDataWid Hospitals Authorizing Vendor to Upload Data
n_ds_cart_sub_authorizing_vendor O84 QIONATIONAL
3 pb/PBReportService 1
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
1
1 row selected.
Elapsed: 00:00:00.35
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)
---------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 20 (5)
1 HASH UNIQUE 1 337 20 (5)
2 NESTED LOOPS 1 337 19 (0)
3 NESTED LOOPS 1 311 18 (0)
4 NESTED LOOPS 1 305 17 (0)
5 NESTED LOOPS 1 280 16 (0)
6 NESTED LOOPS 1 271 15 (0)
7 NESTED LOOPS 1 245 4 (0)
8 NESTED LOOPS 1 242 3 (0)
9 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 10 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
11 TABLE ACCESS BY INDEX ROWID CSP_RPT_DEFN 1 228 1 (0)
* 12 INDEX UNIQUE SCAN PK_CSP_RPT_DEFN 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 29 87 1 (0)
* 14 VIEW 1 26 11 (0)
15 UNION-ALL
* 16 TABLE ACCESS BY INDEX ROWID USR_GRP 1 13 2 (0)
* 17 INDEX RANGE SCAN USR_GRP_UK1 1 2 (0)
18 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 19 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 20 TABLE ACCESS BY INDEX ROWID USR_ROLE 9 117 6 (0)
* 21 INDEX RANGE SCAN USR_ROLE_UK1 14 2 (0)
22 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 23 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
24 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 25 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
26 TABLE ACCESS BY INDEX ROWID ROLE 1 25 1 (0)
* 27 INDEX UNIQUE SCAN ROLE_PK 1 1 (0)
* 28 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
* 29 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 30 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("USR"."USR_NAME"='SNEWTON')
12 - access("CSP_RPT_DEFN"."RPT_DEFN_ID"=5)
13 - access("CSP_RPT_DEFN"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
14 - filter("US"."USR_ID"="USR"."USR_ID")
16 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
17 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
19 - access("USR_NAME"='SNEWTON')
20 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
21 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
23 - access("USR_NAME"='SNEWTON')
25 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
27 - access("UR"."ROLE_ID"="R"."ROLE_ID")
28 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
29 - filter("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
30 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
681 consistent gets
0 physical reads
0 redo size
883 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Original Query – 2
=============================
SQL> SELECT distinct CSP_RPT_DEFN.RPT_ARGMNT_CMPNT_NAME,
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
4 CSP_RPT_DEFN.DBMS_TXT,
5 CSP_RPT_DEFN.CACHE_TXT,
6 CSP_RPT_DEFN.RPT_CRTR_TXT,
7 CSP_RPT_DEFN.CTGRY_ID,
8 CSP_RPT_DEFN.RPT_CMPNT_NAME,
9 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_WT_NUM
12 FROM qri.CSP_RPT_CTGRY,
13 qri.CSP_RPT_DEFN,
14 qri.CSP_RPT_GRP_CTGRY,
15 qri.CSP_RPT_GRP,
16 qri.csp_roles
17 WHERE ( CSP_RPT_DEFN.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
18 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
19 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
20 ((CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) AND
21 (csp_roles.user_id = 'SNEWTON' ) AND
22 ( CSP_RPT_DEFN.RPT_DEFN_ID = 5 ) ) ;
RPT_ARGMNT_CMPNT_NAME RPT_NAME
---------------------------------------- ----------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME DBMS_TXT CACHE_TXT
---------------------------------------- -------------------- --------------------
RPT_CRTR_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
CTGRY_ID RPT_CMPNT_NAME RPT_FNCTN_CALL_CD
---------- ---------------------------------------------------------------------------------------------------- -----------------
RPT_PARM_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
RPT_WT_NUM
----------
QIOHospsAuthToUploadDataWid Hospitals Authorizing Vendor to Upload Data
n_ds_cart_sub_authorizing_vendor O84 QIONATIONAL
3 pb/PBReportService 1
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
1
1 row selected.
Elapsed: 00:00:04.65
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU)
---------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 12809 (1)
1 HASH UNIQUE 1 337 12809 (1)
2 NESTED LOOPS 1 337 12808 (1)
* 3 HASH JOIN 8 2624 12802 (1)
* 4 HASH JOIN 1 302 8 (0)
5 NESTED LOOPS 1 277 6 (0)
6 NESTED LOOPS 1 251 5 (0)
7 NESTED LOOPS 1 245 4 (0)
8 NESTED LOOPS 1 242 3 (0)
9 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 10 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
11 TABLE ACCESS BY INDEX ROWID CSP_RPT_DEFN 1 228 1 (0)
* 12 INDEX UNIQUE SCAN PK_CSP_RPT_DEFN 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 29 87 1 (0)
* 14 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
15 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 16 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
17 TABLE ACCESS FULL ROLE 217 5425 2 (0)
18 VIEW USR_SUMMARY_VW 367K 9343K 12791 (1)
19 SORT UNIQUE 367K 45M 96M 12791 (99)
20 UNION-ALL
* 21 HASH JOIN 8611 546K 186 (3)
* 22 TABLE ACCESS FULL USR_GRP 30014 527K 82 (3)
23 TABLE ACCESS FULL GRP 30702 1409K 103 (1)
* 24 HASH JOIN 359K 45M 1986 (3)
25 TABLE ACCESS FULL USR 41073 1163K 390 (1)
* 26 HASH JOIN 359K 35M 1592 (3)
27 TABLE ACCESS FULL SYSTEMS 11 143 2 (0)
* 28 HASH JOIN 359K 30M 1587 (3)
29 TABLE ACCESS FULL APP 47 799 2 (0)
* 30 HASH JOIN 359K 25M 1582 (3)
31 TABLE ACCESS FULL ROLE 217 6076 2 (0)
* 32 HASH JOIN RIGHT OUTER 359K 15M 1577 (2)
33 TABLE ACCESS FULL DETAIL_ITEM 31 496 2 (0)
* 34 HASH JOIN RIGHT OUTER 359K 9M 2312K 1572 (2)
35 INDEX FAST FULL SCAN USR_ROLE_DETAIL_PK 98547 1154K 86 (2)
* 36 TABLE ACCESS FULL USR_ROLE 359K 5966K 873 (3)
* 37 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 38 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("US"."USR_ID"="USR"."USR_ID")
4 - access("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
10 - access("USR"."USR_NAME"='SNEWTON')
12 - access("CSP_RPT_DEFN"."RPT_DEFN_ID"=5)
13 - access("CSP_RPT_DEFN"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
14 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
16 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
21 - access("GRP"."GRP_ID"="UG"."GRP_ID")
22 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
24 - access("U"."USR_ID"="UG"."USR_ID")
26 - access("SYSTEMS"."SYSTEM_ID"="APP"."SYSTEM_ID")
28 - access("ROLE"."APP_ID"="APP"."APP_ID")
30 - access("ROLE"."ROLE_ID"="UG"."ROLE_ID")
32 - access("D"."DETAIL_ITEM_ID"="DI"."DETAIL_ITEM_ID"(+))
34 - access("UG"."REQUEST_ID"="D"."REQUEST_ID"(+))
36 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
37 - filter("UR"."ROLE_ID"="R"."ROLE_ID")
38 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
7326 consistent gets
2 physical reads
0 redo size
883 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Modified Query - 3 (Vw definations are same as in query 1)
============================================================
SQL> SELECT DISTINCT
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_DEFN_ID,
4 CSP_RPT_DEFN.RPT_DESC,
5 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
6 CSP_RPT_DEFN.CTGRY_ID,
7 CSP_RPT_DEFN.DBMS_TXT,
8 CSP_RPT_DEFN.CACHE_TXT,
9 CSP_RPT_DEFN.RPT_CRTR_TXT,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_CMPNT_NAME,
12 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
13 CSP_RPT_TYPE_CD.ICON_NAME,
14 CSP_RPT_TYPE_CD.RPT_TYPE_CD_DESC
15 FROM qri.CSP_RPT_DEFN,
16 qri.CSP_RPT_CTGRY,
17 qri.CSP_RPT_GRP_CTGRY,
18 qri.CSP_RPT_GRP,
19 qri.CSP_RPT_TYPE_CD,
20 (select usr.usr_name user_id,
21 r.role_name role_name
22 from
23 user_admin.usr_role ur,
24 (
25 select ug.usr_id,ug.request_id from user_admin.usr_grp ug
26 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
27 union all
28 select ug.usr_id,ug.request_id from user_admin.usr_role ug
29 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
30 ) us,
31 user_admin.usr,
32 user_admin.role r
33 where ur.request_id = us.request_id
34 and us.usr_id = usr.usr_id
35 and ur.role_id = r.role_id
36 and usr.usr_name='SNEWTON') csp_roles
37 WHERE ( CSP_RPT_CTGRY.CTGRY_ID = CSP_RPT_DEFN.CTGRY_ID ) and
38 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
39 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
40 ( CSP_RPT_DEFN.CTGRY_ID = 3) AND
41 ( CSP_RPT_DEFN.RPT_TYPE_CD = CSP_RPT_TYPE_CD.RPT_TYPE_CD ) and
42 ( CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) and
43 ( csp_roles.user_id = 'SNEWTON')
44 ORDER BY 1 ASC;
RPT_NAME RPT_DEFN_ID
---------------------------------------------------------------------------------------------------- -----------
RPT_DESC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME CTGRY_ID DBMS_TXT CACHE_TXT
---------------------------------------- ---------- -------------------- --------------------
RPT_CRTR_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_PARM_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_CMPNT_NAME RPT_FNCTN_CALL_CD ICON_NAME RPT_TYPE_CD_DESC
---------------------------------------------------------------------------------------------------- ----------------- ------------------------- -------------------------
Hospitals Authorizing Vendor to Upload Data 5
Hospitals Authorizing Vendor to Upload Data.
n_ds_cart_sub_authorizing_vendor 3 O84 QIONATIONAL
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse ICD Population Submission Report 4015
The report provides information regarding the submission of ICD population and sampling data. The report displays whether the data was added or deleted, successfully accepted and
any errors related to rejection.
n_ds_icd_population_submission 3 O84 STAGING
BatchIDBatchIdListHspProvIDHspProviderIdWDatesqlQuerycartids1001
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Error Code 6
Displays detailed file information of selected uploaded data grouped by error code.
n_ds_cart_sub_detail_by_errorcode 3 O84 STAGING
link./dynamicReport.do?id=28RepFormatyeserror_selcartDBsort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids28actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Provider 8
Displays detail file information of selected uploaded data grouped by provider.
n_ds_cart_sub_detail_by_provider 3 O84 STAGING
link./dynamicReport.do?id=30RepFormatyesmsg_typeqiosort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids30actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Submission Summary 7
Displays summary information of selected uploaded data (provider, topic, status, and number of records).
n_ds_cart_sub_summary 3 O84 STAGING
link./dynamicReport.do?id=29doc_frmt_cd2sort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids29sqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
5 rows selected.
Elapsed: 00:00:00.48
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 464 26 (12)
1 SORT ORDER BY 1 464 26 (12)
2 HASH UNIQUE 1 464 25 (8)
3 NESTED LOOPS 1 464 24 (5)
4 NESTED LOOPS 1 439 23 (5)
5 NESTED LOOPS 1 430 22 (5)
6 NESTED LOOPS 1 404 21 (5)
* 7 HASH JOIN 1 377 20 (5)
* 8 HASH JOIN 5 1755 8 (0)
9 NESTED LOOPS 1 23 4 (0)
10 NESTED LOOPS 1 17 3 (0)
11 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 12 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 1 3 1 (0)
* 14 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
* 15 TABLE ACCESS FULL CSP_RPT_DEFN 5 1640 4 (0)
16 VIEW 10 260 11 (0)
17 UNION-ALL
* 18 TABLE ACCESS BY INDEX ROWID USR_GRP 1 13 2 (0)
* 19 INDEX RANGE SCAN USR_GRP_UK1 1 2 (0)
20 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 21 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 22 TABLE ACCESS BY INDEX ROWID USR_ROLE 9 117 6 (0)
* 23 INDEX RANGE SCAN USR_ROLE_UK1 14 2 (0)
24 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 25 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
26 TABLE ACCESS BY INDEX ROWID CSP_RPT_TYPE_CD 1 27 1 (0)
* 27 INDEX UNIQUE SCAN PK_CSP_RPT_TYPE_CD 1 1 (0)
28 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 29 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
30 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 31 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
* 32 TABLE ACCESS BY INDEX ROWID ROLE 1 25 1 (0)
* 33 INDEX UNIQUE SCAN ROLE_PK 1 1 (0)
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("US"."USR_ID"="USR"."USR_ID")
8 - access("CSP_RPT_CTGRY"."CTGRY_ID"="CSP_RPT_DEFN"."CTGRY_ID")
12 - access("USR"."USR_NAME"='SNEWTON')
13 - access("CSP_RPT_CTGRY"."CTGRY_ID"=3)
14 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
15 - filter("CSP_RPT_DEFN"."CTGRY_ID"=3)
18 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
19 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
21 - access("USR_NAME"='SNEWTON')
22 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
23 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
25 - access("USR_NAME"='SNEWTON')
27 - access("CSP_RPT_DEFN"."RPT_TYPE_CD"="CSP_RPT_TYPE_CD"."RPT_TYPE_CD")
29 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
31 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
32 - filter("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
33 - access("UR"."ROLE_ID"="R"."ROLE_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3624 consistent gets
0 physical reads
0 redo size
2357 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
Original Query – 3
==================
SQL>
SQL> SELECT DISTINCT
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_DEFN_ID,
4 CSP_RPT_DEFN.RPT_DESC,
5 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
6 CSP_RPT_DEFN.CTGRY_ID,
7 CSP_RPT_DEFN.DBMS_TXT,
8 CSP_RPT_DEFN.CACHE_TXT,
9 CSP_RPT_DEFN.RPT_CRTR_TXT,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_CMPNT_NAME,
12 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
13 CSP_RPT_TYPE_CD.ICON_NAME,
14 CSP_RPT_TYPE_CD.RPT_TYPE_CD_DESC
15 FROM qri.CSP_RPT_DEFN,
16 qri.CSP_RPT_CTGRY,
17 qri.CSP_RPT_GRP_CTGRY,
18 qri.CSP_RPT_GRP,
19 qri.CSP_RPT_TYPE_CD,
20 qri.csp_roles
21 WHERE ( CSP_RPT_CTGRY.CTGRY_ID = CSP_RPT_DEFN.CTGRY_ID ) and
22 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
23 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
24 ( CSP_RPT_DEFN.CTGRY_ID = 3 ) AND
25 ( CSP_RPT_DEFN.RPT_TYPE_CD = CSP_RPT_TYPE_CD.RPT_TYPE_CD ) and
26 ( CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) and
27 ( csp_roles.user_id = 'SNEWTON')
28 ORDER BY 1 ASC;
RPT_NAME RPT_DEFN_ID
---------------------------------------------------------------------------------------------------- -----------
RPT_DESC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME CTGRY_ID DBMS_TXT CACHE_TXT
---------------------------------------- ---------- -------------------- --------------------
RPT_CRTR_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_PARM_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_CMPNT_NAME RPT_FNCTN_CALL_CD ICON_NAME RPT_TYPE_CD_DESC
---------------------------------------------------------------------------------------------------- ----------------- ------------------------- -------------------------
Hospitals Authorizing Vendor to Upload Data 5
Hospitals Authorizing Vendor to Upload Data.
n_ds_cart_sub_authorizing_vendor 3 O84 QIONATIONAL
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse ICD Population Submission Report 4015
The report provides information regarding the submission of ICD population and sampling data. The report displays whether the data was added or deleted, successfully accepted and
any errors related to rejection.
n_ds_icd_population_submission 3 O84 STAGING
BatchIDBatchIdListHspProvIDHspProviderIdWDatesqlQuerycartids1001
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Error Code 6
Displays detailed file information of selected uploaded data grouped by error code.
n_ds_cart_sub_detail_by_errorcode 3 O84 STAGING
link./dynamicReport.do?id=28RepFormatyeserror_selcartDBsort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids28actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Provider 8
Displays detail file information of selected uploaded data grouped by provider.
n_ds_cart_sub_detail_by_provider 3 O84 STAGING
link./dynamicReport.do?id=30RepFormatyesmsg_typeqiosort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids30actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Submission Summary 7
Displays summary information of selected uploaded data (provider, topic, status, and number of records).
n_ds_cart_sub_summary 3 O84 STAGING
link./dynamicReport.do?id=29doc_frmt_cd2sort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids29sqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
5 rows selected.
Elapsed: 00:00:04.76
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU)
-----------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 464 12815 (1)
1 SORT ORDER BY 1 464 12815 (1)
2 HASH UNIQUE 1 464 12814 (1)
3 NESTED LOOPS 1 464 12813 (1)
* 4 HASH JOIN 1 437 12812 (1)
5 NESTED LOOPS 1 109 12808 (1)
* 6 HASH JOIN 9 900 12801 (1)
* 7 HASH JOIN 1 74 7 (0)
8 NESTED LOOPS 1 49 5 (0)
9 NESTED LOOPS 1 23 4 (0)
10 NESTED LOOPS 1 17 3 (0)
11 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 12 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 1 3 1 (0)
* 14 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
15 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 16 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
17 TABLE ACCESS FULL ROLE 217 5425 2 (0)
18 VIEW USR_SUMMARY_VW 367K 9343K 12791 (1)
19 SORT UNIQUE 367K 45M 96M 12791 (99)
20 UNION-ALL
* 21 HASH JOIN 8611 546K 186 (3)
* 22 TABLE ACCESS FULL USR_GRP 30014 527K 82 (3)
23 TABLE ACCESS FULL GRP 30702 1409K 103 (1)
* 24 HASH JOIN 359K 45M 1986 (3)
25 TABLE ACCESS FULL USR 41073 1163K 390 (1)
* 26 HASH JOIN 359K 35M 1592 (3)
27 TABLE ACCESS FULL SYSTEMS 11 143 2 (0)
* 28 HASH JOIN 359K 30M 1587 (3)
29 TABLE ACCESS FULL APP 47 799 2 (0)
* 30 HASH JOIN 359K 25M 1582 (3)
31 TABLE ACCESS FULL ROLE 217 6076 2 (0)
* 32 HASH JOIN RIGHT OUTER 359K 15M 1577 (2)
33 TABLE ACCESS FULL DETAIL_ITEM 31 496 2 (0)
* 34 HASH JOIN RIGHT OUTER 359K 9M 2312K 1572 (2)
35 INDEX FAST FULL SCAN USR_ROLE_DETAIL_PK 98547 1154K 86 (2)
* 36 TABLE ACCESS FULL USR_ROLE 359K 5966K 873 (3)
* 37 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 38 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
* 39 TABLE ACCESS FULL CSP_RPT_DEFN 5 1640 4 (0)
40 TABLE ACCESS BY INDEX ROWID CSP_RPT_TYPE_CD 1 27 1 (0)
* 41 INDEX UNIQUE SCAN PK_CSP_RPT_TYPE_CD 1 1 (0)
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CSP_RPT_CTGRY"."CTGRY_ID"="CSP_RPT_DEFN"."CTGRY_ID")
6 - access("US"."USR_ID"="USR"."USR_ID")
7 - access("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
12 - access("USR"."USR_NAME"='SNEWTON')
13 - access("CSP_RPT_CTGRY"."CTGRY_ID"=3)
14 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
16 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
21 - access("GRP"."GRP_ID"="UG"."GRP_ID")
22 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
24 - access("U"."USR_ID"="UG"."USR_ID")
26 - access("SYSTEMS"."SYSTEM_ID"="APP"."SYSTEM_ID")
28 - access("ROLE"."APP_ID"="APP"."APP_ID")
30 - access("ROLE"."ROLE_ID"="UG"."ROLE_ID")
32 - access("D"."DETAIL_ITEM_ID"="DI"."DETAIL_ITEM_ID"(+))
34 - access("UG"."REQUEST_ID"="D"."REQUEST_ID"(+))
36 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
37 - filter("UR"."ROLE_ID"="R"."ROLE_ID")
38 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
39 - filter("CSP_RPT_DEFN"."CTGRY_ID"=3)
41 - access("CSP_RPT_DEFN"."RPT_TYPE_CD"="CSP_RPT_TYPE_CD"."RPT_TYPE_CD")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7342 consistent gets
0 physical reads
0 redo size
2357 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
r.role_name role_name
from user_admin.usr_role ur,
user_admin.role r,
user_admin.usr_summary_vw us,
user_admin.usr usr
where ur.request_id = us.request_id
and us.usr_id = usr.usr_idand ur.role_id = r.role_id
where USR_SUMMARY_VW is a view
Select case when grp.grp_type_id > 0 then 'ORG: ' else 'WORK GROUP: '
ENDgrp.description as Requested_access,
ug.usr_id ,
ug.request_id
from usr_grp ug,
grp
where grp.grp_id = ug.grp_id
and ug.status_cd not in (4,7)
union
select systems.system_name' 'app.app_name' 'role.role_name'
'di.detail_itemdecode(d.detail_value,null,null,': ')
d.detail_valuedecode(role.role_id, 119,' email: 'u.email,null) as
Requested_access,
ug.usr_id ,
ug.request_id
from usr_role ug,
usr_role_detail d,
detail_item di,
usr u,
role,
app,
systems
where role.role_id = ug.role_id
and role.app_id = app.app_id
and u.usr_id = ug.usr_id
and systems.system_id = app.system_id
and ug.status_cd not in (4,7)
and ug.request_id = d.request_id (+)
and d.detail_item_id = di.detail_item_id (+)
CATEGORY ID
-------------------------------------------------- ----------
Annual Payment Update Reports 7
HCAHPS Warehouse Feedback Reports 18
HCAHPS Warehouse Submission Reports 17
HQA Preview Reports 9
Hospital Validation Reports 6
NHIFT Warehouse Comparison Reports 12
NHIFT Warehouse Submission Feedback Reports 4
Physician Office Data Submission Reports 15
Physician Office Data Submission Vendor Report 16
Physician Office Practice Info Reports 14
Physician Office Quality Measure Reports 26
Premier HQI Validation Reports 13
Premier Reports 8
QIO Clinical Warehouse Feedback Reports 5
QIO Clinical Warehouse Submission Reports 3
15 rows selected.
Elapsed: 00:00:04.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11192 Card=1 Bytes=137)
1 0 SORT (UNIQUE) (Cost=11191 Card=1 Bytes=137)
2 1 NESTED LOOPS (Cost=11190 Card=1 Bytes=137)
3 2 NESTED LOOPS (Cost=11189 Card=1 Bytes=105)
4 3 HASH JOIN (Cost=11188 Card=1 Bytes=99)
5 4 HASH JOIN (Cost=11179 Card=8 Bytes=584)
6 5 NESTED LOOPS (Cost=11177 Card=8 Bytes=392)
7 6 NESTED LOOPS (Cost=11171 Card=8 Bytes=320)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'USR' (TABLE) (Cost=2 Card=1 Bytes=14)
9 8 INDEX (UNIQUE SCAN) OF 'USR_UK1' (INDEX (UNIQUE)) (Cost=1 Card=1)
10 7 VIEW OF 'USR_SUMMARY_VW' (VIEW) (Cost=11169 Card=8 Bytes=208)
11 10 SORT (UNIQUE) (Cost=11169 Card=324682 Bytes=41411762)
12 11 UNION-ALL
13 12 HASH JOIN (Cost=177 Card=21586 Bytes=1403090)
14 13 TABLE ACCESS (FULL) OF 'USR_GRP' (TABLE) (Cost=76 Card=28755 Bytes=517590)
15 13 TABLE ACCESS (FULL) OF 'GRP' (TABLE) (Cost=100 Card=30000 Bytes=1410000)
16 12 HASH JOIN (Cost=1695 Card=303096 Bytes=40008672)
17 16 TABLE ACCESS (FULL) OF 'USR' (TABLE) (Cost=367 Card=38417 Bytes=1114093)
18 16 HASH JOIN (Cost=1325 Card=303096 Bytes=31218888)
19 18 TABLE ACCESS (FULL) OF 'SYSTEMS' (TABLE) (Cost=2 Card=10 Bytes=140)
20 18 HASH JOIN (Cost=1320 Card=303096 Bytes=26975544)
21 20 TABLE ACCESS (FULL) OF 'APP' (TABLE) (Cost=2 Card=47 Bytes=799)
22 20 HASH JOIN (Cost=1316 Card=303096 Bytes=21822912)
23 22 TABLE ACCESS (FULL) OF 'ROLE' (TABLE) (Cost=2 Card=193 Bytes=5211)
24 22 HASH JOIN (RIGHT OUTER) (Cost=1311 Card=303096 Bytes=13639320)
25 24 TABLE ACCESS (FULL) OF 'DETAIL_ITEM' (TABLE) (Cost=2 Card=31 Bytes=496)
26 24 HASH JOIN (RIGHT OUTER) (Cost=1306 Card=303096 Bytes=8789784)
27 26 INDEX (FAST FULL SCAN) OF'USR_ROLE_DETAIL_PK' (INDEX (UNIQUE)) (Cost=81 Card=94615 By tes=1135380)
28 26 TABLE ACCESS (FULL) OF 'USR_ROLE' (TABLE) (Cost=695 Card=303096 Bytes=5152632)
29 6 TABLE ACCESS (BY INDEX ROWID) OF 'USR_ROLE' (TABLE) (Cost=1 Card=1 Bytes=9)
30 29 INDEX (UNIQUE SCAN) OF 'USR_ROLE_PK' (INDEX(UNIQUE)) (Cost=1 Card=1)
31 5 TABLE ACCESS (FULL) OF 'ROLE' (TABLE) (Cost=2 Card=193 Bytes=4632)
32 4 TABLE ACCESS (FULL) OF 'CSP_RPT_GRP' (TABLE) (Cost=8 Card=22 Bytes=572)
33 3 INDEX (RANGE SCAN) OF 'PK_CSP_RPT_GRP_CTGRY' (INDEX(UNIQUE)) (Cost=1 Card=1 Bytes=6)
34 2 TABLE ACCESS (BY INDEX ROWID) OF 'CSP_RPT_CTGRY' (TABLE) (Cost=1 Card=1 Bytes=32)
35 34 INDEX (UNIQUE SCAN) OF 'PK_CSP_RPT_CTGRY' (INDEX (UNIQUE)) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6391 consistent gets
0 physical reads
0 redo size
873 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
15 rows processed
MODIFIED QUERY - 1
==================
SELECT distinct CSP_RPT_CTGRY.CTGRY_NAME Category
,CSP_RPT_CTGRY.CTGRY_ID Id
FROM qri.CSP_RPT_CTGRY
, qri.CSP_RPT_GRP
, qri.CSP_RPT_GRP_CTGRY
, User_AdmIn.Usr
, User_AdmIn.ROLE
, user_admin.usr_role
WHERE (CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID)
AND (CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID)
AND (CSP_RPT_GRP.RPT_GRP_NAME = role.Role_Name)
AND (usr_role.usr_id = usr.usr_id)
AND (usr_role.role_id = role.role_id)
AND (Usr.usr_name = 'SNEWTON')
AND (usr_role.status_cd not in (4,7))
ORDER BY 1 ASC;
CATEGORY ID
-------------------------------------------------- ----------
Annual Payment Update Reports 7
HCAHPS Warehouse Feedback Reports 18
HCAHPS Warehouse Submission Reports 17
HQA Preview Reports 9
Hospital Validation Reports 6
NHIFT Warehouse Comparison Reports 12
NHIFT Warehouse Submission Feedback Reports 4
Physician Office Data Submission Reports 15
Physician Office Data Submission Vendor Report 16
Physician Office Practice Info Reports 14
Physician Office Quality Measure Reports 26
Premier HQI Validation Reports 13
Premier Reports 8
QIO Clinical Warehouse Feedback Reports 5
QIO Clinical Warehouse Submission Reports 3
15 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1 Bytes=114)
1 0 SORT (UNIQUE) (Cost=19 Card=1 Bytes=114)
2 1 NESTED LOOPS (Cost=18 Card=1 Bytes=114)
3 2 NESTED LOOPS (Cost=17 Card=1 Bytes=82)
4 3 HASH JOIN (Cost=16 Card=1 Bytes=76)
5 4 HASH JOIN (Cost=8 Card=8 Bytes=400)
6 5 NESTED LOOPS (Cost=5 Card=8 Bytes=208)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'USR' (TABLE) (Cost=2 Card=1 Bytes=14)
8 7 INDEX (UNIQUE SCAN) OF 'USR_UK1' (INDEX (UNIQUE)) (Cost=1 Card=1)
9 6 TABLE ACCESS (BY INDEX ROWID) OF 'USR_ROLE' (TABLE) (Cost=4 Card=8 Bytes=96)
10 9 INDEX (RANGE SCAN) OF 'USR_ROLE_UK1' (INDEX(UNIQUE)) (Cost=1 Card=12)
11 5 TABLE ACCESS (FULL) OF 'ROLE' (TABLE) (Cost=2 Card=193 Bytes=4632)
12 4 TABLE ACCESS (FULL) OF 'CSP_RPT_GRP' (TABLE) (Cost=8 Card=22 Bytes=572)
13 3 INDEX (RANGE SCAN) OF 'PK_CSP_RPT_GRP_CTGRY' (INDEX(UNIQUE)) (Cost=1 Card=1 Bytes=6)
14 2 TABLE ACCESS (BY INDEX ROWID) OF 'CSP_RPT_CTGRY' (TABLE) (Cost=1 Card=1 Bytes=32)
15 14 INDEX (UNIQUE SCAN) OF 'PK_CSP_RPT_CTGRY' (INDEX (UNIQUE)) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
122 consistent gets
0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
Modified Query – 2 (Vw defination is same in query 1)
=======================================================
SQL> SELECT distinct CSP_RPT_DEFN.RPT_ARGMNT_CMPNT_NAME,
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
4 CSP_RPT_DEFN.DBMS_TXT,
5 CSP_RPT_DEFN.CACHE_TXT,
6 CSP_RPT_DEFN.RPT_CRTR_TXT,
7 CSP_RPT_DEFN.CTGRY_ID,
8 CSP_RPT_DEFN.RPT_CMPNT_NAME,
9 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_WT_NUM
12 FROM qri.CSP_RPT_CTGRY,
13 qri.CSP_RPT_DEFN,
14 qri.CSP_RPT_GRP_CTGRY,
15 qri.CSP_RPT_GRP,
16 (select usr.usr_name user_id,
17 r.role_name role_name
18 from
19 user_admin.usr_role ur,
20 (
21 select ug.usr_id,ug.request_id from user_admin.usr_grp ug
22 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
23 union all
24 select ug.usr_id,ug.request_id from user_admin.usr_role ug
25 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
26 ) us,
27 user_admin.usr,
28 user_admin.role r
29 where ur.request_id = us.request_id
30 and us.usr_id = usr.usr_id
31 and ur.role_id = r.role_id
32 and usr.usr_name='SNEWTON') csp_roles
33 WHERE ( CSP_RPT_DEFN.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
34 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
35 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
36 ((CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) AND
37 (csp_roles.user_id = 'SNEWTON' ) AND
38 ( CSP_RPT_DEFN.RPT_DEFN_ID = 5 ) );
RPT_ARGMNT_CMPNT_NAME RPT_NAME
---------------------------------------- ----------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME DBMS_TXT CACHE_TXT
---------------------------------------- -------------------- --------------------
RPT_CRTR_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
CTGRY_ID RPT_CMPNT_NAME RPT_FNCTN_CALL_CD
---------- ---------------------------------------------------------------------------------------------------- -----------------
RPT_PARM_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
RPT_WT_NUM
----------
QIOHospsAuthToUploadDataWid Hospitals Authorizing Vendor to Upload Data
n_ds_cart_sub_authorizing_vendor O84 QIONATIONAL
3 pb/PBReportService 1
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
1
1 row selected.
Elapsed: 00:00:00.35
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)
---------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 20 (5)
1 HASH UNIQUE 1 337 20 (5)
2 NESTED LOOPS 1 337 19 (0)
3 NESTED LOOPS 1 311 18 (0)
4 NESTED LOOPS 1 305 17 (0)
5 NESTED LOOPS 1 280 16 (0)
6 NESTED LOOPS 1 271 15 (0)
7 NESTED LOOPS 1 245 4 (0)
8 NESTED LOOPS 1 242 3 (0)
9 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 10 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
11 TABLE ACCESS BY INDEX ROWID CSP_RPT_DEFN 1 228 1 (0)
* 12 INDEX UNIQUE SCAN PK_CSP_RPT_DEFN 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 29 87 1 (0)
* 14 VIEW 1 26 11 (0)
15 UNION-ALL
* 16 TABLE ACCESS BY INDEX ROWID USR_GRP 1 13 2 (0)
* 17 INDEX RANGE SCAN USR_GRP_UK1 1 2 (0)
18 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 19 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 20 TABLE ACCESS BY INDEX ROWID USR_ROLE 9 117 6 (0)
* 21 INDEX RANGE SCAN USR_ROLE_UK1 14 2 (0)
22 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 23 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
24 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 25 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
26 TABLE ACCESS BY INDEX ROWID ROLE 1 25 1 (0)
* 27 INDEX UNIQUE SCAN ROLE_PK 1 1 (0)
* 28 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
* 29 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 30 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("USR"."USR_NAME"='SNEWTON')
12 - access("CSP_RPT_DEFN"."RPT_DEFN_ID"=5)
13 - access("CSP_RPT_DEFN"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
14 - filter("US"."USR_ID"="USR"."USR_ID")
16 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
17 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
19 - access("USR_NAME"='SNEWTON')
20 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
21 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
23 - access("USR_NAME"='SNEWTON')
25 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
27 - access("UR"."ROLE_ID"="R"."ROLE_ID")
28 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
29 - filter("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
30 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
681 consistent gets
0 physical reads
0 redo size
883 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Original Query – 2
=============================
SQL> SELECT distinct CSP_RPT_DEFN.RPT_ARGMNT_CMPNT_NAME,
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
4 CSP_RPT_DEFN.DBMS_TXT,
5 CSP_RPT_DEFN.CACHE_TXT,
6 CSP_RPT_DEFN.RPT_CRTR_TXT,
7 CSP_RPT_DEFN.CTGRY_ID,
8 CSP_RPT_DEFN.RPT_CMPNT_NAME,
9 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_WT_NUM
12 FROM qri.CSP_RPT_CTGRY,
13 qri.CSP_RPT_DEFN,
14 qri.CSP_RPT_GRP_CTGRY,
15 qri.CSP_RPT_GRP,
16 qri.csp_roles
17 WHERE ( CSP_RPT_DEFN.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
18 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
19 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
20 ((CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) AND
21 (csp_roles.user_id = 'SNEWTON' ) AND
22 ( CSP_RPT_DEFN.RPT_DEFN_ID = 5 ) ) ;
RPT_ARGMNT_CMPNT_NAME RPT_NAME
---------------------------------------- ----------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME DBMS_TXT CACHE_TXT
---------------------------------------- -------------------- --------------------
RPT_CRTR_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
CTGRY_ID RPT_CMPNT_NAME RPT_FNCTN_CALL_CD
---------- ---------------------------------------------------------------------------------------------------- -----------------
RPT_PARM_TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
RPT_WT_NUM
----------
QIOHospsAuthToUploadDataWid Hospitals Authorizing Vendor to Upload Data
n_ds_cart_sub_authorizing_vendor O84 QIONATIONAL
3 pb/PBReportService 1
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
1
1 row selected.
Elapsed: 00:00:04.65
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU)
---------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 12809 (1)
1 HASH UNIQUE 1 337 12809 (1)
2 NESTED LOOPS 1 337 12808 (1)
* 3 HASH JOIN 8 2624 12802 (1)
* 4 HASH JOIN 1 302 8 (0)
5 NESTED LOOPS 1 277 6 (0)
6 NESTED LOOPS 1 251 5 (0)
7 NESTED LOOPS 1 245 4 (0)
8 NESTED LOOPS 1 242 3 (0)
9 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 10 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
11 TABLE ACCESS BY INDEX ROWID CSP_RPT_DEFN 1 228 1 (0)
* 12 INDEX UNIQUE SCAN PK_CSP_RPT_DEFN 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 29 87 1 (0)
* 14 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
15 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 16 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
17 TABLE ACCESS FULL ROLE 217 5425 2 (0)
18 VIEW USR_SUMMARY_VW 367K 9343K 12791 (1)
19 SORT UNIQUE 367K 45M 96M 12791 (99)
20 UNION-ALL
* 21 HASH JOIN 8611 546K 186 (3)
* 22 TABLE ACCESS FULL USR_GRP 30014 527K 82 (3)
23 TABLE ACCESS FULL GRP 30702 1409K 103 (1)
* 24 HASH JOIN 359K 45M 1986 (3)
25 TABLE ACCESS FULL USR 41073 1163K 390 (1)
* 26 HASH JOIN 359K 35M 1592 (3)
27 TABLE ACCESS FULL SYSTEMS 11 143 2 (0)
* 28 HASH JOIN 359K 30M 1587 (3)
29 TABLE ACCESS FULL APP 47 799 2 (0)
* 30 HASH JOIN 359K 25M 1582 (3)
31 TABLE ACCESS FULL ROLE 217 6076 2 (0)
* 32 HASH JOIN RIGHT OUTER 359K 15M 1577 (2)
33 TABLE ACCESS FULL DETAIL_ITEM 31 496 2 (0)
* 34 HASH JOIN RIGHT OUTER 359K 9M 2312K 1572 (2)
35 INDEX FAST FULL SCAN USR_ROLE_DETAIL_PK 98547 1154K 86 (2)
* 36 TABLE ACCESS FULL USR_ROLE 359K 5966K 873 (3)
* 37 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 38 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("US"."USR_ID"="USR"."USR_ID")
4 - access("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
10 - access("USR"."USR_NAME"='SNEWTON')
12 - access("CSP_RPT_DEFN"."RPT_DEFN_ID"=5)
13 - access("CSP_RPT_DEFN"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
14 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"="CSP_RPT_CTGRY"."CTGRY_ID")
16 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
21 - access("GRP"."GRP_ID"="UG"."GRP_ID")
22 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
24 - access("U"."USR_ID"="UG"."USR_ID")
26 - access("SYSTEMS"."SYSTEM_ID"="APP"."SYSTEM_ID")
28 - access("ROLE"."APP_ID"="APP"."APP_ID")
30 - access("ROLE"."ROLE_ID"="UG"."ROLE_ID")
32 - access("D"."DETAIL_ITEM_ID"="DI"."DETAIL_ITEM_ID"(+))
34 - access("UG"."REQUEST_ID"="D"."REQUEST_ID"(+))
36 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
37 - filter("UR"."ROLE_ID"="R"."ROLE_ID")
38 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
7326 consistent gets
2 physical reads
0 redo size
883 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Modified Query - 3 (Vw definations are same as in query 1)
============================================================
SQL> SELECT DISTINCT
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_DEFN_ID,
4 CSP_RPT_DEFN.RPT_DESC,
5 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
6 CSP_RPT_DEFN.CTGRY_ID,
7 CSP_RPT_DEFN.DBMS_TXT,
8 CSP_RPT_DEFN.CACHE_TXT,
9 CSP_RPT_DEFN.RPT_CRTR_TXT,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_CMPNT_NAME,
12 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
13 CSP_RPT_TYPE_CD.ICON_NAME,
14 CSP_RPT_TYPE_CD.RPT_TYPE_CD_DESC
15 FROM qri.CSP_RPT_DEFN,
16 qri.CSP_RPT_CTGRY,
17 qri.CSP_RPT_GRP_CTGRY,
18 qri.CSP_RPT_GRP,
19 qri.CSP_RPT_TYPE_CD,
20 (select usr.usr_name user_id,
21 r.role_name role_name
22 from
23 user_admin.usr_role ur,
24 (
25 select ug.usr_id,ug.request_id from user_admin.usr_grp ug
26 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
27 union all
28 select ug.usr_id,ug.request_id from user_admin.usr_role ug
29 where ug.status_cd not in (4,7) and ug.usr_id = (select usr_id from user_admin.usr where usr_name='SNEWTON')
30 ) us,
31 user_admin.usr,
32 user_admin.role r
33 where ur.request_id = us.request_id
34 and us.usr_id = usr.usr_id
35 and ur.role_id = r.role_id
36 and usr.usr_name='SNEWTON') csp_roles
37 WHERE ( CSP_RPT_CTGRY.CTGRY_ID = CSP_RPT_DEFN.CTGRY_ID ) and
38 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
39 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
40 ( CSP_RPT_DEFN.CTGRY_ID = 3) AND
41 ( CSP_RPT_DEFN.RPT_TYPE_CD = CSP_RPT_TYPE_CD.RPT_TYPE_CD ) and
42 ( CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) and
43 ( csp_roles.user_id = 'SNEWTON')
44 ORDER BY 1 ASC;
RPT_NAME RPT_DEFN_ID
---------------------------------------------------------------------------------------------------- -----------
RPT_DESC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME CTGRY_ID DBMS_TXT CACHE_TXT
---------------------------------------- ---------- -------------------- --------------------
RPT_CRTR_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_PARM_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_CMPNT_NAME RPT_FNCTN_CALL_CD ICON_NAME RPT_TYPE_CD_DESC
---------------------------------------------------------------------------------------------------- ----------------- ------------------------- -------------------------
Hospitals Authorizing Vendor to Upload Data 5
Hospitals Authorizing Vendor to Upload Data.
n_ds_cart_sub_authorizing_vendor 3 O84 QIONATIONAL
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse ICD Population Submission Report 4015
The report provides information regarding the submission of ICD population and sampling data. The report displays whether the data was added or deleted, successfully accepted and
any errors related to rejection.
n_ds_icd_population_submission 3 O84 STAGING
BatchIDBatchIdListHspProvIDHspProviderIdWDatesqlQuerycartids1001
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Error Code 6
Displays detailed file information of selected uploaded data grouped by error code.
n_ds_cart_sub_detail_by_errorcode 3 O84 STAGING
link./dynamicReport.do?id=28RepFormatyeserror_selcartDBsort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids28actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Provider 8
Displays detail file information of selected uploaded data grouped by provider.
n_ds_cart_sub_detail_by_provider 3 O84 STAGING
link./dynamicReport.do?id=30RepFormatyesmsg_typeqiosort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids30actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Submission Summary 7
Displays summary information of selected uploaded data (provider, topic, status, and number of records).
n_ds_cart_sub_summary 3 O84 STAGING
link./dynamicReport.do?id=29doc_frmt_cd2sort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids29sqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
5 rows selected.
Elapsed: 00:00:00.48
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 464 26 (12)
1 SORT ORDER BY 1 464 26 (12)
2 HASH UNIQUE 1 464 25 (8)
3 NESTED LOOPS 1 464 24 (5)
4 NESTED LOOPS 1 439 23 (5)
5 NESTED LOOPS 1 430 22 (5)
6 NESTED LOOPS 1 404 21 (5)
* 7 HASH JOIN 1 377 20 (5)
* 8 HASH JOIN 5 1755 8 (0)
9 NESTED LOOPS 1 23 4 (0)
10 NESTED LOOPS 1 17 3 (0)
11 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 12 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 1 3 1 (0)
* 14 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
* 15 TABLE ACCESS FULL CSP_RPT_DEFN 5 1640 4 (0)
16 VIEW 10 260 11 (0)
17 UNION-ALL
* 18 TABLE ACCESS BY INDEX ROWID USR_GRP 1 13 2 (0)
* 19 INDEX RANGE SCAN USR_GRP_UK1 1 2 (0)
20 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 21 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 22 TABLE ACCESS BY INDEX ROWID USR_ROLE 9 117 6 (0)
* 23 INDEX RANGE SCAN USR_ROLE_UK1 14 2 (0)
24 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 25 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
26 TABLE ACCESS BY INDEX ROWID CSP_RPT_TYPE_CD 1 27 1 (0)
* 27 INDEX UNIQUE SCAN PK_CSP_RPT_TYPE_CD 1 1 (0)
28 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 29 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
30 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 31 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
* 32 TABLE ACCESS BY INDEX ROWID ROLE 1 25 1 (0)
* 33 INDEX UNIQUE SCAN ROLE_PK 1 1 (0)
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("US"."USR_ID"="USR"."USR_ID")
8 - access("CSP_RPT_CTGRY"."CTGRY_ID"="CSP_RPT_DEFN"."CTGRY_ID")
12 - access("USR"."USR_NAME"='SNEWTON')
13 - access("CSP_RPT_CTGRY"."CTGRY_ID"=3)
14 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
15 - filter("CSP_RPT_DEFN"."CTGRY_ID"=3)
18 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
19 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
21 - access("USR_NAME"='SNEWTON')
22 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
23 - access("UG"."USR_ID"= (SELECT "USR_ID" FROM "USER_ADMIN"."USR" "USR" WHERE
"USR_NAME"='SNEWTON'))
25 - access("USR_NAME"='SNEWTON')
27 - access("CSP_RPT_DEFN"."RPT_TYPE_CD"="CSP_RPT_TYPE_CD"."RPT_TYPE_CD")
29 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
31 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
32 - filter("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
33 - access("UR"."ROLE_ID"="R"."ROLE_ID")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3624 consistent gets
0 physical reads
0 redo size
2357 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
Original Query – 3
==================
SQL>
SQL> SELECT DISTINCT
2 CSP_RPT_DEFN.RPT_NAME,
3 CSP_RPT_DEFN.RPT_DEFN_ID,
4 CSP_RPT_DEFN.RPT_DESC,
5 CSP_RPT_DEFN.RPT_RTRV_CMPNT_NAME,
6 CSP_RPT_DEFN.CTGRY_ID,
7 CSP_RPT_DEFN.DBMS_TXT,
8 CSP_RPT_DEFN.CACHE_TXT,
9 CSP_RPT_DEFN.RPT_CRTR_TXT,
10 CSP_RPT_DEFN.RPT_PARM_TXT,
11 CSP_RPT_DEFN.RPT_CMPNT_NAME,
12 CSP_RPT_DEFN.RPT_FNCTN_CALL_CD,
13 CSP_RPT_TYPE_CD.ICON_NAME,
14 CSP_RPT_TYPE_CD.RPT_TYPE_CD_DESC
15 FROM qri.CSP_RPT_DEFN,
16 qri.CSP_RPT_CTGRY,
17 qri.CSP_RPT_GRP_CTGRY,
18 qri.CSP_RPT_GRP,
19 qri.CSP_RPT_TYPE_CD,
20 qri.csp_roles
21 WHERE ( CSP_RPT_CTGRY.CTGRY_ID = CSP_RPT_DEFN.CTGRY_ID ) and
22 ( CSP_RPT_GRP_CTGRY.CTGRY_ID = CSP_RPT_CTGRY.CTGRY_ID ) and
23 ( CSP_RPT_GRP_CTGRY.RPT_GRP_ID = CSP_RPT_GRP.RPT_GRP_ID ) and
24 ( CSP_RPT_DEFN.CTGRY_ID = 3 ) AND
25 ( CSP_RPT_DEFN.RPT_TYPE_CD = CSP_RPT_TYPE_CD.RPT_TYPE_CD ) and
26 ( CSP_RPT_GRP.RPT_GRP_NAME = csp_roles.role_name) and
27 ( csp_roles.user_id = 'SNEWTON')
28 ORDER BY 1 ASC;
RPT_NAME RPT_DEFN_ID
---------------------------------------------------------------------------------------------------- -----------
RPT_DESC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_RTRV_CMPNT_NAME CTGRY_ID DBMS_TXT CACHE_TXT
---------------------------------------- ---------- -------------------- --------------------
RPT_CRTR_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_PARM_TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RPT_CMPNT_NAME RPT_FNCTN_CALL_CD ICON_NAME RPT_TYPE_CD_DESC
---------------------------------------------------------------------------------------------------- ----------------- ------------------------- -------------------------
Hospitals Authorizing Vendor to Upload Data 5
Hospitals Authorizing Vendor to Upload Data.
n_ds_cart_sub_authorizing_vendor 3 O84 QIONATIONAL
link./dynamicReport.do?id=17provIdyesHspProvIDHspProviderIdListRepFormatyessort_byyesid17ids17sortNameSort BysqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse ICD Population Submission Report 4015
The report provides information regarding the submission of ICD population and sampling data. The report displays whether the data was added or deleted, successfully accepted and
any errors related to rejection.
n_ds_icd_population_submission 3 O84 STAGING
BatchIDBatchIdListHspProvIDHspProviderIdWDatesqlQuerycartids1001
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Error Code 6
Displays detailed file information of selected uploaded data grouped by error code.
n_ds_cart_sub_detail_by_errorcode 3 O84 STAGING
link./dynamicReport.do?id=28RepFormatyeserror_selcartDBsort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids28actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Import Detail by Provider 8
Displays detail file information of selected uploaded data grouped by provider.
n_ds_cart_sub_detail_by_provider 3 O84 STAGING
link./dynamicReport.do?id=30RepFormatyesmsg_typeqiosort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids30actionFieldyessqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
QIO Clinical Warehouse Submission Summary 7
Displays summary information of selected uploaded data (provider, topic, status, and number of records).
n_ds_cart_sub_summary 3 O84 STAGING
link./dynamicReport.do?id=29doc_frmt_cd2sort_byyesBatchIDBatchIdListHspProvIDHspProviderIdWDateids29sqlQuerycart
pb/PBReportService 1 dw8.gif Powerbuilder Report
5 rows selected.
Elapsed: 00:00:04.76
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU)
-----------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 464 12815 (1)
1 SORT ORDER BY 1 464 12815 (1)
2 HASH UNIQUE 1 464 12814 (1)
3 NESTED LOOPS 1 464 12813 (1)
* 4 HASH JOIN 1 437 12812 (1)
5 NESTED LOOPS 1 109 12808 (1)
* 6 HASH JOIN 9 900 12801 (1)
* 7 HASH JOIN 1 74 7 (0)
8 NESTED LOOPS 1 49 5 (0)
9 NESTED LOOPS 1 23 4 (0)
10 NESTED LOOPS 1 17 3 (0)
11 TABLE ACCESS BY INDEX ROWID USR 1 14 2 (0)
* 12 INDEX UNIQUE SCAN USR_UK1 1 1 (0)
* 13 INDEX UNIQUE SCAN PK_CSP_RPT_CTGRY 1 3 1 (0)
* 14 INDEX FULL SCAN PK_CSP_RPT_GRP_CTGRY 1 6 1 (0)
15 TABLE ACCESS BY INDEX ROWID CSP_RPT_GRP 1 26 1 (0)
* 16 INDEX UNIQUE SCAN PK_CSP_RPT_GRP 1 1 (0)
17 TABLE ACCESS FULL ROLE 217 5425 2 (0)
18 VIEW USR_SUMMARY_VW 367K 9343K 12791 (1)
19 SORT UNIQUE 367K 45M 96M 12791 (99)
20 UNION-ALL
* 21 HASH JOIN 8611 546K 186 (3)
* 22 TABLE ACCESS FULL USR_GRP 30014 527K 82 (3)
23 TABLE ACCESS FULL GRP 30702 1409K 103 (1)
* 24 HASH JOIN 359K 45M 1986 (3)
25 TABLE ACCESS FULL USR 41073 1163K 390 (1)
* 26 HASH JOIN 359K 35M 1592 (3)
27 TABLE ACCESS FULL SYSTEMS 11 143 2 (0)
* 28 HASH JOIN 359K 30M 1587 (3)
29 TABLE ACCESS FULL APP 47 799 2 (0)
* 30 HASH JOIN 359K 25M 1582 (3)
31 TABLE ACCESS FULL ROLE 217 6076 2 (0)
* 32 HASH JOIN RIGHT OUTER 359K 15M 1577 (2)
33 TABLE ACCESS FULL DETAIL_ITEM 31 496 2 (0)
* 34 HASH JOIN RIGHT OUTER 359K 9M 2312K 1572 (2)
35 INDEX FAST FULL SCAN USR_ROLE_DETAIL_PK 98547 1154K 86 (2)
* 36 TABLE ACCESS FULL USR_ROLE 359K 5966K 873 (3)
* 37 TABLE ACCESS BY INDEX ROWID USR_ROLE 1 9 1 (0)
* 38 INDEX UNIQUE SCAN USR_ROLE_PK 1 1 (0)
* 39 TABLE ACCESS FULL CSP_RPT_DEFN 5 1640 4 (0)
40 TABLE ACCESS BY INDEX ROWID CSP_RPT_TYPE_CD 1 27 1 (0)
* 41 INDEX UNIQUE SCAN PK_CSP_RPT_TYPE_CD 1 1 (0)
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CSP_RPT_CTGRY"."CTGRY_ID"="CSP_RPT_DEFN"."CTGRY_ID")
6 - access("US"."USR_ID"="USR"."USR_ID")
7 - access("CSP_RPT_GRP"."RPT_GRP_NAME"="R"."ROLE_NAME")
12 - access("USR"."USR_NAME"='SNEWTON')
13 - access("CSP_RPT_CTGRY"."CTGRY_ID"=3)
14 - access("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
filter("CSP_RPT_GRP_CTGRY"."CTGRY_ID"=3)
16 - access("CSP_RPT_GRP_CTGRY"."RPT_GRP_ID"="CSP_RPT_GRP"."RPT_GRP_ID")
21 - access("GRP"."GRP_ID"="UG"."GRP_ID")
22 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
24 - access("U"."USR_ID"="UG"."USR_ID")
26 - access("SYSTEMS"."SYSTEM_ID"="APP"."SYSTEM_ID")
28 - access("ROLE"."APP_ID"="APP"."APP_ID")
30 - access("ROLE"."ROLE_ID"="UG"."ROLE_ID")
32 - access("D"."DETAIL_ITEM_ID"="DI"."DETAIL_ITEM_ID"(+))
34 - access("UG"."REQUEST_ID"="D"."REQUEST_ID"(+))
36 - filter("UG"."STATUS_CD"<>4 AND "UG"."STATUS_CD"<>7)
37 - filter("UR"."ROLE_ID"="R"."ROLE_ID")
38 - access("UR"."REQUEST_ID"="US"."REQUEST_ID")
39 - filter("CSP_RPT_DEFN"."CTGRY_ID"=3)
41 - access("CSP_RPT_DEFN"."RPT_TYPE_CD"="CSP_RPT_TYPE_CD"."RPT_TYPE_CD")
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7342 consistent gets
0 physical reads
0 redo size
2357 bytes sent via SQL*Net to client
242 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
