Solved

High Performance in Producotn

Posted on 2011-02-17
37
241 Views
Last Modified: 2012-05-11
Following quiery cause lot of performance issue in Production


select distinct ssv.ccv_GROUP_ID from variables ssv, ccv_secure  ssv_sec
where (ssv.nnc in(152968274,511078805)   or ssv_sec.nnc in(152968274,511078805))
and ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID and ssv.ccv_GROUP_ID=(select ccv_group_id from ccv_groups where group_name='MAT');



CREATE INDEX IDX_VARIABLES_ALL3 ON variables
(nnc, ccv_GROUP_ID, ccv_VARIABLE_ID);

CREATE INDEX IDX_ccv_SECURE_ALL3 ON SSV_sec
(nnc, ccv_GROUP_ID, ccv_VARIABLE_ID);

Not sure how to fix this.
0
Comment
Question by:vadicherla
  • 13
  • 13
  • 9
  • +2
37 Comments
 

Author Comment

by:vadicherla
ID: 34917061

variable table have 3 million records and ccv_secure table have 5 thousand records
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 350 total points
ID: 34917128
Please post the explain plan

explain plan for
select distinct ssv.ccv_GROUP_ID from variables ssv, ccv_secure  ssv_sec
where (ssv.nnc in(152968274,511078805)   or ssv_sec.nnc in(152968274,511078805))
and ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID and ssv.ccv_GROUP_ID=(select ccv_group_id from ccv_groups where group_name='MAT');

