12 Replies Latest reply: Nov 21, 2012 5:22 AM by 969801 RSS

    a question about hints

    969801
      if i have a view
                    create or replace force view v1 as 
                     select * from t1
                     union all
                     select * from (select * from t2 ,t3 where t2=t3);
      
      
      
      
                    i have a sql statement like :   
          
      
                    select * from t,v1 where t.id=v1.id;
      
      
                        if i want change the order of the explain ?
      how to use the hints?

      Edited by: 966798 on 2012-11-20 下午11:40
        • 1. Re: a question about hints
          jeneesh
          966798 wrote:
          if i have a view
          create or replace force view v1 as 
          select * from t1
          union all
          select * from (select * from t2 ,t3 where t2=t3);
          
          
          
          
          i have a sql statement like :   
          
          
          select * from t,v1 where t.id=v1.id;
          
          
          if i want change the order of the explain ?
          how to use the hints?

          Edited by: 966798 on 2012-11-20 下午11:40
          you hav e a table T2 with column name as T2..? Strange..!

          between what are you trying to achieve?

          Could you elaborate?
          • 2. Re: a question about hints
            BluShadow
            966798 wrote:
            if i have a view
            create or replace force view v1 as 
            select * from t1
            union all
            select * from (select * from t2 ,t3 where t2=t3);
            
            
            
            
            i have a sql statement like :   
            
            
            select * from t,v1 where t.id=v1.id;
            if i want change the order of the explain ?


            how to use the hints?
            Why do you want to change the explain plan? Do you know better how to access the data from the disks and whether certain indexes should be used, especially as data is added and deleted etc. compared to letting the optimiser do it's job? I very much doubt it.

            Most hints are great for debugging performance issues to try and see where there may be a fault in the database design or a fault in the way an SQL query has been structured, but rarely should hints be used in production code (with the exception of a small number of hints such as APPEND, PARALLEL etc.)

            So, rather than ask how to use hints, why not explain what the issue is you are trying to resolve?

            If it's a performance issue, read the two threads linked to by the FAQ: {message:id=9360003}

            Also make sure you read the FAQ: {message:id=9360002} so that you can help people to understand your issue, and then you'll get better answers.
            • 3. Re: a question about hints
              AlbertoFaenza
              Hi,

              This is not the way to ask a question.

              Please read SQL and PL/SQL FAQ

              If you have a performance issue have a look at SQL and PL/SQL FAQ

              Before using a hint you should post the information regarding current explain plan and what you see wrong in that.

              Regards.
              Al
              • 4. Re: a question about hints
                969801
                sorry, it's only a example!

                because i have a sql runing in oracle 9i ,it's very slow ! but it fast on 11g;


                and i found the difference in the explain;


                so what i want to know is that :

                if i have a view,how can change the order of join ?

                if i have a view like this(example)
                  
                                     create view v1 as  select a.* from a ,(select b.col1 from b,c where b.col1=c.col2) where a.col=b.col1
                
                              
                              i have a sql  like this
                
                
                                       select * from v1 ,d where v1.col=d.col
                
                
                
                                i try this one can do               /*+use_hash(view_name.a d) leading(view_name.a)*/  --it works ! but in another sql !  
                
                                      
                              
                  
                if i want b first join with a,then join with c, how to ?


                best regards!!!!
                • 5. Re: a question about hints
                  969801
                  this isn't a performance issue; thk you any way !



                  i just want to know the way how to use hints in a view!!!
                  • 6. Re: a question about hints
                    969801
                    thk you adv !!! i didn't post a goog question!!


                    in a view ,how to use hint ! if the view is complex view?
                    • 7. Re: a question about hints
                      BluShadow
                      966798 wrote:
                      sorry, it's only a example!

                      because i have a sql runing in oracle 9i ,it's very slow ! but it fast on 11g;


                      and i found the difference in the explain;

                      Yes, because the internals of the SQL engine work differently in both versions, and the optimiser is very different, likely using rule based optimisation in 9i and cost based optimisation in 11g.

                      Adding hints to your 9i query to try and make the explain plans similar won't necessarily make it faster.
                      If your problem is the peformance of a query in 9i, you need to identify what the cause of that is (ignoring the fact the same runs quicker in 11g) and focus on sorting out the underlying cause.
                      (or just upgrade all unsupported 9i instances to 11g, after all 9i was replaced around 10 years ago by 10g, and in computing terms that's 'old technology' now.)
                      • 8. Re: a question about hints
                        AlbertoFaenza
                        966798 wrote:
                        this isn't a performance issue; thk you any way !
                        If you are writing in another post:
                        966798 wrote:
                        because i have a sql runing in oracle 9i ,it's very slow ! but it fast on 11g;
                        Then this is a performance issue.
                        Query very slow = perfomance issue in my dictionary.
                        Am I saying something wrong?

                        Regards.
                        Al
                        • 9. Re: a question about hints
                          969801
                          sorry, i didn't discribe clearly!

                          i had upgraded my db to 11g!

                          but i have a test db that is 9i ;

                          i donot need to perfomance that sql at all!




                          can you help me: how to use hints to tell CBO how to join in a complex view!!

                          sorry again!


                          best regards!!!
                          • 10. Re: a question about hints
                            AlbertoFaenza
                            Hi,

                            as you are in Oracle 9i I will give you the link to documentation of this version:
                            Optimizer Hints

                            You will find at a certain point of this page:

                            <h2>Using Hints Against Views</h2>
                            By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.

                            Regards.
                            Al
                            • 11. Re: a question about hints
                              BluShadow
                              966798 wrote:
                              sorry, i didn't discribe clearly!

                              i had upgraded my db to 11g!

                              but i have a test db that is 9i ;

                              i donot need to perfomance that sql at all!
                              So what's the point in mentioning that it's slow on 9i and fast on 11g?
                              can you help me: how to use hints to tell CBO how to join in a complex view!!
                              You still haven't explained what issue you are trying to solve, especially if it's not a performance issue.

                              There's absolutely no reason to use hints to make the CBO do some join differently unless you can provide us with some evidence and justification as to why you think there is.
                              If you've upgraded to 11g and the query is working performant, what are you actually trying to fix?
                              • 12. Re: a question about hints
                                969801
                                            
                                     this is a part of the sql and it's explain
                                ---------------------------------------------------------------------------------------------
                                | Id  | Operation                    |  Name           | Rows  | Bytes |TempSpc| Cost (%CPU)|
                                ---------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT             |                 |     5 |   715 |       |   891   (2)|
                                |*  1 |  COUNT STOPKEY               |                 |       |       |       |            |
                                |   2 |   VIEW                       |                 | 13077 |  1826K|       |            |
                                |*  3 |    SORT ORDER BY STOPKEY     |                 | 13077 |  2158K|  4664K|   891   (2)|
                                |*  4 |     HASH JOIN OUTER          |                 | 13077 |  2158K|       |   403   (2)|
                                |*  5 |      HASH JOIN OUTER         |                 | 13077 |  1826K|       |   396   (2)|
                                |*  6 |       HASH JOIN              |                 | 13077 |  1775K|       |   389   (1)|
                                |*  7 |        TABLE ACCESS FULL     | T_ZHIDAO        |  2136 |   156K|       |   111   (1)|
                                |   8 |        VIEW                  | V_INDEXNAME     | 11467 |   716K|       |            |
                                |   9 |         UNION-ALL            |                 |       |       |       |            |
                                |  10 |          NESTED LOOPS        |                 |  2845 |   236K|       |   196   (2)|
                                |* 11 |           TABLE ACCESS FULL  | T_CUSTOMER      |  2845 |   133K|       |   194   (1)|
                                |* 12 |           INDEX RANGE SCAN   | PK_CS_C_UID     |     1 |    37 |       |            |
                                |  13 |          NESTED LOOPS        |                 |  2249 |   208K|       |    57   (4)|
                                |* 14 |           TABLE ACCESS FULL  | T_BARBERSHOP    |  2249 |   127K|       |    56   (2)|
                                |* 15 |           INDEX RANGE SCAN   | PK_BS_B_UID     |     1 |    37 |       |            |
                                |* 16 |          TABLE ACCESS FULL   | T_OTHERMEMBER   |    36 |  1908 |       |     4  (25)|
                                |  17 |          NESTED LOOPS        |                 |  6337 |   526K|       |   225   (3)|
                                |* 18 |           TABLE ACCESS FULL  | T_HAIRSTYLIST   |  6337 |   297K|       |   221   (1)|
                                |* 19 |           INDEX RANGE SCAN   | PK_HS_H_UID     |     1 |    37 |       |            |
                                |  20 |       VIEW                   |                 |   159 |   636 |       |            |
                                |  21 |        SORT GROUP BY         |                 |   159 |  1113 |       |     7  (29)|
                                |* 22 |         TABLE ACCESS FULL    | T_ZHIDAOANSWER  |   278 |  1946 |       |     6  (17)|
                                |* 23 |      VIEW                    |                 |   278 |  7228 |       |            |
                                |* 24 |       WINDOW SORT PUSHED RANK|                 |   278 |  3336 |       |     7  (29)|
                                |  25 |        TABLE ACCESS FULL     | T_ZHIDAOANSWER  |   278 |  3336 |       |     6  (17)|
                                
                                
                                ---------------------------------------------------------------------------------------------
                                
                                
                                
                                after  i use hints ,the explain  changed , and it run much faster
                                
                                
                                 
                                ------------------------------------------------------------------------------------------
                                | Id  | Operation                         |  Name           | Rows  | Bytes | Cost (%CPU)|
                                ------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT                  |                 |  1702 |   219K|   673  (42)|
                                |   1 |  SORT ORDER BY                    |                 |  1702 |   219K|   673  (42)|
                                |   2 |   SORT GROUP BY                   |                 |  1702 |   219K|   673  (42)|
                                |   3 |    NESTED LOOPS                   |                 |  1702 |   219K|   671  (42)|
                                |*  4 |     HASH JOIN                     |                 |   278 | 18904 |   117   (2)|
                                |   5 |      TABLE ACCESS FULL            | T_ZHIDAOANSWER  |   278 |  1112 |     6  (17)|
                                |   6 |      TABLE ACCESS FULL            | T_ZHIDAO        |  4272 |   267K|   111   (1)|
                                |   7 |     VIEW                          | V_INDEXNAME     |     6 |   384 |            |
                                |   8 |      UNION-ALL PARTITION          |                 |       |       |            |
                                |   9 |       NESTED LOOPS                |                 |     1 |    85 |     3  (34)|
                                |* 10 |        INDEX UNIQUE SCAN          | PK_CS_C_UID     |     1 |    37 |     2  (50)|
                                |* 11 |        TABLE ACCESS BY INDEX ROWID| T_CUSTOMER      |     1 |    48 |     2  (50)|
                                |* 12 |         INDEX UNIQUE SCAN         | PK_C_UID        |     1 |       |            |
                                |  13 |       NESTED LOOPS                |                 |     1 |    95 |     3  (34)|
                                |* 14 |        INDEX UNIQUE SCAN          | PK_BS_B_UID     |     1 |    37 |     2  (50)|
                                |* 15 |        TABLE ACCESS BY INDEX ROWID| T_BARBERSHOP    |     1 |    58 |     2  (50)|
                                |* 16 |         INDEX UNIQUE SCAN         | PK_B_UID        |     1 |       |            |
                                |* 17 |       TABLE ACCESS BY INDEX ROWID | T_OTHERMEMBER   |     1 |    53 |     2  (50)|
                                |* 18 |        INDEX UNIQUE SCAN          | PK_OM_UID       |     1 |       |            |
                                |  19 |       NESTED LOOPS                |                 |     1 |    85 |     3  (34)|
                                |* 20 |        INDEX UNIQUE SCAN          | PK_HS_H_UID     |     1 |    37 |     2  (50)|
                                |* 21 |        TABLE ACCESS BY INDEX ROWID| T_HAIRSTYLIST   |     1 |    48 |     2  (50)|
                                |* 22 |         INDEX UNIQUE SCAN         | PK_H_UID        |     1 |       |            |
                                ------------------------------------------------------------------------------------------
                                 
                                and also i don't know why the CBO choose  the first  explain ! 
                                
                                    i have gather the statistics;
                                
                                it may useless thing to fix the sql , but i  am hungry to know  how to use hints in a view , may be i will face that in the future~ 
                                
                                
                                thk you!