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  |
|
|
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  |
|
|
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 #552450 is a reply to message #552098] |
Wed, 25 April 2012 22:49 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Tue Feb 04 02:24:21 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
