All,
We are using a flattened table for the Reports. This table is called
as JP_HISTORY_TBL. This table is like a Data Warehouse having all the
required fields for the reports in a flattened structure. So it has
the codes as well as the description. For Example it has the Member ID
as well as the member name.
Prior to this development it had only the Member ID and we used to
fetch the Member ID from its corresponding SET UP table(PERSON
Table).
We thought of reducing the Joins and got the Member name also into the
JP_HISTORY_TBL...
Similarly we have got the other DESCRIPTION data as well into the
JP_HISTORY_TBL.
We expected this to reduce the query cost but oppposed to this the
query cost is getting increased and it is taking a lot of time to get
executed.
We are not able to figure our why this is happening..Is this because
JP_HISTORY_TBL is a very large table containing a lot of TRANSACTIONAL
data ? Then how do they create the datawarehouse with all the
flattened information ?

For example
Select Member_ID,MEMBER_NAME from JP_HISTORY_TBL is costlier than this
select A.MEMBER_ID,B.MEMBER_NAME from JP_HISTORY_TBL A,PS_RD_PERSON B
where a.memberid = b.memberid

Your help in this action will be greatly greatly appreciated..

Thanks
KN Aravindh

On Tue, 07 Aug 2007 04:10:44 -0000, Aravindh <knaravindh81 (AT) gmail (DOT) com>
wrote:


Just a wild guess using my crystal ball.
What is the length of a record in JP_HISTORY_TBL?
Do they still fit in one single database block?
If not, then that is your answer.
You have massive row chaining.
Row chaining results in extra IO, which is not for free.
Row chaining is not taking into account by the optimizer.
You need to, either
a) recreate your database and increase the block size to 16 or 32k
b) deflatten the table.

Other than that, this post belongs in
news:comp.databases.oracle.server

Hth

--
Sybrand Bakker
Senior Oracle DBA

On Aug 7, 10:41 am, sybra... (AT) hccnet (DOT) nl wrote:
HI Sybrand,

Thanks for your quick reply. Can you tell us how to see whether the
JP_HISTORY_TBL is present in a single block or not ? Is there a
command which can be executed in TOAD or SQL PLUS ? If you could tell
us the command we would execute it and find out whether the table is
in a SINGLE Block or not.

Your quick reply will be appreciated. Thanks

Regards
KN Aravindh

On Aug 7, 6:08 pm, Aravindh <knaravind... (AT) gmail (DOT) com> wrote:
Hi ,

As an addition please look into the two sample queries below

SELECT 1 AS "A"
FROM sysadm.jp_history_tbl a,
sysadm.jp_history_tbl z,
sysadm.ps_jp_dm_alt_stg c,
sysadm.psopralias d,
sysadm.ps_jp_cycle_time e,
sysadm.ps_rf_provider_grp f,
sysadm.ps_rd_person_name g,
sysadm.jp_history_tbl x
WHERE x.case_id = a.case_id
AND z.status_new = 'Open - In Progress'
AND f.provider_grp_id = a.assigned_to_pg_new
AND (g.name_type = 'PREFERRED' OR g.name_type IS NULL)
AND (g.primary_ind = 'Y' OR g.primary_ind IS NULL)
AND g.person_id(+) = a.assigned_to_new
AND a.case_id = e.case_id(+)
AND a.assigned_to_pg_new = 'IS00000025'
AND a.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.jp_history_tbl b1
WHERE a.case_id = b1.case_id
AND b1.row_lastmant_dttm < TO_DATE ('21-Jul-2007')
AND b1.assigned_to_pg_flag = 'Y')
AND d.opraliastype = 'PER'
AND z.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.jp_history_tbl b1
WHERE a.case_id = b1.case_id
AND b1.row_lastmant_dttm < TO_DATE ('21-Jul-2007')
AND b1.status_flag = 'Y')
AND (z.status_new NOT IN ('Closed', 'Cancelled', 'Draft'))
AND a.case_id = z.case_id
AND c.case_id = a.case_id
AND d.oprid = c.row_added_oprid
AND (c.jp_orig_prvdr_grp != 'IS00000025')
AND x.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.jp_history_tbl b1
WHERE a.case_id = b1.case_id
AND b1.row_lastmant_dttm <= TO_DATE ('21-Jul-2007')
AND b1.status_flag = 'Y')
AND x.status_new != 'Cancelled'

