Home » RDBMS Server » Performance Tuning » Tuneup the SQL (Oracle 11.2.0.1.0 OS RH Linux 5.1)
Tuneup the SQL [message #551980] Mon, 23 April 2012 01:30 Go to next message
javed.khan
Messages: 304
Registered: November 2006
Location: Banglore
Senior Member

Hi All,
I have a view which is fetching 150,000 records on load of a form because of that the form is taking a long time to load. The problem here is i cannot use a PL as we are bound to supply small sql query's to Javascript pages. My query is simple
CREATE OR REPLACE FORCE VIEW ms_dms_ns_employee_info_v (employee_id,
                                                        employee_name,
                                                        first_name,
                                                        middle_name,
                                                        last_name,
                                                        physical_location,
                                                        payroll_store,
                                                        physical_location_code,
                                                        payroll_store_code,
                                                        business_unit,
                                                        payroll_dept,
                                                        payroll_dept_desc,
                                                        user_apr_manager,
                                                        user_assign_status,
                                                        user_role,
                                                        user_oracle_job_name,
                                                        job_exempt_status_code,
                                                        job_family_code,
                                                        person_type,
                                                        user_segment
                                                       )
AS
   SELECT emp_user_id employee_id,
             b.first_name
          || NVL2 (b.middle_initial, ' ' || b.middle_initial, '')
          || NVL2 (b.last_name, ' ' || b.last_name, '') AS employee_name,
          b.first_name, b.middle_initial middle_name, b.last_name,
          physical_location physical_location, payroll_store payroll_store,
          physical_location_desc physical_location_code,
          payroll_store_desc payroll_store_code, business_unit, payroll_dept,
          payroll_dept_desc, user_apr_manager, user_assign_status, user_role,
          user_oracle_job_name, job_exempt_status_code, job_family_code,
          person_type, user_segment
     FROM ms_gen_user_details a, si_users_t b
    WHERE a.lan_id = b.user_name;


Is there any way to fasten this without using a PL.

Javed A. Khan
Re: Tuneup the SQL [message #551985 is a reply to message #551980] Mon, 23 April 2012 01:50 Go to previous messageGo to next message
javed.khan
Messages: 304
Registered: November 2006
Location: Banglore
Senior Member

DDL for refrence.

DBMS_METADATA.GET_DDL('TABLE','MS_GEN_USER_DETAILS')                            
--------------------------------------------------------------------------------
                                                                                
  CREATE TABLE "DMS_60_NS_STG_1"."MS_GEN_USER_DETAILS"                          
   (	"EMP_USER_ID" VARCHAR2(200 CHAR),                                          
	"ATS_USERNAME" VARCHAR2(4000 CHAR),                                            
	"PAYROLL_STORE" VARCHAR2(4000 CHAR),                                           
	"PAYROLL_STORE_DESC" VARCHAR2(4000 CHAR),                                      
	"PHYSICAL_LOCATION" VARCHAR2(4000 CHAR),                                       
	"PHYSICAL_LOCATION_DESC" VARCHAR2(4000 CHAR),                                  
	"BUSINESS_UNIT" VARCHAR2(4000 CHAR),                                           
	"PAYROLL_DEPT" VARCHAR2(4000 CHAR),                                            
	"PAYROLL_DEPT_DESC" VARCHAR2(4000 CHAR),                                       
	"USER_APR_MANAGER" VARCHAR2(4000 CHAR),                                        
	"USER_ASSIGN_STATUS" VARCHAR2(4000 CHAR),                                      
	"USER_ROLE" VARCHAR2(4000 CHAR),                                               
	"USER_ORACLE_JOB_NAME" VARCHAR2(4000 CHAR),                                    
	"JOB_EXEMPT_STATUS_CODE" VARCHAR2(4000 CHAR),                                  
	"JOB_FAMILY_CODE" VARCHAR2(4000 CHAR),                                         
	"PERSON_TYPE" VARCHAR2(4000 CHAR),                                             
	"USER_SEGMENT" VARCHAR2(4000 CHAR),                                            
	"LAN_ID" VARCHAR2(4000 CHAR)                                                   
   ) SEGMENT CREATION IMMEDIATE                                                 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                 
 NOCOMPRESS LOGGING                                                             
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                   
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)             
  TABLESPACE "DMS_60_NS_STG_1_DATA"                                             
                                                                                
1 row selected.

DBMS_METADATA.GET_DDL('TABLE','SI_USERS_T')                                     
--------------------------------------------------------------------------------
                                                                                
  CREATE TABLE "DMS_60_NS_STG_1"."SI_USERS_T"                                   
   (	"USER_ID" NUMBER,                                                          
	"ENTERPRISE_ID" NUMBER,                                                        
	"LAST_NAME" VARCHAR2(30 CHAR),                                                 
	"MIDDLE_INITIAL" VARCHAR2(6 CHAR),                                             
	"FIRST_NAME" VARCHAR2(30 CHAR),                                                
	"EMAIL_ADDRESS" VARCHAR2(80 CHAR),                                             
	"PAGER_NUMBER" VARCHAR2(80 CHAR),                                              
	"PHONE_NUMBER" VARCHAR2(80 CHAR),                                              
	"USER_NAME" VARCHAR2(80 CHAR),                                                 
	"PASSWORD" VARCHAR2(120 CHAR),                                                 
	"TIMEZONE" NUMBER,                                                             
	"REPORT_FORMAT" NUMBER,                                                        
	"START_DATE" DATE,                                                             
	"END_DATE" DATE,                                                               
	"CREATED_BY" NUMBER,                                                           
	"CREATION_DATE" DATE,                                                          
	"LAST_UPDATED_BY" NUMBER,                                                      
	"LAST_UPDATE_DATE" DATE,                                                       
	"XML" VARCHAR2(4000 CHAR),                                                     
	"COMMENTS" VARCHAR2(1000 CHAR),                                                
	"SERVER_GENERATED_CHARTS" VARCHAR2(1 CHAR),                                    
	"LOCATION" VARCHAR2(100 CHAR),                                                 
	"LOCALE" VARCHAR2(100 CHAR),                                                   
	"INFOCENTER_FLAG" VARCHAR2(1 CHAR),                                            
	"DEFAULT_FIRST_PAGE" NUMBER,                                                   
	"ELECTRONIC_SIGNATURE" VARCHAR2(120 CHAR),                                     
	"PASSWORD_LAST_UPDATE_DATE" DATE,                                              
	"SIGNATURE_LAST_UPDATE_DATE" DATE,                                             
	"FORGOT_PWD_QUESTION_ID" NUMBER,                                               
	"LOCALE_FK" NUMBER                                                             
   ) SEGMENT CREATION IMMEDIATE                                                 
  PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255                                 
 NOCOMPRESS LOGGING                                                             
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                   
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)             
  TABLESPACE "DMS_60_NS_STG_1_DATA"                                             
                                                                                
