New to SQL, performance issues with query. 1

[AZN2SQL]

Hello all,

I'm new to the forum and had some questions about a query I'm trying to speed up.

Here's the environment, Oracle db (version 9) and I'm querying 4 tables. The tables and sizes are:

Table row count
QUESTION 193,336
survey_custom 3,600,279
survey_results 136,993,573
user_survey 4,208,759

The query is:

SELECT   
  trim(QUESTION.QUES_VER_CONTENT),
  SURVEY_RESULTS.ANSWERTEXT,
  SURVEY_CUSTOM.LOCATION,
  SURVEY_CUSTOM.COURSESTARTDATE,
  SURVEY_CUSTOM.InstructorID,
  SURVEY_CUSTOM.STUDENTID,
  SURVEY_CUSTOM.LOCATIONID,
  SURVEY_CUSTOM.COURNUM
FROM
  QUESTION,
  SURVEY_RESULTS,
  SURVEY_CUSTOM,
  USER_SURVEY
WHERE
  ( USER_SURVEY.PK_USERSURVEYID=SURVEY_RESULTS.FK_USERSURVEYID  )
  AND  ( SURVEY_RESULTS.FK_QUESTIONVERSIONID=QUESTION.PK_QUESTIONVERSIONID  )
  AND  ( USER_SURVEY.PK_USERSURVEYID=SURVEY_CUSTOM.USERSURVEYID  )
  AND  ( USER_SURVEY.SURVEYNAME)  IN  ('Survey_1', 'Survey_2','Survey_3')
  AND  trim(QUESTION.QUES_VER_CONTENT)  IN
 ('Would you like to be contacted?', 'Are you happy with the service?')
AND asap_user_survey.completedate > trunc(ADD_MONTHS(LAST_DAY(SYSDATE), -15))

All of the fields that are joined are indexed. The IN qualifier on the surveyname field is also indexed. The only field that's not indexed in the conditions is the ques_ver_content IN portion.

I did an explain plan on the query and it shows that my cost is: 147,608.

Most of the cost is coming from a nested loop with survey_results and the question_PK_IDX.

I have tried some sub queries to see if I can speed up the query, but the cost increased on every attempt I made.

Any assistance would be great!

Thank you!

 

[slaing]

Hi,

A few things spring to mind -

1) Have you analyzed these table so that the Oracle optimizer has got some statistics to work with ?

The following statement will do that for a table

SQL>ANALYZE TABLE table_name COMPUTE STATISTICS;

2) Modifying a column in the WHERE clause will stop Oracle using an index for that column. You're using the TRIM() function on QUESTION.QUES_VER_CONTENT.

Would it be possible to perform the TRIM on QUES_VER_CONTENT when it's stored in the 1st place (eg via an insert/update trigger) ?

3) Are you using all the preceding columns in the indexes from the top down in your WHERE clause ? eg if your index is on columns A,B,C there's no point in just having B=value AND C=value in the WHERE clause


By all means post the output from your explain plan if you're still having problems.


Steve

 

[carp]

Reference Steve's Item 2: You stated that this column is not indexed. Is it possible for you to build an index on it? If so, and you cannot TRIM the data itself, you might consider building a function-based index on QUESTION.QUES_VER_CONTENT.
Another thing that would help us help you would be if you could post the execution plan - that way we can actually look for possible bottlenecks instead of speculating on what "might" help.

 

[slaing]

Ha! Thanks, carp - I should have read the text more carefully rather than just staring at the code !

As QUESTION.QUES_VER_CONTENT isn't indexed then my 2nd point isn't really significant.

Also, in (1) I should have said 'up to date statistics'

Steve

 

[AZN2SQL]

First off, thanks so much for the feed back!!!

Ok I'm not the dba, but a user of the data (read only access only).

I did check with the dba and the tables are monitored and stats are ran regularly, as needed.

I personally don't have the ablility to run the trim/update on the field QUESTION.QUES_VER_CONTENT or to index it, but I will request it.

The execution plan is below.. though it's probably not the prettiest in this format.

Thanks again!!

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=147601	Cardinality=11153	Bytes=2899780	Optimizer=ALL_ROWS
 HASH JOIN			Cost=147601	Cardinality=11153	Bytes=2899780	
  HASH JOIN			Cost=140637	Cardinality=11153	Bytes=2353283	
   INLIST ITERATOR						
    TABLE ACCESS BY INDEX ROWID	Object owner=ASAP	Object name=USER_SURVEY	Cost=12	Cardinality=2595	Bytes=132345	Optimizer=ANALYZED
     INDEX RANGE SCAN	Object owner=ASAP	Object name=USER_SURVEY_NAME_IDX	Cost=2	Cardinality=1		
   TABLE ACCESS BY INDEX ROWID	Object owner=ASAP	Object name=QUESTION		Cardinality=1	Bytes=96	Optimizer=ANALYZED
    NESTED LOOPS			Cost=139367	Cardinality=7630970	Bytes=1220955200	
     TABLE ACCESS FULL	Object owner=ASAP	Object name=SURVEY_RESULTS	Cost=139367	Cardinality=135867000	Bytes=8695488000	Optimizer=ANALYZED
     INDEX RANGE SCAN	Object owner=ASAP	Object name=QUEESTION_PK_IDX		Cardinality=1		Optimizer=ANALYZED
  TABLE ACCESS FULL	Object owner=ASAP	Object name=SURVEY_CUSTOM	Cost=4295	Cardinality=3530254	Bytes=172982446	Optimizer=ANALYZED

 

