SQL*Plus Timers
SQL*Plus TIMING is a handy little utility that you can use to do a performance analysis on any commands or blocks run during a period. It is different from the SET TIMING command which is used to display timing data after each SQL command or PL/SQL block you run.
Here is an example:
TIMING SHOW lists the current timer’s name and timing data:
SQL> timing show
SP2-0325: no timing elements to show
TIMING START sets up a timer and makes t1 (or whatever timer name you like) the name of the timer:
SQL> timing start t1
SQL> timing show
timing for: t1
Elapsed: 00:00:06.95
Another call to TIMING SHOW displays the elapsed time since the start of the timer:
SQL> timing show
timing for: t1
Elapsed: 00:00:16.01
You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer:
SQL> timing start t2
t2 is the current timer now:
SQL> timing show
timing for: t2
Elapsed: 00:00:02.25
TIMING STOP lists the current timer’s name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer, in this example it’s t1:
SQL> timing stop
timing for: t2
Elapsed: 00:00:18.81
SQL> timing show
timing for: t1
Elapsed: 00:01:14.18
SQL> timing stop
timing for: t1
Elapsed: 00:01:22.09
SQL> timing show
SP2-0325: no timing elements to show
TIMING with no clauses lists the number of active timers:
SQL> timing
no timing elements in use
SQL> timing start t1
SQL> timing
1 timing element in use
SQL> timing start t2
SQL> timing
2 timing elements in use
CLEAR TIMING deletes all timers:
SQL> clear timing
timing for: t2
Elapsed: 00:22:38.36
timing for: t1
Elapsed: 00:22:53.96
Sources and Resources:
Filed in Oracle, Tips with 0 Comments | Tags: sqlplusAbout Oracle Press
Oracle Press is a marketing label used by McGraw Hill to market its line of Oracle books – the books are not formally written by Oracle Corporation nor are they formally tech edited by Oracle Corporation, although some employees of Oracle Corporation have used McGraw Hill as a publishing company. At least for me, for several years the “Officially Authorized Oracle Press” logo on books published by McGraw Hill seemed to be endorsement of the books by Oracle Corporation, but that is not the case.
via Charles Hooper’s Oracle Notes.
Filed in Oracle with 0 Comments | Tags: book5 Interesting Things You May Have Missed On This Aug 16, 2010
-
A guide to database performance for developers
-
A simple, yet a powerful IDE for Oracle databases. Instead of reinventing the wheel, VoraX relies on the well-known SqlPLUS which is actually used to connect to the database, to execute and to get the results back. However, this interaction is hidden from the user who just uses his/her Vim environment without knowing that, behind, a SqlPLUS process does all the work.
-
Different ways to write Top-N SQL and the performance implication of each.
-
Various techniques for querying and manipulating LONG columns.
-
Different ways to create a database link.
6 Interesting Things You May Have Missed on 2010-07-28
-
On July 27, Oracle completed the migration of all non-obsolete content from developers.sun.com, java.sun.com, and BigAdmin to a completely re-architected, re-designed Oracle Technology Network site. This site – which offers different entry points for Java Developers, Database Admins and Developers, System Admins and Developers, and Architects – reflects the full diversity of this huge technical end-user community.
-
Larry Ellison, founder and chief executive of software maker Oracle Corp., topped the list of best-paid executives of public companies during the past decade, receiving $1.84 billion in compensation, according to a Wall Street Journal analysis of CEO pay.
-
Code reviews are very important in any software project. More often only "superficial" reviews happen, the types where you get review comments like, "You know the documentation for that method doesn't have the version number", or "this variable is unused", etc. Although this is an essential part of any code review, it doesn't cover the subtle aspects of a real code review. It takes more than just a static code analyzer to perform a good code review. In this article the author shares a few points on how to make code reviews really effective.
-
Before there were VPNs, there was ssh. And when you don’t have access to your VPN or can’t set one up, you can still get most of the functionality over ssh.
-
Firebug is one of the most famous extension for the Firefox web browser. In the past years, the functionalities offered by this plugin alone sold Firefox as the platform for web development over Internet Explorer (besides the respect of W3C standards that characterized Firefox). Then, Chrome had not been invented yet and when it was released, it incorporated some of the innovations of Firebug like a functional JavaScript console.
-
Partition Alignment, Choosing a file system, Optimizing ext3, Enabling Huge Pages, Using Async and Direct IO, Tuning Swapping Priority and more.
7 Golden Rules That Make You a Better Programmer
Steven Feuerstein published a new presentation titled: Golden Rules for Developers [PDF].
Here is a summary of his seven “golden rules” that will help you write better code:
- Don’t repeat anything.
- Hide everything.
- Don’t take shortcuts.
- Embrace standards.
- Build on a foundation.
- Never lose information.
- Don’t write code alone.
Agreed with all. I would also add an eighth one: Understand your data.
What would you add?
Filed in Oracle, Tips with 11 Comments | Tags: pl/sqlPlease Request a Reasonable Conversion
Here is something you need to be aware of when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements in PL/SQL.
Let’s execute this simple anonymous PL/SQL block:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4000);
3 l_rc sys_refcursor;
4 BEGIN
5 l_string := RPAD (' ', 4000);
6 OPEN l_rc FOR 'select :1 from dual' USING l_string;
7 EXECUTE immediate 'select :1 from user_objects where rownum = 1'
8 INTO l_string USING l_string;
9 END;
10 /
PL/SQL procedure successfully completed.
The EXECUTE IMMEDIATE and OPEN FOR statements are used with the USING clause. USING supplies a bind argument for the SQL string. In this example the value of the bind argument is a 4,000 character string.
Now, let’s pass a string greater than 4,000 characters to the OPEN FOR statement:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4001);
3 l_rc sys_refcursor;
4 BEGIN
5 l_string := RPAD (' ', 4001);
6 OPEN l_rc FOR 'select :1 from dual' USING l_string;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6
Oops, got an error. Let’s also try the EXECUTE IMMEDIATE statement with a string greater than 4,000 characters:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4001);
3 BEGIN
4 l_string := RPAD (' ', 4001);
5 EXECUTE immediate 'select :1 from user_objects where rownum = 1'
6 INTO l_string USING l_string;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5
Got the same error: “ORA-01460: unimplemented or unreasonable conversion requested”.
How about if we pass a NULL:
eddie@db11gr2> DECLARE
2 l_rc sys_refcursor;
3 BEGIN
4 OPEN l_rc FOR 'select :1 from dual' USING NULL;
5 END;
6 /
OPEN l_rc FOR 'select :1 from dual' USING NULL;
*
ERROR at line 4:
ORA-06550: line 4, column 45:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:
In this case we get “PLS-00457: expressions have to be of SQL types”.
Well, there is a restriction on what values you can bind: When binding values to dynamic SQL, only SQL datatypes are supported. You can bind strings, numbers, dates, collections, LOBs, XML documents… However, you cannot bind values having a PL/SQL specific datatype such as Booleans, associative arrays and varchar2 values greater than 4000.
Make sure you keep the above restriction in mind when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements.
Source and resources:
- EXECUTE IMMEDIATE Statement
- Unsupported Data Type in Native Dynamic SQL
- OPEN FOR Statement
- Discussions about the ORA-01460 error
See How Easily You Can Post to Twitter From an Oracle DB
Posting to Twitter from inside an Oracle database is not something new (OraTweet, ORA_Tweet). However, what if you could post to Twitter by simply executing a SELECT statement without using any custom PL/SQL code?
Try the following in your SQL*Plus. It will ask you for your Twitter username and password and the status message that you want to post:
eddie@db11gr2> set define !
eddie@db11gr2> set verify off
eddie@db11gr2> set sqlterminator off
eddie@db11gr2> SELECT HTTPURITYPE(
2 UriFactory.escapeUri(
3 'http://query.yahooapis.com/v1/public/yql?q=
4 USE "http://awads.net/yql/twitter.xml" as tw_t;
5 INSERT INTO tw_t (status, username,password)
6 VALUES("!status","!!username","!!password")
7 &format=json
8 &env=store://datatables.org/alltableswithkeys&format=xml&callback=cbfunc')).getclob()
9 json_response
10 FROM dual
11 /
Enter value for status: Tweeting from SQL*Plus.
Enter value for username: sqlyql
Enter value for password: sqlyqlpw
JSON_RESPONSE
--------------------------------------------------------------------------------
cbfunc({"query":{"count":"1","created":"2010-05-02T02:02:14Z","lang":"en-US","re
sults":{"status":{"created_at":"Sun May 02 02:02:14 +0000 2010","id":"1322057475
5","text":"Tweeting from SQL*Plus.","source":"<a href=\"http://apiwiki.twitter.c
om/\" rel=\"nofollow\">API</a>","truncated":"false","in_reply_to_status_id":null
,"in_reply_to_user_id":null,"favorited":"false","in_reply_to_screen_name":null,"
user":{"id":"139255376","name":"sqlyql","screen_name":"sqlyql","location":null,"
description":null,"profile_image_url":"http://s.twimg.com/a/1272578449/images/de
fault_profile_1_normal.png","url":null,"protected":"false","followers_count":"1"
,"profile_background_color":"9ae4e8","profile_text_color":"000000","profile_link
_color":"0000ff","profile_sidebar_fill_color":"e0ff92","profile_sidebar_border_c
olor":"87bc44","friends_count":"0","created_at":"Sun May 02 01:59:28 +0000 2010"
,"favourites_count":"0","utc_offset":null,"time_zone":null,"profile_background_i
mage_url":"http://s.twimg.com/a/1272578449/images/themes/theme1/bg.png","profile
_background_tile":"false","notifications":"false","geo_enabled":"false","verifie
d":"false","following":"false","statuses_count":"1","lang":"en","contributors_en
abled":"false"},"geo":null,"coordinates":null,"place":null,"contributors":null}}
}});
Elapsed: 00:00:01.92
The above is made possible using HTTPURITYPE and YQL, the Yahoo! Query Language. Awesome combination!
Note that on June 30, 2010 Twitter will be shutting off basic authentication on the Twitter API. After that date, the above will stop working unless the YQL query is modified to use OAuth.
Are you on Twitter? Follow me at eddieawad and the rest of the Oracle Tweeple at eddieawad/oracle.
Filed in Oracle with 2 Comments | Tags: sql, twitter, yqlWhat Error
I have been playing the PL/SQL Challenge quiz every day. It’s a great way to brush up on my PL/SQL skills. After many years of using PL/SQL, I am still learning about features that I have never known before or totally forgotten about. Take for example one of last week’s quiz questions:
What is the output of this PL/SQL block?
eddie@db11gr2> DECLARE
2 aname varchar2 (5);
3 BEGIN
4 DECLARE
5 aname varchar2 (5) := 'Big String';
6 BEGIN
7 DBMS_OUTPUT.put_line (aname);
8 EXCEPTION
9 WHEN VALUE_ERROR
10 THEN
11 DBMS_OUTPUT.put_line ('Inner block');
12 END;
13 DBMS_OUTPUT.put_line ('What error');
14 EXCEPTION
15 WHEN VALUE_ERROR
16 THEN
17 DBMS_OUTPUT.put_line ('Outer block');
18 END;
19 /
Instead of telling you what the answer is, I will point you to the documentation:
In declarations, faulty initialization expressions can raise exceptions, but exception handlers cannot handle them… Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.
Even though I selected the wrong choice when I took the quiz, adding the above fact to my PL/SQL knowledge and sharing it with you is a big win for me.
By the way, should we consider initializing variables in the declaration section a bad practice?
Filed in Oracle, Tips with 3 Comments | Tags: pl/sqlWant to Learn Java? Hear What the Father of Java Has to Say About It
In this video, James Gosling, best known as the father of the Java programming language, provides some wisdom about Java and software development for students. In short, Java is easy to learn especially to people who have not done software programming before.
Filed in Oracle with Comments Off | Tags: java, videoAre You Soft on Your Deletes?
Frustrated with soft deletes in SQL, Reeditor writes:
In the past, I’ve added a deleted_at (datetime) column and appended “WHERE deleted_at IS NULL” to every query involving that table. But it’s a total pain in the ass, and it’s complicated as shit: there’s always a few queries that are missed, a few developers that forgot or didn’t know they had to check that the record wasn’t deleted, denormalized counts are hard to keep in sync, etc. etc. It always turns into an embarrassing fiasco rather than something you add once and then don’t have to worry about.
Good discussion followed which included a couple of interesting links: What is the best way to implement soft deletion? and The trouble with soft delete.
So, if there is a business need to retain deleted records, do you go soft or hard?
Filed in Oracle with Comments Off | Tags: sql
