|
Replies:
27
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Jan 13, 2008 11:18 AM
Last Post By: antu
|
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 8:35 AM
|
|
|
on 10gR2 EE Solaris 10, I run below script from unix session sql*plus;
conn usr/paswd
SET LINESIZE 2000
SET AUTOT ON
SET TIME ON
SET TIMING ON
ALTER SESSION ENABLE PARALLEL DML ;
SELECT c1,
c2,
SUM(c3),
SUM(c4)
FROM fact_table cs,
dim1 ct,
dim2 tr,
dim3 ta,
dim4 pa
WHERE cs.a_id = ct.a_id
AND cs.b_id = tr.b_id
AND cs.c_id = ta.c_id
AND cs.d_id = pa.d_id
AND ct.c5 = 'ABC' AND pa.c4 = 'Bla Bla'
GROUP BY c1, c2 ;
-- all single-column bitmap indexes were built on fact table’s dimension keys
-- unique key indexes were created on dimension tables’ join columns and
appropriate indexes were also built on dimension filters
-- analyzed all tables in the form clause with DBMS_STATS and appropriate olap init.ora parameters were set;
SELECT name, value FROM v$system_parameter a
WHERE a.NAME IN ('compatible', 'optimizer_features_enable', 'optimizer_mode',
'pga_aggregate_target', 'workarea_size_policy', 'query_rewrite_enabled',
'query_rewrite_integrity', 'parallel_max_servers', 'disk_asynch_io',
'db_file_multiblock_read_count', 'star_transformation_enabled')
disk_asynch_io TRUE
compatible 10.2.0.3.0
db_file_multiblock_read_count 16
parallel_max_servers 80
optimizer_features_enable 10.2.0.3
optimizer_mode ALL_ROWS
star_transformation_enabled TRUE
query_rewrite_enabled TRUE
query_rewrite_integrity enforced
pga_aggregate_target 4848297984
workarea_size_policy AUTO
exec dbms_stats.gather_schema_stats( ownname =>'SCH1', degree=>16, options=>'GATHER STALE', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', granularity=>'ALL');
exec dbms_stats.gather_dictionary_stats( degree=>16, options=>'GATHER STALE', cascade=>TRUE);
exec dbms_stats.gather_fixed_objects_stats ;
after this introduction information here comes my question 
when I ran the query it does star transformation, but I see db file sequential read waits on Enterprise Manager Top Activity ans Ash report, not direct path waits, is this something expected and related to bitmap indexes?
any advices related to star transformation desing, tuning will be welcomed.
Thank you. |
|
|
Posts:
106
Registered:
03/19/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 9:12 AM
in response to: antu
|
|
|
|
The db file sequential read is normal since that indicates an index read is taking place and the blocks are being read into cache. The direct path read event would indicate a bottleneck in reading the data from the data files doing table scans.
Reviewing the explain plan and run statistics will indicate your waits and ares for tuning.
|
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 10:50 AM
in response to: stuart_wong
|
|
|
I know that db file sequential read wait is related to index access and direct path waits for parallel query, but since star transformation and bitmap indexes are positioned to be olap options it was a suprise for me to see my queries almost %90 is db file sequential read wait.
I hoped this to be related to design, wrong statistics collection, data distribution or even a hidden parameter behaviour |
|
|
Posts:
454
Registered:
05/08/04
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 2:41 PM
in response to: antu
|
|
|
|
Hi,
I've been doing a lot of work on star transforms recently and I may be able to help.
db file sequential read is not an unusual wait in star transformed query - after all,
when the bitmap and'ing and or'ing has been done, you are effectively left with a bunch of rowids that is used to probe the fact table.
So at the very least you would expect these waits even if it was just at the last step of a query.
However, to be able to comment further you need to provide the most important piece of info - the explain plan for the above query.
HTH
Chris
|
|
|
Posts:
215
Registered:
10/03/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 8:44 PM
in response to: antu
|
|
|
exec dbms_stats.gather_schema_stats(
ownname =>'SCH1', degree=>16,
options=>'GATHER STALE',
estimate_percent=>dbms_stats.auto_sample_size,
cascade=>TRUE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
granularity=>'ALL');
I don't know your partitioning strategy, but unless you are getting subpartition pruning you probably don't need granularity=>'ALL'
. Generally the default is sufficient which I believe is 'GLOBAL AND PARTITION'
--
Regards,
Greg Rahn
http://structureddata.org |
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 10:07 PM
in response to: Chris Poole
|
|
|
Thank you for your interest, below is the plan from awr, I just changed the table names and the filters.
My fact table in this example is ~83Gb and each bitmap is ~16Gb, with this plan it takes "Elapsed: 01:25:41.10" and full of db file sequential read waits. Where as if I drop the indexes it is only "Elapsed: 00:11:12.81"
Since the join columns bitmap indexed here are low cardinality columns I thought I must be doing something wrong to have star transformation this much slower;
"Elapsed: 01:25:41.10"
SELECT * FROM TABLE(dbms_xplan.display_awr('a7y71896krsks', NULL, NULL, 'ALL'));
SQL_ID a7y71896krsks
--------------------
CREATE TABLE t1 NOLOGGING PARALLEL 4 as SELECT call_type_group, traffic_desc, tariff_name, payment_opt_desc, SUM(qty_calls) sum_qty_calls,
SUM(tl_call_cost) sum_tl_call_cost FROM fact_table cs, dim1 ct, dim2 tr, dim3
ta, dim4 pa WHERE cs.call_type_id = ct.call_type_id AND cs.traffic_id = tr.traffic_id AND cs.tariff_id = ta.tariff_id AND
cs.payment_opt_id = pa.payment_opt_id AND ct.call_type_group = 'ABC' AND pa.payment_opt_desc = 'Bla Bla' GROUP BY call_type_group, traffic_desc,
tariff_name, payment_opt_desc
Plan hash value: 919244636
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 40484 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10008 | 1 | 216 | 40483 (2)| 00:01:42 | | | Q1,08 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | | | Q1,08 | PCWP | |
| 4 | HASH GROUP BY | | 1 | 216 | 40483 (2)| 00:01:42 | | | Q1,08 | PCWP | |
| 5 | PX RECEIVE | | 1 | 216 | 40483 (2)| 00:01:42 | | | Q1,08 | PCWP | |
| 6 | PX SEND HASH | :TQ10007 | 1 | 216 | 40483 (2)| 00:01:42 | | | Q1,07 | P->P | HASH |
| 7 | HASH GROUP BY | | 1 | 216 | 40483 (2)| 00:01:42 | | | Q1,07 | PCWP | |
| 8 | HASH JOIN | | 1 | 216 | 40482 (2)| 00:01:42 | | | Q1,07 | PCWP | |
| 9 | PX RECEIVE | | 1 | 151 | 40442 (2)| 00:01:42 | | | Q1,07 | PCWP | |
| 10 | PX SEND HASH | :TQ10005 | 1 | 151 | 40442 (2)| 00:01:42 | | | Q1,05 | P->P | HASH |
| 11 | HASH JOIN | | 1 | 151 | 40442 (2)| 00:01:42 | | | Q1,05 | PCWP | |
| 12 | PX RECEIVE | | 1 | 111 | 40441 (2)| 00:01:42 | | | Q1,05 | PCWP | |
| 13 | PX SEND HASH | :TQ10004 | 1 | 111 | 40441 (2)| 00:01:42 | | | Q1,04 | P->P | HASH |
| 14 | HASH JOIN BUFFERED | | 1 | 111 | 40441 (2)| 00:01:42 | | | Q1,04 | PCWP | |
| 15 | PX RECEIVE | | 1 | 46 | 40371 (2)| 00:01:42 | | | Q1,04 | PCWP | |
| 16 | PX SEND HASH | :TQ10002 | 1 | 46 | 40371 (2)| 00:01:42 | | | Q1,02 | P->P | HASH |
| 17 | HASH JOIN | | 1 | 46 | 40371 (2)| 00:01:42 | | | Q1,02 | PCWP | |
| 18 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 19 | PX RECEIVE | | 1 | 25 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | PX SEND BROADCAST | :TQ10000 | 1 | 25 | 1 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 21 | TABLE ACCESS BY INDEX ROWID | dim1 | 1 | 25 | 1 (0)| 00:00:01 | | | | | |
| 22 | INDEX RANGE SCAN | NUI_dim1_CALL_TYPE_GROUP | 1 | | 1 (0)| 00:00:01 | | | | | |
| 23 | PX PARTITION HASH ALL | | 778K| 15M| 40355 (1)| 00:01:42 | 1 | 16 | Q1,02 | PCWC | |
| 24 | TABLE ACCESS BY LOCAL INDEX ROWID| fact_table | 778K| 15M| 40355 (1)| 00:01:42 | 1 | 64 | Q1,02 | PCWP | |
| 25 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q1,02 | PCWP | |
| 26 | BITMAP AND | | | | | | | | Q1,02 | PCWP | |
| 27 | BITMAP MERGE | | | | | | | | Q1,02 | PCWP | |
| 28 | BITMAP KEY ITERATION | | | | | | | | Q1,02 | PCWP | |
| 29 | BUFFER SORT | | | | | | | | Q1,02 | PCWP | |
| 30 | TABLE ACCESS FULL | dim3 | 1 | 65 | 17 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 31 | BITMAP INDEX RANGE SCAN | BI_MV_SUM05_TARIFF_ID | | | | | 1 | 64 | Q1,02 | PCWP | |
| 32 | BITMAP MERGE | | | | | | | | Q1,02 | PCWP | |
| 33 | BITMAP KEY ITERATION | | | | | | | | Q1,02 | PCWP | |
| 34 | BUFFER SORT | | | | | | | | Q1,02 | PCWP | |
| 35 | TABLE ACCESS BY INDEX ROWID| dim1 | 1 | 25 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 36 | INDEX RANGE SCAN | NUI_dim1_CALL_TYPE_GROUP | 1 | | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 37 | BITMAP INDEX RANGE SCAN | BI_MV_SUM05_CALL_TYPE_ID | | | | | 1 | 64 | Q1,02 | PCWP | |
| 38 | BITMAP MERGE | | | | | | | | Q1,02 | PCWP | |
| 39 | BITMAP KEY ITERATION | | | | | | | | Q1,02 | PCWP | |
| 40 | BUFFER SORT | | | | | | | | Q1,02 | PCWP | |
| 41 | TABLE ACCESS BY INDEX ROWID| dim4 | 1 | 40 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 42 | INDEX RANGE SCAN | NUI_PAYM_OPTS_PAYM_OPT_DESC | 1 | | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 43 | BITMAP INDEX RANGE SCAN | BI_MV_SUM05_OPTS_PAYM_OPT_ID | | | | | 1 | 64 | Q1,02 | PCWP | |
| 44 | PX RECEIVE | | 1 | 65 | 69 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 45 | PX SEND HASH | :TQ10003 | 1 | 65 | 69 (0)| 00:00:01 | | | Q1,03 | P->P | HASH |
| 46 | PX BLOCK ITERATOR | | 1 | 65 | 69 (0)| 00:00:01 | | | Q1,03 | PCWC | |
| 47 | TABLE ACCESS FULL | dim3 | 1 | 65 | 69 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 48 | BUFFER SORT | | | | | | | | Q1,05 | PCWC | |
| 49 | PX RECEIVE | | 1 | 40 | 1 (0)| 00:00:01 | | | Q1,05 | PCWP | |
| 50 | PX SEND HASH | :TQ10001 | 1 | 40 | 1 (0)| 00:00:01 | | | | S->P | HASH |
| 51 | TABLE ACCESS BY INDEX ROWID | dim4 | 1 | 40 | 1 (0)| 00:00:01 | | | | | |
| 52 | INDEX RANGE SCAN | NUI_PAYM_OPTS_PAYM_OPT_DESC | 1 | | 1 (0)| 00:00:01 | | | | | |
| 53 | PX RECEIVE | | 9 | 585 | 39 (0)| 00:00:01 | | | Q1,07 | PCWP | |
| 54 | PX SEND HASH | :TQ10006 | 9 | 585 | 39 (0)| 00:00:01 | | | Q1,06 | P->P | HASH |
| 55 | PX BLOCK ITERATOR | | 9 | 585 | 39 (0)| 00:00:01 | | | Q1,06 | PCWC | |
| 56 | TABLE ACCESS FULL | dim2 | 9 | 585 | 39 (0)| 00:00:01 | | | Q1,06 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5208623C
21 - SEL$5208623C / CT@SEL$1
22 - SEL$5208623C / CT@SEL$1
24 - SEL$5208623C / CS@SEL$1
30 - SEL$16BC9C10 / TA@SEL$16BC9C10
35 - SEL$294154DE / CT@SEL$294154DE
36 - SEL$294154DE / CT@SEL$294154DE
41 - SEL$311A8338 / PA@SEL$311A8338
42 - SEL$311A8338 / PA@SEL$311A8338
47 - SEL$5208623C / TA@SEL$1
51 - SEL$5208623C / PA@SEL$1
52 - SEL$5208623C / PA@SEL$1
56 - SEL$5208623C / TR@SEL$1
Note
-----
- dynamic sampling used for this statement
- star transformation used for this statement
-------------------------
"Elapsed: 00:11:12.81"
SELECT * FROM TABLE(dbms_xplan.display_awr('ahhu9thqdbmgy', NULL, NULL, 'ALL'));
SQL_ID ahhu9thqdbmgy
--------------------
CREATE TABLE t3 NOLOGGING PARALLEL 4 as SELECT /*+ parallel(cs) parallel(ct) parallel(tr) parallel(ta) parallel(pa) */ call_type_group, traffic_desc,
tariff_name, payment_opt_desc, SUM(qty_calls) sum_qty_calls, SUM(tl_call_cost) sum_tl_call_cost FROM fact_table cs,
dim1 ct, dim2 tr, dim3 ta, dim4 pa WHERE cs.call_type_id =
ct.call_type_id AND cs.traffic_id = tr.traffic_id AND cs.tariff_id = ta.tariff_id AND cs.payment_opt_id = pa.payment_opt_id AND ct.call_type_group =
'ABC' AND pa.payment_opt_desc = 'Bla Bla' GROUP BY call_type_group, traffic_desc, tariff_name, payment_opt_desc
Plan hash value: 2121060768
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 244K(100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 3765 | 334K| 238K (23)| 00:09:56 | | | Q1,04 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | | | Q1,04 | PCWP | |
| 4 | HASH GROUP BY | | 3765 | 334K| 238K (23)| 00:09:56 | | | Q1,04 | PCWP | |
| 5 | PX RECEIVE | | 3765 | 334K| 238K (23)| 00:09:56 | | | Q1,04 | PCWP | |
| 6 | PX SEND HASH | :TQ10003 | 3765 | 334K| 238K (23)| 00:09:56 | | | Q1,03 | P->P | HASH |
| 7 | HASH GROUP BY | | 3765 | 334K| 238K (23)| 00:09:56 | | | Q1,03 | PCWP | |
| 8 | HASH JOIN | | 30M| 2636M| 234K (22)| 00:09:48 | | | Q1,03 | PCWP | |
| 9 | PX RECEIVE | | 12762 | 872K| 70 (2)| 00:00:01 | | | Q1,03 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10002 | 12762 | 872K| 70 (2)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |
| 11 | MERGE JOIN CARTESIAN | | 12762 | 872K| 70 (2)| 00:00:01 | | | Q1,02 | PCWP | |
| 12 | SORT JOIN | | | | | | | | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 9 | 387 | 26 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 9 | 387 | 26 (0)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 15 | MERGE JOIN CARTESIAN | | 9 | 387 | 26 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 16 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 17 | PX RECEIVE | | 1 | 30 | 5 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ10000 | 1 | 30 | 5 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 19 | MERGE JOIN CARTESIAN | | 1 | 30 | 5 (0)| 00:00:01 | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | dim4 | 1 | 18 | 2 (0)| 00:00:01 | | | | | |
| 21 | INDEX RANGE SCAN | NUI_PAYM_OPTS_PAYM_OPT_DESC | 1 | | 1 (0)| 00:00:01 | | | | | |
| 22 | BUFFER SORT | | 1 | 12 | 3 (0)| 00:00:01 | | | | | |
| 23 | TABLE ACCESS BY INDEX ROWID| dim1 | 1 | 12 | 2 (0)| 00:00:01 | | | | | |
| 24 | INDEX RANGE SCAN | NUI_dim1_CALL_TYPE_GROUP | 1 | | 1 (0)| 00:00:01 | | | | | |
| 25 | BUFFER SORT | | 9 | 117 | 24 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 26 | PX BLOCK ITERATOR | | 9 | 117 | 20 (0)| 00:00:01 | | | Q1,01 | PCWC | |
| 27 | TABLE ACCESS FULL | dim2 | 9 | 117 | 20 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 28 | BUFFER SORT | | 1418 | 38286 | 50 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 29 | PX BLOCK ITERATOR | | 1418 | 38286 | 35 (0)| 00:00:01 | | | Q1,02 | PCWC | |
| 30 | TABLE ACCESS FULL | dim3 | 1418 | 38286 | 35 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 31 | PX BLOCK ITERATOR | | 820M| 16G| 226K (19)| 00:09:29 | 1 | 16 | Q1,03 | PCWC | |
| 32 | TABLE ACCESS FULL | fact_table | 820M| 16G| 226K (19)| 00:09:29 | 1 | 64 | Q1,03 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
20 - SEL$1 / PA@SEL$1
21 - SEL$1 / PA@SEL$1
23 - SEL$1 / CT@SEL$1
24 - SEL$1 / CT@SEL$1
27 - SEL$1 / TR@SEL$1
30 - SEL$1 / TA@SEL$1
32 - SEL$1 / CS@SEL$1
Message was edited by:
FENERBAHCE |
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 10:10 PM
in response to: Greg Rahn
|
|
|
Greg hi, I am a fan of your blog
Do you think or experienced using "granularity=>'ALL'" option may cause any problems, Or you just warned that this might not cause harm but not needed?
Thank you. |
|
|
Posts:
454
Registered:
05/08/04
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 10:49 PM
in response to: antu
|
|
|
|
Hi,
Well first port of call is the number 778K. This is the number of rows that Oracle thinks will be accessed in the fact table as a result of all the bitmap and'ing and merging from the bitmap indexes on the dim tables.
Does this number sound even vaguely correct?
Chris
|
|
|
Posts:
454
Registered:
05/08/04
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 11:00 PM
in response to: antu
|
|
|
|
Error!
Message was edited by:
Chris Poole
|
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 7, 2008 11:03 PM
in response to: Chris Poole
|
|
|
Yes that estimation is correct, also below are the counts which may give some more idea I guess;
SQL> SELECT /*+ parallel(t) parallel_index(t) */ count(*) FROM fact_table t ;
COUNT(*)
----------
821433524
SQL> SELECT /*+ parallel(t) parallel_index(t) */ DISTINCT call_type_id FROM fact_table t ;
CALL_TYPE_ID
------------
1000
1300
1500
2500
2100
2700
1600
1900
1200
3100
1100
2400
12 rows selected
SQL> SELECT /*+ parallel(t) parallel_index(t) */ DISTINCT traffic_id FROM fact_table t ;
TRAFFIC_ID
----------
6
1
5
3
4
8
2
7 rows selected
SQL> SELECT /*+ parallel(t) parallel_index(t) */ DISTINCT tariff_id FROM fact_table t ;
TARIFF_ID
----------
17878
24241
8291
..
17882
6447
8688
75 rows selected
SQL> SELECT /*+ parallel(t) parallel_index(t) */ DISTINCT payment_opt_id FROM fact_table t ;
PAYMENT_OPT_ID
--------------
1
3
2
|
|
|
Posts:
1,039
Registered:
03/20/99
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 8, 2008 12:07 AM
in response to: antu
|
|
|
Hi
dynamic sampling used for this statement
I find very interesting to note that you are using dynamic sampling. With such a SQL statement, IMO, this is because object statistics for one of the referenced objects are missing. It is the case? Note that this might be due to the utilization of GATHER STALE...
Of course my wild guess is that with dynamic sampling (which per default operates on few blocks) the query optimizer gets inaccurate statistics that leads to inaccurate costs... I find especially low the cost of getting 778K rows via rowid.
HTH
Chris |
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 8, 2008 1:25 AM
in response to: Chris Antognini
|
|
|
Chris thank you for your interest, my STATISTICS_LEVEL is set to TYPICAL at instance level. I used GATHER_SCHEMA_STATS with GATHER STALE option after all test schema tables were created, and I used COMPUTE STATISTICS option with each CREATE BITMAP INDEX(which seems to be the default behavior after 10g)
But indeed some of my tables have missing statistics still, I asumed wrong and didn't control; STALE = new objects + 10% changed monitored objects(with 10g statistics_level typical monitors all, no need to alter individually) but this seems to be AUTO option;
SELECT count(*) FROM dba_tables WHERE owner = 'SCH1' and last_analyzed IS NULL ;
EXEC DBMS_STATS.gather_schema_stats( ownname =>'SCH1', degree=>16, options=>'GATHER STALE', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', granularity=>'ALL');
commit;
SELECT count(*) FROM dba_tables WHERE owner = 'SCH1' and last_analyzed IS NULL ;
EXEC DBMS_STATS.gather_schema_stats( ownname =>'SCH1', degree=>16, options=>'GATHER AUTO', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', granularity=>'ALL');
SQL>
COUNT(*)
----------
14
PL/SQL procedure successfully completed
Commit complete
COUNT(*)
----------
14
:: collecting & waiting ::
Now after my stats collection finished I will immediately try star transformation again, lucky to have a close lunch break
Again thank you for your feedback, best regards. |
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 8, 2008 2:17 AM
in response to: Chris Antognini
|
|
|
This was huge I ran the query with "autotrace traceonly" option of sql*plus, and here is the new output;
58 rows selected.
Elapsed: 00:14:39.76
Execution Plan
----------------------------------------------------------
Plan hash value: 1144436755
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3765 | 334K| 101K (2)| 00:04:15 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 3765 | 334K| 101K (2)| 00:04:15 | | | Q1,05 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 3765 | 334K| 101K (2)| 00:04:15 | | | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 3765 | 334K| 101K (2)| 00:04:15 | | | Q1,05 | PCWP | |
| 5 | PX SEND HASH | :TQ10004 | 3765 | 334K| 101K (2)| 00:04:15 | | | Q1,04 | P->P | HASH |
| 6 | HASH GROUP BY | | 3765 | 334K| 101K (2)| 00:04:15 | | | Q1,04 | PCWP | |
|* 7 | HASH JOIN | | 1125K| 97M| 101K (2)| 00:04:15 | | | Q1,04 | PCWP | |
| 8 | PX RECEIVE | | 1418 | 38286 | 17 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10002 | 1418 | 38286 | 17 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 1418 | 38286 | 17 (0)| 00:00:01 | | | Q1,02 | PCWC | |
| 11 | TABLE ACCESS FULL | CADM_TARIFFS | 1418 | 38286 | 17 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 12 | HASH JOIN | | 1125K| 68M| 101K (2)| 00:04:15 | | | Q1,04 | PCWP | |
| 13 | PX RECEIVE | | 9 | 117 | 10 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10003 | 9 | 117 | 10 (0)| 00:00:01 | | | Q1,03 | P->P | BROADCAST |
| 15 | PX BLOCK ITERATOR | | 9 | 117 | 10 (0)| 00:00:01 | | | Q1,03 | PCWC | |
| 16 | TABLE ACCESS FULL | CADM_TRAFFIC | 9 | 117 | 10 (0)| 00:00:01 | | | Q1,03 | PCWP | |
|* 17 | HASH JOIN | | 1125K| 54M| 101K (2)| 00:04:15 | | | Q1,04 | PCWP | |
| 18 | BUFFER SORT | | | | | | | | Q1,04 | PCWC | |
| 19 | PX RECEIVE | | 1 | 18 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 20 | PX SEND BROADCAST | :TQ10000 | 1 | 18 | 2 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 21 | TABLE ACCESS BY INDEX ROWID | CADM_PAYMENT_OPTIONS | 1 | 18 | 2 (0)| 00:00:01 | | | | | |
|* 22 | INDEX RANGE SCAN | NUI_PAYM_OPTS_PAYM_OPT_DESC | 1 | | 1 (0)| 00:00:01 | | | | | |
|* 23 | HASH JOIN | | 3375K| 106M| 101K (2)| 00:04:15 | | | Q1,04 | PCWP | |
| 24 | BUFFER SORT | | | | | | | | Q1,04 | PCWC | |
| 25 | PX RECEIVE | | 1 | 12 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 26 | PX SEND BROADCAST | :TQ10001 | 1 | 12 | 2 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 27 | TABLE ACCESS BY INDEX ROWID | CADM_CALL_TYPES | 1 | 12 | 2 (0)| 00:00:01 | | | | | |
|* 28 | INDEX RANGE SCAN | NUI_CALL_TYPES_CALL_TYPE_GROUP | 1 | | 1 (0)| 00:00:01 | | | | | |
| 29 | PX PARTITION HASH ALL | | 30M| 608M| 101K (2)| 00:04:15 | 1 | 16 | Q1,04 | PCWC | |
| 30 | TABLE ACCESS BY LOCAL INDEX ROWID| CADM_MV_SUM05_NEW2 | 30M| 608M| 101K (2)| 00:04:15 | 1 | 64 | Q1,04 | PCWP | |
| 31 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q1,04 | PCWP | |
| 32 | BITMAP AND | | | | | | | | Q1,04 | PCWP | |
| 33 | BITMAP MERGE | | | | | | | | Q1,04 | PCWP | |
| 34 | BITMAP KEY ITERATION | | | | | | | | Q1,04 | PCWP | |
| 35 | BUFFER SORT | | | | | | | | Q1,04 | PCWP | |
| 36 | TABLE ACCESS BY INDEX ROWID| CADM_CALL_TYPES | 1 | 12 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
|* 37 | INDEX RANGE SCAN | NUI_CALL_TYPES_CALL_TYPE_GROUP | 1 | | 1 (0)| 00:00:01 | | | Q1,04 | PCWP | |
|* 38 | BITMAP INDEX RANGE SCAN | BI_MV_SUM05_CALL_TYPE_ID | | | | | 1 | 64 | Q1,04 | PCWP | |
| 39 | BITMAP MERGE | | | | | | | | Q1,04 | PCWP | |
| 40 | BITMAP KEY ITERATION | | | | | | | | Q1,04 | PCWP | |
| 41 | BUFFER SORT | | | | | | | | Q1,04 | PCWP | |
| 42 | TABLE ACCESS BY INDEX ROWID| CADM_PAYMENT_OPTIONS | 1 | 18 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
|* 43 | INDEX RANGE SCAN | NUI_PAYM_OPTS_PAYM_OPT_DESC | 1 | | 1 (0)| 00:00:01 | | | Q1,04 | PCWP | |
|* 44 | BITMAP INDEX RANGE SCAN | BI_MV_SUM05_OPTS_PAYM_OPT_ID | | | | | 1 | 64 | Q1,04 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("CS"."TARIFF_ID"="TA"."TARIFF_ID")
12 - access("CS"."TRAFFIC_ID"="TR"."TRAFFIC_ID")
17 - access("CS"."PAYMENT_OPT_ID"="PA"."PAYMENT_OPT_ID")
22 - access("PA"."PAYMENT_OPT_DESC"='Prepaid Service')
23 - access("CS"."CALL_TYPE_ID"="CT"."CALL_TYPE_ID")
28 - access("CT"."CALL_TYPE_GROUP"='VOICE')
37 - access("CT"."CALL_TYPE_GROUP"='VOICE')
38 - access("CS"."CALL_TYPE_ID"="CT"."CALL_TYPE_ID")
43 - access("PA"."PAYMENT_OPT_DESC"='Prepaid Service')
44 - access("CS"."PAYMENT_OPT_ID"="PA"."PAYMENT_OPT_ID")
Note
-----
- star transformation used for this statement
Statistics
----------------------------------------------------------
165 recursive calls
3 db block gets
3975129 consistent gets
3943603 physical reads
1956 redo size
4212 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
138 sorts (memory)
0 sorts (disk)
58 rows processed
but still if any advice on db file sequential read wait this responce time may be so much lower compared to its full¶llel table access alternative?
thank you.
Message was edited by:
FENERBAHCE |
|
|
Posts:
1,039
Registered:
03/20/99
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 8, 2008 2:36 AM
in response to: antu
|
|
|
|
Please, format the output otherwise it is not readable...
|
|
|
Posts:
285
Registered:
09/16/07
|
|
|
|
Re: star transformation tuning and db file sequential read wait
Posted:
Jan 8, 2008 3:10 AM
in response to: Chris Antognini
|
|
|
|
Sorry about formating, now I fixed.
|
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
