12 Replies Latest reply: Jan 16, 2008 2:55 AM by Billy~Verreynne RSS

    telnet vs client

    582889
      Hi
      Is there any benefit if I run long sql querıes from the server (by usıng telnet,etc) or from the remote by sql client.
        • 1. Re: telnet vs client
          616766
          Hi,

          If you run from the server console, you don't need telnet.
          Sqlplus directly then there's a benefit : no Network/Workstation dependence
          • 2. Re: telnet vs client
            582889
            Lets say,I use wındows, and server is unix. I connect server via telnet and connect to sqlplus and submit a query,
            in the scenerio 2, From my computer I connect to server by using toad,sqlplus and submıt a query.
            Which one will be faster?
            • 3. Re: telnet vs client
              Colin'tHart
              Hi,

              There's no real difference; both ways the data has to go over the network to your machine. In one case it's as SQL*Net traffic to your local SQL*Plus or TOAD session, in the other case it's as character data in your telnet session.

              I'm not sure if SQL*Net does compression or is more efficient than a telnet session; if so, SQL*Net could actually be faster.

              Cheers,

              Colin
              • 4. Re: telnet vs client
                153119
                Question 1: Did you test? (Answer: No of course not, that's why I post to this forum, I'm with my legs on my desk, and just waiting until the responses come in)

                Question 2: Aren't you aware you will be using different protocols?
                (Answer: No, I am not, because I don't want to read any docs. That's why I post to this forum)

                --
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: telnet vs client
                  582889
                  :) :)
                  do u know any tutorial link sybran
                  • 6. Re: telnet vs client
                    ViragSharma
                    Is there any benefit if I run long sql querıes from
                    the server (by usıng telnet,etc) or from the remote by sql client.
                    run long sql queries from telnet and use "nohup" command , so even there is network issue, your command / queries will complete
                    • 7. Re: telnet vs client
                      582889
                      thanks virag
                      • 8. Re: telnet vs client
                        Billy~Verreynne
                        > Is there any benefit if I run long sql querıes from the server (by usıng telnet,etc) or from the
                        remote by sql client.

                        Oracle does not care. It does not care if the client is local SQL*Plus running via telnet, or a TOAD client on Windows, or a Java program on an application server.

                        Why should it care? The Oracle server understand SQL and PL/SQL. These are the two languages that ALL clients use to communicate with it. Heck, Oracle even talks SQL to itself internally. So why would Oracle care just who is talking SQL or PL/SQL to it? It does not make the slightest difference to Oracle.

                        Thus, why would one client result in running a long/slow SQL any faster than another client?

                        It is important to understand just how the Oracle client-server architecture works. The [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm]Oracle® Database Concepts guide is an excellent place to start.
                        • 9. Re: telnet vs client
                          585179
                          Hi Ricardinho


                          If you want to know the difference then do a testing

                          Here i have a small testing

                          Output testing from server

                          bahamut> select count(*) from vd.a1;

                          COUNT(*)
                          ----------
                          64075

                          bahamut> select * from vd.a1;

                          64075 rows selected.


                          Statistics
                          ----------------------------------------------------------
                          0 recursive calls
                          0 db block gets
                          942 consistent gets
                          0 physical reads
                          0 redo size
                          2498992 bytes sent via SQL*Net to client
                          1876 bytes received via SQL*Net from client
                          130 SQL*Net roundtrips to/from client

                          0 sorts (memory)
                          0 sorts (disk)
                          64075 rows processed



                          and this from my local computer

                          bahamut_local> select * from vd.a1;

                          64075 rows selected.


                          Statistics
                          ----------------------------------------------------------
                          0 recursive calls
                          0 db block gets
                          1024 consistent gets
                          0 physical reads
                          0 redo size
                          2484600 bytes sent via SQL*Net to client
                          1939 bytes received via SQL*Net from client
                          215 SQL*Net roundtrips to/from client

                          0 sorts (memory)
                          0 sorts (disk)
                          64075 rows processed



                          you can see the difference, don't you?

                          Again, Do the testing


                          Cheers

                          FZheng
                          • 10. Re: telnet vs client
                            Billy~Verreynne
                            You're looking not at the SQL performance, but cost of getting the response to the SQL from the Oracle server process to the client process.

                            The nature of the client has no impact on SQL performance. It does however have an impact on the cost of the getting the data from server to client.

                            A local client for example uses IPC (Inter Process Communication). The bytes send/received via SQL*Net does not go via the network, but directly via memory. This is a lot faster than sending data to a remote client via the network.

                            The behaviour of remote clients also play a role.

                            SQL> set array 1
                            SQL> select * from emp;

                            <sniped>
                            17 rows selected.


                            Statistics
                            ----------------------------------------------------------
                            0 recursive calls
                            0 db block gets
                            15 consistent gets
                            0 physical reads
                            0 redo size
                            1424 bytes sent via SQL*Net to client
                            294 bytes received via SQL*Net from client
                            10 SQL*Net roundtrips to/from client
                            0 sorts (memory)
                            0 sorts (disk)
                            17 rows processed

                            SQL> set array 100
                            SQL> select * from emp;

                            <snipped>
                            17 rows selected.


                            Statistics
                            ----------------------------------------------------------
                            0 recursive calls
                            0 db block gets
                            8 consistent gets
                            0 physical reads
                            0 redo size
                            1082 bytes sent via SQL*Net to client
                            238 bytes received via SQL*Net from client
                            2 SQL*Net roundtrips to/from client
                            0 sorts (memory)
                            0 sorts (disk)
                            17 rows processed

                            SQL>
                            Same client. A change in fetch size. The number of packets send differs significantly. Less packets means more data per packet, less chance of collisions, better network utilisation, less latency.

                            But this is very different aspect of performance, than what I understood the original poster's question was - how is SQL performance impacted by using a different client? And the answer to that is a clear "no difference!" as the client itself does not determine the performance of the SQL inside Oracle*.


                            * The above example shows that the client behaviour can however impact performance (in this case, a larger fetch array results in less consistent gets). But this is client behaviour and not who/what/where the client is. A Pro*C client for example will show the exact same behaviour when array fetch sizes are 1 and a 100.
                            • 11. Re: telnet vs client
                              585179
                              I think cost of getting the response is also part of performance, rite?


                              Like i said at the end : DO The Testing!!!



                              Cheers
                              • 12. Re: telnet vs client
                                Billy~Verreynne
                                I think this may confuse the OP as his problem statement was:
                                "Is there any benefit if I run long sql querıes from the server (by usıng telnet,etc) or from the remote by sql client?"

                                He is specifically asking about reducing the execution time of a long running query. And as he wants it running in the background, it would seem that the output of that long running query is far less important than making that query run faster.

                                No problem myself with you adding more beef to the problem (a good thing I think in general to do) - but when we do, we need to make sure that we do not confuse that with the answer for the basic problem statement. We are saying early in the thread that it does not make a difference.. and later on in the thread we show that there is a (different) difference. And that could be confusing for someone that may not understand the concepts of client server we're highlighting ito his original question. Which is why I posted that response to you about networking being a different component in the performance equation.

                                Hopefully the OP will speak up if there is any confusion on his part.