Home » RDBMS Server » Performance Tuning » Performance tuning SQL
Performance tuning SQL [message #190755] Fri, 01 September 2006 02:26 Go to next message
speaker
Messages: 30
Registered: April 2006
Member
SELECT *
FROM
(SELECT DISTINCT NULL AS CTRACK_INSTANCE_COL_ID, NULL AS
CTRACK_TITLE_NARRATION, NULL AS CTRACK_PERFORMER_LIST, NULL AS
CTRACK_IS_DEFAULT_LID, MI.LID AS CTRACK_TITLE_LANGUAGE, NULL AS
CTRACK_PRT_NARRATION, NULL AS CTRACK_IS_PUBLISHED, NULL AS
CTRACK_CREATED_DATE, NULL AS CTRACK_INSTANCE_ID, NULL AS CTRACK_GENRES_LIST,
NULL AS CTRACK_SHORT_TITLE, NULL AS CTRACK_OWNER_NAME, NULL AS
CTRACK_PRICE_DESC, NULL AS CTRACK_FILESTATUS, NULL AS CTRACK_DTMF_TITLE,
NULL AS CTRACK_IS_SAMPLE, NULL AS CTRACK_ORIGIN_ID, NULL AS CTRACK_POSITION,
NULL AS CTRACK_PRT_LIST, NULL AS CTRACK_IS_AVAIL, NULL AS CTRACK_FILESIZE,
NULL AS CTRACK_FILENAME, NULL AS CTRACK_OWNER_ID, NULL AS CTRACK_ENCODING,
NULL AS CTRACK_URLPATH, NULL AS CTRACK_UNCPATH, NULL AS CTRACK_LENGTH, NULL
AS CTRACK_EXT_ID, NULL AS CTRACK_PRICE, NULL AS CTRACK_BEGIN, MI.TITLE AS
CTRACK_TITLE, NULL AS CTRACK_ML_ID, NULL AS CTRACK_DESC, NULL AS
CTRACK_TYPE, NULL AS CTRACK_RANK, NULL AS CTRACK_END, MI.ID AS CTRACK_ID,
NULL AS FE_ID FROM INST, MI_FULL_PLAIN MI, PDL, PGROUP WHERE MI.ID=
INST.MI_ID AND INST.ID = PDL.INST_ID AND PDL.PGROUP_ID=PGROUP.ID AND
PDL.IS_READY= 'Y' AND PDL.FEGLIST_ID = 7111 AND INST.ENCTYPE IN (61) AND
PDL.IS_PUBLISHED = 'Y' and upper(MI.TITLE ) LIKE upper('%EVERYBODY%') and
MI.TYPE <> '9' ORDER BY upper(MI.TITLE)) WHERE
ROWNUM <= 10

The explain plan is

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 489 | 15 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | 489 | 15 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 57 | 15 |
| 4 | SORT UNIQUE | | 1 | 57 | 10 |
|* 5 | TABLE ACCESS BY INDEX ROWID | MI_FULL_PLAIN | 1 | 25 | 2 |
| 6 | NESTED LOOPS | | 1 | 57 | 5 |
| 7 | NESTED LOOPS | | 1 | 32 | 3 |
| 8 | NESTED LOOPS | | 1 | 20 | 2 |

|* 9 | TABLE ACCESS BY INDEX ROWID| PDL | 1 | 15 | 2 |
|* 10 | INDEX RANGE SCAN | IX_PDL_FEGLIST_ID | 1 | | 1 |
|* 11 | INDEX UNIQUE SCAN | PK_PGROUP_ID | 1 | 5 | |
|* 12 | TABLE ACCESS BY INDEX ROWID | INST | 1 | 12 | 1 |
|* 13 | INDEX UNIQUE SCAN | PK_INST_ID | 7 | | |
|* 14 | INDEX RANGE SCAN | IX_MI_FP_ID | 2 | | 1 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)

5 - filter(UPPER("MI"."TITLE") LIKE '%EVERYBODY%' AND "MI"."TYPE"<>9)
9 - filter("PDL"."IS_READY"='Y' AND "PDL"."IS_PUBLISHED"='Y')
10 - access("PDL"."FEGLIST_ID"=7111)
11 - access("PDL"."PGROUP_ID"="PGROUP"."ID")
12 - filter("INST"."ENCTYPE"=61)
13 - access("INST"."ID"="PDL"."INST_ID")
14 - access("MI"."ID"="INST"."MI_ID")



