From John.Hallas@btcellnet.net Wed, 21 Nov 2001 07:41:13 -0800 From: Hallas John Date: Wed, 21 Nov 2001 07:41:13 -0800 Subject: RE: SQL query tuning problem Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: Blank Henry,   I remember reading some reviews of it when it came out and thinking I would like it. I presume it includes function based indexes (indices?), more on partitioning etc. I also recall the book being about £40 UK whereas other comparable sized books such as Jonathan Lewis's one are normally around £33 UK. Looking at Amazon now both books are still available. In the review section somebody has commented about the tools provided on CD as being pretty non-robust. That reminds me that I never managed to get any of them working which was a bit of a shame. Thinggs have moved on since then and I have found alternative tools or scripts to do the same job.       John -----Original Message-----From: Henry Poras [mailto:Henry.Poras@ctp.com]Sent: 19 November 2001 14:25To: Multiple recipients of list ORACLE-LSubject: FW: SQL query tuning problem John, What has changed? I think we have both versions floating around here somewhere (unless one of the books left with some of our people). I'll try to take a stroll through the Table of Contents.   Henry -----Original Message-----From: Hallas John [mailto:John.Hallas@btcellnet.net]Sent: Monday, November 19, 2001 5:45 AMTo: Multiple recipients of list ORACLE-LSubject: RE: SQL query tuning problem I have the first edition which is an excellent book but I cannot justify buying the later version - pity really -----Original Message-----From: SARKAR, Samir [mailto:Samir.SARKAR@nottingham.sema.slb.com]Sent: 16 November 2001 15:50To: Multiple recipients of list ORACLE-LSubject: RE: SQL query tuning problem Well Greg......I just ordered the SQL Tuning book by Guy Harrison from Amazon.... hope it is real good as u recommend :) Thanks and Cheers !!   Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email :  samir.sarkar@nottingham.sema.slb.com             samir.sarkar@sema.co.uk Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018            -----Original Message-----From: Greg Moore [mailto:sqlgreg@pacbell.net]Sent: 08 November 2001 18:28To: SARKAR, SamirSubject: Re: SQL query tuning problem >> syntax for having multiple tables in the hint   /*+ full( a b ) */   Look in the Oracle documentation, in the Tuning Guide.  There is a complete chapter on Hints.    If you are interested in tuning, get Guy Harrison's book.  It's available on Amazon.  It's one of the best Oracle books ever written.
----- Original Message -----
From: SARKAR, Samir To: 'Greg Moore' Cc: 'ORACLE-L@fatcity.com' Sent: Thursday, November 08, 2001 2:04 AM Subject: RE: SQL query tuning problem Greg,   Thanks a bunch for ur detailed reply. I am trying out all the options enumerated by you and I will hopefully get some results. I was doing a mistake with the hint to disable indexes.....I was using the table name instead of the alias in the hint. Could u please tell me the syntax for having multiple tables in the hint ?? Tahat is, if I want the query to run by disabling the indexes in all the joined tables, what would the syntax be ??   Thanks again, Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email :  samir.sarkar@nottingham.sema.slb.com             samir.sarkar@sema.co.uk Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018            -----Original Message-----From: Greg Moore [mailto:sqlgreg@pacbell.net]Sent: 07 November 2001 20:09To: SARKAR, SamirSubject: Re: SQL query tuning problem Samir,   To disable an index, use the hint FULL in the SQL.    select /*+ full(e) */ ename, phone, address from emp e where ename = 'Smith'   This will cause a full table scan on the emp table (instead of using an index).  Note you must give the table an alias in the FROM clause, and you must use that alias in the hint.  Also, the hint must have no errors.  If it contains errors you won't get an error message.  So after putting in a hint, be sure to run an explain plan to see if your hint worked.  For the FULL hint, obviously, your explain plan should show a full table scan and should not show use of an index.   The explain plan you sent is very interesting.  The words "MERGE JOIN (CARTESIAN)" are very interesting.  Normally a Cartesian join is a mistake, because the programmer forgot to include a join between two tables in the WHERE clause.  In your case, however, the tables appear to be all properly joined.   Oracle enhanced the optimizer for data warehouses, where there is usually one big table an many smaller ones.  The smaller tables can't be joined to each other, only to the large table.  However, the most efficient solution is for Oracle to first join all the small tables, and since they can't be linked with primary and foreign keys, Oracle just does a Cartesian join.  This produces lots of results, but not too many because the tables are all small.  Then, for the last step, Oracle finally joins the results from these small tables to the big table.   The idea is to put off dealing with the big table until the end, and then only deal with it once.  This is faster than joining each small table to the big table, one at a time.   Although this was meant for data warehouses, sometimes Oracle will create a plan like this simply because it sees that one table is very big and the others are small.  I think that is what is happening with your SQL and explain plan.   I am sorry to report that even with the proper indentation, I cannot fully understand your explain plan.  It is simply different from what I am used to seeing and I don't understand how a couple of the steps work together.   I can, however, offer some ideas for things to try:   (1)  Use timed_statistics=true and then run a trace and TKPROF.  Then look in the TKPROF report at the very left of the explain plan.  There is a column that says ROWS.  Right now it contains all zero's!  This column has very useful information -- it tells how many rows were processed by each step in the plan.  Often you can use this information to isolate the step where things are going wrong because you see too many rows being processed.  Often this is an indication that an index needs to have a column added, or some other change needs to be made.  At least you see where lots of work is being done, so you can focus your efforts on that step.   (2)  It will be interesting to try your idea of using the FULL hint instead of indexes.  I hope you are able to turn on timed_statistics, if only for your session, so you can see complete TKPROF reports when you try various hints, so you can see all the things that change.   (3)  The major thing that jumps out at me is the last line of the explain plan, where there is a full table scan on valid_pricing_unit.  I'm reasonably sure that this step is the inner part of the nested loops step that appears near the top of the plan.  This means that Oracle is looping through the valid_pricing_unit table again and again, and each time it's a full table scan!  If you had timed_statistics=true then you could see if this is causing trouble, because each full scan means all the rows are processed, so if you are looping through that table thousands of times, the ROWS column would show a very large number.  But even without that information, this seems suspicious, unless the table is very small.  Is there an index on this table?  What columns?  Is it on the columns used in your WHERE clause?  Does it also include any columns from this table in the SELECT list?  If it includes all the columns in the SQL, Oracle can use the index alone and not use the table, and this might be the best solution of all, unless this table is very small.   That's all from me.  Have fun!  
----- Original Message -----
From: SARKAR, Samir To: 'Greg Moore' Cc: 'ORACLE-L@fatcity.com' Sent: Wednesday, November 07, 2001 3:40 AM Subject: RE: SQL query tuning problem Hi Greg,   Thanks for replying.......I have reformatted the Explain Plan statement which is appended below :      Rows    Execution Plan ----------     ----------------------------------------------------------------------------------------------------------------------------------------------     0     INSERT STATEMENT GOAL: CHOOSE                            0            FILTER       0        SORT ( GROUP BY )     0         NESTED LOOPS     0          MERGE JOIN (CARTESIAN)    0         & nbsp;  TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF                'VALID_SEGMENT’    0               INDEX (RANGE SCAN) OF ‘IX_VALID_SEGMENT’ (UNIQUE)     0                 SORT (JOIN)     0                   TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF                         ‘VALID_PRICING_UNIT’     0                       INDEX (RANGE SCAN) OF ‘IX_VALID_PR_UNIT’ (UNIQUE)     0                    INDEX (UNIQUE SCAN) OF ‘IX_VALID_TRANS’ (UNIQUE)     0                              SORT (AGGREGATE)     0                                TABLE ACCESS GOAL: ANALYZED (FULL) OF                                  ‘VALID_PRICING_UNIT’   Also, if I want to disable the indexes on this query, could u please tell me how do I provide a hint here ?? Sorry to ask this but I am not very conversant with Application Query Tuning. Any help would be greatly appreciated. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email :  samir.sarkar@nottingham.sema.slb.com            s amir.sarkar@sema.co.uk Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018            -----Original Message-----From: Greg Moore [mailto:sqlgreg@pacbell.net]Sent: 06 November 2001 20:42To: SARKAR, SamirSubject: SQL query tuning problem Samir,   (1)  The TKPROF report is incomplete because your init.ora parameter TIMED_STATISTICS is not set to true.  This is why your report shows elapsed time = 0, etc.  Issue  alter system set timed_statistics=true This allows you to use TKPROF and see the complete TKPROF report.  If you don't do this the TKPROF reports don't show much useful information, so you might as well just be using explain plan. (2)  Your explain plan is difficult to read because in your email the indentation is messed up.  Can you run an explain plan on this statement and then send it using a font in your email that will preserve the spacing, such as courier font?___________________________________________________________________________This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received thisemail in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited.If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.___________________________________________________________________________**********************************************************************This email and any attachments may be confidential and the subject oflegal professional privilege. Any disclosure, use, storage or copyingof this email without the consent of the sender is strictly prohibited.Please notify the sender immediately if you are not the intendedrecipient and then delete the email from your inbox and do notdisclose the contents to another person, use, copy or store theinformation in any medium.**********************************************************************