10 Replies Latest reply on Nov 3, 2019 8:30 PM by andreml

    19.2.1 is now available.

    thatJeffSmith-Oracle

      Version 19.2.1 is available for download.

       

      In this release:

       

      + BUG: Truncated Excel Exports

      + BUG: Slow connect times and/or non-functional Code Insight.

       

       

       

      BOTH of these items were caught by folks in this community, so thanks very much for helping us keep the product where it should be.

       

       

      News-wise, version 19.3 remains on schedule for the end of the month.

       

      If you take a look at SQLcl 19.2.1, there's a new FEEDBACK option, courtesy Tim Hall aka oraclebase.

        • 1. Re: 19.2.1 is now available.
          DMC-1053066

          Hi Jeff,

           

          Unfortunately, the auto complete still does not work for me.

           

          My settings:

          (Windows 64-bit with JDK 8 included, Windows 10 64b)

           

          I tried this in SQL Editor:

          1. type: "SELECT * FROM DU"

              press: <CTRL>+<SPACE>

              result: appear list of "DUAL" TABLES (SYS.DUAL, PUBLIC.DUAL)

              choose one

          2. continue typing: " where "

              a. press: <CTRL>+<SPACE>

                  result: auto complete with "DUMMY" - the only column in this table

                  action: the query can be run

              b. type "DU" - the first two characters of "DUMMY"

                 press: <CTRL>+<SPACE>

                 result: nothing for this table. For other specific tables or columns here I get only rows (statements) from help instead of local auto complete list, like this:

          (I was expecting to get the list of columns like 'CUS%' at least CUSTOMER_NAME column, for example. This table has many columns like "CU%").

           

          My conclusion: auto complete works for tables (or for like a_part_from_table_name%) and does not work for columns (for the case like a_part_of_column_name%).

          Due to the malfunctions related to auto complete feature I remained at version 18.4.

           

          Thanks,

          DMC

          • 2. Re: 19.2.1 is now available.
            thatJeffSmith-Oracle

            select * from all_objects

            where ob

             

            • 3. Re: 19.2.1 is now available.
              Vadim Tropashko-Oracle

              Column completion in 19.2.1 should be slightly faster compared to 18.4. Here is the statement log as a proof:

               

              18.4:

              18.4.png

               

               

              19.2.1:

              19.2.1.png

               

              Could you please run the two queries manually in SQL Worksheet to check what data dictionary query performance is in your scenario, and if indeed the shorter union query takes longer to execute?

              • 4. Re: 19.2.1 is now available.
                user3804244

                Excel still truncated exports when rows enough max and multi date column. etc.

                my_tbl  contain 25000 rows.

                 

                select sysdate,sysdate,sysdate,sysdate,sysdate,sysdate,sysdate from my_tbl;

                 

                the excel still truncated.

                 

                 

                exports row it seem limit by:     dateColumn * rows

                 

                if the date column less than 4, in this case, it can export successful:

                select sysdate,sysdate from my_tbl;

                • 5. Re: 19.2.1 is now available.
                  thatJeffSmith-Oracle

                  yeah there's now a new bug, i was seeing resultsets cut off at 32,000 rows, the developer has fixed this for 19.3, due in a few weeks

                  • 6. Re: 19.2.1 is now available.
                    DMC-1053066

                    For a database with select count(*) from all_objects = ~57.000 objects:

                    very fast (elapsed = 9) :

                    But for another (big) database with ~1.4 millions in all_objects:

                    (autocomplete only from help statements)

                    not so fast (elapsed = 1005) :

                     

                    And that query:

                    SELECT 'COLUMN' type, owner, table_name object_name, column_name, column_id, data_type

                    FROM sys.all_tab_cols

                    WHERE hidden_column = 'NO' and rownum <=50 and owner = 'SYS' and table_name = 'ALL_OBJECTS' and column_name like 'OB%'

                    union all

                    SELECT object_type type, owner  owner, object_name, null column_name, null column_id, null data_type

                    FROM all_objects

                    WHERE rownum <=50 and object_name not like 'BIN$%'  and object_type IN ('PROCEDURE','FUNCTION','PACKAGE','OPERATOR')

                    and object_name like 'OB%'

                    union all

                    SELECT 'SEQUENCE' type, USER owner,  object_name, 'NEXTVAL' column_name, null column_id, null data_type

                    FROM sys.user_objects

                    WHERE object_type = 'SEQUENCE' and rownum <=50   and object_name like 'OB%'

                     

                    - in the first database (The small one):

                    - in the second database (The big one):

                     

                    Thanks!

                    • 7. Re: 19.2.1 is now available.
                      Vadim Tropashko-Oracle

                      To remove any confusion let's focus on large database only, because for smaller databases all dictionary queries are fast.

                       

                      Is the first disjunct query

                       

                      SELECT 'COLUMN' type, owner, table_name object_name, column_name, column_id, data_type

                      FROM sys.all_tab_cols

                      WHERE hidden_column = 'NO' and rownum <=50 and owner = 'SYS' and table_name = 'ALL_OBJECTS' and column_name like 'OB%'

                       

                      any faster than the aforementioned 3-pieces union query? If it is faster, then it is advisable to switch the mode to "PARALLEL". In this scenario code insight fires those queries in parallel on a separate connections so that the response time is the maximum of the timings rather than the sum.

                       

                      P.S. There are also the two modes with caching (CACHE_UNION, and CACHE_PARALLEL), but it would be practical only if

                       

                      SELECT object_type, owner, object_name

                      FROM all_objects

                      WHERE object_type in (‘TABLE’,‘VIEW’,‘SYNONYM’, ‘PACKAGE’,‘PROCEDURE’,‘FUNCTION’, ‘SEQUENCE’) and object_name not like ‘BIN$%’

                       

                      executes in reasonable time. (When measuring this timing, please, don't forget to fetch all the records, not just the first 50).

                      • 8. Re: 19.2.1 is now available.
                        DMC-1053066

                        Thanks Vadim!

                         

                        Fast or slow, large or small databases, Auto Complete must wait for the query result from the metadata, no? (regardless of the type of query launch set in your preferences: UNION, PARALLEL, etc.).

                        This is happening in 18.4.

                         

                        In my case above 1005 (time managed by auto complete process)  < 1474 (real duration of that query).

                        (I wonder if it would be possible: Is it possible to set the time how long the complete auto expects the metadata results? Now (in 19.2.1) it seems to be around 1 second - sometimes insufficient).

                         

                        Thanks!

                        • 9. Re: 19.2.1 is now available.
                          Vadim Tropashko-Oracle

                          In 18.4 there was only one insight mode, and it has been carried over to later versions as UNION. Basically, it is one combined  query against data dictionary. Therefore, when you reported that 19.2.1 insight in the UNION mode is less performant than 18.4, then I'm puzzled what might be the reason.

                           

                          Apart from the data dictionary query becoming little smaller (not querying users and other objects unnecessarily) only one thing has been changed: the default Completion Insight -> Popup speed has been lowered from 0.6 sec to 0.4. This is the time interval completion insight waits before attempting to do the job. This is because a user can continue typing, and the wait time prevents firing insight for already obsolete input. The only explanation of your experience I can offer is that some other sqldev query is fired in this period and took up all the RDBMS attention. The best way to investigate this hypothesis further is to check the statements log.

                           

                          As for the insight timeouts, they are currently hard-coded as follows:

                          • When auto invoked, it is 750 ms
                          • When invoked manually (ctrl-space), it is 15 sec.
                          • During the very first insight invocation the timeout is guaranteed not to be lower than 2 sec, because there is some one time insight initialization overhead.
                          • 10. Re: 19.2.1 is now available.
                            andreml

                            Hi Jeff,

                            just a question:

                            Will there really be a 19.3 or will you skip it and directly jump to 19.4

                            Thank you and Regards

                            Andre