1 2 Previous Next 22 Replies Latest reply on Jun 25, 2012 3:18 AM by rp0428 Go to original post
      • 15. Re: Query is slow taking so much Time
        817202
        Hi below is my update query can anyone of you guru's tell me what eactly happens to this query why it taking so much time
        SQL> EXPLAIN PLAN FOR
          2  Update CDR.cat_in_data_042012_sub set offers = '299' where Transparentdata like '%ctr{299}%';
        
        Explained.
        
        SQL> @C:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\utlxpls.sql
        
        PLAN_TABLE_OUTPUT
        ---------------------------------------------------------------------------------------------------------
        Plan hash value: 368230230
        
        ------------------------------------------------------------------------------------------------
        | Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------------------
        |   0 | UPDATE STATEMENT      |                        |   281M|    77G|  1457K  (1)| 02:27:21 |
        |   1 |  UPDATE               | CAT_IN_DATA_042012_SUB |       |       |            |          |
        |   2 |   PX COORDINATOR      |                        |       |       |            |          |
        |   3 |    PX SEND QC (RANDOM)| :TQ10000               |   281M|    77G|  1457K  (1)| 02:27:21 |
        |   4 |     PX BLOCK ITERATOR |                        |   281M|    77G|  1457K  (1)| 02:27:21 |
        |*  5 |      TABLE ACCESS FULL| CAT_IN_DATA_042012_SUB |   281M|    77G|  1457K  (1)| 02:27:21 |
        
        PLAN_TABLE_OUTPUT
        ---------------------------------------------------------------------------------------------------------
        ------------------------------------------------------------------------------------------------
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           5 - filter("TRANSPARENTDATA" LIKE '%ctr{299}%')
        
        Note
        -----
           - dynamic sampling used for this statement
        
        21 rows selected.
        • 16. Re: Query is slow taking so much Time
          817202
          Kindly help me over the same guru's to understand better
          • 17. Re: Query is slow taking so much Time
            sb92075
            Vikas Kohli wrote:
            Kindly help me over the same guru's to understand better
            changing many rows in a BIG table that requires a Full Table Scan takes time.
            some operations can not be made faster.
            • 18. Re: Query is slow taking so much Time
              Nikolay Savvinov
              Hi,

              your plan is showing that you are doing a full table scan of a 77G table. Assuming that your disk copies data at 10 Mb/s, you're looking at 2 hours just to read the data in. Parallelism helps to reduce this time, but still, it will be slow because there is a lot of data involved, and data transfer has finite speed.

              BTW even if your table had an index on the TRANSPARENTDATA column, the optimizer still would have to do the full table scan, because you're using wildcards on both ends of the substring. As far as I know, this cannot be solved by standard Oracle tools, but if your license covers it, you can use Oracle text features to make your update faster. E.g. see http://docs.oracle.com/cd/B13789_01/text.101/b10730/cqoper.htm

              Best regards,
              Nikolay
              • 19. Re: Query is slow taking so much Time
                817202
                So what i need to do next.Kindly suggest me further steps from your experience guru's
                • 20. Re: Query is slow taking so much Time
                  Nikolay Savvinov
                  Hi,

                  see if it's possible for you to use Oracle Text to solve the problem. Was it really necessary for me to say the same thing twice?

                  Best regards,
                  Nikolay
                  1 person found this helpful
                  • 21. Re: Query is slow taking so much Time
                    817202
                    Thanks Nikolay for your suggestion, I think there is some performance issue with the database as exporting a table also take much time so what i need to do in this case

                    Because i initiated the export of a month table that will size approx 450G using original export utility and from 3 days it is continuously running.
                    • 22. Re: Query is slow taking so much Time
                      rp0428
                      Please open a new thread for the new question as it is not related to the question you ask in this thread.
                      1 2 Previous Next