ORACLE & VS 2005 performance issue

Question

I'm evaluating performance issues in a data entry application using c# (VS 2005), framework 2.0 with ORACLE 11. The application is being used by more than 120 people online. Basically the task is simple, only verify salary data in papers and then write them in a database. To valid data, the user most verify employee identification ID or first name or last name in a large database (5 Million). Sometimes during the task, the application blocks for a moment (seconds to a few minutes), making impossible to continue working. This issue lacks productivity and is producing significant delays on work planning.

The application (3 layer: frontend, middle tier and backend) is using main tables to validate the data and gridviews to add, modify or delete data. By the moment, no IT renovation is possible.

First question: Im wondering if is good practice to use views instead of main tables to reduce overhead and application blocks?

Queries to search data are:

To search by EmployeeID:

SELECT name FROM employees WHERE id = EmployeeID OR af = EmployeeID;

(only id field is PK, af field correspond to other identification number but no everyone have it)

To search by name:

SELECT id, DECODE(complete_name, null, name_null) name, first_name, last_name, sex FROM employees WHERE name_null LIKE CONVERT(REPLACE'" + first_name.ToUpper() + "%" + last_name.ToUpper() + "%" + "','Ñ','N'),'US7ASCII')" OR name_null LIKE CONVERT(REPLACE'" + last_name.ToUpper() + "%" + first_name.ToUpper() + "%" + "','Ñ','N'),'US7ASCII')";

This two queries are created in functions, so data is returned by demand. Then data is showed in DataGrids to user.

Second question: If there are other ways to optimize the queries, considering the facts presented?

Thank you for any tip.

1) A view can reduce *the complexity of the query* but never the *overhead and application blocks*.2) You'll have to post the execution plans of your queries and some additional information see [the discussion here](https://stackoverflow.com/a/34975420/4808122). — Marmite Bomber, Jan 31 '20 at 14:58
The seond query most probably *doesn't use bind variables*, which can be with 120 concurent session a serious problem of extensive *parsing*. — Marmite Bomber, Jan 31 '20 at 15:08
As @MarmiteBomber mentions, not using bind variables is a performance-killer. FIx that. — OldProgrammer, Jan 31 '20 at 16:19

score 0 · Accepted Answer · answered Jan 31 '20 at 17:04

I don't think you can fix this from the application side and need to look in the DB for a solution. I recommend creating and testing indexes on employees.name_null and employees.af. If you are seeing delays as you mention it is probably due to full table scans on employees tables (not "blocking" [blocking locks can be checked as per SQL below]); you can check this by examining the execution plan for the suspected slow SQL.

select * from
(
SELECT distinct inst_id "Inst Id"
       , LPAD('>', (level-1)*2, '>') || NVL(s.username, '(oracle)') AS "Username" 
       , TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS "Logon Time"
       , s.osuser
       , s.sid "Session Id"
       , s.serial# "Sess Ser#"
       , s.blocking_session "Session Id (Blocker)"
       , s.blocking_instance "Inst Id (Blocker)"
       , s.status "Sess Status"
       , s.event "Event Name"
       , s.sql_id
       , (select max(sql_text) from gv$sqlarea sa where sa.sql_id = s.sql_id) sql_text
       , s.module
       , s.machine
       , s.program
       , s.lockwait
       , level
FROM   gv$session s
where (sid, inst_id) in (select blocking_session, blocking_instance from gv$session) or blocking_session is not null
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
--order by  LPAD('>', (level-1)*2, '>') || NVL(s.username, '(oracle)') desc, blocking_Session
order by  (level-1)*2 , blocking_Session
)
where "Session Id" = nvl(:sid_blk, "Session Id") or "Session Id (Blocker)" = nvl(:sid_blk,"Session Id (Blocker)")
;

ORACLE & VS 2005 performance issue

1 Answers1