Wednesday, April 14, 2010

Been a while - new theme coming...

Been a while since I've written here. I've decided to start posting again from time to time, but I'm going to follow a theme for a while.

The theme will be "I didn't know that..." - or "What I learned new today..."

I am constantly amazed as what I don't know - given that I've been using Oracle for 23 years now - you might think that would be a small set of things. It isn't, it is quite large.

Here is one thing I learned new today. It is about SQL*Plus. A pretty simple tool, been using it for a long long long time. Been using it since it was an extra cost option to the database!

Anyway, did you know that you could put @file into a SQL command?


ops$tkyte%ORA10GR2> !cat test.sql
select * from dual;

ops$tkyte%ORA10GR2> explain plan for
2 @test

Explained.

ops$tkyte%ORA10GR2> l
1 explain plan for
2* select * from dual
ops$tkyte%ORA10GR2>


Neat - I did not know that.


I have more, I'll let them leak out day by day (or so)...
POST A COMMENT

12 Comments:

Blogger Belly said....

Tom,

It's nice to be able to say "I knew something before Tom Kyte did" ;-)

Anyway, the following script uses this functionality to get the explain plan for the last statement that was executed.
Often I run a sql statement and notice the performence is not what I expected and I want to do an explain plan.
All I have to do then is execute "@xp" and there it is.

Feel free to use, alter or trash it.
And if you see things that make you go "why the $%&%* does he do it that way, it should be..." then please enlighten me.

Erik van Roon


--
-------------------------------------------------------------------------
-- --
-- _/_/_/_/ _/_/_/ _/_/ _/_/_/ _/_/_/ --
-- _/ _/ _/ _/ _/ _/ _/ _/ _/ --
-- _/_/_/ _/ _/ _/_/_/ _/ _/ _/ _/_/ --
-- _/ _/ _/ _/ _/ _/ _/ _/ _/ --
-- _/_/_/_/ _/ _/ _/ _/_/ _/_/_/ _/_/_/ --
-- --
-------------------------------------------------------------------------
--
-- ****************************************************************************
-- Function : Shows the explain plan for the query that was last executed
-- Author : Erik van Roon
-- Version : 01.00
-- Dependencies
-- ------------
-- execute permision for dbms_xplan
-------------------------------------------------------------------------------
-- Changes:
-- Version When Who What
-- ------- ---------- --- ---------------------------------------------------
-- ****************************************************************************

DEF stmnt_id=ERO
DEF outputformat=TYPICAL -- BASIC / TYPICAL / ALL / SERIAL
DEF savefile=afiedt.buf

SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 500
SET HEADING OFF
SET TERMOUT OFF

SAVE afiedt.buf REPLACE -- won't work with variable 'savefile'

DELETE
FROM plan_table
WHERE statement_id = '&stmnt_id'
;

COMMIT;

EXPLAIN PLAN
SET STATEMENT_ID = '&stmnt_id'
INTO plan_table
FOR
@&savefile

SET TERMOUT ON

PROMPT
PROMPT #######################################################################################################
PROMPT EXPLAIN PLAN FOR:
PROMPT ================
get &savefile

PROMPT #######################################################################################################

SELECT *
FROM TABLE(dbms_xplan.display ('PLAN_TABLE' -- table_name VARCHAR2 DEFAULT 'PLAN_TABLE'
,'&stmnt_id' -- statement_id VARCHAR2 DEFAULT NULL
,'&outputformat' -- format VARCHAR2 DEFAULT 'TYPICAL'
)
)
;

UNDEF stmnt_id
UNDEF outputformat
UNDEF savefile
PROMPT

Wed Apr 14, 06:08:00 PM EDT  

Blogger Belly said....

Hmmm,

Tom, did you know al multi-spaces in a reply are broken down to single spaces?
In the script above it's not a big deal because only the asciiart I have at the top of all my scripts look like rubish, but sometimes in a script multiple spaces are functional :-(

Erik van Roon

Wed Apr 14, 06:13:00 PM EDT  

Blogger Thomas Kyte said....

@Belly -

thanks - and yes, I know about the comments, I have zero control over that... sorry...

Wed Apr 14, 06:55:00 PM EDT  

Anonymous Sokrates said....

nice, didn't know that !

Thu Apr 15, 02:15:00 AM EDT  

Blogger Ankit Rathi said....

This comment has been removed by the author.

Thu Apr 15, 10:24:00 AM EDT  

Blogger Curtis said....

Tom,

I actually stumbled on this separately through writing some security checking scripts in PL/SQL that I didn't want to modify the database. So with this I can include procedures/functions (havn't tried inline packages) in my testing scripts

So each of my scripts look like this:

DECLARE
PROCEDURE foo AS
... END;

@@SHARED
BEGIN
sharedproc('a');
foo();

END;

Fri Apr 16, 10:45:00 AM EDT  

Anonymous Rams said....

Thanks.Learnt another new one.

Fri Apr 16, 11:12:00 AM EDT  

Blogger Chris said....

Hi!

Some time ago I wrote a programming language (superset of PL/SQL), whose parser was implemented with SQL*Plus scripts (http://code.google.com/p/m4o/).

Chris Neumueller

Sun Apr 18, 05:41:00 AM EDT  

Anonymous Michael Olin said....

Tom - I knew about the @file construct, but SQL*Plus as an option you had to pay for? That was news to me and I've been using Oracle since the days when you entered your SQL commands using the "User Friendly Interface (UFI)". I also remember talk back then of Oracle developing a procedural interface (the widely anticipated "Super-UFI") which eventually arrived as PL/SQL. I always thought that SQL*Plus was just a renaming of UFI, just as the "Interactive Application Facility (IAF)" became SQL*Forms. We really had to pay extra for SQL*Plus?

Sun Apr 18, 08:54:00 PM EDT  

Blogger Thomas Kyte said....

@Michael,

well, UFI the user friendly interface, was to become AFI (a for advanced, but was superceded by PL/SQL (AFI didn't arrive as pl/sql - that was another project altogether, but since plsql sort of removed the need for a semi-procedural scripting language, AFI 'died')


and yes, sqlplus was an option. If you used it during version six, you were using the sqlplus option.


just like row level locking was (an option) :)

Mon Apr 19, 08:20:00 AM EDT  

Anonymous George Johnston said....

Didn't know that so thanks :) Will be very useful especially in the explain plan area.

Interesting that it does not work if the file to be substituted is on the same line

SQL> !cat dual.sql
select * from dual;

SQL> explain plan for @dual
2 ;
explain plan for @dual
*
ERROR at line 1:
ORA-00905: missing keyword

SQL> explain plan for
2 @dual

Explained.

Wed Apr 21, 10:00:00 AM EDT  

Blogger galactic_hitchhiker said....

Just saw the post...
I was going to add pretty much what Erik Van Roon said..
in you script
instead of @test
you can change it to
@&1

and then just pass the file name to the explain plan script (instead of using afiedt.buf from Erik's script)...
@ex test.sql

Cool thing.
Thanks to you too Erik.

Mon Apr 26, 05:03:00 PM EDT  

POST A COMMENT

<< Home