Then to see it (the results I would like posted:
select * from table(dbms_xplan.display);

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34917168
You might also try (I think this will return the same results):

select ssv.ccv_GROUP_ID
from (
select ssv.ccv_GROUP_ID from variables ssv, ccv_secure  ssv_sec,
   row_number() over(partition by group_id, order by group_id) myrownum
where (ssv.nnc in(152968274,511078805)   or ssv_sec.nnc in(152968274,511078805))
and ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID and ssv.ccv_GROUP_ID=(select ccv_group_id from ccv_groups where group_name='MAT')
) where myrownum=1;

0
 
LVL 34

Expert Comment

by:johnsone
ID: 34917236
Without the plan, it is tough to say, but my guess of the access method would be that it is trying to resolve GROUP_ID first.

You might want to reverse your index so that GROUP_ID is the leading column of the index and NNC is the second column on both tables.  We would need to see the plan to be able to tell for sure if that is would help.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 150 total points
ID: 34917290
try this

SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   (ssv.nnc IN (152968274, 511078805) OR ssv_sec.nnc IN (152968274, 511078805))
         AND ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID
         AND ssv.ccv_GROUP_ID = g.ccv_group_id
         AND g.group_name = 'MAT'
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34917323
looks like you need another index on all tables for group_id column only...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34917384
>>looks like you need another index on all tables for group_id column only...

I don't think so but we'll need to wait for the plan.

The large table is the variable table.  Since the query is only looking  for two nnc values and group_id is part of the index already, I don't see an index only on group_id helping a lot.

Guess it might depending on the cardinality of the columns.
0
 
LVL 32

Expert Comment

by:sarabande
ID: 34917742
what about

select count (*) from variables
where nnc in(152968274,511078805)
       and ccv_group_id = (select group_id from ccv_groups where group_name = 'MAT')

union

select count (*) from ccv_secure
where nnc in(152968274,511078805)
       and ccv_group_id = (select group_id from ccv_groups where group_name = 'MAT')

each of the selects should be fast and give more information than your joint select.

i wonder why you always fetch the group_id from ccv_groups. isn't it a constant that never would change?

i mostly made bad experiences with multi-field index. i could imagine that separate index on nnc and variable_id would give better results overall.

Sara
0
 

Author Comment

by:vadicherla
ID: 34919364
0
 

Author Comment

by:vadicherla
ID: 34919370
Hi

I attached Explain Plan Report
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34919500
I assume that plan is for your original SQL and not for any of the ones we've posted?

I would be interested to see what the plans look like for those.

First try HainKurt's removing the sub-select in http:#a34917290

Then we'll worry about how to remove the 'distinct'.

If you want to retry your original try this hint:

select /*+ FIRST_ROWS */ distinct ssv.ccv_GROUP_ID from variables ssv, ccv_secure  ssv_sec
where (ssv.nnc in(152968274,511078805)   or ssv_sec.nnc in(152968274,511078805))
and ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID and ssv.ccv_GROUP_ID=(select ccv_group_id from ccv_groups where group_name='MAT');

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34919583
also try this one
SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   ssv.nnc IN (152968274, 511078805)
         AND ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID
         AND ssv.ccv_GROUP_ID = g.ccv_group_id
         AND g.group_name = 'MAT' 
union
SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   ssv_sec.nnc IN (152968274, 511078805)
         AND ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID
         AND ssv.ccv_GROUP_ID = g.ccv_group_id
         AND g.group_name = 'MAT'

Open in new window

0
 

Author Comment

by:vadicherla
ID: 34926849
Below Query giving me error

select ssv.ccv_GROUP_ID
from (
select ssv.ccv_GROUP_ID from variables ssv, ccv_secure  ssv_sec,
   row_number() over(partition by group_id, order by group_id) myrownum
where (ssv.nnc in(152968274,511078805)   or ssv_sec.nnc in(152968274,511078805))
and ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID and ssv.ccv_GROUP_ID=(select ccv_group_id from ccv_groups where group_name='MAT')
) where myrownum=1;

 ROW_NUMBER() over(partition by group_id order by group_id) as 'myrownum'
             *
ERROR at line 4:
ORA-00907: missing right parenthesis
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34926871
>>ORA-00907: missing right parenthesis

My mistake.  No comma between partition and order by:
over(partition by group_id order by group_id)
0
 

Author Comment

by:vadicherla
ID: 34927137
This query improved Performance but we can't push to produciton as its not doing much of a hlep what we really looking for

SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   (ssv.nnc IN (152968274, 511078805) OR ssv_sec.nnc IN (152968274, 511078805))
         AND ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID
         AND ssv.ccv_GROUP_ID = g.ccv_group_id
         AND g.group_name = 'MAT'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927160
>>what we really looking for

What about the FIRST_ROWS suggestion?

How much did the execution plan change?

0
 

Author Comment

by:vadicherla
ID: 34927190
I tried all those bofore updating here. I still getting same error  

 row_number() over(partition by ccv_group_id order by ccv_ group_id) myrownum
             *
ERROR at line 4:
ORA-00907: missing right parenthesis
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927376
>>ORA-00907: missing right parenthesis

What is your version or Oracle?

Are you running this from sqlplus, inside P/SQL or some other tool?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:HainKurt
ID: 34927925
try this query (to solve row_number "," issue)

SELECT   ssv.ccv_GROUP_ID
  FROM   (SELECT   ROW_NUMBER ()
                      OVER (PARTITION BY GROUP_ID ORDER BY GROUP_ID)
                      myrownum,
                   ssv.ccv_GROUP_ID
            FROM   variables ssv, ccv_secure ssv_sec
           WHERE   (ssv.nnc IN (152968274, 511078805)
                    OR ssv_sec.nnc IN (152968274, 511078805))
                   AND ssv.ccv_GROUP_ID = ssv_sec.ccv_GROUP_ID
                   AND ssv.ccv_GROUP_ID = (SELECT   ccv_group_id
                                             FROM   ccv_groups
                                            WHERE   group_name = 'MAT'))
 WHERE   myrownum = 1;
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34927937
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 150 total points
ID: 34927944
sorry, ignore my previous post :) wrong link

any comment on my post, @ 34919583

http://www.experts-exchange.com/experts.jsp?etIndex=3&mid=4923051#a34919583
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927960
HainKurt,

To correctly reference a post in the current question enter it as: http:#a34919583
0
 

Author Comment

by:vadicherla
ID: 34929432
http:#a34919583

I was aware of that query before but that did not help much
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34929468
You never responded to the questions in http:#a34927376 or did the code in http:#a34927925 fix the paran error?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34929677
what about these two? do these give the same result?
SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   (ssv.nnc IN (152968274, 511078805) OR ssv_sec.nnc IN (152968274, 511078805))
         AND ssv.ccv_GROUP_ID = g.ccv_GROUP_ID
         AND ssv_sec.ccv_GROUP_ID = g.ccv_GROUP_ID
         AND g.group_name = 'MAT' 
         
