Performance issues on 2 different users.

I have 2 different users, both tied to 2 different tablespaces.

Both of these users have the same exact privileges.

I also have a public database link to our ERP (mssql)

USER A = No Performance issues locally or over dblink

USER B = a query that i perform with user A and excutes in 0.281 seconds, hangs here. I created a table, and importing a CSV via SQL Developer also hangs, query to dblink also hangs.

i've done the general administration and performance tuning. I have trc turn on at the DB gateway but the issue is beyond that.

Here are the permisions for both

select * from session_privs;

CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
SELECT ANY DICTIONARY

Open in new window

FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Are the tablespaces the two users are using different sizes?
Are they on different disks?
Could the one that 'hangs' be extending itself?  Autoextend is expensive.
0
FutureDBA-Author Commented:
auto extend is on, on the TS that's hanging, i dont remember if it is in the good TS, is there any way to check?

this is what i used for the hanging TS

create tablespace TS_EE
  logging
  datafile '/u01/app/oracle/product/11.2.0/xe/dbs/ts_ee.dbf' 
  size 32m 
  autoextend on 
  next 32m maxsize 2048m;

Open in new window

0
FutureDBA-Author Commented:
expanding tables on sysdba via sqldeveloper also hangs, works fine on my main TS
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
>> i dont remember if it is in the good TS

I do not understand what you mean by 'good'.

>>this is what i used for the hanging TS

How much data are you loading in?  32Meg is really nothing as far as size goes.  So for every 32Meg of data, you need to allocate another 32Meg of space to the data file.  

This can be the cause.

How does the size of this tablespace compare to the other one in terms of size?
0
slightwv (䄆 Netminder) Commented:
>>expanding tables on sysdba

Sorry but I don't know what this means either.
0
FutureDBA-Author Commented:
the other DB has maybe 400 mb of data? <-- "good" db, the one that works with no problems.

>>expanding tables on sysdba  <<

Sorry but I don't know what this means either.<<


if i log in to the sysdba account with sqldeveloper. When i expand to see tables, sql developer hangs
0
slightwv (䄆 Netminder) Commented:
>>the other DB has maybe 400 mb of data?

Other DB or tablespace?  This is an important distinction.


Assuming the users are in the same database but different tablespaces as described in the initial question:

Unless you have very slow disks I wouldn't expect 400 Meg of data to cause a long hang even with the extending of the data file.

Just for grins post the results of:
ls -al /u01/app/oracle/product/11.2.0/xe/dbs

I would also check the alert log to see if there are some messages in there that might show you what is happening.

I wonder if is something like the archived redo is full and the database is waiting for space to be freed up.
0
DavidSenior Oracle Database AdministratorCommented:
Does this hang occur every time, consistently?  And, approximately how long of a period (minutes, hours, days).  

Are you using SQL*Developer for both users, or only B?  IOW what happens when you run the "same" query from B but using SQL*Plus?

Usual reminder to check your alert.log for captured errors....

I would be curious if your connection pipeline is identical for both.  For instance, are both users confirmed to be connecting with the same TNSNAMES.ora & SQLNET.ora?

Does either A or B own the tables or views being queried?  

More to come...but reply with what you have :)
0
FutureDBA-Author Commented:
Other tablespace. sorry,

drwxr-xr-x.  2 oracle dba          4096 Oct 15 12:29 .
drwxr-xr-x. 26 oracle dba          4096 Jan  4  2013 ..
-rw-rw----.  1 oracle dba          1544 Sep 12 12:23 hc_XE.dat
-rwxr-xr-x.  1 oracle dba          2851 Aug 29  2011 init.ora
-rw-r-----.  1 oracle dba            24 Jan  4  2013 lkXE
-rw-r-----.  1 oracle oinstall     1536 Jan  4  2013 orapwXE
-rw-r-----.  1 oracle dba          2560 Oct 15 16:10 spfileXE.ora
-rw-r-----.  1 oracle dba      33562624 Oct 15 11:41 ts_ee.dbf
-rw-r-----.  1 oracle dba      33562624 Oct 15 11:41 ts_ee2.dbf
-rw-r-----.  1 oracle dba      33562624 Oct 15 12:29 ts_ps.dbf
-rw-r-----.  1 oracle dba      33562624 Oct 15 11:41 ts_sales.dbf

Open in new window


How do i check the archived redo being full, it's something that makes sense
0
slightwv (䄆 Netminder) Commented:
>>How do i check the archived redo being full, it's something that makes sense

Check the alert log.

I find it very strange that all of the tablespaces you created are the exact same size.

Not really part of the question but is there a reason you created them in the dbs folder instead of with the rest of them?

You can see them all with:
select file_name from dba_data_files;
0
FutureDBA-Author Commented:
>>I find it very strange that all of the tablespaces you created are the exact same size.

no particular reason, i start them small and let them auto expand as need be.




so when I use sql*plus, i get no performance issues...

eg;

