Lothar,
Thanks for the response. I tried putting the hint into the statement, and it
works the same way. I've also tried changing star_transformation_enabled to
TRUE, but again, same results.
Regarding the SQL, it's being generated by an application, so we have no
control over it (ad-hoc reporting tool).
This query is just one example of many that we're trying to make work with star
transformation, the hope is that if we can see what's happening with this one,
it will lead us towards a solution with the others.
Thanks,
Scott
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Lothar Flatz
Sent: Thursday, October 22, 2015 4:49 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Star Transformation Issue
HI,
maybe something too simple:
Have you tried the hint?
Why star_transformation_enabled at temp_disable? Only makes it complicated.
Probably you should not put conditions in a on clause that have nothing to do
with the Join. Put them in the where clause. E.g.
AND DIM_PS.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_PS.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
regards
Lothar
On 22.10.2015 22:17, Deas, Scott wrote:
All,
I'm trying to enable star transformation in an 11.2.0.4.5 EE RAC database
(running on AIX 6.1).
We have bitmap indexes on FK (with RELY option) on our fact tables, and
non-validated PKs on our Dimension tables. Fact table is a daily partitioned
table, with approx. 6,811,000,000 rows (each partition has on average
32,000,000 rows).
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- -------------
_optimizer_cartesian_enabled boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> show parameter star
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_always_star_transformation boolean TRUE
dg_broker_start boolean FALSE
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string HIGH
log_archive_start boolean FALSE
star_transformation_enabled string TEMP_DISABLE
Query (not my query, I'm just the DBA...) :
SELECT
DIM_PS.PLAN_SPONSOR_NAME,
DIM_P.PLAN_CODE,
DIM_MT.MONEY_TYPE_CODE,
DIM_MT.MONEY_TYPE_NAME,
DIM_F.FUND_CODE,
DIM_F.FUND_NAME,
DIM_F.FUND_TICKER_SYMBOL,
DIM_P.PLAN_LEGAL_NAME,
sum(FACT_PFS.TOTAL_BALANCE) as TOTAL_BALANCE
FROM
DIM_F
INNER JOIN FACT_PFS
ON (FACT_PFS.FUND_KEY=DIM_F.FUND_KEY
AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_F.START_DATE <= to_date('03/06/2015','mm/dd/yyyy') AND
DIM_F.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
INNER JOIN DIM_PART
ON (FACT_PFS.PARTICIPANT_KEY=DIM_PART.PARTICIPANT_KEY
AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy'))
INNER JOIN DIM_P
ON (FACT_PFS.PLAN_KEY=DIM_P.PLAN_KEY
AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_P.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_P.END_DATE >= to_date('03/06/2015','mm/dd/yyyy') AND
DIM_P.PLAN_CODE IN ('UTX-003'))
INNER JOIN DIM_MT
ON (FACT_PFS.MONEY_TYPE_KEY=DIM_MT.MONEY_TYPE_KEY
AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_MT.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_MT.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
INNER JOIN DIM_PS
ON (FACT_PFS.PLAN_SPONSOR_KEY=DIM_PS.PLAN_SPONSOR_KEY
AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_PS.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
AND DIM_PS.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
GROUP BY
DIM_PS.PLAN_SPONSOR_NAME,
DIM_P.PLAN_CODE,
DIM_MT.MONEY_TYPE_CODE,
DIM_MT.MONEY_TYPE_NAME,
DIM_F.FUND_CODE,
DIM_F.FUND_NAME,
DIM_F.FUND_TICKER_SYMBOL,
DIM_P.PLAN_LEGAL_NAME;
The query is not using star transformation, and when running a 10053 trace, I
can see the message "ST: not valid since query block has less than 3 tables".
Obviously the query has more than 3 tables (7 tables actually), but what I'm
seeing is that the optimizer is re-writing the query into two separate query
blocks, and then saying that neither qualify for star transformation :
ST: not valid since query block has less than 3 tables
discarded since not big enough
discarded since not big enough
...
ST: not valid since query block has less than 3 tables
discarded since not big enough
discarded since not big enough
Any way to bypass this re-write so we can see if star transformation can be
used on this query?
Thanks,
Scott
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
--
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