Can you help to tune the query ....
i have loaded all the tables in keep pool but no improvement in performance



Re: Performance tuning SQL [message #190761 is a reply to message #190755] Fri, 01 September 2006 02:48 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Keep pool should not do anything really good, at least not at this stage.

The execution plan does not look ugly - ie. no full table/index scans, although they can be beneficial too.

Have You gathered statistics?

I *think* You need to reduce the amount of data Your query works on, but it is a little hard, not having You tables etc. to look at.

Maybe You could use the WITH operator to extract a small set of data, and then join to those?

Br
Kim
Re: Performance tuning SQL [message #190794 is a reply to message #190755] Fri, 01 September 2006 05:02 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

but no improvement in performance


Well maybe the query is the most performant that it can be. You have supplied no metrics. For all we know, you are running this query on several million rows and getting reults back in a matter of seconds.
I also don't think that you have provided the whole 'story'

SELECT *
FROM   (SELECT   DISTINCT NULL AS cTrack_Instance_col_Id,
                          NULL AS cTrack_Title_Narration,
                          NULL AS cTrack_Performer_List,
                          NULL AS cTrack_Is_Default_Lid,
                          mi.Lid AS cTrack_Title_Language,
                          NULL AS cTrack_prt_Narration,
                          NULL AS cTrack_Is_Published,
                          NULL AS cTrack_Created_Date,
                          NULL AS cTrack_Instance_Id,
                          NULL AS cTrack_Genres_List,
                          NULL AS cTrack_Short_Title,
                          NULL AS cTrack_Owner_Name,
                          NULL AS cTrack_Price_desc,
                          NULL AS cTrack_FileStatus,
                          NULL AS cTrack_dtmf_Title,
                          NULL AS cTrack_Is_Sample,
                          NULL AS cTrack_Origin_Id,
                          NULL AS cTrack_Position,
                          NULL AS cTrack_prt_List,
                          NULL AS cTrack_Is_Avail,
                          NULL AS cTrack_FileSize,
                          NULL AS cTrack_FileName,
                          NULL AS cTrack_Owner_Id,
                          NULL AS cTrack_enCodIng,
                          NULL AS cTrack_urlPath,
                          NULL AS cTrack_uncPath,
                          NULL AS cTrack_Length,
                          NULL AS cTrack_ext_Id,
                          NULL AS cTrack_Price,
                          NULL AS cTrack_Begin,
                          mi.Title AS cTrack_Title,
                          NULL AS cTrack_ml_Id,
                          NULL AS cTrack_desc,
                          NULL AS cTrack_Type,
                          NULL AS cTrack_Rank,
                          NULL AS cTrack_End,
                          mi.Id AS cTrack_Id,
                          NULL AS fe_Id
        FROM     Inst,
                 mi_Full_Plain mi,
                 pdl,
                 pGroup
        WHERE    mi.Id = Inst.mi_Id
                 AND Inst.Id = pdl.Inst_Id
                 AND pdl.pGroup_Id = pGroup.Id
                 AND pdl.Is_Ready = 'Y'
                 AND pdl.fegList_Id = 7111
                 AND Inst.encType IN (61)
                 AND pdl.Is_Published = 'Y'
                 AND Upper(mi.Title) LIKE Upper('%EVERYBODY%')
                 AND mi.TYPE <> '9'
        ORDER BY Upper(mi.Title))
WHERE  ROWNUM <= 10


is this by any chance ACTUALLY part of a combined query (union, intersect, minus etc)

Jim

Jim
Re: Performance tuning SQL [message #190889 is a reply to message #190755] Fri, 01 September 2006 20:57 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>FROM Inst, mi_Full_Plain mi,pdl, pGroup
Why in the WORLD are there FOUR tables in the FROM clause
when only the mi table is returning data?????????????
Remove the other three tables & subordinate them into the WHERE clause!
I can assure you that performance will improve dramatically if you do so!!!!!!
Previous Topic: Cursor time out error(open cursor on bottleneck)
Next Topic: Check if the Index is performing good or not
Goto Forum:
  


Current Time: Sat Nov 23 13:44:34 CST 2024