Link to home
Create AccountLog in
Avatar of gram77
gram77Flag for India

asked on

Sql Tunning

I have the following select statement which is slow running in QA and fast in UAT

There is a difference in the plan generated in both the environments.

Why is Oracle not selecting index in the slow plan.

tablePKG (80 rows), tableGRP (160 rows), and tablePKG_GRP (600 rows) - are small master tables - and are not analyzed since an year, so it seems there is no need to rebuild indexes or analyze them.

Indexes are valid on all these tables.

Why is not Oracle picking up indexes in the slow plan.

How do i tune the sql?
Avatar of gram77
gram77
Flag of India image

ASKER

Query:
SELECT col1,
       col2,
       col3,
            col4
FROM tableA m
INNER JOIN tablePKG p
ON UPPER (p.package_nme) =
                           UPPER (m.inst_vndr_src_id)
                           || '-SRDB'
INNER JOIN tableGRP g
ON UPPER (g.group_nme) =
                         UPPER (m.group_nme)
INNER JOIN tablePKG_GRP pg
ON pg.package_id = p.package_id
AND pg.GROUP_ID = g.GROUP_ID
WHERE m.as_of_dt ='28-JAN-10';
Avatar of gram77

ASKER

Fast Plan:
SELECT STATEMENT  ALL_ROWSCost: 8 K  Bytes: 170  Cardinality: 1                                            
      16 NESTED LOOPS                                      
            14 NESTED LOOPS  Cost: 8 K  Bytes: 170  Cardinality: 1                                
                  12 HASH JOIN  Cost: 8 K  Bytes: 142  Cardinality: 1                          
                        7 HASH JOIN  Cost: 8 K  Bytes: 124  Cardinality: 1                    
                              2 PARTITION LIST ALL  Cost: 8 K  Bytes: 110  Cardinality: 1  Partition #: 5  Partitions accessed #1 - #7            
                                    1 TABLE ACCESS FULL TABLE tableA Cost: 8 K  Bytes: 110  Cardinality: 1  Partition #: 5  Partitions accessed #1 - #7      
                              6 VIEW VIEW index$_join$_002 Cost: 3  Bytes: 1 K  Cardinality: 81              
                                    5 HASH JOIN        
                                          3 INDEX FAST FULL SCAN INDEX (UNIQUE) tablePKG1_idx Cost: 1  Bytes: 1 K  Cardinality: 81                                            4 INDEX FAST FULL SCAN INDEX (UNIQUE) tablePKG2_idx Cost: 1  Bytes: 1 K  Cardinality: 81                          11 VIEW VIEW index$_join$_004 Cost: 3  Bytes: 3 K  Cardinality: 170                    
                              10 HASH JOIN              
                                    8 INDEX FAST FULL SCAN INDEX (UNIQUE) tableGRP1_idx Cost: 1  Bytes: 3 K  Cardinality: 170        
                                    9 INDEX FAST FULL SCAN INDEX (UNIQUE) tableGRP2_idx Cost: 1  Bytes: 3 K  Cardinality: 170        
                  13 INDEX RANGE SCAN INDEX (UNIQUE) PK_tablePKG_GRP_idx Cost: 1  Cardinality: 1                          
            15 TABLE ACCESS BY INDEX ROWID TABLE tablePKG_GRP Cost: 2  Bytes: 28  Cardinality: 1
Avatar of gram77

ASKER

Slow Plan:
SELECT STATEMENT  ALL_ROWSCost: 7 K  Bytes: 3 M  Cardinality: 16 K                                
      11 HASH JOIN  Cost: 7 K  Bytes: 3 M  Cardinality: 16 K                          
            8 HASH JOIN  Cost: 12  Bytes: 32 K  Cardinality: 551                    
                  4 VIEW VIEW index$_join$_004 Cost: 3  Bytes: 3 K  Cardinality: 171              
                        3 HASH JOIN        
                              1 INDEX FAST FULL SCAN INDEX (UNIQUE) tableGRP1_idx Cost: 1  Bytes: 3 K  Cardinality: 171  
                              2 INDEX FAST FULL SCAN INDEX (UNIQUE) tableGRP2_idx Cost: 1  Bytes: 3 K  Cardinality: 171  
                  7 HASH JOIN  Cost: 9  Bytes: 23 K  Cardinality: 551              
                        5 TABLE ACCESS FULL TABLE tablePKG Cost: 3  Bytes: 1 K  Cardinality: 82                                6 TABLE ACCESS FULL TABLE tablePKG_GRP Cost: 5  Bytes: 15 K  Cardinality: 551                    10 PARTITION LIST ALL  Cost: 7 K  Bytes: 30 M  Cardinality: 286 K  Partition #: 10  Partitions accessed #1 - #7                  
                  9 TABLE ACCESS FULL TABLE tableA Cost: 7 K  Bytes: 30 M  Cardinality: 286 K  Partition #: 10  Partitions accessed #1 - #7
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
update statistics using following , but need to used your own schema and table name.

EXEC DBMS_STATS.GATHER_TABLE_STATS ('my_schema', 'my_table');
Avatar of gram77

ASKER

All I know is that the slow query takes 3 hours to complete and fast one completes in an hour. The only difference is saw was in the plan
query plan is generated using the current statistics available.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>The only difference is saw was in the plan

Check the setup.
Are the databases on simiar hardware?
Do other queries between the two systems typically compare equally?

Make sure the tables have the same amounts of data in them.  A query with 600 rows should not take an hour...

I would still analyze the tables just for grins.
Is table size same in QA and UAT environment?

I still suggest to gather stats on all underlying table and then try to compare execution plans and run the query.

If you don't have rights, ask you DBA
Avatar of gram77

ASKER

tableA has a million rows to which smaller tables are joined
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gram77

ASKER

There are no function based indexes in UAT then how is Oracle picking up right indexes in UAT.
>>Oracle picking up right indexes in UAT.

What columns are the indexes on?  If they aren't on the columns using UPPER then this isn't the issue.

Can you explain the byte difference in the two plans that I mentioned above?
Avatar of gram77

ASKER

Looks like the slow plan has many more bytes than the fast plan. Since the fast plan is not picking as many data as the slow plan. Can't confirm now.

Also to me it looks like the issue is not with indexes on the smaller tables, but with tableA which is a big table. tableA date column does not have an index. A full table scan here seems a drag to me.
Explain plain is good only if your table stats are up to date.

Please compare the plans once you have gathered stats
The full table scan on tablea is from the join condition.  That is why I was suggesting the use of the function based index.

Again, I think the suggesting of updating statistics and checking the plans again is probably your best first step.
>> There are no function based indexes in UAT then how is Oracle picking up right indexes in UAT.
It is an INDEX FAST FULL SCAN, not used for the predicate.
Avatar of gram77

ASKER

I did analysis on all the tables involved and still no change in the slow plan. It's still doing full table scan on the small tables.
>>It's still doing full table scan on the small tables.

The CBO can easily choose a full scan of small tables.  It's 'cost' based.

Have you verified the row counts between the two databases?
in addition to row counts, are there differences like 'optimizer mode'?
(you can get quite different explain plan results by changing the optimizer mode)
Avatar of gram77

ASKER

Made the changes, let's see how it fares.