Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

algorithm with area filling.

WestDraytonMay 2 2010 — edited May 6 2010
I have table T defined below.
All 4 columns contain only positive integer data, minimal value is 1 in all columns.
1. Column pairs (STARTVAL, ENDVAL) define integer range. And we can be sure that there is at least 2 records with same (STARTVAL, ENDVAL) pair. For example pair "2-5" has 2 records and pair 5-10 has 3 records, there is no pair that has only one record. Data surely have been inserted as STARTVAL < ENDVAL, so that there is not pairs with same start and end value.

2. Column pairs (FILLSTART, FILLEND) are suppoused to fill perfectly range (STARTVAL, ENDVAL) for the one (STARTVAL, ENDVAL) pair. Data surely have been inserted as FILLSTART< FILLEND, so that there is not pairs with same start and end value. For example for (STARTVAL, ENDVAL)=(2-5) we have fillers (FILLSTART, FILLEND)=(2-3) and (FILLSTART, FILLEND)=(4-5), so we can say that logical unit (STARTVAL, ENDVAL)=(2-5) is perfectly filled. "Pefectly filled" means that no additional area is filled and that no are is filled more than once. For example region "3-6" has in fillers not needed addional area filled in point 7-7 and area 4-4 intersects for both records there as you see.
with T as
(  --OK. Case 2-5 filled with 2-3 and 4-5.
   select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
   select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
   --NOT OK. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
   select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
   --NOT OK. Case 4-7 filled with 3-4 and 4-7, too much filled in point "4-4" and not needed point "3-3"
   select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
   select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all  
   --NOT OK. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
   select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
   select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
   --OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
   select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
   select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
   --NOT OK. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
   select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
   select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual
)
select * from T
order by 1,2,3,4;
/*
2	5	2	3
2	5	4	5--OK!
3	6	3	4
3	6	4	7--NOT OK: intersects at (4,4), and (7,7) is out of range (3,6)
4	7	3	4
4	7	4	7--NOT OK: intersects at (4,4)
4	8	4	5
4	8	7	8--NOT OK: area (6,6) is not filled
5	10	5	6
5	10	7	8
5	10	9	10--OK!
5	11	5	6
5	11	8	9--NOT OK: area (7,7) not filled
5	11	10	11
*/
I want to display all (STARTVAL, ENDVAL) pairs that doesnt have "Perfect filling" in columns (FILLSTART, FILLEND). The query should output pairs that i have marked as "NOT OK"..or it should marked only those which i have marked as "OK"- doesn't matter which output, i will later customize query i think. My point is to determine if the filelrs fill perfectly the region or not, if they don't fill then i raise business error later.
How to write such query?

My initial query looks like this:
select * from T T2,
(select T.STARTVAL, T.ENDVAL, min(T.FILLSTART) MINFILLSTART, max(T.FILLEND) MAXFILLEND, 
SUM(T.FILLEND - T.FILLSTART) SUMFILLER from T
group by T.STARTVAL, T.ENDVAL) MINMAX
where T2.STARTVAL = MINMAX.STARTVAL and T2.ENDVAL = MINMAX.ENDVAL
   --The leftmost Filler should start with same value as T2.STARTVAL:
   and T2.STARTVAL = MINMAX.MINFILLSTART
   --The rightmost Filler should end with same value as T2.ENDVAL:
   and T2.ENDVAL = MINMAX.MAXFILLEND
order by 1, 2, 3, 4;
It shows values MINFILLSTARTand MAXFILLEND which define fillers minimal and maximal value, but this is not useful still. Maybe "SUMFILLER" could be useful?

Edited by: CharlesRoos on May 2, 2010 6:30 AM

Edited by: CharlesRoos on May 2, 2010 6:36 AM

Edited by: CharlesRoos on May 2, 2010 6:39 AM

Edited by: CharlesRoos on May 2, 2010 6:48 AM
This post has been answered by Aketi Jyuuzou on May 3 2010
Jump to Answer

Comments

thatJeffSmith-Oracle

What instant client are you using?

What if you just use JDBC Thin (no client)?

Are you a member of a resource consumer group that has an idle/disconnect plan directive in play?

DeeTeeAkl

Jeff,