i can query remote database from sql*plus with no problem, via sqldeveloper that same process hangs..

The thing is, with a specific user (the one i've used for development for the last year or so) I can query remote databases from sqldeveloper without a problem.

another eg;,

I created a materialized view via sql*plus, I can query the table for that view...

from sqldeveloper, i see the table when i expand the tables tree node. but when i select * from mvtable.. also hangs..


i even went as far as trying same queries from apex, all work fine.

So then i figured, maybe something happened to my install at work.. but when I got home, I tried from my home computer (where i am now) and i am getting the SAME exact results with sqldeveloper..

SQLDEVELOPER
-------------------------------
USERA on TABLESPACE A <---- all Fine
USERB on TABLESPACE B <---- HANGS
sys as sysdba <---- HANGS

SQL*PLUS <---all fine globally...

I looked into the archive logs, nothing out of the norm.

>>Does either A or B own the tables or views being queried?  

they own the tables being queried
0
slightwv (䄆 Netminder) Commented:
>>so when I use sql*plus, i get no performance issues...

So it seems specific to sql developer not the database?

On the client where you are running sql developer, open up performance monitor.  Add counters for cpu, memory and disk io.

Then launch sql developer.  Then try something that 'hangs' and watch the counters.  See if something is maxing out some resource.
0
DavidSenior Oracle Database AdministratorCommented:
Are there any other users of SQL*Developer (check for identical release and configuration) where you can replicate the symptom?  And are you on a relatively current release?  Before I would mess with monitoring, I would consider deinstalling and reinstalling the SQL*Developer client.
0
FutureDBA-Author Commented:
SQL Developer isnt "installed" When you download SQLDeveloper, you just run the executable. As long as you have jdk, you should be good to go.

"slightvw"
>> So it seems specific to sql developer not the database?

I still think that it's related to the database, some sort of interaction with the database, simply because this is now happening to my home computer and work computer, with the same circumstances.
0
slightwv (䄆 Netminder) Commented:
But if it only happens with SQL Developer and not sqlplus, then I'm not seeing how it is the database.

If there is nothing in the alert log then it likely isn't a severe problem.

If sqlplus runs a query fine and SQL developer hangs, it likely isn't a database resource problem.

I was thinking that you just ran out of space for XE but that should be logged in the alert log.
0
FutureDBA-Author Commented:
cdc tablespace is the tablespace that works under sql developer.

I didnt find anything in the logs but ill check again, i was looking manually, is there any specific error that I could search for ?


# ls -la /u01/app/oracle/product/11.2.0/xe/dbs/

-rw-r-----. 1 oracle dba        402661376 Oct 16 09:40 cdc.dbf
-rw-r-----. 1 oracle dba          9748480 Oct 16 10:30 control.dbf
-rw-r-----. 1 oracle dba       1520443392 Oct 16 10:30 sysaux.dbf
-rw-r-----. 1 oracle dba        471867392 Oct 16 10:25 system.dbf
-rw-r-----. 1 oracle dba      15003025408 Oct 16 08:31 temp.dbf
-rw-r-----. 1 oracle dba        723525632 Oct 16 10:30 undotbs1.dbf
-rw-r-----. 1 oracle dba        104865792 Oct 16 09:11 users.dbf


# ls -la /u01/app/oracle/oradata/XE/

-rw-rw----.  1 oracle dba           1544 Oct 15 23:20 hc_XE.dat
-rwxr-xr-x.  1 oracle dba           2851 Aug 29  2011 init.ora
-rw-r-----.  1 oracle dba             24 Jan  4  2013 lkXE
-rw-r-----.  1 oracle oinstall      1536 Jan  4  2013 orapwXE
-rw-r-----.  1 oracle dba           2560 Oct 16 00:33 spfileXE.ora
-rw-r-----.  1 oracle dba       33562624 Oct 16 09:11 ts_ee.dbf
-rw-r-----.  1 oracle dba       33562624 Oct 16 09:11 ts_ee2.dbf
-rw-r-----.  1 oracle dba       33562624 Oct 16 09:11 ts_ps.dbf
-rw-r-----.  1 oracle dba      201334784 Oct 16 09:40 ts_sales.dbf

Open in new window

0
FutureDBA-Author Commented:
do note that ts_sales.dbf has grown since i originally posted the outputed, I was able to create materialized view from sql*plus, i can query that view from sql*plus, same query hangs in sqldeveloper
0
slightwv (䄆 Netminder) Commented:
OK, now I'm consufed.  Above when I asked for:
ls -la /u01/app/oracle/product/11.2.0/xe/dbs/

The files you posted then don't match what you just posted.

Please clarify.
0
FutureDBA-Author Commented:
mistake on my end,  sorry for the confusion

This is accurate

[root@db alert]# ls -la /u01/app/oracle/oradata/XE/
total 17078632
drwxr-xr-x. 2 oracle oinstall        4096 Jan  4  2013 .
drwxr-x---. 3 oracle oinstall        4096 Jan  4  2013 ..
-rw-r-----. 1 oracle dba        402661376 Oct 16 09:40 cdc.dbf
-rw-r-----. 1 oracle dba          9748480 Oct 16 10:39 control.dbf
-rw-r-----. 1 oracle dba       1520443392 Oct 16 10:31 sysaux.dbf
-rw-r-----. 1 oracle dba        471867392 Oct 16 10:25 system.dbf
-rw-r-----. 1 oracle dba      15003025408 Oct 16 08:31 temp.dbf
-rw-r-----. 1 oracle dba        723525632 Oct 16 10:35 undotbs1.dbf
-rw-r-----. 1 oracle dba        104865792 Oct 16 09:11 users.dbf

Open in new window



[root@db alert]# ls -la /u01/app/oracle/product/11.2.0/xe/dbs/
total 294976
drwxr-xr-x.  2 oracle dba           4096 Oct 15 23:20 .
drwxr-xr-x. 26 oracle dba           4096 Jan  4  2013 ..
-rw-rw----.  1 oracle dba           1544 Oct 15 23:20 hc_XE.dat
-rwxr-xr-x.  1 oracle dba           2851 Aug 29  2011 init.ora
-rw-r-----.  1 oracle dba             24 Jan  4  2013 lkXE
-rw-r-----.  1 oracle oinstall      1536 Jan  4  2013 orapwXE
-rw-r-----.  1 oracle dba           2560 Oct 16 00:33 spfileXE.ora
-rw-r-----.  1 oracle dba       33562624 Oct 16 09:11 ts_ee.dbf
-rw-r-----.  1 oracle dba       33562624 Oct 16 09:11 ts_ee2.dbf
-rw-r-----.  1 oracle dba       33562624 Oct 16 09:11 ts_ps.dbf
-rw-r-----.  1 oracle dba      201334784 Oct 16 09:40 ts_sales.dbf
[root@db alert]#

Open in new window

0
slightwv (䄆 Netminder) Commented:
Your TEMP file looks a little large.  Still don't think it's a database issue if sqlplus works fine.

But just in case, check your disk space.

Going from memory but try:
df -k
0
FutureDBA-Author Commented:
diskspace is fine..

[root@db alert]# df
Filesystem          		 1K-blocks      Used 	   Available 	Use% 	Mounted on
/dev/mapper/vg_db-lv_root	 32712576  	21076944   9973888  	68% 	/
tmpfs                  		 4026112    	683372     3342740  	17% 	/dev/shm
/dev/sda1               	 495844     	75268      394976  	17% 	/boot

Open in new window

0
slightwv (䄆 Netminder) Commented:
Still don't think it's a database issue.

I would be looking at the client running SQL Developer or SQL Developer itself.
0
FutureDBA-Author Commented:
I almost didnt think it's a database issue either, until i started getting the same errors from 3 different machines running SQL Developer, 2 at work, 1 at home.


I am going to check if I am having issues with Toad
0
FutureDBA-Author Commented:
extrmely uncomfortable.. i've only worked with sql developer, sql*plus makes like harder
0
slightwv (䄆 Netminder) Commented:
GUIs are fine but you really need to understand what the GUI is doing for you.  There are times, like this, when the command line may be all you have.  If you don't know what the GUI did for you, you will not be able to work from the command line.  This is why you feel it is harder.

Personally, I never use the GUI.  I've been command line with Oracle for over 20+ years.

Now, this isn't to say that we need to figure out what is going on with SQL Developer.  I would be interested in what you find with Toad as a comparison.

It might be as simple as the Java version you are running.
0
FutureDBA-Author Commented:
i don't use the gui for doing functions for me, it's just more comfortable on the eyes when you have your sql color coded, or when you can browse the data in a table;

verified that oracle is working fine with toad.

i thought about Java last night, but i dont remember updating java in a while, i also checked it it auto updates, but could not find the answer. in win7 i can turn on/off java auto update from control panel, not the case with either win8 or the latest java versions
0
slightwv (䄆 Netminder) Commented:
I would focus on SQL Developer.  Check Java.

As dvz suggested above:  Delete the SQL Developer you have and go get a 'new' one.  I would start with the SQL Developer that comes with Java.
0
FutureDBA-Author Commented:
tried a "new" download of sqldeveloper, it comes prepackaged with java

same results.
0
slightwv (䄆 Netminder) Commented:
Try uninstalling all Java components from the machine and try SQL Developer with the packaged java.
0
FutureDBA-Author Commented:
I'm going to take it a step further. im going to install it on a brand new build that has never had java installed.. ill be back in a bit with results
0
FutureDBA-Author Commented:
brand new windows 7 install... same results..


im back to thinking it's oracle
0
slightwv (䄆 Netminder) Commented:
Open up windows Performance Monitor on the client and database server, add CPU, memory and disk counters.

Launch SQL Developer.  See what reacts and which machine it reacts on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Add network usage counters as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.