OraFAQ Forum: Performance Tuning » Tuning of Select for a View in Report

Home » RDBMS Server » Performance Tuning » Tuning of Select for a View in Report

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning of Select for a View in Report [message #151031]

Mon, 12 December 2005 13:19

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

This is the select statement used for creating the view, furthur this view is used to run a report, Report is taking around 10 minutes, i am putting the plan for the select statement of view and the select statement of report.

Somebody suggested for creating of materialized view instead of
normal view , i don't know how to create materialized view for this select, Another thing can we create materialized view for this type of select with UNION and CONNECT_BY clauses.

Please Give some approach where i should move whether materialized view or how to tune this select.

The plan output, the create view statement and the select statement in report is attached.

Thanks for your Adivces.

Attachment: VIEW_SCRIP_PLAN_SELECT REPORT.txt
(Size: 13.45KB, Downloaded 1691 times)

[Updated on: Mon, 12 December 2005 13:20]

Report message to a moderator

Re: Tuning of Select for a View in Report [message #151036 is a reply to message #151031]

Mon, 12 December 2005 14:16

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

This isn't a complete answer, but a couple things pop into mind first:

1. The point of a materialized view is simply to go ahead and have run the query (view) ahead of time, so that when it comes time to use it in a report, you don't have to go run it then, you simply pull the already generated data data directly and use that to join with the rest of the tables in running your report. You may want to read more about them in the data warehousing guide.

2. I see lots of first rows hints. Not sure why. If you are looking to complete the entire report quickly, then you would want all_rows, not first_rows. Especially since most of your first_rows hints are nested, they are possibly leading to more nested loops rather than hash joins which tend to be slow.

3. You are doing a FTS of the same table many times. Try to avoid that, either by using the with clause or inline views or getting rid of the first rows hints and nested loops.

4. You have individual queries that all use distinct. Do they need to use distinct? Or is the data already distinct? You also use union, instead of union all, which is another distinct like operation being performed at the end. Eliminate these sorts if possible.

Report message to a moderator

Re: Tuning of Select for a View in Report [message #151038 is a reply to message #151036]

Mon, 12 December 2005 14:45

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi Thanks Smartin for Response.

Out of your 4 suggestions 1 is impelemented and here is the output after it.
In the Create view statement there are 3 selects out of which 2 are very fine when i run individual the 2 select result comes in 0.04 sec but this third one result is coming in 28 seconds, Can you put a higlight on this why it is like this,and i removed distinct from those 2 select also result is same but from this select there is a difference of 1 record.

Regarding Hints First Rows i don't know why reporting people added and left now it is for Oracle people to tune now.

Regarding materialized view what you experts suggest will it be helpful from performance point of view, and Any help how can i create a materialized view from the existing create view statements what i have.

3rd select statement from Create View and plan for it here ;-

select distinctRLS.USER_ID,
BUT.BUS_UT_ID,
BUT.BUS_UT_NM,
BUT.BUS_UT_KY,
BUT.FEED_TYPE_ID
from INT_USER_ROLES_BUS_UT_DET RLS,
BUSINESS_UNIT BUT
where RLS.USER_ROLE_ID = 'PM'
AND RLS.CURRENT_REC_IN = 'Y'
AND RLS.STATUS_IN = 'A'
-- AND RLS.USER_ID = 'alstimpson'
AND BUT.BUS_UT_ID in( SELECT business_unit_id buid
FROM INT_BUSINESS_UT_HIERARCHY
WHERE ROWID IN( SELECT MAX(ROWID)
FROM INT_BUSINESS_UT_HIERARCHY
GROUP BY LEVEL,
business_unit_id,
parent_business_unit_id,
current_rec_in )
CONNECT BY PRIOR business_unit_id = parent_business_unit_id
START WITH business_unit_id = RLS.BUSINESS_UNIT_ID )

------------- PLAN

SELECT STATEMENT, GOAL = CHOOSE Cost=210384 Cardinality=1654 Bytes=82700
SORT UNIQUE Cost=326 Cardinality=1654 Bytes=82700
FILTER
NESTED LOOPS Cost=307 Cardinality=1654 Bytes=82700
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_USER_ROLES_BUS_UT_DET Cost=3 Cardinality=152 Bytes=2736
TABLE ACCESS FULL Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=2 Cardinality=11 Bytes=352
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_BUS_UT_HIERARCHY_PK Cost=1 Cardinality=1 Bytes=6
TABLE ACCESS BY USER ROWID Object owner=PM_DBA Object name=INT_BUSINESS_UT_HIERARCHY
NESTED LOOPS
BUFFER SORT Cardinality=11 Bytes=187
CONNECT BY PUMP
INDEX FULL SCAN Object owner=PM_DBA Object name=INT_BUS_UT_HIERARCHY_PK Cost=127 Cardinality=11 Bytes=187
FILTER
SORT GROUP BY Cost=8 Cardinality=3 Bytes=54
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_BUSINESS_UT_HIERARCHY Cost=2 Cardinality=212 Bytes=3816

Thanks a Lot.

[Updated on: Mon, 12 December 2005 16:22]

Report message to a moderator

Re: Tuning of Select for a View in Report [message #151178 is a reply to message #151038]

Tue, 13 December 2005 10:26

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

Smartin i implemeted another suggestion from your 4 points, regarding that FIRST ROWS hints in report select statement, i confirmed with reporting team they wanted first rows only and for performance only they issued this , i removed that and saw no improvement, what i have noticed is sometimes this report takes 4-5 minutes and some times 8-10 minutes, Why changes any clues what could be the reason.
statistics are upto date.

One more question as you avoid FTS i have seen those table are having around 700 records only, how can we force to avoid FTS becuase i learned from you people only sometimes FTS are better than index scan on small tables, Do you still insist on avoiding FTS in this scenaria..

Expecting some advices.

Thanks

[Updated on: Tue, 13 December 2005 10:27]

Report message to a moderator

Re: Tuning of Select for a View in Report [message #151185 is a reply to message #151031]

Tue, 13 December 2005 10:53

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I have never insisted on avoiding FTS, especially on a 700 row table...although I would like to avoid multiple FTS of the same table in the same query.

Have you read the sticky?

About first rows hint, when they say they are only interested in the first few rows, how many are they interested in? They would need to put some kind of limiting criteria, such as rownum <= 10, to only get the first 10 rows for example. If that is truly the case, then it would help oracle to know that and the CBO would probably be able to optimize for it.

And even if you saw no improvement, there shouldn't be a hint there unless there IS an improvement. If it doesn't add meaning or clarification or performance then it is just clutter.

The different runtimes might be due to different query values being used as criteria, leading to different explain plans.

Report message to a moderator

Re: Tuning of Select for a View in Report [message #151196 is a reply to message #151185]

Tue, 13 December 2005 12:43

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

Thanks Smartin and You all ,

I agree if HINTS are not useful we should remove it,
I got plan table with a difference of 1 sec with and without HINTS
Please tell me what to conclude from these difference in output of plan, there were no difference in data during this time.
After having a look at my plan what do you suggest should HINTS BE THERE or not.

Regarding FTS how to avoid query doing FTS any ideas,

1 General question what to read and what to conclude from the output of a plan.

PLAN WITH FIRST ROWS HINT

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS			

Cost=172	Cardinality=2	Bytes=464
 SORT ORDER BY			Cost=172	Cardinality=2	

Bytes=464
  WINDOW SORT			Cost=172	Cardinality=2	

Bytes=464
   CONCATENATION					
    MERGE JOIN			Cost=80	Cardinality=1	Bytes=232
     SORT JOIN			Cost=23	Cardinality=1	Bytes=204
      NESTED LOOPS			Cost=17	Cardinality=1	

Bytes=204
       NESTED LOOPS			Cost=16	Cardinality=1	

Bytes=168
        NESTED LOOPS			Cost=15	Cardinality=1	

Bytes=153
         NESTED LOOPS			Cost=14	Cardinality=1	

Bytes=127
          NESTED LOOPS			Cost=13	Cardinality=1	

Bytes=117
           NESTED LOOPS			Cost=12	Cardinality=1	

Bytes=108
            NESTED LOOPS			Cost=4	

Cardinality=1	Bytes=68
             NESTED LOOPS			Cost=3	

Cardinality=1	Bytes=58
              NESTED LOOPS			Cost=2	

Cardinality=1	Bytes=31
               NESTED LOOPS			Cost=17	

Cardinality=1	Bytes=201
                NESTED LOOPS			Cost=1	

Cardinality=1	Bytes=6
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=COUNTRY_PK		Cardinality=1	Bytes=3
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=REGION_PK		Cardinality=1	Bytes=3
                TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=INDUSTRY_HIERARCHY_PK		Cardinality=10	
               TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=ACCOUNT	Cost=1	Cardinality=1	Bytes=25
                INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=ACCOUNT_PK		Cardinality=6255	
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
               INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=BUSINESS_UNIT_PK		Cardinality=218	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	

Bytes=10
              INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=MONTHLY_COMBINED_RETURNS	Cost=8	Cardinality=1	

Bytes=40
             INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=MCR_LROR_IE01	Cost=7	Cardinality=1	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
            INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
          TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=CODE_TYPE_PK		Cardinality=105	
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	

Bytes=26
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=390	
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=CALENDAR_MONTH	Cost=1	Cardinality=1	Bytes=15
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=CALENDAR_MONTH_PK		Cardinality=1	
       INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=CALENDAR_MONTH_PK		Cardinality=1	Bytes=3
     SORT JOIN			Cost=57	Cardinality=871	Bytes=24388
      VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	

Cost=2206	Cardinality=871	Bytes=24388
       SORT UNIQUE			Cost=2206	

Cardinality=871	Bytes=46958
        UNION-ALL					
         MERGE JOIN CARTESIAN			Cost=10	

Cardinality=852	Bytes=46008
          NESTED LOOPS			Cost=2	Cardinality=4	

Bytes=88
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=44	Bytes=572
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
          BUFFER SORT			Cost=10	Cardinality=216	

Bytes=6912
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=BUSINESS_UNIT	Cost=2	Cardinality=216	Bytes=6912
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
          NESTED LOOPS			Cost=5	Cardinality=2	

Bytes=100
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
           INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=BUSINESS_UNIT_IE01		Cardinality=1	
         FILTER					
          NESTED LOOPS			Cost=7	Cardinality=17	

Bytes=850
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
          FILTER					
           CONNECT BY WITH FILTERING					
            NESTED LOOPS					
             INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
             TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	

Object name=INT_BUSINESS_UT_HIERARCHY			
            NESTED LOOPS					
             BUFFER SORT				

Cardinality=11	Bytes=187
              CONNECT BY PUMP					
             INDEX FULL SCAN	Object owner=PM_DBA	Object 

name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	

Bytes=187
           FILTER					
            SORT GROUP BY			Cost=8	

Cardinality=3	Bytes=54
             TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=212	Bytes=3816
    MERGE JOIN			Cost=80	Cardinality=1	Bytes=232
     SORT JOIN			Cost=23	Cardinality=1	Bytes=204
      NESTED LOOPS			Cost=17	Cardinality=1	

Bytes=204
       NESTED LOOPS			Cost=17	Cardinality=1	

Bytes=201
        NESTED LOOPS			Cost=16	Cardinality=1	

Bytes=168
         NESTED LOOPS			Cost=15	Cardinality=1	

Bytes=153
          NESTED LOOPS			Cost=14	Cardinality=1	

Bytes=127
           NESTED LOOPS			Cost=13	Cardinality=1	

Bytes=117
            NESTED LOOPS			Cost=12	

Cardinality=1	Bytes=108
             NESTED LOOPS			Cost=4	

Cardinality=1	Bytes=68
              NESTED LOOPS			Cost=3	

Cardinality=1	Bytes=58
               NESTED LOOPS			Cost=2	

Cardinality=1	Bytes=31
                NESTED LOOPS			Cost=1	

Cardinality=1	Bytes=6
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=COUNTRY_PK		Cardinality=1	Bytes=3
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=REGION_PK		Cardinality=1	Bytes=3
                TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=ACCOUNT	Cost=1	Cardinality=1	Bytes=25
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=ACCOUNT_PK		Cardinality=6255	
               TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
                INDEX UNIQUE SCAN	Object owner=PM_DBA	

Object name=BUSINESS_UNIT_PK		Cardinality=218	
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	

Bytes=10
               INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=MONTHLY_COMBINED_RETURNS	Cost=8	Cardinality=1	

Bytes=40
              INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=MCR_LROR_IE01	Cost=7	Cardinality=1	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
             INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
            INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=CODE_TYPE_PK		Cardinality=105	
          TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	

Bytes=26
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=390	
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=CALENDAR_MONTH	Cost=1	Cardinality=1	Bytes=15
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=CALENDAR_MONTH_PK		Cardinality=1	
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=INDUSTRY_HIERARCHY_PK		Cardinality=10	
       INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=CALENDAR_MONTH_PK		Cardinality=1	Bytes=3
     SORT JOIN			Cost=57	Cardinality=871	Bytes=24388
      VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	

Cost=2206	Cardinality=871	Bytes=24388
       SORT UNIQUE			Cost=2206	

Cardinality=871	Bytes=46958
        UNION-ALL					
         MERGE JOIN CARTESIAN			Cost=10	

Cardinality=852	Bytes=46008
          NESTED LOOPS			Cost=2	Cardinality=4	

Bytes=88
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=44	Bytes=572
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object 

name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
          BUFFER SORT			Cost=10	Cardinality=216	

Bytes=6912
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=BUSINESS_UNIT	Cost=2	Cardinality=216	Bytes=6912
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	

Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
          NESTED LOOPS			Cost=5	Cardinality=2	

Bytes=100
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
           INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=BUSINESS_UNIT_IE01		Cardinality=1	
         FILTER					
          NESTED LOOPS			Cost=7	Cardinality=17	

Bytes=850
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
           TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
          FILTER					
           CONNECT BY WITH FILTERING					
            NESTED LOOPS					
             INDEX RANGE SCAN	Object owner=PM_DBA	Object 

name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
             TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	

Object name=INT_BUSINESS_UT_HIERARCHY			
            NESTED LOOPS					
             BUFFER SORT				

Cardinality=11	Bytes=187
              CONNECT BY PUMP					
             INDEX FULL SCAN	Object owner=PM_DBA	Object 

name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	

Bytes=187
           FILTER					
            SORT GROUP BY			Cost=8	

Cardinality=3	Bytes=54
             TABLE ACCESS FULL	Object owner=PM_DBA	Object 

name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=212	Bytes=3816

----------- PLAN WITH OUT FIRST ROWS HINT -----


SELECT STATEMENT, GOAL = CHOOSE			Cost=80	Cardinality=1	Bytes=232
 SORT ORDER BY			Cost=80	Cardinality=1	Bytes=232
  WINDOW SORT			Cost=80	Cardinality=1	Bytes=232
   MERGE JOIN			Cost=80	Cardinality=1	Bytes=232
    SORT JOIN			Cost=23	Cardinality=1	Bytes=204
     NESTED LOOPS			Cost=17	Cardinality=1	Bytes=204
      NESTED LOOPS			Cost=16	Cardinality=1	Bytes=189
       NESTED LOOPS			Cost=16	Cardinality=1	Bytes=186
        NESTED LOOPS			Cost=16	Cardinality=1	Bytes=183
         NESTED LOOPS			Cost=15	Cardinality=1	Bytes=157
          NESTED LOOPS			Cost=15	Cardinality=1	Bytes=154
           NESTED LOOPS			Cost=14	Cardinality=1	Bytes=121
            NESTED LOOPS			Cost=13	Cardinality=1	Bytes=111
             NESTED LOOPS			Cost=12	Cardinality=1	Bytes=102
              NESTED LOOPS			Cost=4	Cardinality=1	Bytes=62
               NESTED LOOPS			Cost=3	Cardinality=1	Bytes=52
                TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=ACCOUNT	Cost=2	Cardinality=1	Bytes=25
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=ACCOUNT_PK	Cost=1	Cardinality=6255	
                TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
                 INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_PK		Cardinality=218	
               TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	Bytes=10
                INDEX RANGE SCAN	Object owner=PM_DBA	Object name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=MONTHLY_COMBINED_RETURNS	Cost=8	Cardinality=1	Bytes=40
               INDEX RANGE SCAN	Object owner=PM_DBA	Object name=MCR_LROR_IE01	Cost=7	Cardinality=1	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
              INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
             INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CODE_TYPE_PK		Cardinality=105	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
            INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY_PK		Cardinality=1	
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=COUNTRY_PK		Cardinality=1	Bytes=3
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	Bytes=26
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=1	
        INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=REGION_PK		Cardinality=1	Bytes=3
       INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CALENDAR_MONTH_PK		Cardinality=1	Bytes=3
      TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CALENDAR_MONTH	Cost=1	Cardinality=1	Bytes=15
       INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CALENDAR_MONTH_PK		Cardinality=1	
    SORT JOIN			Cost=57	Cardinality=871	Bytes=24388
     VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	Cost=2206	Cardinality=871	Bytes=24388
      SORT UNIQUE			Cost=2206	Cardinality=871	Bytes=46958
       UNION-ALL					
        MERGE JOIN CARTESIAN			Cost=10	Cardinality=852	Bytes=46008
         NESTED LOOPS			Cost=2	Cardinality=4	Bytes=88
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=44	Bytes=572
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
         BUFFER SORT			Cost=10	Cardinality=216	Bytes=6912
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=216	Bytes=6912
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
         NESTED LOOPS			Cost=5	Cardinality=2	Bytes=100
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          INDEX RANGE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_IE01		Cardinality=1	
        FILTER					
         NESTED LOOPS			Cost=7	Cardinality=17	Bytes=850
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
         FILTER					
          CONNECT BY WITH FILTERING					
           NESTED LOOPS					
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
            TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY			
           NESTED LOOPS					
            BUFFER SORT				Cardinality=11	Bytes=187
             CONNECT BY PUMP					
            INDEX FULL SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	Bytes=187
          FILTER					
           SORT GROUP BY			Cost=8	Cardinality=3	Bytes=54
            TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=212	Bytes=3816

Thanks A Lot..

[Updated on: Tue, 13 December 2005 21:48]

Report message to a moderator

Re: Tuning of Select for a View in Report [message #151199 is a reply to message #151031]

Tue, 13 December 2005 14:15

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Come on man, read the sticky, read the documentation, learn how to use code tags. You have to put more into it.

Report message to a moderator

Re: Tuning of Select for a View in Report [message #152325 is a reply to message #151199]

Thu, 22 December 2005 09:14

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi Smartin

Sorry for not formatting the code, I need some advice on this,

As i have attached the Create View statement also earlier, Let me add 1 more scenario to this,

These is a table which has data column like

USER_ID, ROLE_ASSIGNED, ASSIGNED_DATE

IF the value for a role_Assigned column is PM and in this case the query and the report takes a long time like anything,
where as if role other than PM it is normal,

My question where could i look into this matter if for a particular value of role it takes time and for other role it is normal.What could be the possibilites.

If you have a look at create view statement any suggesting on tuning or modifying it please tell me,or any thing from plan output to be considered.

Thanks

Report message to a moderator

Re: Tuning of Select for a View in Report [message #152349 is a reply to message #151031]

Thu, 22 December 2005 12:24

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Do you have histograms on the role assigned column?

Also, run two queries with explain plan, one with a value for role_assigned that goes quickly and one that goes slow. What are the values for the rows (or cardinality) columns of the explain plan for that row of the plan?

Is the table partitioned in such a way, such as list partitioned, that some columns are in their own partition, but others are all lumped together in a single partition?

Report message to a moderator

Re: Tuning of Select for a View in Report [message #152367 is a reply to message #152349]

Thu, 22 December 2005 13:27

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Thanks Smartin

There are no histograms on role assigned column.(As per my colleague), How and where can i see whether column is having histogram or not, Can you please tell me.

Here is the plan with 2 different values for same select statement .

1 More time taking PLAN, THIS Took 13.45 seconds

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS			Cost=242	Cardinality=2	Bytes=458
 WINDOW SORT			Cost=242	Cardinality=2	Bytes=458
  CONCATENATION					
   MERGE JOIN			Cost=118	Cardinality=1	Bytes=229
    SORT JOIN			Cost=61	Cardinality=1	Bytes=168
     NESTED LOOPS			Cost=54	Cardinality=1	Bytes=168
      NESTED LOOPS			Cost=53	Cardinality=1	Bytes=143
       NESTED LOOPS			Cost=52	Cardinality=1	Bytes=132
        NESTED LOOPS			Cost=51	Cardinality=1	Bytes=106
         NESTED LOOPS			Cost=50	Cardinality=1	Bytes=96
          NESTED LOOPS			Cost=49	Cardinality=1	Bytes=87
           NESTED LOOPS			Cost=3	Cardinality=1	Bytes=43
            NESTED LOOPS			Cost=55	Cardinality=1	Bytes=201
             NESTED LOOPS			Cost=2	Cardinality=1	Bytes=33
              NESTED LOOPS			Cost=1	Cardinality=1	Bytes=6
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=COUNTRY_PK		Cardinality=1	Bytes=3
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=REGION_PK		Cardinality=1	Bytes=3
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_PK		Cardinality=220	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
              INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY_PK		Cardinality=10	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	Bytes=10
             INDEX RANGE SCAN	Object owner=PM_DBA	Object name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=DAILY_COMBINED_RETURNS	Cost=46	Cardinality=1	Bytes=44
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=DCR_LROR_IE01	Cost=45	Cardinality=1	
          TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CODE_TYPE_PK		Cardinality=105	
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	Bytes=26
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=390	
       TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CALENDAR_DATE	Cost=1	Cardinality=1	Bytes=11
        INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CALENDAR_DATE_PK		Cardinality=1	
      INLIST ITERATOR					
       TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=ACCOUNT	Cost=1	Cardinality=1	Bytes=25
        INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=ACCOUNT_PK		Cardinality=1	
    SORT JOIN			Cost=57	Cardinality=886	Bytes=24808
     VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	Cost=2206	Cardinality=886	Bytes=24808
      SORT UNIQUE			Cost=2206	Cardinality=886	Bytes=47768
       UNION-ALL					
        MERGE JOIN CARTESIAN			Cost=10	Cardinality=867	Bytes=46818
         NESTED LOOPS			Cost=2	Cardinality=4	Bytes=88
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=45	Bytes=585
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
         BUFFER SORT			Cost=10	Cardinality=218	Bytes=6976
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=218	Bytes=6976
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
         NESTED LOOPS			Cost=5	Cardinality=2	Bytes=100
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          INDEX RANGE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_IE01		Cardinality=1	
        FILTER					
         NESTED LOOPS			Cost=7	Cardinality=17	Bytes=850
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
         FILTER					
          CONNECT BY WITH FILTERING					
           NESTED LOOPS					
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
            TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY			
           NESTED LOOPS					
            BUFFER SORT				Cardinality=11	Bytes=187
             CONNECT BY PUMP					
            INDEX FULL SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	Bytes=187
          FILTER					
           SORT GROUP BY			Cost=8	Cardinality=3	Bytes=54
            TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=214	Bytes=3852
   MERGE JOIN			Cost=118	Cardinality=1	Bytes=229
    SORT JOIN			Cost=61	Cardinality=1	Bytes=201
     NESTED LOOPS			Cost=55	Cardinality=1	Bytes=201
      NESTED LOOPS			Cost=54	Cardinality=1	Bytes=168
       NESTED LOOPS			Cost=53	Cardinality=1	Bytes=143
        NESTED LOOPS			Cost=52	Cardinality=1	Bytes=132
         NESTED LOOPS			Cost=51	Cardinality=1	Bytes=106
          NESTED LOOPS			Cost=50	Cardinality=1	Bytes=96
           NESTED LOOPS			Cost=49	Cardinality=1	Bytes=87
            NESTED LOOPS			Cost=3	Cardinality=1	Bytes=43
             NESTED LOOPS			Cost=2	Cardinality=1	Bytes=33
              NESTED LOOPS			Cost=1	Cardinality=1	Bytes=6
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=COUNTRY_PK		Cardinality=1	Bytes=3
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=REGION_PK		Cardinality=1	Bytes=3
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_PK		Cardinality=220	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	Bytes=10
              INDEX RANGE SCAN	Object owner=PM_DBA	Object name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=DAILY_COMBINED_RETURNS	Cost=46	Cardinality=1	Bytes=44
             INDEX RANGE SCAN	Object owner=PM_DBA	Object name=DCR_LROR_IE01	Cost=45	Cardinality=1	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
            INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
          TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CODE_TYPE_PK		Cardinality=105	
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	Bytes=26
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=390	
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CALENDAR_DATE	Cost=1	Cardinality=1	Bytes=11
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CALENDAR_DATE_PK		Cardinality=1	
       INLIST ITERATOR					
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=ACCOUNT	Cost=1	Cardinality=1	Bytes=25
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=ACCOUNT_PK		Cardinality=1	
      TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
       INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY_PK		Cardinality=10	
    SORT JOIN			Cost=57	Cardinality=886	Bytes=24808
     VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	Cost=2206	Cardinality=886	Bytes=24808
      SORT UNIQUE			Cost=2206	Cardinality=886	Bytes=47768
       UNION-ALL					
        MERGE JOIN CARTESIAN			Cost=10	Cardinality=867	Bytes=46818
         NESTED LOOPS			Cost=2	Cardinality=4	Bytes=88
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=45	Bytes=585
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
         BUFFER SORT			Cost=10	Cardinality=218	Bytes=6976
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=218	Bytes=6976
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
         NESTED LOOPS			Cost=5	Cardinality=2	Bytes=100
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          INDEX RANGE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_IE01		Cardinality=1	
        FILTER					
         NESTED LOOPS			Cost=7	Cardinality=17	Bytes=850
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
         FILTER					
          CONNECT BY WITH FILTERING					
           NESTED LOOPS					
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
            TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY			
           NESTED LOOPS					
            BUFFER SORT				Cardinality=11	Bytes=187
             CONNECT BY PUMP					
            INDEX FULL SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	Bytes=187
          FILTER					
           SORT GROUP BY			Cost=8	Cardinality=3	Bytes=54
            TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=214	Bytes=3852

Second one THIS Took 1.15 seconds

SELECT STATEMENT, GOAL = HINT: FIRST_ROWS			Cost=242	Cardinality=2	Bytes=458
 WINDOW SORT			Cost=242	Cardinality=2	Bytes=458
  CONCATENATION					
   MERGE JOIN			Cost=118	Cardinality=1	Bytes=229
    SORT JOIN			Cost=61	Cardinality=1	Bytes=168
     NESTED LOOPS			Cost=54	Cardinality=1	Bytes=168
      NESTED LOOPS			Cost=53	Cardinality=1	Bytes=143
       NESTED LOOPS			Cost=52	Cardinality=1	Bytes=132
        NESTED LOOPS			Cost=51	Cardinality=1	Bytes=106
         NESTED LOOPS			Cost=50	Cardinality=1	Bytes=96
          NESTED LOOPS			Cost=49	Cardinality=1	Bytes=87
           NESTED LOOPS			Cost=3	Cardinality=1	Bytes=43
            NESTED LOOPS			Cost=55	Cardinality=1	Bytes=201
             NESTED LOOPS			Cost=2	Cardinality=1	Bytes=33
              NESTED LOOPS			Cost=1	Cardinality=1	Bytes=6
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=COUNTRY_PK		Cardinality=1	Bytes=3
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=REGION_PK		Cardinality=1	Bytes=3
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_PK		Cardinality=220	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
              INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY_PK		Cardinality=10	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	Bytes=10
             INDEX RANGE SCAN	Object owner=PM_DBA	Object name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=DAILY_COMBINED_RETURNS	Cost=46	Cardinality=1	Bytes=44
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=DCR_LROR_IE01	Cost=45	Cardinality=1	
          TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CODE_TYPE_PK		Cardinality=105	
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	Bytes=26
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=390	
       TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CALENDAR_DATE	Cost=1	Cardinality=1	Bytes=11
        INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CALENDAR_DATE_PK		Cardinality=1	
      INLIST ITERATOR					
       TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=ACCOUNT	Cost=1	Cardinality=1	Bytes=25
        INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=ACCOUNT_PK		Cardinality=1	
    SORT JOIN			Cost=57	Cardinality=886	Bytes=24808
     VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	Cost=2206	Cardinality=886	Bytes=24808
      SORT UNIQUE			Cost=2206	Cardinality=886	Bytes=47768
       UNION-ALL					
        MERGE JOIN CARTESIAN			Cost=10	Cardinality=867	Bytes=46818
         NESTED LOOPS			Cost=2	Cardinality=4	Bytes=88
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=45	Bytes=585
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
         BUFFER SORT			Cost=10	Cardinality=218	Bytes=6976
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=218	Bytes=6976
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
         NESTED LOOPS			Cost=5	Cardinality=2	Bytes=100
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          INDEX RANGE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_IE01		Cardinality=1	
        FILTER					
         NESTED LOOPS			Cost=7	Cardinality=17	Bytes=850
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
         FILTER					
          CONNECT BY WITH FILTERING					
           NESTED LOOPS					
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
            TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY			
           NESTED LOOPS					
            BUFFER SORT				Cardinality=11	Bytes=187
             CONNECT BY PUMP					
            INDEX FULL SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	Bytes=187
          FILTER					
           SORT GROUP BY			Cost=8	Cardinality=3	Bytes=54
            TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=214	Bytes=3852
   MERGE JOIN			Cost=118	Cardinality=1	Bytes=229
    SORT JOIN			Cost=61	Cardinality=1	Bytes=201
     NESTED LOOPS			Cost=55	Cardinality=1	Bytes=201
      NESTED LOOPS			Cost=54	Cardinality=1	Bytes=168
       NESTED LOOPS			Cost=53	Cardinality=1	Bytes=143
        NESTED LOOPS			Cost=52	Cardinality=1	Bytes=132
         NESTED LOOPS			Cost=51	Cardinality=1	Bytes=106
          NESTED LOOPS			Cost=50	Cardinality=1	Bytes=96
           NESTED LOOPS			Cost=49	Cardinality=1	Bytes=87
            NESTED LOOPS			Cost=3	Cardinality=1	Bytes=43
             NESTED LOOPS			Cost=2	Cardinality=1	Bytes=33
              NESTED LOOPS			Cost=1	Cardinality=1	Bytes=6
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=COUNTRY_PK		Cardinality=1	Bytes=3
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=REGION_PK		Cardinality=1	Bytes=3
              TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=27
               INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_PK		Cardinality=220	
             TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=PORTFOLIO_LEVEL_TYPE	Cost=1	Cardinality=1	Bytes=10
              INDEX RANGE SCAN	Object owner=PM_DBA	Object name=PORT_LEVEL_TYPE_IE01		Cardinality=1	
            TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=DAILY_COMBINED_RETURNS	Cost=46	Cardinality=1	Bytes=44
             INDEX RANGE SCAN	Object owner=PM_DBA	Object name=DCR_LROR_IE01	Cost=45	Cardinality=1	
           TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE	Cost=1	Cardinality=1	Bytes=9
            INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=RETURN_OBJECT_TYPE_PK		Cardinality=6	
          TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CODE_TYPE	Cost=1	Cardinality=1	Bytes=10
           INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CODE_TYPE_PK		Cardinality=105	
         TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY	Cost=1	Cardinality=1	Bytes=26
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INSTRUMENT_TYPE_HIERARCHY_PK		Cardinality=390	
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=CALENDAR_DATE	Cost=1	Cardinality=1	Bytes=11
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=CALENDAR_DATE_PK		Cardinality=1	
       INLIST ITERATOR					
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=ACCOUNT	Cost=1	Cardinality=1	Bytes=25
         INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=ACCOUNT_PK		Cardinality=1	
      TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY	Cost=1	Cardinality=1	Bytes=33
       INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INDUSTRY_HIERARCHY_PK		Cardinality=10	
    SORT JOIN			Cost=57	Cardinality=886	Bytes=24808
     VIEW	Object owner=PM_DBA	Object name=V_USER_BUT_DET	Cost=2206	Cardinality=886	Bytes=24808
      SORT UNIQUE			Cost=2206	Cardinality=886	Bytes=47768
       UNION-ALL					
        MERGE JOIN CARTESIAN			Cost=10	Cardinality=867	Bytes=46818
         NESTED LOOPS			Cost=2	Cardinality=4	Bytes=88
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLE_DETAILS	Cost=2	Cardinality=45	Bytes=585
          INDEX UNIQUE SCAN	Object owner=PM_DBA	Object name=INT_USER_DETAILS_PK		Cardinality=4	Bytes=36
         BUFFER SORT			Cost=10	Cardinality=218	Bytes=6976
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=218	Bytes=6976
        TABLE ACCESS BY INDEX ROWID	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=1	Cardinality=1	Bytes=32
         NESTED LOOPS			Cost=5	Cardinality=2	Bytes=100
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          INDEX RANGE SCAN	Object owner=PM_DBA	Object name=BUSINESS_UNIT_IE01		Cardinality=1	
        FILTER					
         NESTED LOOPS			Cost=7	Cardinality=17	Bytes=850
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_USER_ROLES_BUS_UT_DET	Cost=3	Cardinality=2	Bytes=36
          TABLE ACCESS FULL	Object owner=PM_DBA	Object name=BUSINESS_UNIT	Cost=2	Cardinality=11	Bytes=352
         FILTER					
          CONNECT BY WITH FILTERING					
           NESTED LOOPS					
            INDEX RANGE SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=1	Cardinality=1	Bytes=6
            TABLE ACCESS BY USER ROWID	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY			
           NESTED LOOPS					
            BUFFER SORT				Cardinality=11	Bytes=187
             CONNECT BY PUMP					
            INDEX FULL SCAN	Object owner=PM_DBA	Object name=INT_BUS_UT_HIERARCHY_PK	Cost=127	Cardinality=11	Bytes=187
          FILTER					
           SORT GROUP BY			Cost=8	Cardinality=3	Bytes=54
            TABLE ACCESS FULL	Object owner=PM_DBA	Object name=INT_BUSINESS_UT_HIERARCHY	Cost=2	Cardinality=214	Bytes=3852

Thanks a Lot

[Updated on: Thu, 22 December 2005 13:39]

Report message to a moderator

Re: Tuning of Select for a View in Report [message #152377 is a reply to message #151031]

Thu, 22 December 2005 14:34

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I don't have time to look at the plans in detail, they are huge massive things, you are talking an all day or longer project to evaluate the tables involved and their data distributions and other factors not to mention database setup and params. But try to get rid of the nested loops and make them hash joins. Maybe your hash area size or memory in general is too small?

Definitely go with all rows not first rows. And there are dictionary tables that show if you have histograms, but just regather stats on the affected tables as per the sticky in this forum, which would include stats on indexes and histograms.

Also in general, try to limit the rows you process as soon as you can. That means inside inline views limit the data you are processing so that all future steps do so with far less data.

Report message to a moderator

Previous Topic:	suggest
Next Topic:	how to change snapshot timings

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 17:50:21 CDT 2024