Thanks for getting back to me.

1. The version I am using is instantclient_19_6

2. I had issues with maintaining connection when on my windows7 laptop and I raised an SR.  Support told me to install instantclient and setup sql developer to use it.  This resolved the issues I had at the time so I assumed I needed it on my new setup.  I will try without it.

3. I am not entirely sure what you mean by resource consumer group.  I am connecting to our on premise Ebusiness suite environments if that helps.

DeeTeeAkl

BTW I tried switching back to thin client and I have the same issue.  Sql developer is working fine in that it responds, I can open edit windows, open new connections and run queries .. but the connections I had opened no longer function.  If I view task progress it shows a message like "statementRunner Task (Running)".. the task cant be cancelled (option greyed out).  The only way to recover is kill the process and start again.

Glen Conway

Some of your comments here open more questions, so it would be nice to clarify a couple of things.  You say you have switched between the thin and thick JDBC drivers while using jdk1.8.0_261 and SQL Dev 20.2.

1. Does that mean you are aware of, and have dealt appropriately with, the problem that 20.2 has with using u261?

    That is, u261 no longer includes msvcr100.dll as noted in

2. You also say

         but the connections I had opened no longer function

    Does that mean you switched between thin and thick without restarting SQL Dev?  Some settings changes take effect without a restart, but I am not certain if a driver change is one of those.

As for Oracle Database resource consumer groups, IDLE_TIME limits, and so on, it would be best to ask those administering your environment.

DeeTeeAkl

1) Yes I am fully aware of this. 

I have no problem with connecting to an instance.  The problem seems to be that SQL developer cannot maintain the connection.  As per Jeff's suggestion I switched back to thin client and this has made no difference.

2) There are no database resource consumer groups or any idle time control that would be causing this issue.

On further testing this issue can occur immediately after connecting to the database.  Basically the SQL session stops working, I dont get any error message or warning that its going to happen.  I can be trying to open a package, running a query, basically any operation that requires the connection to be open and one second it works and the next it stops and the only option I have is to kill the application and try again.  The exact same operation will then work with no issue .. until the next time.

gfury

We seem to have the same issue.

SQL Developer 20.2 (Win10) configured to use the OCI driver of the 19c client.  We must use this configuration because we use Radius authentication.

Connections intermittently drop and hang.  Once a query hangs, the only way to resolve the issue is to kill SQL Developer via the task manager.

a connection drops, it doesn't hang

the UI can hang if the connection becomes unavailable

You need to find out why your connections are dropping. It might help to see if this happens to you in SQLcl as well.

gfury

Still wrestling with this.   It's intermittent.

On the DB side the session is waiting for "SQL*Net break/reset to client"

I'm going to try sqlcl with the oci option.

stom

I have the same problem as you. Win 10, SQL Developer 20.2 and I use Radius authentication. The database connection drops several times in SQL Developer.

I also use Toad and I don't have this problem there.

gfury

That's our environment.  Migrating from TOAD to SQL Developer.  Using Radius authentication (which requires using OCI).  TOAD has no issues. 

The difficult thing is that the issue is intermittent.

We've got several people at out site trying to figure this out ....

kgilli01

We recently updated to 20.2 and started experiencing the same types of issues.  SQL Developer randlomly seems to hang (unresponsive) have to close with task manager.

We ran perfectly fine on earlier releases with windows 10.  Nothing else changed except going to 20.2 with JRE bundled.

Glen Conway

Anyone experiencing a hang on SQL Developer should...

1) Open a Cmd window. Do not kill anything using Task Manager

2) Run a Java utility called jps to find the process ID (PID) for oracle.ide.osgi.boot.OracleIdeLauncher

3) Run a Java utility called jstack for that PID to get the JVM full thread. Best to redirect output to a file so nothing is lost by scrolling past the console size limit.

4) Post the output back here (using the Advanced Editor so formatting is retained).

Note that the bundled JRE no longer contains jstack and jps as it did back in the days of Java 6 and 7 (pre-4.1 SQL Developer), so you will need to have a full Java JDK installed somewhere.

Edit: Unless you use the alternate method of

1) Run sqldeveloper\sqldeveloper\bin\sqldeveloper.exe from a Cmd window that has its console size limit configured to be large

