In case you only have SQL*Plus as your editor, you can use the following codes to show the execution plan of your query.
First Step : create plan
EXPLAIN PLAN FOR
SELECT field1, field2
FROM table;
Second Step: show results
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
You will need the necessary privileges to execute this. You’ll also need to have the PLAN table. If not, you’ll have to create it as that’s the placeholder where execution plan is written.
This technique is important especially when you don’t have a GUI-based SQL editor like PL/SQL Developer or TOAD.
To see DB processes running on your Oracle DB, the code below can be used. Specially handy if you want to check which part of your package or stored procedure is taking time to execute. Indispensable for performance tuning.
SELECT sess.sid, sess.serial#, sess.sql_child_number, sess.sql_exec_id,
sess.process, sess.status, sess.username, sess.osuser, sess.program,
sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
--AND sess.osuser = 'yourwindowslogin';
Equivalent in SQL Server/Sybase
The equivalent command in Sybase or SQL Server is SP_WHO. However, the Oracle version provides more information.
For now, just this. I’ll provide additional details later. For additional fields, try to check the schema of v$session.
Recently, I needed to sort the result of a query in random and show a limited number of records. Honestly, I didn’t know how to do it and had to search various online forums to get the correct answer. So for my own sanity, I’ll be posting the solution here.
Step 1
SELECT field_name1, field_name2
FROM table_name
WHERE field_query1 = 'ARGUMENT VAL'
ORDER BY dbms_random.VALUE;
The key here is the function dmbs_random.VALUE that generates, you guess it, random number. Its value is then used for ordering. The second step is to limit the output by using ROWNUM as below.
Step 2
SELECT field_name1, field_name2
FROM (SELECT field_name1, field_name2
FROM table_name
WHERE field_query1 = 'ARGUMENT VAL'
ORDER BY dbms_random.VALUE)
WHERE ROWNUM < 11;
If you’ll notice, I used a subquery here. Why didn’t I just include the rownum inside the subquery? It is because I wanted the subquery to finish the random ordering first before limiting the output to 10.
Real World Example
SELECT order_num, pr_postn_id
FROM (SELECT order_num, pr_postn_id
FROM siebel.s_order
WHERE order_num LIKE 'V-%'
ORDER BY dbms_random.VALUE)
WHERE ROWNUM < 11;
I'll also need this similar solution for MySQL and will be discussed in my next post.
In case you are maintaining a pl/sql program developed by someone else and that program writes into a file, then basically that file is written to a folder/directory. And since the directories are saved in codes, you have to find the meaning (or location) of those codes. The code below will help you display the information required.
SELECT * FROM ALL_DIRECTORIES;
The query above will display all the directories accessible to the current user. They are folders in the server where you can write your output files. For some reason, I forgot about this twice already and had to scour the web to find it. The problem is, I also forgot the keywords. So, I’m writing it now.
To register new directories, you can follow the example below.
Example
create or replace directory mydir as 'c:\mydirectory';
To use, run the code below.
declare
f utl_file.file_type;
begin
f := utl_file.fopen('MYDIR', 'myfilename.txt', 'w');
utl_file.put_line(f, 'just another line text 1');
utl_file.put_line(f, 'just another line text 2');
utl_file.fclose(f);
end;