performance - Can anyone explain how the oracle "hash group" works? -

-

i've come across feature of doing large query in oracle, changing 1 thing resulted in query used take 10 minutes taking 3 hours.

to briefly summarise, store lot of coordinates in database, each coordinate having probability. want 'bin' these coordinates 50 metre bins (basically round coordinate down nearest 50 metres) , sum probability.

to this, part of query 'select x,y,sum(probability) .... group x,y'

initially storing large number of points probability of 0.1 , queries running reasonably ok, taking 10 minutes each one.

then had request change how probabilities calculated adjust distribution, rather of them being 0.1, different values (e.g. 0.03, 0.06, 0.12, 0.3, 0.12, 0.06, 0.03). running same query resulted in queries of 3 hours.

changing 0.1 brought queries 10 minutes.

looking @ query plan , performance of system, looked problem 'hash group' functionality designed speed grouping in oracle. i'm guessing creating hash entries each unique x,y,probability value , summing probability each unique x,y value.

can explain behaviour better?

additional info

thanks answers. allowed me verify going on. i'm running query , tempseg_size v$sql_workarea_active @ 7502561280 , growing rapidly.

given development server i'm running on has 8gb of ram, looks query needs use temporary tables.

i've managed workaround changing types of queries , precalculating of information.

hash group (and hash joins, other operations such sorts etc.) can use either optimal (i.e. in-memory), one-pass or multi-pass methods. last 2 methods use temp storage , slower.

by increasing number of possible items might have exceeded number of items fit in memory reserved type of operations.

try looking @ v$sql_workarea_active whilst query running, see if case. or @ v$sql_workarea historical information. give indication of how memory and/or temp space needed operation.

if turns out actual problem - try increasing pga_aggregate_target initialization parameter, if possible. amount of memory available optimal hash/sort operations around 5% fraction of pga_aggregate_target.

see performance tuning guide more detail.


Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more