This has the PSOPRALIAS table (SET UP table joined). When we have this
table the query results come up in less than 285 msec...
When we remove the join of the PSOPRALIAS table (remove the set up
table) the cost of the query goes up drastically and the results come
up in about 50 sec.
Please note that this table does not have any impact on the number of
rows returned.

On Tue, 07 Aug 2007 08:38:06 -0700, Aravindh <knaravindh81 (AT) gmail (DOT) com>
wrote:


The typical way to address this in a forum like this one would be to
post the query in conjunction with EXPLAIN PLAN results.
Right now I can only guess.

2 different variants of a statement, 2 different EXPLAIN PLAN
somehow in the join situation psopralias is the driving table in the
query, and CBO chooses efficient indexes to query the rest of the
disaster.
In the variant without psopralias *different* indexes are being used.
As you don't disclose the EXPLAIN PLAN results, nor the table
definition, who can tell?

This bit strikes me
with inline views, or the WITH statement.

--
Sybrand Bakker
Senior Oracle DBA

On Tue, 07 Aug 2007 06:08:37 -0700, Aravindh <knaravindh81 (AT) gmail (DOT) com>
wrote:

The table will not be in a single block. *records* should be in a
single block.
You would need to use
analyze table .... compute statistics or better
exec
dbms_stats.gather_table_stats(user,'<table_name>', estimate_percent=>NULL)

doing so you will can query
select avg_row_len,chain_cnt, num_rows,
from user_tables where table_name = '<table_name>'

--
Sybrand Bakker
Senior Oracle DBA

sybrandb (AT) hccnet (DOT) nl wrote:





Why? Can't you simply get the db_block_size parameter
SQL> SHOW PARAMETER BLOCK and then check against avg_row_len?


My point here is, why gather the stats (which could be useful
for other reasons) to find out if the record size exceeds the
size of a db block?



Paul...

On Aug 8, 2:20 am, "Paul Linehan" <plinehan__A (AT) T__yahoo__D (DOT) OT__COM>
wrote:
Thanks for your reply.. I am also attaching the results of the user
tables and also attaching the Explain plan statements for the query
with and without the PSOPRALIAS.The problem here as stated earlier is
that the Cose of the query is drastically coming down when we JOIN
with the second PSOPRALIAS TABLE.

SELECT avg_row_len,chain_cnt, num_rows
FROM user_tables WHERE table_name = 'JP_HISTORY_TBL'

query result :-

198 3006586 5797557

Next Execution plan without PSOPRALIAS :- is 13135

Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=CHOOSE 1 13135
FILTER
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 31 4
NESTED LOOPS 1 1 K 13123
NESTED LOOPS 1 1 K 13119
HASH JOIN 1 150 13115
NESTED LOOPS OUTER 1 128 2321
NESTED LOOPS 1 106 2319
INDEX FAST FULL SCAN JP_HISTORY_IDX2 1 24 2315
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 82 4
INDEX RANGE SCAN JP_HISTORY_IDX2 1 2
TABLE ACCESS BY INDEX ROWID PS_JP_CYCLE_TIME 1 22 2
INDEX RANGE SCAN PS_JP_CYCLE_TIME 1 1
VIEW VW_SQ_1 2 M 50 M 10783
SORT GROUP BY 2 M 36 M 10783
INDEX FAST FULL SCAN JP_HISTORY_TBL_TEST 2 M 44 M 1347
TABLE ACCESS BY INDEX ROWID PS_JP_DM_ALT_STG 1 1 K 4
INDEX RANGE SCAN JP_DM_ALT_STG_IDX1 1 2
INDEX RANGE SCAN CID_SNEW 1 2
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 16 6
INDEX RANGE SCAN CID_SNEW 2 3
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 16 6
INDEX RANGE SCAN CID_SNEW 2 3


