Home » Other » Client Tools » How does a SQL Statement work? (Oracle 9i,Client OS is Windows XP & Server is Redhat Linux)
How does a SQL Statement work? [message #319195] Fri, 09 May 2008 05:29 Go to next message
kiran.satyan
Messages: 25
Registered: March 2008
Location: Bangalore
Junior Member
Hi All,

SQL* Plus places each command into a buffer,when it finds a semi colon Sql* Plus executes that query.

In this explanation the "Buffer",specified is in client's Memory
or in SGA of Server(Assume we have a client connected to a Server)?

When we press enter after typing semicolon exactly what is happening?

If the execution plan is created in SGA,then what is transmitted from the client to the server.

Thanks & Regards

Kiran Sathyan


Re: How does a SQL Statement work? [message #319218 is a reply to message #319195] Fri, 09 May 2008 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL*Plus keeps a copy of the last query run internally - that's why you can re-run the query with a single key stroke.

The query is sent to the server, where it is parsed, an executpion plan is generated for it, the data is fetched, andthen the data is packed up into sets of rows based on the SQL*Plus ARRAYSIZE, and sent interactively to the client.
Re: How does a SQL Statement work? [message #319219 is a reply to message #319195] Fri, 09 May 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the "Buffer",specified is in client's Memory
or in SGA of Server(Assume we have a client connected to a Server)?

In SQL*Plus

Quote:
When we press enter after typing semicolon exactly what is happening?

SQL*Plus analyzes the command to know if it is a SQL*Plus or a SQL/PLSQL command. If it is a SQL*Plus command, it executes it. If it is a SQL/PLSQL it sends it to the server.

Quote:
If the execution plan is created in SGA,then what is transmitted from the client to the server.

The SQL/PLSQL text.

Regards
Michel
Re: How does a SQL Statement work? [message #319236 is a reply to message #319195] Fri, 09 May 2008 07:50 Go to previous messageGo to next message
kiran.satyan
Messages: 25
Registered: March 2008
Location: Bangalore
Junior Member
Hi Michel,

In which format SQL*PLUS transmits the SQL/PLSQL command?

All the SQL/PLSQL commands are parsed in SGA only ,then in which format it is transmitted from client to server?

So this English sentences are converted to which format?

Its pretty low level,I'm bit curious to get it displayed!

Thanks & Regards

Kiran Sathyan
Re: How does a SQL Statement work? [message #319238 is a reply to message #319236] Fri, 09 May 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In which format SQL*PLUS transmits the SQL/PLSQL command?

Text.

Quote:
All the SQL/PLSQL commands are parsed in SGA only ,then in which format it is transmitted from client to server?

They are parsed in the server, so question is irrelevant.

Quote:
So this English sentences are converted to which format?

No conversion. This is text.

Quote:
Its pretty low level,I'm bit curious to get it displayed!


Add to your client sqlnet.ora:
TRACE_LEVEL_CLIENT=support
TRACE_DIRECTORY_CLIENT=<your trace directory>
TRACE_FILE_CLIENT=<your trace file>

Then all what comes in and goes out of your client is traced in the file.
Take care, it will be big soon.

Regards
Michel
Re: How does a SQL Statement work? [message #319245 is a reply to message #319218] Fri, 09 May 2008 08:24 Go to previous messageGo to next message
kiran.satyan
Messages: 25
Registered: March 2008
Location: Bangalore
Junior Member
Hi JRowbottom ,

Thanks alot for this wonderful information.

Kiran Sathyan


Re: How does a SQL Statement work? [message #319247 is a reply to message #319238] Fri, 09 May 2008 08:27 Go to previous message
kiran.satyan
Messages: 25
Registered: March 2008
Location: Bangalore
Junior Member
Hi Michel

Thank you very much for making me understand it.

I hope I'm trying to keep a good standard for my doubts.

Regards

Kiran Sathyan
Previous Topic: Same query, same server, different clients and different results
Next Topic: SQL*PLUS HTML reports
Goto Forum:
  


Current Time: Fri Mar 29 00:19:20 CDT 2024