High Performance in Producotn

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.
vadicherlaAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
 
vadicherlaAuthor Commented:

variable table have 3 million records and ccv_secure table have 5 thousand records
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
looks like you need another index on all tables for group_id column only...
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
sarabandeCommented:
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
 
vadicherlaAuthor Commented:
0
 
vadicherlaAuthor Commented:
Hi

I attached Explain Plan Report
0
 
slightwv (䄆 Netminder) Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>ORA-00907: missing right parenthesis

My mistake.  No comma between partition and order by:
over(partition by group_id order by group_id)
0
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>what we really looking for

What about the FIRST_ROWS suggestion?

How much did the execution plan change?

0
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
0
 
slightwv (䄆 Netminder) Commented:
HainKurt,

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

I was aware of that query before but that did not help much
0
 
slightwv (䄆 Netminder) Commented:
You never responded to the questions in http:#a34927376 or did the code in http:#a34927925 fix the paran error?
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:


                      OVER (PARTITION BY ccv_GROUP_ID ORDER BY ccv_GROUP_ID)

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

0
 
slightwv (䄆 Netminder) Commented:
>>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
 
HainKurtSr. System AnalystCommented:
I am out :)
0
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
Attached Explain Plan
Performance.xlsx
0
 
slightwv (䄆 Netminder) Commented:
Post the explain plan for:

SELECT ccv_group_id FROM ccv_groups WHERE group_name = 'MAT'
0
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.