2) Use Ctrl+Break to get the JVM full thread dump.

Tim St.

Full dump files included per your notes in the links below.

This dump was taken when the SQL Developer UI has locked and becomes unresponsive.  The message states the connection is close, that is fine, but the UI breaks down.

Note: Same workstation has a SQL*Plus connection simultaneously remaining active without interruption.

"AliveLockThread.CRP0-OAQCPU-APPS" #503 prio=6 os_prio=0 tid=0x0000000028709800 nid=0x4b28 waiting for monitor entry [0x0000000062f8f000]

  java.lang.Thread.State: BLOCKED (on object monitor)

at oracle.jdbc.driver.PhysicalConnection.isClosed(PhysicalConnection.java:2573)

- waiting to lock <0x000000008c967260> (a oracle.jdbc.driver.T2CConnection)

at oracle.jdbc.driver.T2CConnection.isClosed(T2CConnection.java:80)

at oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy.isClosed(Unknown Source)

at oracle.javatools.db.AbstractDatabase$AliveTester.run(AbstractDatabase.java:487)

- locked <0x000000008c972820> (a oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy)

at java.lang.Thread.run(Thread.java:748)

https://gitlab.com/Sthilaire/sqldevelopernotes/-/raw/master/locking_issue/2020.09.20-sqldev_dump_3.txt

Details:

Oracle Instant Client 19.8

SQL Dev Version 20.2.0.175

Build 175.1842

Glen Conway

OK, perfect.

The UI is hanging since the main event thread (AWT-EventQueue-0) is

- waiting to lock <0x000000008c967260> (a oracle.jdbc.driver.T2CConnection)

which is locked by

"QueryThread" #502 ...

and also blocking

"AliveLockThread.CRP0-OAQCPU-APPS" #503 ...

   java.lang.Thread.State: BLOCKED (on object monitor)

        at oracle.jdbc.driver.PhysicalConnection.isClosed(PhysicalConnection.java:2573)

        - waiting to lock <0x000000008c967260> (a oracle.jdbc.driver.T2CConnection)

        at oracle.jdbc.driver.T2CConnection.isClosed(T2CConnection.java:80)

as mentioned in your post.

So, like @"thatJeffSmith-Oracle" said,

the UI can hang if the connection becomes unavailable

You need to find out why your connections are dropping.

Cheers

Edit: And use SQLcl (which also uses JDBC) as basis for comparison rather than SQL*PLus.

Tim St.

so..  what I am trying to understand is why SQL Developer is so easy to drop a connection where a SQL*Plus (or SQLcl) window will continue without issue right next to it.  What technology will make SQL Developer operate like a thick client?

Using JDBC - Still an issue

Using Oracle Client - Still an issue

Using OCI - Still an issue

I will admit that I can have more than one connection open at a time, but that should not impact the individual connection pool.

What can we do debug SQL Developer dropping connections?

What can we do to SQL Developer to have it recover from a lost connection from the UI side?

What parameter can we set a to tell Java "please stop trying so hard" and just throw an error rather than lock an entire program?

This impacts the perception of SQL Developer and it's stability.

-- Tim St.

thatJeffSmith-Oracle

sqlcl - no JDev, no GUI, much simpler code base than SQL Developer

SQLPlus pure OCI, no JDBC at all

We don't have connection pools in SQL Developer or SQLcl

What you can do - keep posting these jstack dumps - we've identified and fixed more than several UI threads being locked when they shoudn't be.

Investigating the dropped connections themselves, we have theories that we're tracking down, but on our side we're not seeing connections dropping like some of our customers are, so debugging that gets challenging.

Glen Conway
what I am trying to understand is why SQL Developer is so easy to drop a connection

I think that statement is your assumption rather than a fact.  I suppose it is possible there is a bug in SQL Developer or the JDBC driver causing connections to close, but I would think it more likely that something on the database or network-side is responsible. Perhaps others will comment on the possible causes / reasons for such occurrences.

gfury

I'll toss this into the mix ...