or this one:

SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   ssv.nnc IN (152968274, 511078805)
         AND ssv.ccv_GROUP_ID = g.ccv_GROUP_ID
         AND ssv_sec.ccv_GROUP_ID = g.ccv_GROUP_ID
         AND g.group_name = 'MAT' 
union
SELECT   DISTINCT ssv.ccv_GROUP_ID
  FROM   variables ssv, ccv_secure ssv_sec, ccv_groups g
 WHERE   ssv_sec.nnc IN (152968274, 511078805)
         AND ssv.ccv_GROUP_ID = g.ccv_GROUP_ID
         AND ssv_sec.ccv_GROUP_ID = g.ccv_GROUP_ID
         AND g.group_name = 'MAT'

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34929697
i am trying to understand the query but I cannot :)

please post a sample data in excel showing some sample values for 3 tables and what are you trying to get:
0
 

Author Comment

by:vadicherla
ID: 34930216
Without the plan, it is tough to say, but my guess of the access method would be that it is trying to resolve GROUP_ID first.

You might want to reverse your index so that GROUP_ID is the leading column of the index and NNC is the second column on both tables.  We would need to see the plan to be able to tell for sure if that is would help.

Above comment also helped. I created  compoind index on gouup_id and nnc. It helped peformance of the query   Can someone tell why it that ??
0
 

Author Comment

by:vadicherla
ID: 34930232


                      OVER (PARTITION BY ccv_GROUP_ID ORDER BY ccv_GROUP_ID)

ERROR at line 3:
ORA-00918: column ambiguously defined

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34930448
>>ORA-00918: column ambiguously defined

You need to add the correct table alias the specific group_id you want.

>>Can someone tell why it that ??

Not without the explain plans.  The optimizer must have liked that new index.


You still aren't responding to all our posts.  Please try to be more responsive to our questions.

For example:  I still haven't seen where you have tried the SQL hint I suggested.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34931359
I am out :)
0
 

Author Comment

by:vadicherla
ID: 34971517
Hi,

We changed the query to below and removed the joint but still this query Causing High CPU

variable table have 2.5 million Records  and ccv_grops have 100 records

select  count(ccv_group_id) total  from variables ssv where nnc in (152968274,511078805) and ccv_group_id = (SELECT ccv_group_id FROM ccv_groups WHERE group_name = 'MAT');

0
 

Author Comment

by:vadicherla
ID: 34971583
Attached Explain Plan
Performance.xlsx
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34983522
Post the explain plan for:

SELECT ccv_group_id FROM ccv_groups WHERE group_name = 'MAT'
0
 

Author Comment

by:vadicherla
ID: 34984462
ecution Plan
----------------------------------------------------------
Plan hash value: 1097087496

--------------------------------------------------------------------------------
--------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

--------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT            |                |     1 |     8 |     2   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| CCV_GROUPS     |     1 |     8 |     2   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INX_GROUP_NMAE |     1 |       |     1   (
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 350 total points
ID: 34984513
I can't find anything that might be causing the high cpu.

What group ccv_group_id does that query return?

Then run the plan for:
select  count(ccv_group_id) total  from variables ssv where nnc in (152968274,511078805) and ccv_group_id = <hard coded value from select above>:



0
 

Author Comment

by:vadicherla
ID: 34984886
ecution Plan
----------------------------------------------------------
Plan hash value: 2782565558

--------------------------------------------------------------------------------
-------

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |             |     1 |    13 |     1 (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |             |     1 |    13 |          |
      |

|*  2 |   INDEX RANGE SCAN| INDX_NNC_GROUP_ID |     3 |    39 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------



0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 350 total points
ID: 34985013
This question has grown pretty big so I apologize if I ask you to repeat anything.

Just to clarify the plan posted in http:#a34971583 was generated from:

select  count(ccv_group_id) total  from variables ssv where nnc in (152968274,511078805) and ccv_group_id = (SELECT ccv_group_id FROM ccv_groups WHERE group_name = 'MAT');


If so based on the last two plans you posted, you might be hitting a Cost Based Optimize bug of some type.  You really might want to contact Oracle Support on this one.

I have a few tricks we can try but I want to ensure the CPU was based on the count(*) query above.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now