[slaing]

TABLE_ACCESS_FULL means that it's reading all rows in a table rather than using an index.

What are the indexes on SURVEY_RESULTS and SURVEY_CUSTOM ?

Can your DBA confirm that all indexex on those tables are valid ?

 

[blom0344]

Instead of using the text column QUES_VER_CONTENT look up the primary key values for the 2 expressions in the QUESTION table and change the condition in the where clause to:
(123 and 321 being the PK values for example)

( USER_SURVEY.PK_USERSURVEYID=SURVEY_RESULTS.FK_USERSURVEYID  )
  AND  ( SURVEY_RESULTS.FK_QUESTIONVERSIONID=QUESTION.PK_QUESTIONVERSIONID  )
  AND  ( USER_SURVEY.PK_USERSURVEYID=SURVEY_CUSTOM.USERSURVEYID  )
  AND  ( USER_SURVEY.SURVEYNAME)  IN  ('Survey_1', 'Survey_2','Survey_3')
  AND  [COLOR=red](QUESTION.QUESTIONVERSIONID = 123 OR
QUESTION.QUESTIONVERSIONID = 321)[/COLOR]
AND asap_user_survey.completedate > trunc(ADD_MONTHS(LAST_DAY(SYSDATE), -15))

This may make a world of difference..

Ties Blom

 

[AZN2SQL]

Ties,

That worked out.. I did a query and pulled out the PK_QUESTIONVERSIONID ID's for the QUESTION table (lucky for me their were only 18 of them). I then replaced the trim(QUESTION.QUES_VER_CONTENT) IN ('Would you like to be contacted?', 'Are you happy with the service?') with an IN clause that looks at the PK_QUESTIONVERSIONID field and then I listed the 18 primary key's..

This dropped my cost from 147,601 down to 78,115 and the query ran in 14 minutes.

Thanks to everyone for their inputs. As I stated in my first post, I'm new to Oracle and very experienced with SQL but I am learning. I'll continue to lurk here in the forum so I can learn more.

Take care all!!

 

[jimirvine]

Hi, I appreciate that you appear to have solve your issue, I just thought I'd throw my hat into the ring.

This dropped my cost from 147,601 down to 78,115 and the query ran in 14 minutes.

forget about the value of cost. This has absolutely nothing to do with the comparison of one query against another. It is an internal value calculated based on one execution plan against another for the same SQL statement and does not correlate between different statements. You need to look at LIO and PIO to evaluate the efficiency of queries if you are simply looking at autotrace. Read the P&T manual at

http://tahiti.oracle.com

for more.

TABLE_ACCESS_FULL means that it's reading all rows in a table rather than using an index.

which means that it is able to take advantage of the db_file_multiblock_read_count parameter. FTS is not necessarily a bad thing.

Are you using all the preceding columns in the indexes from the top down in your WHERE clause ? eg if your index is on columns A,B,C there's no point in just having B=value AND C=value in the WHERE clause

Not true. Check out Index Skip Scan (9i and after)

Your hard coded values. do they ever change? if so, are you using bind variables (and missing that out for the sake of simplicity) or are you hard coding the operands on each run? (bad idea)
The only sub query (that I can see) that would be useful would be to remove the requirement for the reference to the 'USER_SURVEY' table, since it is not actualy referenced in the SELECT clause of your query.

Obviously, since you appear to have achieved your goal of improving the effiency of your quey, feel free to ignore my comments.

 

[slaing]

Jim-

Thanks for pointing out the Skip Scan feature - not one I'd come across (our legacy apps require us to set the optimizer back to 8.1.7 so I guess that's why I don't see them in execution plans).

I agree that FTS isn't necessarily a bad thing, but in this case they're happening on fairly chunky tables

Steve

 

[AZN2SQL]

Jim,

Thank you for the feed back. And although the query is running faster and meeting my needs for now, I am interested in learning more about sql tuning.

I looked on

http://tahiti.oracle.com

for the P&T Manual but didn't find it in a search, or by just browsing around.

As for my hard coding the values in, I realized what the side effects could be of doing that when I did it. As I understand it, these questions are static at this point (until they introduce new ones). I would have liked to have left the query the way it was, so anytime a new question is added (that is the same as the others), but has a different PK_ID, it would be picked up... but I doubt there will be any.

Also, thank you for the info on the index skip scan. All of the dba's here at my office and others I spoke with told me if there were 2 or 3 fields in an indexed for a table, at least two have to be used (and always the first one). I read up on the index skip scan here:

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/schumacher_skipscan.html

Very good info to know!! thanks again.
-Kyle