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?
Neat - I did not know that.
I have more, I'll let them leak out day by day (or so)...
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)...
posted by Thomas Kyte at 4:59 PM
12 Comments:
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
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
@Belly -
thanks - and yes, I know about the comments, I have zero control over that... sorry...
nice, didn't know that !
This comment has been removed by the author.
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;
Thanks.Learnt another new one.
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
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?
@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) :)
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.
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.
POST A COMMENT
<< Home