Execution Plan with PSOPRALIAS is coming down drastically to about 100

SELECT STATEMENT Optimizer Mode=CHOOSE 1 103
FILTER
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 31 4
NESTED LOOPS 1 1 K 97
NESTED LOOPS 1 1 K 93
NESTED LOOPS 1 1 K 93
NESTED LOOPS 1 128 89
NESTED LOOPS OUTER 1 104 87
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 82 85
INDEX RANGE SCAN JP_HISTORY_IDX2 1 83
SORT AGGREGATE 1 16
INDEX RANGE SCAN JP_HISTORY_TBL_TEST 1 16 3
TABLE ACCESS BY INDEX ROWID PS_JP_CYCLE_TIME 1 22 2
INDEX RANGE SCAN PS_JP_CYCLE_TIME 1 1
INDEX RANGE SCAN JP_HISTORY_TBL_I3 1 24 2
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 16 6
INDEX RANGE SCAN CID_SNEW 2 3
TABLE ACCESS BY INDEX ROWID PS_JP_DM_ALT_STG 1 1 K 4
INDEX RANGE SCAN JP_DM_ALT_STG_IDX1 1 2
INDEX UNIQUE SCAN PS_PSOPRALIAS 1 21
INDEX RANGE SCAN CID_SNEW 1 2
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL 1 16 6
INDEX RANGE SCAN CID_SNEW 2 3



Also I have attached both the queries for your reference :- (both of
them are same except that one has the PSOPRALIAS and the other not
having that table)

List of Indexes Used
*********************************

CREATE INDEX JP_HISTORY_TBL_I3 ON JP_HISTORY_TBL
(CASE_ID, ROW_LASTMANT_DTTM, STATUS_NEW)

CREATE INDEX JP_HISTORY_IDX2 ON JP_HISTORY_TBL
(ASSIGNED_TO_PG_NEW, CASE_ID, STATUS_NEW, ROW_LASTMANT_DTTM)

CREATE INDEX CID_SNEW ON JP_HISTORY_TBL
(CASE_ID, STATUS_NEW)

CREATE INDEX JP_HISTORY_TBL_TEST ON JP_HISTORY_TBL
(CASE_ID, ROW_LASTMANT_DTTM, ASSIGNED_TO_PG_FLAG)

CREATE UNIQUE INDEX PS_PSOPRALIAS ON PSOPRALIAS
(OPRID, OPRALIASTYPE)

CREATE UNIQUE INDEX PS_JP_CYCLE_TIME ON PS_JP_CYCLE_TIME
(CASE_ID, BUSINESS_UNIT)

CREATE INDEX JP_DM_ALT_STG_IDX1 ON PS_JP_DM_ALT_STG
(CASE_ID, PROVIDER_GRP_ID, ROW_LASTMANT_DTTM, CLOSED_DTTM,
JP_REOPEN_DATE,
RC_STATUS)



qUERY WITH oPRALIAS TABLE
***************************

