This discussion is archived
6 Replies Latest reply: Mar 8, 2013 12:59 AM by Marwim RSS

Using data tab causes SD users to be black-listed! Why is this happening?

ThePlutonian Newbie
Currently Being Moderated
Several SQL Developer users in our workgroup were recently black-listed by our IT department for submitting queries that ran for hours against large tables. Many of these users emphatically stated that they had not submitted ANY queries. Finally someone theorized that clicking on the data tab to view a few rows of data may be causing SD to to submit a SELECT * query to Oracle. Yikes! There are now hundreds of users out there using SD with more coming on board every week across the enterprise. Can such an innocuous action of clicking on a tab actually be causing this problem and if so how can we globally prevent SD from doing this?
  • 1. Re: Using data tab causes SD users to be black-listed! Why is this happening?
    Marwim Expert
    Currently Being Moderated
    Hello,

    maybe the user has filtered and sorted the data before. The sort is stored and will be applied when the user opens the data tab again, while the filter is not applied. This leads to a SELECT * query because it sorts the whole table.

    There are feature requests to change this behaviour or to allow filtering before opening the data tab at [SQL Developer Exchange|http://sqldeveloper.oracle.com/].

    Until this is eventually fixed you can only remove every sort
    Tools - Preferences - Database - ObjectViewer Parameters ->  Clear

    But of course this cannot prevent an unintended sorting when someone forgets to remove the sort.

    Regards
    Marcus

    Edited by: Marwim on 07.03.2013 10:42

    This request is about filtering views before accessing the data tab, but it should be the same for tables
    https://apex.oracle.com/pls/apex/f?p=43135:7:22282397317628::NO:RP,7:P7_ID:16241
  • 2. Re: Using data tab causes SD users to be black-listed! Why is this happening?
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    Yes, clicking on the data tab will send a SELECT * from and will create one or more processes on the server to accomodate the request. It's important to educate your users that they should close the grids when they're done browsing the data. Or use filters to result in more restrictive result sets.

    Just because it's easy to click on a table, doesn't mean it's free.

    Your IT folks could also create a resource consumer group for your users such that CPU/Memory and other resources are restricted. These can also be used to 'kick' inactive users have a certain period of time.

    There's not much you can do in SQL Developer to NOT issue a query. Every button, click, or mouse-over is ultimately resulting in one to many queries going against the data dictionary.
  • 3. Re: Using data tab causes SD users to be black-listed! Why is this happening?
    ThePlutonian Newbie
    Currently Being Moderated
    Folks, the main competitor to SQL Developer in our national enterprise is SQL Assistant, which asks the user after retrieving 2000 rows if they want to continue pulling all the data AND tells the user how many rows will be pulled.

    That is precisely what SQL Developer should do out of the box! Then the user can modify that setting and take responsibility from that point on.

    The suggestion that we need to educate all SD users is completely impractical and unsustainable. Anyone in the enterprise's thousands of employees can download SD. There are hundreds of Oracle and Teradata databases and hundreds of enterprise departments. It is unreasonable to believe we can educate all users as to this defect in SD. If SA can do it, then so can SD and until Oracle wakes up to this design problem, then it is absolutely a defect.

    Oracle needs to take a serious look at this feature of SQL Assistant. Otherwise the vendor who contracts all our Oracle maintenance may OUTLAW SQL Developer and force people to use SQL Assistant or possibly move all the Oracle data to Teradata.

    Edited by: The Plutonian on Mar 7, 2013 11:46 AM

    Edited by: The Plutonian on Mar 7, 2013 12:52 PM
  • 4. Re: Using data tab causes SD users to be black-listed! Why is this happening?
    ThePlutonian Newbie
    Currently Being Moderated
    If what folks on this forum are saying is true, that unbeknownst to the user, clicking on the data tab is, in the background, submitting a SELECT * FROM for the entire table, then the SD software should be smart enough and courteous enough to give the user a warning and choice to continue or limit the number of rows delevered! No ifs, ands, or buts about it.

    Hello, are we really in the 21st century here and no one at mighty Oracle understands this basic principle?

    This topic must remain unresolved, unaswered, until I receive a phone call from the director of software development at Oracle to discuss this very obvious and destructive flaw in their software.

    Jesse Lane
    510-987-2637 desk
    510-575-1500 cell
  • 5. Re: Using data tab causes SD users to be black-listed! Why is this happening?
    rp0428 Guru
    Currently Being Moderated
    >
    If what folks on this forum are saying is true, that unbeknownst to the user, clicking on the data tab is, in the background, submitting a SELECT * FROM for the entire table, then the SD software should be smart enough and courteous enough to give the user a warning and choice to continue or limit the number of rows delevered! No ifs, ands, or buts about it.
    >
    The number of rows actually fetched each time from the server uses the 'Sql Array Fetch Size' preference setting. This setting is '50' by default. You access it by 'Tools -> Preferences -> Database -> Advanced.

    The user can choose to scroll through as many rows as desired. If the user performs no scrolling beyond the 50 rows then no more rows will be fetched.
    >
    That is precisely what SQL Developer should do out of the box!
    >
    And that IS what SQL Develper does 'out of the box'.
    >
    Several SQL Developer users in our workgroup were recently black-listed by our IT department for submitting queries that ran for hours against large tables. Many of these users emphatically stated that they had not submitted ANY queries. Finally someone theorized that clicking on the data tab to view a few rows of data may be causing SD to to submit a SELECT * query to Oracle
    >
    A 'SELECT * FROM myTable' query will NOT run for hours. That query will almost always perform a full table scan of the table since rarely will any index contain ALL of the columns of the table including NULL columns (null columns are not indexed by heap indexes; only by bitmap indexes).

    A full table scan (without an ORDER BY to sort the data) simply reads every block of the table. That reading will not prevent any other user/session from doing whatever DML they need to run.

    A user can elect to SORT the data and in that case Oracle will have to sort the ENTIRE table BEFORE it can return any data to the user. A sort can definitely use a lot of resources in the background and can definitely interfere with other users/sessions that need those resources.

    But the SORT issue will apply to ANY tool being used including 'SQL Assistant'. No tool exists that can feed 20 sorted rows to the user BEFORE the data has been sorted. And no tool exists that can exert ANY control over the Oracle sort process. The tool will NOT get any data until the sort is 100% complete.

    Always make sure you are comparing the same use cases when you make comparisons like that.

    1. Basic use of sql developer by selecting a data tab WILL issue a SELECT * FROM . . . query to the backed

    2. If the filter options have NOT been modified to include sorting the query will execute very quickly and fetch the first 50 rows. Additional rows will ONLY be fetched if the user asks for them.

    3. A pure SELECT * FROM query will NOT run for hours on the backend.

    4. ANY tool that issues a SELECT * FROM . . . ORDER BY query will cause Oracle to perform a full table scan.

    5. The likely cause of what appears to be a simple SELECT * FROM query is usually a user that selects the data tab and then goes home for the weekend leaving their machine on. That query will remain the the library cache and the session will remain but there will be no real activity or resources being used.

    If you have ANY evidence that contradicts any of the above please post the specifics.


    As previously mentioned by another responder the user CAN perform
  • 6. Re: Using data tab causes SD users to be black-listed! Why is this happening?
    Marwim Expert
    Currently Being Moderated
    This topic must remain unresolved, unaswered, until I receive a phone call
    The issue is IMHO not a SELECT * query. This is done by any tool I know, when you simply use the data grid of the GUI. The SELECT * itself is not bad since SQL Developer only fetches a configurable amout of data until the user requests more, e.g. by scrolling. But - when a sorting of the data is requested, then a full table scan might occure, when the sorting is done on an unindexed column. This might happen unintended as described above.

    At our company we had a similar issue. I published an article about the problem with the autoremembered sort and informed every user about it and how to delete the sorting. Since then it rarely happens.

    Nevertheless the possibility to filter or edit the sorting before opening the data tab would be a valuable enhancement. You might vote for it using the link in my previous posting.

    Regards
    Marcus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points