regarding performance of database [message #322262] |
Thu, 22 May 2008 23:27 |
rahul.warhekar@edisphere.
Messages: 5 Registered: May 2008 Location: nagpur
|
Junior Member |
|
|
hello all,
I am executing one query as
select count(*) from(select a.rec outrec from table_name1 a where a.rel=0 union select b.rec outrec from table_name1 a,table_name1 b where a.rec=b.rel)
it is executing properly but when i execute query
select count(*) from(select a.rec,a.a1,a.b1,a.c1,a.d1,a.f,a.e1,a.f1,a.g1,a.h1,a.i,a.j1,a.k1,a.l1,a.m1,a.n,a.o1,a.p1,a.q1,a.r1 outrec from table_name1 a where a.rel=0 union select b.rec,a.a1,a.b1,a.c1,a.d1,a.f,a.e1,a.f1,a.g1,a.h1,a.i,a.j1,a.k1,a.l1,a.m1,a.n,a.o1,a.p1,a.q1,a.r1 outrec from table_name1 a,table_name1 b where a.rec=b.rel)
i need 20 times more time to get output than in first query.
So i am thinking it may be tablespace problem or may there be any other issue for consuming time so please help me to solve it
really in trouble
thanks & regards
rahul
|
|
|
|
|
Re: regarding performance of database [message #322371 is a reply to message #322287] |
Fri, 23 May 2008 05:04 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
When you use UNION, it sorts the results and removes duplicates.
Your second query uses a lot more columns. This generates a lot more data to sort, which fill up the memory and then spills to disk. This IO is slow.
If you use UNION ALL instead of UNION, it will not remove duplicates and will be much faster.
Also a possibility is that the first query - because it did not use many columns - could be satisfied completely from indexes without accessing the table. Table accesses are expensive because your tables contain a lot of columns. A query where all columns are found in the index can avoid the table access.
Ross Leishman
|
|
|
|
Re: regarding performance of database [message #322506 is a reply to message #322394] |
Fri, 23 May 2008 22:10 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Ahhh, yes I see what you mean.
Hang on. No I don't! You have posted any information that could be considered helpful in any way.
Read this thread from the beginning and pretend you are one of us out in Internet-land. Can you see how difficult it is for us to understand?
Ross Leishman
|
|
|
Re: regarding performance of database [message #322518 is a reply to message #322262] |
Sat, 24 May 2008 00:39 |
rahul.warhekar@edisphere.
Messages: 5 Registered: May 2008 Location: nagpur
|
Junior Member |
|
|
Hello ,
I Understand and i really feel sorry for that,
actually what is happening is my system has got
very unstable an i am not getting the actual reason
for that.
so, actually what i would like to know now is
when i am connecting oracle through python and retrieving
some data from database it is consuming huge time to
get displayed in UI.
so i want to know whether oracle first put all mathing
conditions into buffer and then starts displaying or it
follows some other procedure
or it may be the problem related to code only.
thanks
|
|
|
Re: regarding performance of database [message #322540 is a reply to message #322518] |
Sat, 24 May 2008 04:49 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle retrieves data to the client as it becomes available. Any buffering is controlled by the client. However, if you run a statement that performs a sort (eg UNION), then Oracle will gather all of the matching rows into temp space and sort them before returning any.
If you are having tuning problems and want to try some SQL tuning, then try running the SQLs from SQL*Plus. When they work well there, then try Python.
Ross Leishman
|
|
|
Re: regarding performance of database [message #322613 is a reply to message #322262] |
Sun, 25 May 2008 02:56 |
rahul.warhekar@edisphere.
Messages: 5 Registered: May 2008 Location: nagpur
|
Junior Member |
|
|
thanks,
your guidance help me a lot and now i got what i need to do.
Now i have to set oracle system global area parameters and for
that i need to edit initorcl.ora & spfile.ora
so, if any one knows the procedure to be carried out after
changing sga_max_size and sga_target
any information will be greatly appreciated
|
|
|
|
|
|