oracle - Sql tuning using table and view -


i'm having big performance problem following query. , need make fast possible.

view_shipment_order_release got 2 million rows , i'm sure can make better query speed this. application taking 2 minutes run.

select o.order_release_gid   order_release o, order_release_status s   o.order_release_gid = s.order_release_gid    , s.status_type_gid = 'status'    , s.status_value_gid in ('open', 'open-handle')    , o.source_location_gid = '114'    , o.order_release_gid not in     (select v.order_release_gid view_shipment_order_release v      v.order_release_gid = o.order_release_gid)

here's view code:

create or replace view glogowner.view_shipment_order_release select distinct shp.perspective, shp.shipment_gid, ssul.order_release_gid  shipment shp,     shipment_s_equipment_join ssej,     s_equipment_s_ship_unit_join sessuj,     s_ship_unit_line ssul   shp.shipment_gid = ssej.shipment_gid ,     ssej.s_equipment_gid = sessuj.s_equipment_gid ,     sessuj.s_ship_unit_gid = ssul.s_ship_unit_gid ,     ssul.order_release_gid not null

the explain plan: 

1   plan hash value: 1257125198 2     3   -------------------------------------------------------------------------------------------------------------------------------------- 4   | id  | operation                          | name                           | rows  | bytes |tempspc| cost (%cpu)| time     | inst   | 5   -------------------------------------------------------------------------------------------------------------------------------------- 6   |   0 | select statement remote            |                                |   314 | 98596 |       | 35795   (1)| 00:07:10 |        | 7   |   1 |  nested loops                      |                                |       |       |       |            |          |        | 8   |   2 |   nested loops                     |                                |   314 | 98596 |       | 35795   (1)| 00:07:10 |        | 9   |*  3 |    hash join anti                  |                                |   201 | 48441 |       | 35192   (1)| 00:07:03 |        | 10  |   4 |     table access index rowid    | order_release                  | 20104 |   726k|       |  3893   (1)| 00:00:47 | abc123 | 11  |*  5 |      index range scan              | or_source_location_gid         | 20104 |       |       |   157   (0)| 00:00:02 | abc123 | 12  |   6 |     view                           | vw_sq_1                        |  1515k|   294m|       | 31293   (1)| 00:06:16 | abc123 | 13  |*  7 |      hash join                     |                                |  1515k|   144m|       | 31293   (1)| 00:06:16 |        | 14  |   8 |       index storage fast full scan | ind_ssej_sequipgid             | 69218 |   811k|       |    91   (0)| 00:00:02 | abc123 | 15  |*  9 |       hash join                    |                                |  1515k|   127m|    73m| 31195   (1)| 00:06:15 |        | 16  |  10 |        index storage fast full scan| pk_s_equipment_s_ship_unit_joi |  1515k|    56m|       |  3958   (1)| 00:00:48 | abc123 | 17  |* 11 |        table access storage full   | s_ship_unit_line               |  1619k|    75m|       | 18893   (1)| 00:03:47 | abc123 | 18  |* 12 |    index unique scan               | pk_order_release_status        |     1 |       |       |     2   (0)| 00:00:01 | abc123 | 19  |* 13 |   table access index rowid      | order_release_status           |     2 |   146 |       |     3   (0)| 00:00:01 | abc123 | 20  -------------------------------------------------------------------------------------------------------------------------------------- 21    22  predicate information (identified operation id): 23  --------------------------------------------------- 24    25     3 - access("a2"."order_release_gid"="order_release_gid") 26     5 - access("a2"."source_location_gid"='114') 27     7 - access("ssej"."s_equipment_gid"="sessuj"."s_equipment_gid") 28     9 - access("sessuj"."s_ship_unit_gid"="ssul"."s_ship_unit_gid") 29    11 - storage("ssul"."order_release_gid" not null) 30         filter("ssul"."order_release_gid" not null) 31    12 - access("a2"."order_release_gid"="a1"."order_release_gid" , "a1"."status_type_gid"='status') 32    13 - filter("a1"."status_value_gid"='open' or "a1"."status_value_gid"='open-handle')

i'd make sure following indexed:

shipment.shipment_gid shipment_s_equipment_join.s_equipment_gid s_equipment_s_ship_unit_join.s_ship_unit_gid s_ship_unit_line.order_release_gid

the not in might work better not exists.


Comments

Post a Comment

Popular posts from this blog

assembly - 8086 TASM: Illegal Indexing Mode -

this question has answer here: illegal use of register in indirect addressing 1 answer i writing 8086 assembly program needs compile through tasm v3.1. running error can not seem fix. my data segment has following set purposes of keyboard input: parao label byte maxo db 5 acto db ? datao db 5 dup('$') what trying first character entered, first byte of datao: lea dx, datao mov bl, [dx] however, when attempt compile, error: **error** h5.asm(152) illegal indexing mode line 152 "mov bl, [dx]" any appreciated. if matters, running tasm through dosbox (my laptop running 64-bit win7) google hasn't come helpful answers. can post entirety of code if necessary. pretty sure reason can't use dx register pointer. try instead using [si], [di] or [bx]: lea bx, data0 mov al, [bx]
Read more

Java, LWJGL, OpenGL 1.1, decoding BufferedImage to Bytebuffer and binding to OpenGL across classes -

i've been attempting solve problem without luck. have 4 classes, main class, object class, sprite class , image loader class. i'm attempting load png using image loader , using method found here: http://www.java-gaming.org/topics/bufferedimage-to-lwjgl-texture/25516/msg/220280/view.html#msg220280 convert bytebuffer, , bind opengl. the image in it's own seperate resource folder. should being drawn this: ( http://i.imgur.com/j8gbu4c.png ) (32x32), i'm seeing white box has dimensions of image, not actual texture. if has idea of go fix i'd appreciate it. i'm new opengl , looking avoid using external library in order learn how actual code works. thanks. updated! to following, implemented suggestion provided vallentin, quad taking color of first pixel in image. i've attempted implementing image loader provided c.s here . i'm receiving error: javax.imageio.iioexception: error reading png image data @ com.sun.imageio.plugins.png.pngimagereader.r...
Read more

javascript - addthis share facebook and google+ url -

i attempting use addthis create simple url shares whichever current page user on when click share button. of right now, have twitter working. when user clicks, able see url populated in tweet box. when user clicks facebook or google+, url doesn't seem show in share box. <ul class="addthis_toolbox addthis_default_style "> <li><a class="addthis_button_facebook"><img src="/img/facebook-social.png" width="20" height="20" border="0" alt="share" /></a></li> <li><a class="addthis_button_twitter"><img src="/img/twitter-social.png" width="20" height="20" border="0" alt="share" /></a></li> <li><a class="addthis_button_google_plusone_share"><img src="/img/google-social.png" width="20" height="20" border="0" alt="s...
Read more