Home » SQL & PL/SQL » SQL & PL/SQL » regarding performance of database (oracle 10g,windows)
regarding performance of database [message #322262] Thu, 22 May 2008 23:27 Go to next message
rahul.warhekar@edisphere.
Messages: 5
Registered: May 2008
Location: nagpur
Junior Member
hello all,
Confused 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 #322265 is a reply to message #322262] Thu, 22 May 2008 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: regarding performance of database [message #322287 is a reply to message #322262] Fri, 23 May 2008 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel
Re: regarding performance of database [message #322371 is a reply to message #322287] Fri, 23 May 2008 05:04 Go to previous messageGo to next message
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 #322394 is a reply to message #322371] Fri, 23 May 2008 06:21 Go to previous messageGo to next message
rahul.warhekar@edisphere.
Messages: 5
Registered: May 2008
Location: nagpur
Junior Member
hi,
union all is good but still it is not giving me good plan table Shocked
Re: regarding performance of database [message #322506 is a reply to message #322394] Fri, 23 May 2008 22:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: regarding performance of database [message #322683 is a reply to message #322262] Sun, 25 May 2008 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>so, if any one knows the procedure to be carried out after changing sga_max_size and sga_target

What evidence do you have that the previous values of sga_max_size & sga_target were suboptimal?

What methodology did you use to obtain the new values?

Or did you just grab two arbitrary values for these two parameters which you spontaneously decided to abuse?

Ready, Fire, AIM!



Re: regarding performance of database [message #322692 is a reply to message #322683] Sun, 25 May 2008 23:24 Go to previous messageGo to next message
rahul.warhekar@edisphere.
Messages: 5
Registered: May 2008
Location: nagpur
Junior Member
actually, when i am using the same query with 1 million messages the reply is much quicker than when i use 3 million messages but
the time difference is huge (in my query i am using union and order by clause)

I use some advisor methodology to change those values, i have also
changed sort area size and sort direct writes

[Updated on: Sun, 25 May 2008 23:27]

Report message to a moderator

Re: regarding performance of database [message #322693 is a reply to message #322262] Sun, 25 May 2008 23:31 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You have not posted anything that anyone can reproduce.
You claim problems exist.

Unless or until others can reproduce your claims, you are left with a problem & no solution
Previous Topic: plsql
Next Topic: Get workweek in pl/sql
Goto Forum:
  


Current Time: Mon Nov 11 02:11:35 CST 2024