SELECT c.business_unit, A.case_id, A.assigned_to_pg_old,
A.assigned_to_pg_new,
--A.ASSIGNED_TO_OLD,A.ASSIGNED_TO_NEW,
Z.status_old, Z.status_new,
Z.status_new "SNEW",
CASE WHEN Z.row_lastmant_dttm >
A.row_lastmant_dttm
THEN z.row_lastmant_dttm
ELSE
A.row_lastmant_dttm
END AS row_lastmant_dttm ,
A.transaction_flag, c.rc_short_descr,
sysadm.Datediff (jp_exp_dttm) AS "DIFF",
c.rc_short_descr1,
rptrules.Jp_Func_Tz_Conv
('EST',
'IST',
DECODE (TRIM (c.jp_draft_to_status),
NULL, c.row_added_dttm,
c.jp_draft_chng_dttm
)
) AS row_added_dttm,
c.productdescr, c.jp_probtype_descr,
c.rc_summary,
DECODE(trim(TO_CHAR(c.jp_rsln_dtls)),NULL,'N/
A',SUBSTR(TRIM(TO_CHAR(c.jp_rsln_dtls)),1,762)) AS jp_rsln_dtls,
DECODE (TRIM (c.jp_error_desc),
NULL, 'N/A',
c.jp_error_desc
) AS jp_error_desc,
c.descr1,
REPLACE (INITCAP (c.name_display), ',', ', ') AS
"NAME",
c.jp_orig_pg_name,
DECODE (TRIM (A.assigned_to_new_name),
NULL, 'Unassigned',
REPLACE(INITCAP (A.assigned_to_new_name),
',', ', ')
) AS "ASS",
A.assigned_to_pg_new_name, c.company_name,
DECODE (TRIM (c.jp_cause_desc),
NULL, 'N/A',
c.jp_cause_desc
) AS jp_cause_desc,
c.jp_factor,
DECODE (TRIM (c.jp_reason_for_use),
NULL, 'N/A',
c.jp_reason_for_use
) AS
jp_reason_for_use,
A.DURATION,
rptrules.Jp_Func_Tz_Conv
('EST',
'IST',
TO_DATE
(CONCAT
(TO_CHAR (c.jp_received_dt, 'dd-Mon-yyyy'),

SUBSTR (TO_CHAR (c.jp_received_time,

'dd-Mon-yyyy hh24:mi:ss'
),

12
)
),
'dd-Mon-
yyyy hh24:mi:ss'
)
) AS "RDATE",
--addition of new owner fields
REPLACE (INITCAP (c.BO_NAME), ',', ', ') AS "BO_NAME",
c.OWNER_PG_ID,
c.NAME1 AS OWNER_PG
-- c.JP_STATUS_CONCAT,
FROM sysadm.JP_HISTORY_TBL A,
sysadm.JP_HISTORY_TBL z,
sysadm.PS_JP_DM_ALT_STG c,
SYSADM.PSOPRALIAS D,
sysadm.PS_JP_CYCLE_TIME e,
sysadm.JP_HISTORY_TBL x
WHERE x.case_id = A.case_id
AND Z.status_new = 'Open - In Progress'
AND A.case_id = e.case_id(+)
AND A.assigned_to_pg_new = 'IS00000025'
AND A.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.JP_HISTORY_TBL b1
WHERE A.case_id = b1.case_id
AND b1.row_lastmant_dttm < '21 Jul 2007'
AND b1.assigned_to_pg_flag = 'Y')
AND d.opraliastype = 'PER'
AND z.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.JP_HISTORY_TBL b1
WHERE z.case_id = b1.case_id
AND b1.row_lastmant_dttm < '21 Jul 2007'
AND b1.status_flag = 'Y')
AND (z.status_new NOT IN ('Closed', 'Cancelled',
'Draft'))
AND A.case_id = z.case_id
AND c.case_id = A.case_id
AND D.OPRID = C.ROW_ADDED_OPRID
AND (c.jp_orig_prvdr_grp != 'IS00000025')
AND x.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.JP_HISTORY_TBL b1
WHERE x.case_id = b1.case_id
AND b1.row_lastmant_dttm <= '22 Jul 2007'
AND b1.status_flag = 'Y')
AND x.status_new != 'Cancelled'




qUERY WITHOUT opralias TABLE
**********************************