1 row selected.
Re: Tuneup the SQL [message #551988 is a reply to message #551980] Mon, 23 April 2012 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Tuneup the SQL [message #551999 is a reply to message #551988] Mon, 23 April 2012 02:15 Go to previous messageGo to next message
javed.khan
Messages: 304
Registered: November 2006
Location: Banglore
Senior Member

Any Idea about Pagination?
Re: Tuneup the SQL [message #552001 is a reply to message #551999] Mon, 23 April 2012 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A couple of ones.

Regards
Michel
Re: Tuneup the SQL [message #552004 is a reply to message #552001] Mon, 23 April 2012 02:29 Go to previous messageGo to next message
javed.khan
Messages: 304
Registered: November 2006
Location: Banglore
Senior Member

Thanks.
Re: Tuneup the SQL [message #552098 is a reply to message #552004] Mon, 23 April 2012 09:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Do you have indexes on ms_gen_user_details.lan_id and si_users_t.user_name? Are the statistics current? Please see the following link about paging:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:76812348057
Re: Tuneup the SQL [message #552450 is a reply to message #552098] Wed, 25 April 2012 22:49 Go to previous message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
I just love abbreviations...

what is PL?

is your form querying this view directly? Or is it using the view in some other way? In the old Oracle Forms days for example I would ask... is this view your BASE TABLE OF THE BLOCK? If so then you should be fetching only an arraysize worth of rows at a time. In this case, as long as you dont' have an ALL ROWS style QEP, and the other items are tables, you should have subsecond response time for each page fetch you do.

This is not a true pagination query. There is no ROWNUM monkey business going on. Show us the query you are really having trouble with. Of course you may still be doin pagination through the behavior of your tool if it is caching rows (or not).

Kevin
Previous Topic: Troublesome trigger
Next Topic: Querying GTT in Parallel Mode
Goto Forum:
  


Current Time: Tue Jul 29 15:55:02 CDT 2014

Total time taken to generate the page: 0.10453 seconds