Home » RDBMS Server » Performance Tuning » Performance tuing using FIRST_ROWS (Oracle 9.2.0.1.0)
Performance tuing using FIRST_ROWS [message #359552] Mon, 17 November 2008 04:20 Go to next message
unmeshmohanan
Messages: 3
Registered: November 2008
Location: India
Junior Member

Hi,

I have a query like below

  SELECT  /*+ FIRST_ROWS(10) */ a.cust_ac_no account_no , a.branch_code branch_code 
	  FROM sttms_cust_account a
 WHERE a.cust_no <> nvl(global.user_customer_no,'*%$*')
   and (NOT exists (SELECT 'X'
                      FROM smvws_user_branches b
                     WHERE b.user_id     = global.user_id
                       and b.branch_code = a.branch_code)
        OR
        not exists (SELECT 'X'
                      FROM smvws_user_accclass c
                     WHERE c.user_id = global.user_id  
                       and c.account_class = a.account_class)
       )
   and not exists (SELECT 'X'
                     FROM SMTB_USER_ACCESS_EXCE_ACCTS d
                    WHERE D.cust_ac_no      = A.cust_ac_no
                      AND D.cust_ac_brn     = a.branch_code
                      AND d.account_allowed = 'A'
                      AND d.user_id         = global.user_id)
UNION ALL
SELECT cust_ac_no account_no, cust_ac_brn branch_code
FROM   SMTB_USER_ACCESS_EXCE_ACCTS
WHERE account_allowed = 'D'
AND user_id           = global.user_id

I just added the 'FIRST_ROWS(10)' to this query. The cost seems to be drastically dropped from 4590 to 8 . Please advice me , is it the right way or is there any better way to tune the same.

Thanks In advance

[RL - Added CODE tags]

[Updated on: Mon, 17 November 2008 19:36] by Moderator

Report message to a moderator

Re: Performance tuing using FIRST_ROWS [message #359652 is a reply to message #359552] Mon, 17 November 2008 19:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The COST is GOAL-RELATED.

Oracle is telling you that it will cost 8 to return the first row, or 4590 to return EVERY row. The two are not compareable.

What is more important to you? Getting the first row fastest, or the total time to return every row?

Ross Leishman
Re: Performance tuing using FIRST_ROWS [message #359659 is a reply to message #359552] Mon, 17 November 2008 22:01 Go to previous messageGo to next message
unmeshmohanan
Messages: 3
Registered: November 2008
Location: India
Junior Member

Hi,

Thanks for the reply and sorry fr the lack of clarity in requirement. Basically teh query is taking too much time to retrieve the whole set of rows. I should get the whole records in less response time. The main table used in the query named 'sttms_cust_account' contains 794572 records as a whole.

Thanks
Unmesh
Re: Performance tuing using FIRST_ROWS [message #359982 is a reply to message #359659] Wed, 19 November 2008 01:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan or better yet some TKProf output.

Ross Leishman
Re: Performance tuing using FIRST_ROWS [message #361323 is a reply to message #359552] Tue, 25 November 2008 23:19 Go to previous message
unmeshmohanan
Messages: 3
Registered: November 2008
Location: India
Junior Member

Hi,

Thank for the message. Please find below the plan for the query.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 786 | 6 (34)|
| 1 | UNION-ALL | | | | |
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS ANTI | | 104 | 7800 | 4 (0)|
|* 4 | TABLE ACCESS FULL | STTM_CUST_ACCOUNT | 104 | 4056 | 3 (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID | SMTB_USER_ACCESS_EXCE_ACCTS | 1 | 36 | 1 (0)|
|* 6 | INDEX UNIQUE SCAN | PK_SMTB_USER_EXCE_ACCTS | 1 | | 0 (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 7 | VIEW | SMVW_USER_BRANCHES | 2 | 22 | 12 (0)|
| 8 | UNION-ALL | | | | |
| 9 | MINUS | | | | |
| 10 | NESTED LOOPS | | 1 | 17 | 2 (0)|
|* 11 | TABLE ACCESS BY INDEX ROWID | STTM_BRANCH | 1 | 8 | 1 (0)|
|* 12 | INDEX UNIQUE SCAN | SYS_C007309 | 1 | | 0 (0)|
|* 13 | TABLE ACCESS BY INDEX ROWID | SMTB_USER | 1 | 9 | 1 (0)|
|* 14 | INDEX UNIQUE SCAN | PK_SMUSER | 1 | | 0 (0)|
| 15 | SORT UNIQUE | | 1 | 20 | |
| 16 | NESTED LOOPS | | 1 | 20 | 5 (0)|
|* 17 | TABLE ACCESS BY INDEX ROWID| SMTB_USER | 1 | 9 | 2 (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 18 | INDEX UNIQUE SCAN | PK_SMUSER | 1 | | 1 (0)|
|* 19 | TABLE ACCESS FULL | SMTB_USER_BRANCHES | 1 | 11 | 3 (0)|
| 20 | NESTED LOOPS | | 1 | 20 | 5 (0)|
|* 21 | TABLE ACCESS BY INDEX ROWID | SMTB_USER | 1 | 9 | 2 (0)|
|* 22 | INDEX UNIQUE SCAN | PK_SMUSER | 1 | | 1 (0)|
|* 23 | TABLE ACCESS FULL | SMTB_USER_BRANCHES | 1 | 11 | 3 (0)|
|* 24 | FILTER | | | | |
| 25 | NESTED LOOPS | | 1 | 19 | 2 (0)|
|* 26 | TABLE ACCESS BY INDEX ROWID | STTM_ACCOUNT_CLASS | 1 | 10 | 1 (0)|
|* 27 | INDEX UNIQUE SCAN | STACCCLS_PK | 1 | | 0 (0)|
| 28 | TABLE ACCESS BY INDEX ROWID | SMTB_USER | 1 | 9 | 1 (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 29 | INDEX UNIQUE SCAN | PK_SMUSER | 1 | | 0 (0)|
|* 30 | INDEX UNIQUE SCAN | UI_SM_USER_ACCCLASS | 1 | 12 | 1 (0)|
|* 31 | INDEX UNIQUE SCAN | UI_SM_USER_ACCCLASS | 1 | 12 | 1 (0)|
|* 32 | TABLE ACCESS BY INDEX ROWID | SMTB_USER_ACCESS_EXCE_ACCTS | 1 | 36 | 2 (0)|
|* 33 | INDEX RANGE SCAN | PK_SMTB_USER_EXCE_ACCTS | 2 | | 1 (0)|
-------------------------------------------------------------------------------------------------------

Please find the attached text file for the plan table output , if the above plan is difficult to read

Thanks
Unmesh

[Updated on: Tue, 25 November 2008 23:40]

Report message to a moderator

Previous Topic: SQL Tuning Advisor Feature
Next Topic: procedure running with no end
Goto Forum:
  


Current Time: Fri Dec 02 14:21:20 CST 2016

Total time taken to generate the page: 0.12203 seconds