pga_aggregate_target, sort_area_size and optimal memory usage

First of all thanks to Nobert Debes for exploring PGA memory related parameters.

I ran test in my own environment to compare sort_area_size with PGA and how PGA is being used. Results are as follows:

Table Size: 5.125
Index Size (One that I used for testing with DOP 4): 4.3942

Size in MB


PGA memory and Temporary table space usage (Output from v$sql_workarea_active):

Case 1:

SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
81 42:11 2.3 512 1 1050
83 42:11 2.3 512 1 1041
86 42:11 2.3 512 1 1034
92 42:11 3.3 512.1 1 1558

Case 2:

SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
87 05:02 1.1 1024 1 1034
88 05:02 1.1 1024 1 1041
89 05:02 1.1 1024 1 1050
90 05:02 1.1 1024 1 1558

Case 3:

SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
86 17:47 1103.8 1103.8 0 0
87 17:47 1086.5 1086.5 0 0
88 17:47 1092.8 1092.8 0 0
89 17:47 1.1 1536 1 1558

Case 4 (sort_area_size) :

SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
82 39:10 0 .1 4099 1219
88 39:10 0 .1 4102 1795
89 39:10 0 .1 3710 1248
90 39:10 0 .1 3947 1258



Case 1: This is default setting. MAX_MEM_USED used for each parallel process was 512 MB ie _smm_px_max_size/4.

Case 2: I increased all (_) parameter to a higher value. There was some gain in performance but not much as it still has to spill sorts to temporary tablespace. See MAX_MEM_USED used for each parallel process was 1024 MB ie _smm_px_max_size/4

Case 3: As per Case 1 and Case 2 max it has to spill to temporary tablespace was 1558 MB. So I increased PGA to 6 GB _smm_px_max_size to 6GB so that I can accomodate most of the sorting in memory. I could manage to do it with 3 slaves (see PASSES = 0 for 3 slaves) where as fourth slave needed 1558 MB but Oracle will allocate least of _smm_max_size= 1536 MB and _smm_px_max_size/4 = 1536 MB. Response time improved from 8 mins 27 seconds to 6 mins 57 seconds.

Case 4: I used old way of sorting with sort_area_size but it took lot of time. Also I observed SORT/MERGE after SORT operation which took most of the time.

SID % Complete Time Now ELAPSED_SECONDS MESSAGE
--------------- ---------- --------------- --------------- ----------------------------------------------
90.22 .51 090506 12:36:38 111 Sort/Merge: : 5039 out of 995618 Blocks done
82.9 .49 090506 12:36:38 111 Sort/Merge: : 5025 out of 1027136 Blocks done
89.164 .54 090506 12:36:38 111 Sort/Merge: : 5038 out of 934140 Blocks done
88.33 .32 090506 12:36:38 111 Sort/Merge: : 5015 out of 1581604 Blocks done

Conclude: On large Warehouse systems where PGA is set in range of 10G-32G, changing PGA (_) parameters for load and batch processes for sorting, PL/SQL collections will help speed up operations that use PGA memory. Also it can help in multiple node RAC environments where one node is dedicated for Warehouse type of operation.

Estimated Interconnect traffic (KB) - Oracle RAC

In 10g AWR package dbms_workload_repository is wrapped so if you want to find formulae for any statistics its difficult. If you want to plot interconnect traffic Vs Time in Oracle RAC environment you can use following script and create chart using excel:

Note: It will run full AWR report for range of snap id's so do not run during peak hours.

