OraFAQ Forum: Performance Tuning » Performace issue in transaction system

Home » RDBMS Server » Performance Tuning » Performace issue in transaction system (oracle 10.2.0.5 solaris 64 bit)

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

Performace issue in transaction system [message #556718]

Wed, 06 June 2012 05:02

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi Guys,

My transaction systems is java based which access data through sql statement. there are 10-12 view which are being used by java in the frontend. i need to tune the views as there is no issue on the java side.

there is a master view which is made up of all the 10 views , master view is being called by java which internally expand all the 10 views.

Explain plan show very good result and data comes in 30 sec but according to user it should be much faster than this. al together there are 200000 records.

Since this application are getting update and inserted by new records during the day , ican't create mv because refresh will be dificult
My view defenition looks like below

SELECT ser_id,
STA_NAME,
CAT_NAME,
PER1_NAME,
REF_NAME,
CAL_NAME,
ESC_NAME,
SER_NAME,
ser_deadline,
ser_description,
sd_servicecalls.reg_created,
org_name1,
IMP_NAME,
CAR_NAME,
REL_NAME,
INC_NAME
FROM sd_servicecalls,
cdm_organizations,
GSP_STATUS, --view
GSP_CATEGORY, --- view
GSP_REPORTINGUSER, -- view
GSP_REFERENCE, -- view
GSP_AFFECTEDUSER, --view
GSP_ESCALATION, --view
GSP_SERVICE, --view
GSP_IMPACT, --view
GSP_CARESETTING, --view
GSP_RELATED,-- view
GSP_INCSUM --view
WHERE STA_SER_ID = ser_id
AND CAT_SER_ID = ser_id
AND PER1_SER_ID = ser_id
AND REF_SER_ID = ser_id
AND CAL_SER_ID = ser_id
AND ESC_SER_ID = ser_id
AND SER_SER_ID = ser_id
AND ser_caller_org = org_oid
AND IMP_SER_ID = ser_id
AND CAR_SER_ID = ser_id
AND REL_SER_ID = ser_id
AND INC_SER_ID = ser_id
ORDER BY SER_ID DESC;

i thought to create MV but mv are very expnsie and provide result far delay than the view.

Any recomandation will be greate help.

Report message to a moderator

Re: Performace issue in transaction system [message #556723 is a reply to message #556718]

Wed, 06 June 2012 05:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Performace issue in transaction system [message #556727 is a reply to message #556723]

Wed, 06 June 2012 05:32

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Please find the view in formatted way and explain plan as well

SELECT ser_id,
STA_NAME,
CAT_NAME,
PER1_NAME,
REF_NAME,
CAL_NAME,
ESC_NAME,
SER_NAME,
ser_deadline,
ser_description,
sd_servicecalls.reg_created,
org_name1,
IMP_NAME,
CAR_NAME,
REL_NAME,
INC_NAME
FROM sd_servicecalls,
cdm_organizations,
GSP_STATUS, --view
GSP_CATEGORY, --- view
GSP_REPORTINGUSER, -- view
GSP_REFERENCE, -- view
GSP_AFFECTEDUSER, --view
GSP_ESCALATION, --view
GSP_SERVICE, --view
GSP_IMPACT, --view
GSP_CARESETTING, --view
GSP_RELATED,-- view
GSP_INCSUM --view
WHERE STA_SER_ID = ser_id
AND CAT_SER_ID = ser_id
AND PER1_SER_ID = ser_id
AND REF_SER_ID = ser_id
AND CAL_SER_ID = ser_id
AND ESC_SER_ID = ser_id
AND SER_SER_ID = ser_id
AND ser_caller_org = org_oid
AND IMP_SER_ID = ser_id
AND CAR_SER_ID = ser_id
AND REL_SER_ID = ser_id
AND INC_SER_ID = ser_id
ORDER BY SER_ID DESC;

explain plan

Plan hash value: 1568285926
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |   265 |  1522K|       | 23718   (5)| 00:04:45 |
|   1 |  SORT ORDER BY                           |                        |   265 |  1522K|  4248K| 23718   (5)| 00:04:45 |
|*  2 |   HASH JOIN OUTER                        |                        |   265 |  1522K|       | 23387   (6)| 00:04:41 |
|*  3 |    HASH JOIN                             |                        |   265 |  1003K|       | 18042   (5)| 00:03:37 |
|*  4 |     HASH JOIN                            |                        |   265 |   998K|       | 15915   (5)| 00:03:11 |
|*  5 |      HASH JOIN                           |                        |   265 |   975K|       | 13804   (5)| 00:02:46 |
|   6 |       NESTED LOOPS OUTER                 |                        |   265 |   932K|       | 11100   (5)| 00:02:14 |
|   7 |        NESTED LOOPS                      |                        |   265 |   930K|       | 11070   (5)| 00:02:13 |
|   8 |         NESTED LOOPS                     |                        |   265 |   925K|       | 10802   (5)| 00:02:10 |
|*  9 |          HASH JOIN                       |                        |   265 |   924K|       | 10800   (5)| 00:02:10 |
|* 10 |           HASH JOIN                      |                        |   265 |   881K|       |  9138   (4)| 00:01:50 |
|* 11 |            HASH JOIN                     |                        |   265 |   747K|       |  7188   (4)| 00:01:27 |
|* 12 |             HASH JOIN                    |                        |   265 |   704K|       |  5483   (4)| 00:01:06 |
|  13 |              NESTED LOOPS                |                        |   326 |   701K|       |  4039   (3)| 00:00:49 |
|* 14 |               HASH JOIN                  |                        |   326 |   690K|  3472K|  3710   (4)| 00:00:45 |
|* 15 |                HASH JOIN                 |                        |  3088 |  3434K|  3152K|  2226   (4)| 00:00:27 |
|  16 |                 VIEW                     | GSP_STATUS             |  3088 |  3109K|       |   859   (4)| 00:00:11 |
|  17 |                  SORT UNIQUE             |                        |  3088 |   165K|       |   859   (4)| 00:00:11 |
|* 18 |                   HASH JOIN              |                        |  3088 |   165K|       |   857   (4)| 00:00:11 |
|  19 |                    NESTED LOOPS          |                        |    53 |  2067 |       |    15  (14)| 00:00:01 |
|* 20 |                     TABLE ACCESS FULL    | REP_CODES_TEXT         |    53 |  1484 |       |    15  (14)| 00:00:01 |
|* 21 |                     INDEX UNIQUE SCAN    | OBS_RCD_PK             |     1 |    11 |       |     0   (0)| 00:00:01 |
|  22 |                    TABLE ACCESS FULL     | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|  23 |                 TABLE ACCESS FULL        | SD_SERVICECALLS        | 64092 |  6759K|       |   837   (3)| 00:00:11 |
|  24 |                VIEW                      | GSP_CATEGORY           |  6763 |  6809K|       |   976   (5)| 00:00:12 |
|  25 |                 SORT UNIQUE              |                        |  6763 |   435K|  1032K|   976   (5)| 00:00:12 |
|* 26 |                  HASH JOIN               |                        |  6763 |   435K|       |   865   (5)| 00:00:11 |
|* 27 |                   TABLE ACCESS FULL      | REP_CODES_TEXT         |    34 |   952 |       |    15  (14)| 00:00:01 |
|* 28 |                   HASH JOIN              |                        | 64092 |  2378K|       |   845   (4)| 00:00:11 |
|  29 |                    INDEX FAST FULL SCAN  | OBS_RCD_PK             |  1108 | 12188 |       |     3   (0)| 00:00:01 |
|  30 |                    TABLE ACCESS FULL     | SD_SERVICECALLS        | 64092 |  1689K|       |   837   (3)| 00:00:11 |
|  31 |               TABLE ACCESS BY INDEX ROWID| CDM_ORGANIZATIONS      |     1 |    35 |       |     1   (0)| 00:00:01 |
|* 32 |                INDEX UNIQUE SCAN         | CDM_ORG_PK             |     1 |       |       |     0   (0)| 00:00:01 |
|  33 |              VIEW                        | GSP_CARESETTING        | 45320 |    22M|       |  1440   (6)| 00:00:18 |
|  34 |               SORT UNIQUE                |                        | 45320 |  1150K|  3600K|  1440   (6)| 00:00:18 |
|* 35 |                HASH JOIN                 |                        | 56006 |  1422K|  1208K|  1044   (5)| 00:00:13 |
|  36 |                 TABLE ACCESS FULL        | SD_SCF_TEXT            | 56006 |   546K|       |    47  (13)| 00:00:01 |
|  37 |                 TABLE ACCESS FULL        | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|  38 |             VIEW                         | GSP_SERVICE            | 64022 |    10M|       |  1701   (5)| 00:00:21 |
|  39 |              SORT UNIQUE                 |                        | 64022 |  3188K|  7544K|  1701   (5)| 00:00:21 |
|* 40 |               HASH JOIN                  |                        | 64022 |  3188K|       |   847   (4)| 00:00:11 |
|  41 |                TABLE ACCESS FULL         | CDM_SERVICES           |   250 |  8750 |       |     5   (0)| 00:00:01 |
|  42 |                TABLE ACCESS FULL         | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|  43 |            VIEW                          | GSP_IMPACT             | 64057 |    31M|       |  1944   (4)| 00:00:24 |
|  44 |             SORT UNIQUE                  |                        | 64057 |  3878K|  9080K|  1944   (4)| 00:00:24 |
|* 45 |              HASH JOIN                   |                        | 64057 |  3878K|       |   943   (4)| 00:00:12 |
|* 46 |               TABLE ACCESS FULL          | SD_CODES_LOCALE        |    56 |  2800 |       |   101   (5)| 00:00:02 |
|  47 |               TABLE ACCESS FULL          | SD_SERVICECALLS        | 64092 |   751K|       |   837   (3)| 00:00:11 |
|  48 |           VIEW                           | GSP_AFFECTEDUSER       | 64069 |    10M|       |  1658   (5)| 00:00:20 |
|  49 |            SORT UNIQUE                   |                        | 64069 |  2690K|  6552K|  1658   (5)| 00:00:20 |
|* 50 |             HASH JOIN                    |                        | 64069 |  2690K|       |   914   (4)| 00:00:11 |
|  51 |              TABLE ACCESS FULL           | CDM_PERSONS            |  9730 |   256K|       |    71   (3)| 00:00:01 |
|  52 |              TABLE ACCESS FULL           | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|* 53 |          INDEX UNIQUE SCAN               | SD_SER_ID_UIX          |     1 |     5 |       |     0   (0)| 00:00:01 |
|  54 |         TABLE ACCESS BY INDEX ROWID      | SD_SERVICECALLS        |     1 |    16 |       |     1   (0)| 00:00:01 |
|* 55 |          INDEX UNIQUE SCAN               | SD_SER_ID_UIX          |     1 |       |       |     0   (0)| 00:00:01 |
|  56 |        TABLE ACCESS BY INDEX ROWID       | SD_SER_4K5             |     1 |    10 |       |     1   (0)| 00:00:01 |
|* 57 |         INDEX UNIQUE SCAN                | SD_SE5_PK              |     1 |       |       |     0   (0)| 00:00:01 |
|  58 |       VIEW                               | GSP_REPORTINGUSER      | 64093 |    10M|       |  2700   (5)| 00:00:33 |
|  59 |        SORT UNIQUE                       |                        | 64093 |  4068K|  9592K|  2700   (5)| 00:00:33 |
|* 60 |         HASH JOIN                        |                        | 64093 |  4068K|       |  1662   (5)| 00:00:20 |
|  61 |          TABLE ACCESS FULL               | CDM_PERSONS            |  9730 |   256K|       |    71   (3)| 00:00:01 |
|* 62 |          HASH JOIN                       |                        | 64093 |  2378K|  1760K|  1585   (4)| 00:00:20 |
|  63 |           TABLE ACCESS FULL              | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|  64 |           TABLE ACCESS FULL              | SD_SER_CUSTOM_FIELDS   | 64093 |  1376K|       |   541   (4)| 00:00:07 |
|  65 |      VIEW                                | GSP_REFERENCE          | 64093 |  5445K|       |  2106   (5)| 00:00:26 |
|  66 |       SORT UNIQUE                        |                        | 64093 |  1815K|  5048K|  2106   (5)| 00:00:26 |
|* 67 |        HASH JOIN                         |                        | 64093 |  1815K|  1568K|  1558   (4)| 00:00:19 |
|  68 |         TABLE ACCESS FULL                | SD_SER_CUSTOM_FIELDS   | 64093 |   813K|       |   541   (4)| 00:00:07 |
|  69 |         TABLE ACCESS FULL                | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|  70 |     VIEW                                 | GSP_ESCALATION         | 64093 |  1126K|       |  2121   (5)| 00:00:26 |
|  71 |      SORT UNIQUE                         |                        | 64093 |  1877K|  5048K|  2121   (5)| 00:00:26 |
|* 72 |       HASH JOIN                          |                        | 64093 |  1877K|  1632K|  1561   (4)| 00:00:19 |
|  73 |        TABLE ACCESS FULL                 | SD_SER_CUSTOM_FIELDS   | 64093 |   876K|       |   541   (4)| 00:00:07 |
|  74 |        TABLE ACCESS FULL                 | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|  75 |    VIEW                                  | GSP_RELATEDEVENTS      | 64092 |   122M|       |  5340   (8)| 00:01:05 |
|  76 |     SORT GROUP BY                        |                        | 64092 |  3880K|    36M|  5340   (8)| 00:01:05 |
|* 77 |      HASH JOIN                           |                        |   458K|    27M|       |  2006   (5)| 00:00:25 |
|  78 |       VIEW                               | GSP_EVENTS             | 26624 |   624K|       |  1083   (5)| 00:00:14 |
|  79 |        SORT UNIQUE                       |                        | 26624 |   702K|  1896K|  1083   (5)| 00:00:14 |
|* 80 |         HASH JOIN                        |                        | 26624 |   702K|       |   865   (4)| 00:00:11 |
|  81 |          INDEX FAST FULL SCAN            | CDM_SRE_TO_IX          | 26624 |   286K|       |    20   (0)| 00:00:01 |
|  82 |          TABLE ACCESS FULL               | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
|* 83 |       HASH JOIN                          |                        | 26624 |   988K|       |   918   (4)| 00:00:12 |
|  84 |        TABLE ACCESS FULL                 | CDM_SERV_EVT_RELATIONS | 26624 |   572K|       |    73   (6)| 00:00:01 |
|  85 |        TABLE ACCESS FULL                 | SD_SERVICECALLS        | 64092 |  1001K|       |   837   (3)| 00:00:11 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SER_ID"="R_SER_ID"(+))
   3 - access("ESC_SER_ID"="SER_ID")
   4 - access("REF_SER_ID"="SER_ID")
   5 - access("PER1_SER_ID"="SER_ID")
   9 - access("CAL_SER_ID"="SER_ID")
  10 - access("IMP_SER_ID"="SER_ID")
  11 - access("SER_SER_ID"="SER_ID")
  12 - access("CAR_SER_ID"="SER_ID")
  14 - access("CAT_SER_ID"="SER_ID")
  15 - access("STA_SER_ID"="SER_ID")
  18 - access("SER_STA_OID"="RCD_OID")
  20 - filter("RCT_NAME"='Approved' OR "RCT_NAME"='Awaiting Approval' OR "RCT_NAME"='Awaiting RFC' OR 
              "RCT_NAME"='Awaiting Release' OR "RCT_NAME"='Awaiting Supplier' OR "RCT_NAME"='Closed' OR "RCT_NAME"='Implemented' 
              OR "RCT_NAME"='In Progress' OR "RCT_NAME"='New' OR "RCT_NAME"='On Hold' OR "RCT_NAME"='Resolved')
  21 - access("RCD_OID"="RCT_RCD_OID")
  26 - access("RCD_OID"="RCT_RCD_OID")
  27 - filter("RCT_NAME"='Application' OR "RCT_NAME"='EPR Incident' OR "RCT_NAME"='Environment' OR 
              "RCT_NAME"='Incident' OR "RCT_NAME"='Network' OR "RCT_NAME"='Security' OR "RCT_NAME"='Server')
  28 - access("SER_CAT_OID"="RCD_OID")
  32 - access("SER_CALLER_ORG"="ORG_OID")
  35 - access("SD_SCF_TEXT"."SCT_SER_OID"="SD_SERVICECALLS"."SER_OID")
  40 - access("SER_SRV_OID"="SRV_OID")
  45 - access("SER_IMP_OID"="CDL_COD_OID")
  46 - filter("CDL_LNGPACK_NAME"='Lpc-Sdc-en_GB')
  50 - access("SER_CALLER_PER"="PER_OID")
  53 - access("SD_SERVICECALLS"."SER_ID"="SER_ID")
  55 - access("SD_SERVICECALLS"."SER_ID"="SER_ID")
  57 - access("SE5_SER_OID"(+)="SER_OID")
  60 - access("SCF_PER1_OID"="PER_OID")
  62 - access("SCF_SER_OID"="SER_OID")
  67 - access("SCF_SER_OID"="SER_OID")
  72 - access("SCF_SER_OID"="SER_OID")
  77 - access("SRE_TO"="EVENT_TO_OID")
  80 - access("SRE_TO"="SER_OID")
  83 - access("SRE_FROM"="SER_OID")

Can you please suggest alternative,i am thinking to use global temperory table but insert will be a dificult task as this is transaction system and DML operation can be expected any time

Regards

Rajesh

Report message to a moderator

Re: Performace issue in transaction system [message #556743 is a reply to message #556727]

Wed, 06 June 2012 06:28

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

I'd rewrite the top view to access all the base tables it needs directly and skip the the other views.
When you have views that join lots of other views you usually find the query ends up accessing tables it doesn't need.

Report message to a moderator

Re: Performace issue in transaction system [message #557427 is a reply to message #556727]

Tue, 12 June 2012 13:03

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

How long does it run? Could you please send the execution plan with the runtime statistics.

1. make the following settings

set linesize 1000
set pagesize 1000
set trimspool on
set trim on

2. then run

alter session set statistics_level=all;

3. then run your sql,

4. after that run the following select:

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

5. upload the last formatted output.

If the output is too big, then you can trace alternatively with event 10046 and upload the trace file.

Report message to a moderator

Re: Performace issue in transaction system [message #557432 is a reply to message #556727]

Tue, 12 June 2012 13:35

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

The optimizer is finding it impossible to merge the views, which is why they are being materialized. If you don't want to merge them manually (which is what Cookie is suggesting) you need to find out why they can't be merged automatically. Usually, the reason is that they contain unnecessary set operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on. All those SORT UNIQUEs look a bit suspicious to me.

Report message to a moderator

Re: Performace issue in transaction system [message #557433 is a reply to message #557432]

Tue, 12 June 2012 13:40

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

It seems to be distinct, because of "sort unique" in the explain plan.

Report message to a moderator

Previous Topic:	about V$SQL ......
Next Topic:	query for optimisation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 14:12:56 CST 2025