Home » RDBMS Server » Performance Tuning » Driving Table is not given more speed result (Oracle 11g)
Driving Table is not given more speed result [message #667820] Thu, 18 January 2018 05:13 Go to next message
venkat_kayal
Messages: 5
Registered: January 2018
Junior Member
Hi Kevin / Team,

I have read kevin's sql performance book on that bases I have change the driving table and join order.
intial sql query was running for 3.10 min but after new join order it is now 1.54 mins( see below query).


select /*+ LEADING */ count(*) from 
fnd_attached_documents ad ,
fnd_document_entities_vl det,
fnd_documents_vl d,
fnd_document_categories_vl dct,
fnd_documents_short_text txt
where  1=1
--and ad.entity_name ='OE_ORDER_LINES' --234423
AND ad.entity_name = det.data_object_code(+)--234423
and d.document_id = ad.document_id(+)--233476
AND dct.category_id = d.category_id--233476
AND d.media_id = txt.media_id--232480


But if I add select clause it is running for 2.34 mins

after the select clause

SELECT /*+ Leading  */  d.document_id,
       ad.seq_num,
       dct.user_name                    category_description,
       d.description                    document_description,
       datatype_name,
       txt.short_text,
       d.usage_type_descr USAGE, --lkp.meaning                      USAGE,
       det.user_entity_name,
       ad.entity_name,
       d.security_type,
       Decode (d.security_type, 1, 'Organization',
                                2, 'Set of Books',
                                3, 'Business Unit',
                                4, 'None',
                                'None') security_type_desc,
       d.security_id,
       ( CASE
           WHEN d.security_type IN ( 1, 3 ) THEN (SELECT organization_name
                                                  FROM
           org_organization_definitions
                                                  WHERE
           organization_id = d.security_id)
           WHEN d.security_type = 2 THEN
           (SELECT short_name
            FROM   gl_sets_of_books
            WHERE  set_of_books_id = d.security_id)
           ELSE NULL
         END )                          owner,
       d.start_date_active,
       d.end_date_active,
       d.creation_date,
       d.created_by,
       det.user_entity_prompt,
       txt.media_id,
       ad.pk1_value,
       ad.pk2_value,
       ad.pk3_value,
       ad.pk4_value,
       ad.pk5_value
FROM  fnd_attached_documents ad ,
fnd_document_entities_vl det,
fnd_documents_vl d,
fnd_document_categories_vl dct,
fnd_documents_short_text txt
where  1=1
--and ad.entity_name ='OE_ORDER_LINES' --234423
AND ad.entity_name = det.data_object_code(+)--234423
and d.document_id = ad.document_id(+)--233476
AND dct.category_id = d.category_id--233476
AND d.media_id = txt.media_id--232480
ORDER  BY d.description,
          d.datatype_id;



and below FRP sheet 

owner 	Table 	                  Table alia	Type 	Rowcount	Filter Queries	Filtered Percentage
	fnd_documents_vl	     d	     VIEW	13941997	13941997	0
	fnd_document_categories_v   dct	     VIEW	      325	325	0
	fnd_attached_documents	   ad	  TABLE	        14013558	234423	100
	fnd_documents_short_text	txt	TABLE	12474367	12474367	0
	fnd_document_entities_vl	det	VIEW	441	441	0



Could please help me to reduce time as the above said query is used in the view and in turn it is called in a procedure and inturn called discoverer query.
on whole it is running for more than 20 mins.

Regards
venkat.

--moderator update: added [code] tags, please do so yourself in future How to use [code] tags and make your code easier to read

[Updated on: Thu, 18 January 2018 07:49] by Moderator

Report message to a moderator

Re: Driving Table is not given more speed result [message #667822 is a reply to message #667820] Thu, 18 January 2018 06:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4114193

Report message to a moderator

Re: Driving Table is not given more speed result [message #667823 is a reply to message #667822] Thu, 18 January 2018 07:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Driving Table is not given more speed result [message #667824 is a reply to message #667820] Thu, 18 January 2018 07:44 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I have read kevin's sql performance book on that bases I have change the driving table and join order.
intial sql query was running for 3.10 min but after new join order it is now 1.54 mins( see below query).


select /*+ LEADING */ count(*) from
fnd_attached_documents ad ,
If that is what you are running, you have not changed the join order because your LEADING hint is incomplete: it doesn't specify any table.

Report message to a moderator

Re: Driving Table is not given more speed result [message #667825 is a reply to message #667820] Thu, 18 January 2018 08:08 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
I think you do not understand the FRP method.
First, you can't use the FRP method on views because Oracle will attempt to merge the views. You need to apply it to the tables.
Second, your FRP calculations make no sense because your query does not have any filtering predicates. It has only joins.
Third, with regard to join order, because you have those outer joins Oracle has very little choice.

You can try fiddling around with the join order, but in this case I think you may find that you need to look at restructuring the query. For example, you are projecting a subquery here
17         ( CASE
 18             WHEN d.security_type IN ( 1, 3 ) THEN (SELECT organization_name
 19                                                    FROM
 20             org_organization_definitions
 21                                                    WHERE
 22             organization_id = d.security_id)
 23             WHEN d.security_type = 2 THEN
 24             (SELECT short_name
 25              FROM   gl_sets_of_books
 26              WHERE  set_of_books_id = d.security_id)
 27             ELSE NULL
 28           END )                          owner,
you could try rewriting this as a join, and also replacing the view org_organization_definitions with a reference to the table(s) on which it is based.

[Updated on: Thu, 18 January 2018 08:09]

Report message to a moderator

Re: Driving Table is not given more speed result [message #667827 is a reply to message #667825] Thu, 18 January 2018 09:27 Go to previous messageGo to next message
venkat_kayal
Messages: 5
Registered: January 2018
Junior Member
Thanks for Replying ,

As you said , apply Frp on views doesn't help then how we can go head to do this tuning.

as said above scalar subquery I have just comment that part and executed to check execution time. it same as 2 .44 mins.


will it help if I go with joining that subquery ???

is there any hints ?

as mention frp , views as wells as table having more rows like millions.

driving table and join order is not going to work ? if it is not having constant predicate then most our real time query are similar without constant predicate..

how to take it proceed further.

as mention it is just part long running discoverer report.

Report message to a moderator

Re: Driving Table is not given more speed result [message #667828 is a reply to message #667827] Thu, 18 January 2018 10:06 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
Have you tried any of the things I suggested? No. You have just posted the same question on OTN, and been given pretty similar advice.

If something I said was not clear to you, then say what it was. I can't guess.

Report message to a moderator

Re: Driving Table is not given more speed result [message #667831 is a reply to message #667828] Fri, 19 January 2018 08:06 Go to previous messageGo to next message
venkat_kayal
Messages: 5
Registered: January 2018
Junior Member
As you suggest I have rewrite the query.
I have taken subquery and preferred the table instead of views 

SELECT /* Leading */ d.document_id
        , ad.seq_num
        , dct.user_name category_description
        , d.description document_description
        , datatype_name
        , txt.short_text
        , d.usage_type_descr USAGE
        ,                            --lkp.meaning                      USAGE,
          det.user_entity_name
        , ad.entity_name
        , d.security_type
        , DECODE (d.security_type
                , 1, 'Organization'
                , 2, 'Set of Books'
                , 3, 'Business Unit'
                , 4, 'None'
                , 'None'
                 ) security_type_desc
        , d.security_id
        , (CASE
              WHEN d.security_type IN (1, 3)
                 THEN hou.NAME
              WHEN d.security_type = 2
                 THEN gsb.NAME
              ELSE NULL
           END
          ) owner
        , d.start_date_active
        , d.end_date_active
        , d.creation_date
        , d.created_by
        , det.user_entity_prompt
        , txt.media_id
        , ad.pk1_value
        , ad.pk2_value
        , ad.pk3_value
        , ad.pk4_value
        , ad.pk5_value
    FROM fnd_attached_documents ad
       , fnd_document_entities_vl det
       , fnd_documents_vl d
       , fnd_document_categories_vl dct
       , fnd_documents_short_text txt
       , mtl_parameters mp
       , hr_organization_units hou
       , gl_ledgers  gsb
   WHERE 1 = 1
--and ad.entity_name ='OE_ORDER_LINES' --234423
     AND ad.entity_name = det.data_object_code(+)                     --234423
     AND d.document_id = ad.document_id(+)                            --233476
     AND dct.category_id = d.category_id                              --233476
     AND d.media_id = txt.media_id                                    --232480
     AND (    (    d.security_type IN (1, 3)
               AND mp.organization_id = hou.organization_id
               AND mp.organization_id = d.security_id
              )
          OR (    d.security_type = 2
              AND gsb.ledger_id = d.security_id
              and gsb.object_type_code = 'L'
          AND NVL (gsb.complete_flag, 'Y') = 'Y')
         )
ORDER BY d.description, d.datatype_id
-- it has some problem , it is running more time

is there any problem in query ?


regard
venkat


*BlackSwan added {code} tags & you should do so always in the future
How to use {code} tags and make your code easier to read

[Updated on: Fri, 19 January 2018 08:16] by Moderator

Report message to a moderator

Re: Driving Table is not given more speed result [message #667832 is a reply to message #667831] Fri, 19 January 2018 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
It doesn't seem to correspond to the original query.

Original CASE gets org_organization_definitions.organization_name and gl_sets_of_books.short_name
New CASE gets hr_organization_units.name and gl_ledgers.name

I also wouldn't do an OR for that in the where clause.
Just join to both all the time, if there doesn't have to be a corresponding row then use an outer join rather than OR

Report message to a moderator

Re: Driving Table is not given more speed result [message #667833 is a reply to message #667831] Fri, 19 January 2018 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>SELECT /* Leading */ d.document_id
"Leading" is worthless comment.

Consider to Read The Fine Manual on how to properly use HINT.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E

Report message to a moderator

Re: Driving Table is not given more speed result [message #667835 is a reply to message #667831] Fri, 19 January 2018 09:44 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
As for this,Quote:
preferred the table instead of views
the query is still full of views. For example, FND_DOCUMENTS_VL is a view that joins six tables:
....FROM FND_DOCUMENT_CATEGORIES_TL CL
, FND_DOCUMENT_CATEGORIES C
, FND_DOCUMENT_DATATYPES DD
, FND_LOOKUP_VALUES L
, FND_DOCUMENTS_TL DL
, FND_DOCUMENTS D 
WHERE D.DOCUMENT_ID = DL.DOCUMENT_ID
AND DL.LANGUAGE= USERENV('LANG')
AND D.DATATYPE_ID = DD.DATATYPE_ID
AND DD.LANGUAGE = USERENV('LANG')
AND D.CATEGORY_ID = C.CATEGORY_ID
AND C.CATEGORY_ID = CL.CATEGORY_ID
AND CL.LANGUAGE = USERENV('LANG')
AND D.USAGE_TYPE = L.LOOKUP_CODE
AND L.LANGUAGE = USERENV('LANG')
AND L.LOOKUP_TYPE = 'ATCHMT_DOCUMENT_TYPE'
Do you really need to do that? Why can you not simplify the query by hitting just the table(s) that you want?

Report message to a moderator

Re: Driving Table is not given more speed result [message #667836 is a reply to message #667835] Fri, 19 January 2018 09:58 Go to previous messageGo to next message
venkat_kayal
Messages: 5
Registered: January 2018
Junior Member
for your reference attached explain plan

Report message to a moderator

Re: Driving Table is not given more speed result [message #667837 is a reply to message #667836] Fri, 19 January 2018 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
Senior Member
Put the explain in the post in code tags - read the link on code tags that BS already posted to see how.

Report message to a moderator

Re: Driving Table is not given more speed result [message #667838 is a reply to message #667837] Fri, 19 January 2018 10:13 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
You have missed out severalr join predicates. So you are getting cartesian products that produce over 3 billion rows. Better rewrite the query to use ANSI join syntax, then you won't make that mistake again.

Report message to a moderator

Re: Driving Table is not given more speed result [message #667873 is a reply to message #667820] Tue, 23 January 2018 03:20 Go to previous messageGo to next message
danmaowu
Messages: 1
Registered: January 2018
Junior Member
 
select /*+ Leading  */
 d.document_id,
 ad.seq_num,
 dct.user_name category_description,
 d.description document_description,
 datatype_name,
 txt.short_text,
 d.usage_type_descr usage, --lkp.meaning                      USAGE,
 det.user_entity_name,
 ad.entity_name,
 d.security_type,
 decode(d.security_type,
        1,
        'Organization',
        2,
        'Set of Books',
        3,
        'Business Unit',
        4,
        'None',
        'None') security_type_desc,
 d.security_id,
 (case
   when d.security_type in (1, 3) then
      ood.organization_name
    /*(select organization_name
       from org_organization_definitions
      where organization_id = d.security_id)*/
   when d.security_type = 2 then
    gsob.short_name
    /*(select short_name
       from gl_sets_of_books
      where set_of_books_id = d.security_id)*/
   else
    null
 end) owner,
 d.start_date_active,
 d.end_date_active,
 d.creation_date,
 d.created_by,
 det.user_entity_prompt,
 txt.media_id,
 ad.pk1_value,
 ad.pk2_value,
 ad.pk3_value,
 ad.pk4_value,
 ad.pk5_value
  from fnd_attached_documents     ad,
       fnd_document_entities_vl   det,
       fnd_documents_vl           d,
       fnd_document_categories_vl dct,
       fnd_documents_short_text   txt,

       org_organization_definitions ood,
      /* (select organization_name
       from org_organization_definitions
      where organization_id = d.security_id)*/

      gl_sets_of_books gsob
      /*(select short_name
       from gl_sets_of_books
      where set_of_books_id = d.security_id)*/

 where 1 = 1
      --and ad.entity_name ='OE_ORDER_LINES' --234423
   and ad.entity_name = det.data_object_code(+) --234423
   and d.document_id = ad.document_id(+) --233476
   and dct.category_id = d.category_id --233476
   and d.media_id = txt.media_id --232480

   -- change to left join 
   and d.security_id = ood.organization_id(+)
   and d.security_id = gsob.set_of_books_id(+)

 order by d.description, d.datatype_id;

try this

Report message to a moderator

Re: Driving Table is not given more speed result [message #667940 is a reply to message #667873] Thu, 25 January 2018 03:46 Go to previous message
venkat_kayal
Messages: 5
Registered: January 2018
Junior Member
Hi All,

Thanks for your replys,


I have got solution to get it running less time now .


View was used in fucntion and it is using in main quey of discoverer report.

View has been taking lot of time so I was looking in to views sql stmt . and try to tuning it.

small change in select stmt of view made execution time very less.

SELECT   doc.seq_num
             , doc.category_description
             , doc.document_description
             , doc.short_text
          FROM apps.custom_attached_docs_v doc
         WHERE doc.pk1_value = :B2
           AND doc.entity_name =
                  DECODE (:B1
                        , 'H', 'OE_ORDER_HEADERS'
                        , 'L', 'OE_ORDER_LINES'
                        , NULL
                         )
      ORDER BY doc.seq_num;



so what happen here pk1 value is varchar and value passed to it number. so implicit coversion has been problem. change the code now it has been fine less than 3 mins.

Thanks all

Report message to a moderator

Previous Topic: Non-value Added Indexes
Next Topic: Is there a equivalent of delete nologging operations
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 23 15:14:29 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.