Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have following sqls executed million times a day, single execution isnt an issue but executing million times causing some performance issues..Thanks in advance for your help!

NOTE: data can change in any of the tables any time during the day..cant reduce number of executions and this is Oracle 11g env.

SELECT COUNT (*)
    INTO v_cnt
  FROM t1 cpr, t2 brl
 WHERE cpr.cp_id = :1
     AND cpr.brl_id = brl.brl_id
     AND brl.ranking > 0
     AND cpr.security_enabled_ind='Y'
     AND cpr.brl_id NOT IN (
                   SELECT rpr.brl_id
                     FROM t3 rpr, t4 ppr, t5 plcy
                    WHERE rpr.ppr_id = ppr.ppr_id
                      AND ppr.plcy_id = plcy.plcy_id
                      AND plcy.db_object_name = :2);
IF v_cnt = 0
THEN

SELECT where_clause
  FROM
    (SELECT where_clause
       FROM t4 ppr_o
     WHERE ppr_o.ppr_id IN (
                    SELECT ppr.ppr_id
                      FROM t1 cpr,
                           t3 rpr,
                           t4 ppr,
                           t5 plcy
                     WHERE cpr.cp_id = :1 
                        AND cpr.brl_id = rpr.brl_id
                        AND rpr.ppr_id = ppr.ppr_id
                        AND ppr.plcy_id = plcy.plcy_id
                        AND cpr.security_enabled_ind='Y'
                        AND plcy.db_object_name = :2
                        AND plcy.statement_type = :3)
    ORDER BY NVL (ppr_o.ranking, 0) DESC)
WHERE ROWNUM = 1;
END IF;

No of records

  • t1 -> 205101
  • t2 -> 42
  • t3 -> 142
  • t4 -> 57
  • t5 -> 13
share|improve this question
1  
There are 86,400 seconds in the day. You are running this 12 times a second at least 24/7? –  gbn May 27 '11 at 16:51
2  
What percentage of executions use the same bind values? –  Leigh Riffel May 27 '11 at 18:47
2  
WHAT performance issues? I/O, contention, CPU? Look at the query plan and at AWR so you know what to tune. –  Gaius May 28 '11 at 9:12
2  
I would also consider accept answers to your questions... –  gbn May 29 '11 at 8:38
2  
can you show us the execution plan for both queries? –  a_horse_with_no_name May 29 '11 at 9:23

2 Answers 2

I would first consider trying to combine the two queries into one. If the second query could be refactored to return null in the case where the first query returned zero would you be able to work with that?

For example,

testbed:

create table t1( cp_id integer, 
                 brl_id integer, 
                 security_enabled_ind char(1) );
create table t2( brl_id integer, 
                 ranking number );
create table t3( ppr_id integer,
                 brl_id integer );
create table t4( ppr_id integer, 
                 plcy_id integer, 
                 where_clause varchar(1000), 
                 ranking number );
create table t5( plcy_id integer, 
                 db_object_name varchar(100), 
                 statement_type integer );

refactored query:

with w as ( select rpr.brl_id, ppr_id, plcy.statement_type
            from t3 rpr join t4 ppr using(ppr_id) join t5 plcy using(plcy_id)
            where plcy.db_object_name=:2 )
select where_clause
from( select where_clause
      from t4 ppr_o
      where not exists ( select *
                         from t1 cpr join t2 brl using(brl_id)
                         where cpr.cp_id = 1 and brl.ranking > 0
                               and cpr.security_enabled_ind='Y'
                               and brl_id not in (select brl_id from w) )
            and ppr_o.ppr_id in ( select ppr_id
                                  from t1 cpr join w using(brl_id)
                                  where cpr.cp_id=:1
                                        and cpr.security_enabled_ind='Y'
                                        and w.statement_type=:3 )
    order by ppr_o.ranking desc nulls last )
where rownum=1;
share|improve this answer
    
Thanks for the query..we are actually trying to avoid querying transaction tables million times, looking for snapshots kind of approach or caching tables or any other approaches. –  Sree May 31 '11 at 13:47

If multiple runs with the same parameters occur between changes in the data you could consider a Server Results Cache. It will use some memory, but when it can use the cache it should decrease the performance hit.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.