SELECT c.business_unit, A.case_id, A.assigned_to_pg_old,
A.assigned_to_pg_new,
--A.ASSIGNED_TO_OLD,A.ASSIGNED_TO_NEW,
Z.status_old, Z.status_new,
Z.status_new "SNEW",
CASE WHEN Z.row_lastmant_dttm >
A.row_lastmant_dttm
THEN z.row_lastmant_dttm
ELSE
A.row_lastmant_dttm
END AS row_lastmant_dttm ,
A.transaction_flag, c.rc_short_descr,
sysadm.Datediff (jp_exp_dttm) AS "DIFF",
c.rc_short_descr1,
rptrules.Jp_Func_Tz_Conv
('EST',
'IST',
DECODE (TRIM (c.jp_draft_to_status),
NULL, c.row_added_dttm,
c.jp_draft_chng_dttm
)
) AS row_added_dttm,
c.productdescr, c.jp_probtype_descr,
c.rc_summary,
DECODE(trim(TO_CHAR(c.jp_rsln_dtls)),NULL,'N/
A',SUBSTR(TRIM(TO_CHAR(c.jp_rsln_dtls)),1,762)) AS jp_rsln_dtls,
DECODE (TRIM (c.jp_error_desc),
NULL, 'N/A',
c.jp_error_desc
) AS jp_error_desc,
c.descr1,
REPLACE (INITCAP (c.name_display), ',', ', ') AS
"NAME",
c.jp_orig_pg_name,
DECODE (TRIM (A.assigned_to_new_name),
NULL, 'Unassigned',
REPLACE(INITCAP (A.assigned_to_new_name),
',', ', ')
) AS "ASS",
A.assigned_to_pg_new_name, c.company_name,
DECODE (TRIM (c.jp_cause_desc),
NULL, 'N/A',
c.jp_cause_desc
) AS jp_cause_desc,
c.jp_factor,
DECODE (TRIM (c.jp_reason_for_use),
NULL, 'N/A',
c.jp_reason_for_use
) AS jp_reason_for_use,
A.DURATION,
rptrules.Jp_Func_Tz_Conv
('EST',
'IST',
TO_DATE
(CONCAT
(TO_CHAR (c.jp_received_dt, 'dd-Mon-yyyy'),

SUBSTR (TO_CHAR (c.jp_received_time,

'dd-Mon-yyyy hh24:mi:ss'
),

12
)
),
'dd-Mon-
yyyy hh24:mi:ss'
)
) AS "RDATE",
--addition of new owner fields
REPLACE (INITCAP (c.BO_NAME), ',', ', ') AS "BO_NAME",
c.OWNER_PG_ID,
c.NAME1 AS OWNER_PG
-- c.JP_STATUS_CONCAT,
FROM sysadm.JP_HISTORY_TBL A,
sysadm.JP_HISTORY_TBL z,
sysadm.PS_JP_DM_ALT_STG c,
-- SYSADM.PSOPRALIAS D,
sysadm.PS_JP_CYCLE_TIME e,
sysadm.JP_HISTORY_TBL x
WHERE x.case_id = A.case_id
AND Z.status_new = 'Open - In Progress'
AND A.case_id = e.case_id(+)
AND A.assigned_to_pg_new = 'IS00000025'
AND A.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.JP_HISTORY_TBL b1
WHERE A.case_id = b1.case_id
AND b1.row_lastmant_dttm < '21 Jul 2007'
AND b1.assigned_to_pg_flag = 'Y')
-- AND d.opraliastype = 'PER'
AND z.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.JP_HISTORY_TBL b1
WHERE z.case_id = b1.case_id
AND b1.row_lastmant_dttm < '21 Jul 2007'
AND b1.status_flag = 'Y')
AND (z.status_new NOT IN ('Closed', 'Cancelled',
'Draft'))
AND A.case_id = z.case_id
AND c.case_id = A.case_id
-- AND D.OPRID = C.ROW_ADDED_OPRID
AND (c.jp_orig_prvdr_grp != 'IS00000025')
AND x.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm)
FROM sysadm.JP_HISTORY_TBL b1
WHERE x.case_id = b1.case_id
AND b1.row_lastmant_dttm <= '22 Jul 2007'
AND b1.status_flag = 'Y')
AND x.status_new != 'Cancelled'


Thanks. Please tell me if you require more information..

KN Aravindh