I have a cursor in a package which is taking a lot of time, and using full table scan.
strainge thing is that, when I saw explain plan, before analyzing table there was one full table scan, after analyzing there are three full table scans and cost increased.
Can you experts hepl me tuning this sql?
explain plan is attached.
SELECT
IDE_INSTRUMENT
,IDE_LISTING
,COD_LANG
,TYP_NAME
,IDE_NAME
,COD_REGROUPING
,IDE_INSTITUTION
,COD_MARKET
,COD_CLASS
,COD_SECT_LIST
,DAT_LAST_TRADING
,NAM_DENORM
FROM SMF_NAMES_DENORM_IBS sndi, SMF_INSTR_NAMES inna, SMF_ISTN_NAMES isna
WHERE isna.ide_institution_name = sndi.ide_name
AND inna.ide_instr_name = sndi.ide_name
AND DECODE(typ_name, 'SHOR', UPPER(isna.nam_short),
'LONG', UPPER(isna.nam_long),
'TECH', inna.nam_tech_short, sndi.nam_denorm) != sndi.nam_denorm;
Please help me?
Last edited by ahmad_anees; 10-26-2007 at 05:58 AM.
Here, I would say, By analyzing tables it gets tables statistics, that's mean How the table datas are. So after your analyze command oracle got it's comlete data in it's statistics... and on the basis of this statistics oracle decides to have a Full Table Scan.
Someone to correct me if I am wrong BUT
the first query seems to return one row
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 1 38
The second one however returns
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 501 K
I am not very sure, but before stats seems CBO thought the predicate is highly selective, so it used NL join, hoping the system to return just one row.
After the stats, the CBO considers predicate much less selective (501K rows) so it prefers sort merge join, which generally performs better that NL for huge amount of data and supposes Full Table scans for both tables.
What I would ask is , are you sure that the second query performs worse? Full table scan is not always bad
U r right about the hash joins, but CBO favors hash joins anyway, so since it does not uses hash join, there should be a good reason for that ( say the data should be sorted anyway or...)