11 Replies Latest reply: Aug 11, 2014 6:56 AM by Harmandeep Singh RSS

    Slow Query Performance.


      Hi we are using Release 64bit Production of oracle


      We observe performance issue in which we have one SELECT query which normally took ~.0003 sec per execution took ~.004 second per execution that day and due to the high execution count ~6500000 , this cause the bottlneck and the wrapper procedure took ~7+hrs to completes, whereas previously it normally completes within ~half an hour. I got these info from DBA_HIST_SQLSTAT. Also i verified the plan for the query, its using same plan as it used to follow, so its confirmed that no change in the exection plan happened that day.

      Below is the plan which that query is using.


      SELECT c1 FROM A WHERE A_ID = :B1;

      | Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      |   0 | SELECT STATEMENT                   |             |       |       |     3 (100)|          |       |       |
      |   1 |  PARTITION REFERENCE SINGLE        |             |     1 |    19 |     3   (0)| 00:00:01 |   KEY |   KEY |
      |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| A           |     1 |    19 |     3   (0)| 00:00:01 |   KEY |   KEY |
      |   3 |    INDEX UNIQUE SCAN               | A_PK        |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |


      This table having size ~4GB.



      wait event section for the peak one hr period shows as below


      EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
      db file sequential read4,088,83827,391734.63User I/O
      DB CPU 20,720 26.20
      direct path read282,09411,0573913.98User I/O
      direct path write temp537,9058,0211510.14User I/O
      read by other session363,2291,77352.24User I/O

      What i observe is, this procedure normally runs during down hours but that day it ran during heavy traffic in which we normally see high USER I/O in our system. also from AWR report its only showing high DB file sequential read at the top for the issue period and no other significant waits. And this select also performs hevy I/O due to its high no of execution. But during that period CPU utilization was in normal level(<40%).Now this analysis pointing towards that day somehow we were experiencing "slow logical I/O or index reads' for this query which caused a large difference due to the no of execution.


      Now my question is, is my thought is in right direction, Heavy USER I/O traffic can make other queries(those which dends I/O for its completion) to crawl even if CPU is quite free? And how to overcome this scenario?

        • 1. Re: Slow Query Performance.

          its two node RAC with below configuration:


          Host NamePlatformCPUsCoresSocketsMemory (GB)
          erttydtcccAIX-Based Systems (64-bit)6432 424.00
          • 2. Re: Slow Query Performance.

            I suspect that the EXPLAIN PLAN  was NOT generated against Production DB since it reports row counts under 10.


            can ":B1" ever be NULL?

            can A_ID ever be NULL?



            When your query takes too long ...

            HOW TO: Post a SQL statement tuning request - template posting

            • 3. Re: Slow Query Performance.

              Yes its taken from production DB, inact from AWR plan history using dbms_xplan.display_awr i.e DBA_HIST_SQL_PLAN. An its doing Index unique scan. A_ID is primary key of the table.

              • 4. Re: Slow Query Performance.
                We observe performance issue in which we have one SELECT query which normally took ~.0003 sec per execution took ~.004 second per execution that day and due to the high execution count ~6500000 , this cause the bottlneck and the wrapper procedure took ~7+hrs to completes, whereas previously it normally completes within ~half an hour.

                Which raises the question of why is ONE query being executed several million times per day?


                Please read the FAQ on how to post a tuning request and the info that you need to provide.


                That info includes the table and index DDL, row counts for the tables and query predicates and the actual query.


                That query you posted suggests that it is being executed from PL/SQL since it only returns one column and, presumably only one row. So post the details about the context in which the query is being executed. For example, is the query just part of a batch process and used as a lookup?

                • 5. Re: Slow Query Performance.

                  Honestly, the major flaw in the design of this process is that it needs a response from the database 6.5 million times per execution. This is really not optimal, when developing scallable applications you should reduce the number of round trips you need to do to the database.


                  This is a perfect example of why that approach does not scale. The more you go to the database, the more you are susceptible to even the smallest variation of runtime. Notice we are talking milliseconds here.


                  This variation could be due to several factors, three of the most probable in my opinion:


                  - Small variation in the network response time between user process running the procedure and the database due to peak time.

                  - Other processes doing scans on the A_PK raising the concurrency level on those blocks inside the buffer cache, due to this, some of the LIO could take a few MS more because of CBC latch coordination. Could be the cause of that "read by other session".

                  - Other processes doing DML on the same set of blocks, which could cause Delayed Block Cleanout. This would also incur a small overhead on the LIO scan of those blocks as they would need to be cleaned out first.

                  (so on and so forth)


                  Notice that the main problem here is the design of the process/implementation. It should be going in the database in batches, not for each value of the PK



                  • 6. Re: Slow Query Performance.
                    Hemant K Chitale

                    If one SQL takes, on average, more than ten times as much for an execution,  6.5million executions will also take more than ten times in total.


                    Why is the SQL executed so frequently in the wrapper procedure ?  How frequently is the wrapper procedure executed in a day ? 


                    The table is partitioned.  Is the A_PK index LOCALly partitioned ? 


                    You have a single column PrimaryKey.  And you are using the PK to fetch only one other column.  A workaround to avoid the table fetch completely and only use an index is feasible after evaluating factors like  i) how large (data type and column length) are the two columns relative to the whole table  ii) assuming the PK column is not updated, is the second column frequently updated ?


                    Hemant K Chitale

                    • 7. Re: Slow Query Performance.
                      Harmandeep Singh

                      1. Other experts have already explained the wrong design of program with so high execution count.

                      Check from where it is coming.. Try to merge the logic of sql with base commands if possible..


                      2. Apart from that, single block read time (db file sequential read) count at 7ms seems bit on higher side for production systems. Check with older awr reports of good days, what is the value for the db file sequential read


                      EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
                      db file sequential read4,088,83827,391734.63User I/O


                      3. There is also high buffer level contention on the system. It seems coming from the 6.5 million execution of sql. Also look for the opportunity to tune other sqls


                      read by other session363,2291,77352.24User I/O



                      • 8. Re: Slow Query Performance.

                        Thank you all for the responce. below are answers.


                        Yes the table is reference partitioned on primary key.  and the index is b-tree only. And the index is local partitioned.
                        wrapper procedure executes once in a day. i am not seeing any other waits during the run also this objects were mostlt static kind of objects with less DML exception this procedure.

                        also i am seeing the query has spent mostly time(wait_time+time_waited from dba_hist_active_sess_history) DB FILE SEQUENTIAL read.

                        yes the query is part of a batch process in which its used inside a cursor to validate each and every record before updating records of the transaction table. Like below pseudo code


                        column a_id is having type NUMBER(16).
                        column c1 is type timespamp(6).

                        cusrsor c1_tab1 is select * from tab1;
                        SELECT c1
                                          INTO v_c2
                                          from tab2
                                          WHERE rownum = 1;
                        for c1_rec in c1_tab1
                          select c1  
                           into v_c1
                           from a
                           where a_id= c1_rec.a_id;        

                        if v_c2>v_c1
                               UPDATE a
                               set a.c2=   c1_rec.c2,
                                     a.c3= c1_rec.c3,
                        end if;

                        end loop;



                        i want to know the possibel cause of the slowness that day. procedure runs daily and these days its runs fine without any issue and also before it was running fine , we have similar similat volume excuted without any issue during run in down hours but now for a specifoc day we have large volume came in that to in peak business hour which never happen in past. So just wanted to know the cause of issue, if its possible that the USER I/O bottleneck during the peak hours caue these queries to performa slow I/O and make the over all process to run slow even if CPU was available?  and then how i can overcome this scenario?

                        • 9. Re: Slow Query Performance.
                          Harmandeep Singh

                          Try to do the processing in simple sql command logic , rather than in pl/sql loop.


                          >>>  Cant we write something like below

                          insert into temp_table1

                          select a.c1 , a.c3, a.c3 

                               from a, tab1

                             where a.a_id= tab1.a_id;   


                          UPDATE employee_temp SET (first_name, last_name) =

                            (SELECT first_name, last_name FROM employees

                            WHERE employee_id = employee_temp.employee_id);

                          UPDATE a

                                 set (a.c2, a.c3)=  

                                 ( select tt1.c2,tt2.c3 from temp_table1 tt1 where tt1 > (SELECT c1  from tab2 WHERE rownum = 1)

                                 ) ;






                          • 10. Re: Slow Query Performance.

                            Thanks Harman. i will try to achive it with one update rather looking up the table each time for vaildating the record manually.


                            But my doubt regarding the cause is same, is it possible that if my system is performing heavy I/O then other queries demanding I/O might started perform poorly, even if CPU is <~40% utilized, considering every system has capability to perform certain I/o but not unlimited(i.e DISK limitation)?

                            • 11. Re: Slow Query Performance.
                              Harmandeep Singh

                              Yes your queries with too much physical IO will suffer .

                              Even now, your system is performing high IO.Single block reads at more than 6 million is high. Currently it is like your system is just busy in collecting blocks from disk.


                              As mentioned earlier, check if IO subsystem is fine as single block read time is around 7ms  which seems little bit on higher side.


                              In 1hr period, with direct path reads at  282K, there seems lot of Full scans happening at other places in the code. Tune them too


                              direct path read282,09411,0573913.98User


                              As last resort, try to increase buffer cache, check advisory for same as throwing hardware to tune the system is not good methodology. But before that , tune the code logic