-----------------------------------------------------------------------------------------------
set echo off
set feed off
ACCEPT startt PROMPT'Please enter start time (MM/DD/YYYY):'
ACCEPT endt PROMPT'Please enter end time (MM/DD/YYYY):'
ACCEPT instid PROMPT'Please enter instance number:'
drop table interconnect_traffic;
create table interconnect_traffic (stime varchar2(100), svalue varchar2(100)) tablespace users;
create or replace procedure interconnect_traffic_pr1 (stime date,etime date,instid number)is
intraffic varchar2(100);
databaseid number;
ssnapid number;
esnapid number;
display_date varchar2(100);
i number;
begin
select min(snap_id) into ssnapid from dba_hist_snapshot where (to_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = stime and instance_number = instid;

select max(snap_id)-1 into esnapid from dba_hist_snapshot where (to_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = etime and instance_number = instid;

select dbid into databaseid from v$database ;

for i in ssnapid..esnapid loop

select replace(output,'Estd Interconnect traffic (KB)','') into intraffic from table(dbms_workload_repository.awr_report_text( databaseid,instid,i, i+1,0 )) where output like '%Interconnect%';

select to_char(begin_interval_time,'MM/DD/YYYY HH24:MI') into display_date from dba_hist_snapshot where snap_id = i+1 and instance_number = instid;

insert into interconnect_traffic values (display_date,intraffic);

commit;
end loop;
exception
when others then
dbms_output.put_line(i);
end;
/

execute interconnect_traffic_pr1('&startt','&endt','&instid');

drop procedure interconnect_traffic_pr1;

set echo on
set feed on
column stime format a30
column svalue format a30
select stime,svalue from interconnect_traffic;

---------------------------------------------------------------------------------------------------------------------------------------


spreport.sql shows forumlae of Interconnect traffic as follows:

Estd Interconnect traffic (KB):' hd2, round( ( ((:gccrrv+:gccurv +:gccrsv+:gccusv) :bs) + ((:dpms+:dnpms+:pmrv+:npmrv) 200) * )/&&btokb/:ela,2)

where
gccrrv = gc cr blocks received
gccurv = gc current blocks received
gccrsv = gc cr blocks served
gccusv = gc current blocks served
bs = block_size
dpms = gcs messages sent
dnpms = ges messages sent
pmrv = gcs msgs received
npmrv = ges msgs received
btokb = 1024
ela = elapsed time between two snapshots in seconds.

According to above formulae, script to calculate interconnect traffic is as shown below. Interconnect traffic caculated by below script is close to one calcuated by AWR but not same.

------------------------------------------------------------------------------------------------

set echo off
set feed off
ACCEPT startt PROMPT'Please enter start time (MM/DD/YYYY):'
ACCEPT endt PROMPT'Please enter end time (MM/DD/YYYY):'
ACCEPT instid PROMPT'Please enter instance number:'
drop table interconnect_traffic;
create table interconnect_traffic (stime varchar2(100), svalue varchar2(100)) tablespace users;
create or replace procedure interconnect_traffic_pr1 (stime date,etime date,instid number)isssnapid number;
esnapid number;
ogcstats number;
ngcstats number;
ogmstats number;
ngmstats number;
blksize number;
start_time date;
end_time date;
intraffic number;
cnt number :=2;
display_date varchar2(100);
begin
select min(snap_id) into ssnapid from dba_hist_snapshot where (to_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = stime and instance_number = instid;

select max(snap_id) into esnapid from dba_hist_snapshot where (to_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = etime and instance_number = instid;

select value into blksize from v$parameter where name ='db_block_size';
for i in ssnapid..esnapid loop
select sum(value) into ogcstats from dba_hist_sysstat where stat_name in ('gc cr blocks received','gc current blocks received','gc cr blocks served','gc current blocks served')and snap_id = i and instance_number = instid;
select sum(value) into ngcstats from dba_hist_sysstat where stat_name in ('gc cr blocks received','gc current blocks received','gc cr blocks served','gc current blocks served')and snap_id = i+1 and instance_number = instid;
select to_char(begin_interval_time,'MM/DD/YYYY HH24:MI') into display_date from dba_hist_snapshot where snap_id = i and instance_number = instid;
select sum(value) into ogmstats from dba_hist_sysstat where stat_name in ('gcs messages sent','ges messages sent','gcs msgs received','ges msgs received')and snap_id = i and instance_number = instid;
select sum(value) into ngmstats from dba_hist_sysstat where stat_name in ('gcs messages sent','ges messages sent','gcs msgs received','ges msgs received')and snap_id = i+1 and instance_number = instid;
select (to_date(to_char(begin_interval_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')) into start_time from dba_hist_snapshot where snap_id = i and instance_number = instid;
select (to_date(to_char(begin_interval_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')) into end_time from dba_hist_snapshot wheresnap_id = i + 1 and instance_number = instid;
intraffic := (((ngcstats - ogcstats)*blksize ) + ((ngmstats - ogmstats)*200))/1024/((end_time-start_time)*1440*60);
insert into interconnect_traffic values (display_date,intraffic);
commit;
end loop;
end;
/

execute interconnect_traffic_pr1('&startt','&endt','&instid');

drop procedure interconnect_traffic_pr1;
set echo on
set feed onc
olumn stime format a30
column svalue format a30
select stime,svalue from interconnect_traffic;
-------------------------------------------------------------------------------------------------

IO stress test using Orion

I ran into some long running deployments on Production database affecting end users. Same deployment on QA with production data ran in 12 hours but on production it ran for 4 days but didn't finished.

Production database was sitting on local disks where as QA was sitting on SAN. I ran some IO test using Oracle Orion and plotted them on graph.

I found tool really useful for DBA's and same can be used for comparing different storage options like ASM Vs Veritas FS etc.

- At load level of 2 outstanding large reads and 4 small reads we get a throughput of 185.28 MBPS on QA and 5.68 MBPS on PROD.

- At load level of 2 outstanding large reads and 4 small reads we get a throughput of 72993 IOPS on QA and 336 IOPS on PROD.

- At load level of 2 outstanding large reads and 4 small reads we get a throughput of 0.05 ms on QA and 11.9 ms on PROD.

Oracle RAC - 3 node for HA

Think beyond adding indexes to tune SQL queries

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;

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)

Oracle 11g Advanced Compression – Is it Really Advanced for $10,000 per processor?

Note: This test was performed on a single CPU with 4 concurrent session. The same should be run on production with application
specific test case.

Please click on image below if the image is blur. Results and Analysis are based on following:

Only Tables were compressed in test case using 11g compression feature (compress for all operations)

Transactions Per Second Vs CPU Graph



Results:

1. Comparing Number of Rows with Size of Table before and after compression

a. CUSTOMERS : Inserted 381 records less after compression and table size went
up by 64 bytes

b. LOGON : Without compression 2167 more customers were able to login which
tells compression effects the scalability of system.

c. ORDERS & ORDER_ITEMS : Saved 1984 + 2112 = 4096 KB after compression but
at the same time it processed 1788 orders (6233 items ) less compared to that
of before compression. Overall savings looks good but in my test case avg row
length of orders table was 53 bytes and that of order_items was 30 bytes.
Why it may now work in production? Oracle 11g Advanced compression algorithm:
Oracle’s compression algorithm is based upon eliminating duplicate values in
each block. Avg Row length in production environment will be much higher compared to one in my test case.

Compression feature works best if row length in production is low enough and
repetitive data is loaded sequentially which is still possible in DSS but not OLTP. Unique inline LOB's (avg row length 3-4k) will hardly compress anything.


2. Tablespace Free Space

Overall space utilization went up with compression by 768 KB.

3. Performance

-With compression Total Transaction went down by 85945 - 83625 = 2320 in 45 mins.
-With compression number of logons went down by 2167. For companies counting revenues by transaction/hr this could mean loss of xxxx dollars in N hours.
-With compression Avg T/S went down.
-With compression Avg Wait/IO CPU went up.
-With compression Avg Response time (timings in milliseconds) went up.
-With compression Oracle uncompresses data in RAM which is very fast. This will
require additional RAM on servers where memory foot print shows uptrend.

Synopsis:

Oracle 11g compression feature is licensed separately at $10,000 per processor. Before recommending this feature to your client/company make sure you know your databases very well and how much % of compression you will gain after paying
$10,000 per processor.

"Oracle’s compression algorithm is based upon eliminating duplicate values in
EACH BLOCK." - $10,000 per processor?

In OLTP environment DBA's don't have control on how data will be inserted. In OLTP block size will be relatively small compared to that of DSS. So it will be difficult to guesstimate % of compression achived and $$ saved with overhead of performance.

If you are using RAM SAN (SSD) it might be worth using 11g compression feature. It will also be useful in a warehouse environment where data is loaded sorted by key columns to have more and more repetitive data in same block to achieve maximum compression.

Advanced compression can also be used to compress datapump dump files, compress archives for physical standby database, avoid duplication of LOB's etc.

Last but not least consider cost of SAN Vs 11g compression.

Internode/Intranode Parallelism in RAC

Case 1: Running Query in Serial Mode from Instance 1 - Unbounded

Parallel_max_servers = 5
Parallel_min_servers = 0

select sum(bytes),sum(blocks),sum(blocks/1024), sum(block_id/12) from xyz;

SUM(BYTES) SUM(BLOCKS) SUM(BLOCKS/1024) SUM(BLOCK_ID/12)
---------- ----------- ---------------- ----------------
1.4239E+12 173813760 169740 1.3930E+10

Elapsed: 00:01:00.86

Instance 1



Instance 2



Case 2: Running Query in Parallel mode (Degree 6) from Instance 1 - unbounded

Parallel_max_servers = 5
Parallel_min_servers = 0

select /*+ full(xyz) parallel(xyz,6) */ sum(bytes),sum(blocks),
sum(blocks/1024), sum(block_id/12) from xyz;

SUM(BYTES) SUM(BLOCKS) SUM(BLOCKS/1024) SUM(BLOCK_ID/12)
---------- ----------- ---------------- ----------------
1.4239E+12 173813760 169740 1.3930E+10

Elapsed: 00:01:02.82

oracle 20394 1 4 16:23 ? 00:00:02 ora_p000_sweety1
oracle 20400 1 4 16:23 ? 00:00:03 ora_p001_sweety1
oracle 20402 1 4 16:23 ? 00:00:02 ora_p002_sweety1

oracle 17248 1 3 16:23 ? 00:00:02 ora_p000_sweety2
oracle 17252 1 2 16:23 ? 00:00:02 ora_p001_sweety2
oracle 17259 1 3 16:23 ? 00:00:02 ora_p002_sweety2

Instance 1



Instance 2



Case 3: Running Query in Parallel (Degree 6) mode from Instance 1 - bounded

To restrict parallel processing to specific node, instance groups can be used.

SQL> select inst_id,name,value from gv$parameter where
name like '%instance%grou%';

INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
1 instance_groups s1
1 parallel_instance_group
2 instance_groups s2
2 parallel_instance_group


SQL> select inst_id,name,value from gv$parameter where name
like '%instance%grou%';

INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
1 instance_groups s1
1 parallel_instance_group
2 instance_groups s2
2 parallel_instance_group

SQL> alter session set parallel_instance_group='s1';

Session altered.

SQL> select /*+ full(xyz) parallel(xyz,8) */ sum(bytes),sum(blocks),
sum(blocks/1024), sum(block_id/12) from xyz;

SUM(BYTES) SUM(BLOCKS) SUM(BLOCKS/1024) SUM(BLOCK_ID/12)
---------- ----------- ---------------- ----------------
1.4239E+12 173813760 169740 1.3930E+10


oracle 32285 1 6 17:36 ? 00:00:02 ora_p000_sweety1
oracle 32293 1 6 17:36 ? 00:00:02 ora_p001_sweety1
oracle 32301 1 6 17:36 ? 00:00:02 ora_p002_sweety1
oracle 32304 1 6 17:36 ? 00:00:02 ora_p003_sweety1
oracle 32340 1 6 17:36 ? 00:00:02 ora_p004_sweety1

No parallel processes spawned on instance 2.

All the parallel workers run only on instance1 node.
Why only 5 servers and not 6? Remember parallel_max_servers
is set to 5.

Observation:

Running couple of sessions in parallel unbounded mode can slow down interconnect.
Similar test should be performed on production system before going for cross instance parallelism.

Test was performed using single session on:
2 Node Oracle 11g RAC
Enterprise Linux 5
NFS