We are all working remote and connected to our databases via VPN (and the firewall infrastructure that goes with that).  The issue does not seem to occur on machines that are directly on the corporate network (more testing needed on this since it's intermittent).

Furthermore, we had a similar issue recently when internal firewalls were dropping out-of-band break request packets.  The fact that when I get this issue I see the wait event as "SQL*Net break/reset to client" leads me to believe that it could be network-related.

User_4EKLH

So glad to see this discussion here.  I am having similar issues.  Also on a Win10 machine running SQL 20.2.

I will run a query and everything is fine, go back and make an adjustment and run it again, and it is gone.  My buttons will gray out and it just seems like nobody is home.  There is not a discernable pattern to how often I run the queries, or how many I run.  It is very inconsistent.  Yesterday I had it happen maybe four or five times throughout the day, but today, it has happened 10 times and it isn't even lunch time yet.

I have found that if I kill my VPN connection, Developer will react ( it is displeased that the connection is gone ) but sorts itself out as soon as I re-establish the connection.  It's faster than killing the process, but having to dump my VPN connection for every two or three queries I run is aggravating to say the least.

I will try to do the process above as described by Mr. Conway next.

kgilli01

in my situation, this only started happening after I upgraded to 20.2.x

Prior to that it was rock solid on 18.4.x

for this reason I feel this is a bug vs connections dropping by our network or client.

I have rolled back and still rock solid.

User_4EKLH

First query of the day and it went away and hasn't come back yet. I have attached the jstack dump.
JStackDump.202009300945.txt (78.43 KB)

gfury

Where can I download version 18.4? I've had it in the past, but I removed it.

Glen Conway

Where can I download version 18.4? I've had it in the past, but I removed it.
You can't get there from here (or anywhere). The previous version backward chaining links only go back to 19.1...
https://www.oracle.com/tools/downloads/sqldev-v191-downloads.html

Glen Conway

First query of the day and it went away and hasn't come back yet. I have attached the jstack dump.
A jstack dump is informative if there is an application hang, but less useful for a slow or blocked query on the DB side.
What version of SQL Developer? It looks like Code "InsightThread" and Thread-19 and Thread-40 through Thread-44 are all parked waiting for <0x00000000e1f76e28> (a java.util.concurrent.locks.ReentrantLock$NonfairSync), but we do not see what is actually holding that in the dump.

gfury

Try this in your sqlnet.ora file:
DISABLE_OOB=ON
This works for us as our issue seems to be firewalls that are dropping break packets as a security measure.
My understanding is that setting DISABLE_OOB=ON configures the client to use polling rather than break packets to implement cancel operations.

User_4EKLH

@glen-conway I am using Version 20.2.0.175 on a windows 10 machine.
@gfury I cannot find a sqlnet.ora file. Only a sqlnet.log file.

you would need to implement that via a jvm flag vs sqlnet.ora unless you're using an OCI (thick) connection

-Doracle.net.disableOob=true

gfury

We are using the OCI client (in order to get Radius authentication). That is what seems to cause this issue.
Tools -> Preferences -> Database -> Advanced -> Use Oracle Client -> Use OCI/Thick driver

Edit (or create) a sqlnet.ora in $ORACLE_HOME/network/admin

gfury

This may prove to be very useful as well. Thank you !!!

User_4EKLH

I have implemented that fix. I'll watch it over the next week or so. Thank you!

User_F2O77

We (entire org) have experienced this as well. We are utilizing VM clients and using the same version and build of Oracle SQL Developer. I have made the suggested entry in my client sqlnet.ora but it has not resolved this issue.

Paavo

Also experiencing this with connection to db via vpn over home wifi, mobile data etc. in mac. Somehow this feels like having starvation in dns lookups, so should we try fixed ip's?
BTW while writing this comment the "post comment"-button keeps 'greying out' once while and the comment editor 'hangs' also for couple of seconds. Somehow identical behaviour, maybe the connection drops similarly for sqldeveloper--db and gui gets unresponsive? And personally I really miss plsql package editor in sqldeveloper and when it hangs the last edit is lost.
Maybe this tells something about how poor connection and/or heavy load there is due cofid19 and whole family sharing the same connection.
image.pngbtw. havent noticed these with sdw :)
rgrds Paavo

1 - 32
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 3 2010
Added on May 2 2010
17 comments
2,181 views