Saturday, November 01, 2008
More Fake Performance Tips
We're not sure if this is a joke or just (more likely) the work of an idiot. Here are 15 Tips for better performance and tuning in Oracle SQL and PL/SQL:
- FTS (Full Table Scans) are always bad and Index usage is always good.
- Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.
- Empty Space in an index that gets created due to the DML operations do not get used.
- Indexes should be rebuilt at regular intervals.
- Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.
- Usage of cursors is always bad so avoid them like the plague.
- Truncate command cannot be rolled back because it is a non-logged operation.
- Table variables in SQL Server are always only memory resident.
- Column order in a covered index does not matter.
- In the case of SQL Server, one can separate the clustered index from the table.
- Only committed data gets written to the disk.
- Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.
- Count(1) is better performing than count(*).
- Issue frequent commits in the application to make the transaction faster and also improve concurrency.
- Views are evil evil DB Objects that always slow down performance.
We like the fact that 8, 9 and 10 appear to be about SQL Server, despite the heading. Probably Nawal could only think of 12 fake Oracle tips but thought nobody would notice. Can you help him out with some more misleading tips for Oracle? (Or SQL Server. Nobody will notice.) For example,
- The buffer cache hit ratio is a reliable indicator of system performance.
- Bitmap indexes are perfect for columns with a small number of distinct values, like 'Y' and 'N'.
- The Inuit have fifty words for snow.
Spotted by Michel Cadot on Oracle-L.
Update: Yong Huang pointed out that the source of the article appears to be a list of common myths posted on a SQL Server blog, which makes it slightly less funny than it first seemed. Oh well.
Friday, August 01, 2008
TGI g_friday
Found in a package body:
g_friday CONSTANT VARCHAR2(6) := 'Friday';
...then a couple of hundred lines later:
if to_char(business_date,'fmDay') = g_friday then
...end-of-week processing...
else
...regular processing...
end if;Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.
Saturday, June 21, 2008
Auxiliary Constructs Appeal
Sunday, April 27, 2008
Fine tuning
Mike is doing some work on an application that started life as SQL Server. Now that it has been converted to Oracle, there are one or two bits that could still do with a little fine tuning.
One particular procedure seemed to take rather a lot of time, and several developers had tried to get better performance out of it without much success. Here it is:
CREATE OR REPLACE PROCEDURE getupdatedrunids
( p_customer_id IN VARCHAR2 DEFAULT NULL
, pc_results_out IN OUT SYS_REFCURSOR )
AS
v_fetch_status INTEGER := 0;
v_sql_status INTEGER;
v_fetch_status1 INTEGER := 0;
v_sql_status1 INTEGER;
v_event_id VARCHAR2(50);
v_runid NUMBER(10, 0);
v_count_run_conns INTEGER := 0;
v_temp_runs INTEGER;
BEGIN
DELETE FROM temp_runs;
DELETE FROM temp_run_connections;
INSERT INTO temp_runs
( run_id )
SELECT DISTINCT i.run_id
FROM event_status i
, run_status b
WHERE i.run_id = b.run_id
AND i.event_id IN
( SELECT DISTINCT i.event_id
FROM event_status i
, run_status b
WHERE i.run_id = b.run_id
AND b.customer_id = p_customer_id
GROUP BY i.event_id
HAVING COUNT(i.run_id) > 1)
AND b.customer_id = p_customer_id;
BEGIN
v_temp_runs := 0;
SELECT COUNT(*)
INTO v_temp_runs
FROM dual
WHERE EXISTS
( SELECT *
FROM temp_runs );
END;
IF v_temp_runs > 0 THEN
DECLARE
CURSOR c_runs IS
SELECT DISTINCT run_id
FROM temp_runs;
BEGIN
OPEN c_runs;
FETCH c_runs INTO v_runid;
IF c_runs%NOTFOUND
THEN
v_sql_status1 := 2;
v_fetch_status1 := -1;
ELSE
v_sql_status1 := 0;
v_fetch_status1 := 0;
END IF;
WHILE v_fetch_status1 = 0
LOOP
BEGIN
DECLARE
CURSOR cust_incidents_cs IS
SELECT DISTINCT i.event_id
FROM event_status i
WHERE i.run_id = v_runid
AND i.rejected = 0;
BEGIN
OPEN cust_incidents_cs;
FETCH cust_incidents_cs INTO v_event_id;
IF cust_incidents_cs%NOTFOUND
THEN
v_sql_status := 2;
v_fetch_status := -1;
ELSE
v_sql_status := 0;
v_fetch_status := 0;
END IF;
<<i_loop1>>
WHILE v_fetch_status = 0
LOOP
BEGIN
INSERT INTO temp_run_connections
( run_id
, connectedids)
SELECT DISTINCT v_runid
, i.run_id AS connectedids
FROM event_status i
WHERE i.run_id < v_runid
AND i.event_id = v_event_id
AND i.rejected = 0
AND i.run_id IN
( SELECT DISTINCT run_id
FROM temp_runs );
<<fetchnext>>
FETCH cust_incidents_cs INTO v_event_id;
IF cust_incidents_cs%NOTFOUND
THEN
v_sql_status := 2;
v_fetch_status := -1;
ELSE
v_sql_status := 0;
v_fetch_status := 0;
END IF;
END;
END LOOP;
CLOSE cust_incidents_cs;
END;
FETCH c_runs INTO v_runid;
IF c_runs%NOTFOUND
THEN
v_sql_status1 := 2;
v_fetch_status1 := -1;
ELSE
v_sql_status1 := 0;
v_fetch_status1 := 0;
END IF;
END;
END LOOP;
CLOSE c_runs;
END;
END IF;
SELECT COUNT(*)
INTO v_count_run_conns
FROM temp_run_connections;
IF v_count_run_conns > 0
THEN
OPEN pc_results_out FOR
SELECT DISTINCT run_id, connectedids
FROM temp_run_connections;
END IF;
END getupdatedrunids;His final version was much faster. See if you can spot the difference:
CREATE OR REPLACE PROCEDURE getupdatedrunids
( p_customer_id IN VARCHAR2 DEFAULT NULL
, pc_results_out IN OUT SYS_REFCURSOR )
AS
BEGIN
OPEN pc_results_out FOR
SELECT DISTINCT e2.run_id, ic.run_id AS connectedids
FROM run_status r1
, run_status r2
, event_status e1
, event_status e2
WHERE r1.customer_id = p_customer_id
AND r2.customer_id = r1.customer_id
AND e1.run_id = r2.run_id
AND e1.rejected = 0
AND e2.run_id = r1.run_id
AND e2.event_id = e1.event_id
AND e2.run_id > e1.run_id
AND e2.rejected = 0
ORDER BY 1, 2;
END getupdatedrunids;Sunday, February 10, 2008
WREAK_APPLICATION_HAVOC
Tom Kyte recently blogged about the senseless and yet strangely common practice of coding something like this:
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:' || SQLERRM);
which fairly obviously achieves nothing except take a standard message like this:
ORA-06501: PL/SQL: program error ORA-06512: at line 6
and pointlessly scramble it into this:
ORA-20001: Following Error Occured:ORA-06501: PL/SQL: program error ORA-06512: at line 11
which adds some meaningless text, hides the original line number, and miss-spells "occurred". Not bad for a day's work.
It turned out that some people had been doing this for years because they were simply too stupid to realise that they didn't have to.
Anyway you know all this because you read Tom Kyte's blog. But have a look at this helpful page of advice from Tech On The Net, under "Oracle/PLSQL: SQLERRM Function"
You could use the SQLERRM function to raise an error as follows:
EXCEPTION WHEN OTHERS THEN raise_application_error (-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END;Or you could log the error to a table as follows:
EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; err_msg := substr(SQLERRM, 1, 200); INSERT INTO audit_table (error_number, error_message) VALUES (err_code, err_msg); END;
Now that is even better. The first example adds some meaningless text, hides the original line number, and duplicates the error code (unless it's a NO_DATA_FOUND exception, but let's not go there), to produce something like this:
ORA-20001: An error was encountered - -6501 -ERROR- ORA-06501: PL/SQL: program error
ORA-06512: at line 11
The error logging example pointlessly captures SQLCODE (nobody will ever use it), throws away all but the first 200 characters of the error stack, logs nothing about what happened, and fails to re-raise the exception so if you don't check the log you won't know anything went wrong until your customers start asking where their stuff is.
Wouldn't it be great if there were, say, a Boolean third parameter to RAISE_APPLICATION_ERROR that would make it retain the existing error stack, freeing up the message line for you to put something intelligent and helpful, like, I don't know,
BEGIN
RAISE program_error;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001
, 'Biscuits cannot be ordered on a ' || TO_CHAR(SYSDATE,'fmDay') ||
' without a hot beverage'
, TRUE);
END;to produce something like this:
ORA-20001: Biscuits cannot be ordered on a Sunday without a hot beverage ORA-06512: at line 5 ORA-06501: PL/SQL: program error
We can but dream.
Friday, October 26, 2007
Frameworkia
We thought long and hard about possible titles for this new PL/SQL development standard proposed on OTN, but we couldn't improve on the one it came with.
I want share a new IDEA to create a new standard PL/SQL developing:
Function( Standard Buffer) return number variable Number; variable1 Varchar2; begin variable := get from Standard Buffer; variable1 := get from Standard Buffer; { make your business } put in standard buffer your results end; Give me feedback if you are interested at the new STANDARD called "FRAMEWORKIA".
A lot possibilities are ready.
Do you see the genius of it?
Er, no.
Sensing that there were people who still needed convincing, user601181 posted some sample code developed using the new Frameworkia:
CREATE OR REPLACE FUNCTION iacore
( eobufferia IN OUT typeeodata )
RETURN NUMBER
IS
CURSOR cur_getroutingcondition
( idc_workflow IN VARCHAR2
, idc_operation_node IN VARCHAR2 ) IS
SELECT *
FROM wf_condition
WHERE id_workflow = idc_workflow
AND id_operation_node = idc_operation_node;
rec_getroutingcondition cur_getroutingcondition%ROWTYPE;
CURSOR cur_dobufferiaassign
( idc_workflow IN VARCHAR2
, idc_operation_node IN VARCHAR2 ) IS
SELECT *
FROM wf_assignement
WHERE id_workflow = idc_workflow
AND id_operation_node = idc_operation_node;
rec_dobufferiaassign cur_dobufferiaassign%ROWTYPE;
next_node NUMBER;
next_node_ck NUMBER;
stop_node NUMBER;
operation VARCHAR2(256);
operation_call VARCHAR2(256);
type_node VARCHAR2(32);
workflow VARCHAR2(32);
line VARCHAR2(256);
status_wf_v VARCHAR2(3);
pid_chain_node NUMBER;
ia_tid VARCHAR2(64);
ia_tid_micro VARCHAR2(64);
ret_code_default NUMBER;
ret_code NUMBER;
retval1 NUMBER;
statementexc VARCHAR2(256);
schema_function VARCHAR2(32);
package_function VARCHAR2(32);
dblink_function VARCHAR2(32);
first_node_flag VARCHAR2(2) := 'NO';
id_debug_source NUMBER;
mapin_keyp VARCHAR2(1024);
headerbufferia typebufferia;
assignbufferia typebufferia;
checkbufferia typebufferia;
rec_wfnode wf_node%ROWTYPE;
rec_wffunctionsourcecode wf_function_source_code%ROWTYPE;
rec_wflogger wf_logger%ROWTYPE;
rec_wfbusiness wf_business%ROWTYPE;
rec_wffieldmapping wf_fieldmapping%ROWTYPE;
BEGIN
headerbufferia := eobufferia(1);
workflow := frameworkia.getvalue(headerbufferia,'ID_WORKFLOW');
---- DETERMINO QUALE NODO INVOCARE
pid_chain_node := frameworkia.getvalue(headerbufferia,'WF_NODE_ID');
----- SE IL NODO E' NULL ALLORA E' IL PRIMO NODO
IF pid_chain_node IS NULL
THEN
-------DETERMINO HANDLER E FILENAME PER IL LOGGER
SELECT *
INTO rec_wflogger
FROM wf_logger
WHERE id_workflow = workflow;
-- rec_WfLogger.ID_WORKFLOW
-- rec_WfLogger.ID_DEBUG_LEVEL
-- rec_WfLogger.ID_DIRHANDLER
-- rec_WfLogger.ID_FILENAME
--------INSERISCO NELL'HEADER
frameworkia.setvalue
( headerbufferia
, 'ID_DEBUG_WF'
, rec_wflogger.id_debug_level );
frameworkia.setvalue
( headerbufferia
, 'ID_DIRHANDLER'
, rec_wflogger.id_dirhandler );
frameworkia.setvalue
( headerbufferia
, 'ID_FILENAME'
, rec_wflogger.id_filename );
frameworkia.setvalue
( headerbufferia
, 'CHARACTER_EVIDENCE'
, '§§§§§§§§§§§§§§§§§§§§' );
-------DETERMINO L'ID NODE
SELECT wf_node_id
INTO pid_chain_node
FROM wf_node
WHERE id_workflow = workflow
AND wf_first_node = 'YES';
SELECT *
INTO rec_wfnode
FROM wf_node
WHERE id_workflow = workflow
AND wf_first_node = 'YES';
frameworkia.setvalue
( headerbufferia
, 'WF_NODE_ID'
, rec_wfnode.wf_node_id );
SELECT b.status
INTO status_wf_v
FROM wf_node a
, wf_name b
WHERE a.id_workflow = workflow
AND a.wf_node_id = rec_wfnode.wf_node_id
AND a.id_workflow = b.id_workflow;
IF status_wf_v = 'OFF'
THEN
RETURN -1;
END IF;
ia_tid := frameworkia.getvalue(headerbufferia,'IA_TID');
ret_code_default := 0;
ret_code := 0;
frameworkia.setvalue
( headerbufferia
, 'RET_CODE_DEFAULT'
, ret_code_default );
frameworkia.setvalue
( headerbufferia
, 'RET_CODE'
, ret_code);
IF ia_tid IS NULL
THEN
ia_tid := 'TIA' || dbms_random.STRING('U',1 * 1 + 6) ||
TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6');
frameworkia.setvalue
( headerbufferia
, 'IA_TID'
, ia_tid );
END IF;
That's just the first hundred lines, and I've formatted it. The complete iacore function was well over 600 lines long.
I for one welcome the new standard.
Tuesday, August 14, 2007
What is this "testing" thing?
Recently asked on the OTN PL/SQL forum:
I am reading a book: Learning Oracle PL/SQL by Bill Pribyl and Steven Feuerstein.
I am a newbie of PL/SQL and I got no other programming XPs. but in the very first of this book they are introducing something called: testing, to make some test programms /utilites.
I found that is very hard to make, is it neccessary?
Apparently, after you've typed the final semicolon, you can either sit back and open a beer with the satisfaction of a job well done, or you can check whether the thing works. And apparently it's hard, so, do we really have to?
Well, another poster was recently arguing that PL/SQL is not suited to unit testing anyway, or refactoring either for that matter, because it isn't object oriented. I think that will be my excuse from now on. You want me to test stuff, go make PL/SQL more objecty.
Friday, August 10, 2007
Welcome back
Our guest administrator "Splogger" has now left the building, along with his page of helpful links to items on Amazon.com and a range of gentlemen's health products.
Suspiciously, a couple of days before he arrived we were taken off air by Blogger's spambots, presumably alerted by the amount of irrelevant, repetitive, and nonsensical text and links to Viagra sites they found here. From what I read, it seems possible that the Blogger automated suspension to prevent blog spam might have actually left the account vulnerable to blog spammers. As ironies go, that is up there with rain on your wedding day and good advice that you just didn't take.
Thursday, May 03, 2007
One liner
I was untangling a query when I came across eight variations of this code where only the literals 'a' to 'e' changed. Two of these were nested within an additional NVL so that the second would execute if the first returned null.
nvl(decode(2, 1, 'a', 2, 'b', 3, 'c', 4, 'd', 5, 'e', ' '), 'na')
Sunday, April 08, 2007
DATE comparisons: the scenic route
Need to calculate the number of minutes between two dates? Yes, the dull way is to subtract one from the other and multiply by 1440. But why do that, when you can simply convert each date into Julian format by converting it into a string and then back to a date, and converting the resulting date into a Julian string, and (in a separate step) applying TO_NUMBER to the result to get an integer, so that you can simply subtract one from the other and multiply by 1440.
Except that rounds to the nearest day, so an additional step is to calculate the minutes since midnight for each of the two dates, which you can do by simply converting each date into an 'HH:MI AM' string, then back to a date, then back to an 'SSSSS' string, converting it to a number, dividing by 60 and adding it to the result of the first calculation. I think.
Anyway, consider the following library of handy date functions our Oracle WTF Easter gift to you, the online development community.
CREATE PACKAGE dates_pkg
AS
FUNCTION julian_date
( date_to_convert DATE )
RETURN NUMBER;
FUNCTION minutes_since_midnight
( timevalue DATE )
RETURN NUMBER;
FUNCTION minutes_elapsed
( lowdate DATE
, highdate DATE )
RETURN NUMBER;
END dates_pkg;
/
CREATE PACKAGE BODY dates_pkg
AS
FUNCTION julian_date
( date_to_convert DATE)
RETURN NUMBER
IS
varch_value VARCHAR (10);
num_value NUMBER (20);
BEGIN
SELECT TO_CHAR
( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
, 'J')
INTO varch_value
FROM dual;
SELECT TO_NUMBER (varch_value)
INTO num_value
FROM dual;
RETURN (num_value);
END julian_date;
FUNCTION minutes_since_midnight (
timevalue DATE)
RETURN NUMBER
IS
secs_elapsed NUMBER (20);
mins_elapsed NUMBER (20);
BEGIN
SELECT TO_NUMBER
( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
, 'SSSSS') )
INTO secs_elapsed
FROM dual;
SELECT (secs_elapsed / 60)
INTO mins_elapsed
FROM dual;
RETURN (mins_elapsed);
END minutes_since_midnight;
FUNCTION minutes_elapsed
( lowdate DATE
, highdate DATE )
RETURN NUMBER
IS
final_number NUMBER (20);
low_julian NUMBER (20);
high_julian NUMBER (20);
num_days NUMBER (20);
num_minutes NUMBER (20);
temp_mins NUMBER (20);
min_low NUMBER (20);
min_high NUMBER (20);
BEGIN
SELECT julian_date (lowdate)
INTO low_julian
FROM dual;
SELECT julian_date (highdate)
INTO high_julian
FROM dual;
SELECT (high_julian - low_julian)
INTO num_days
FROM dual;
SELECT (num_days * 1440)
INTO num_minutes
FROM dual;
SELECT minutes_since_midnight (lowdate)
INTO min_low
FROM dual;
SELECT minutes_since_midnight (highdate)
INTO min_high
FROM dual;
SELECT (min_high - min_low)
INTO temp_mins
FROM dual;
SELECT (num_minutes + temp_mins)
INTO final_number
FROM dual;
RETURN (final_number);
END minutes_elapsed;
END dates_pkg;
Just for fun, let's test it:
CREATE TABLE wtf_test (start_date NOT NULL, end_date NOT NULL) AS
SELECT DATE '2006-12-25' + DBMS_RANDOM.VALUE(1,365)
, DATE '2007-12-25' + DBMS_RANDOM.VALUE(1,365)
FROM dual CONNECT BY LEVEL <= 1000;
-- ...several runs here to allow for caching etc, last set of results shown...
SQL> set timing on autotrace traceonly stat
SQL> SELECT dates_pkg.minutes_elapsed(start_date,end_date) FROM wtf_test;
1000 rows selected.
Elapsed: 00:00:03.96
Statistics
----------------------------------------------------------
16000 recursive calls
0 db block gets
74 consistent gets
0 physical reads
0 redo size
9330 bytes sent via SQL*Net to client
809 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> SELECT (end_date - start_date) * 1440 FROM wtf_test;
1000 rows selected.
Elapsed: 00:00:00.16
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
74 consistent gets
0 physical reads
0 redo size
25485 bytes sent via SQL*Net to client
809 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
So the handy package version takes 25 times as long as the 1-line SQL version.
And in the interests of fairness, in case you're thinking perhaps that is just the normal overhead of calling PL/SQL functions in SQL, let's try our own function:
CREATE FUNCTION minutes_elapsed
( lowdate DATE
, highdate DATE )
RETURN NUMBER
AS
BEGIN
RETURN (highdate - lowdate) * 1440;
END minutes_elapsed;
/
SQL> SELECT minutes_elapsed(start_date,end_date) FROM wtf_test;
1000 rows selected.
Elapsed: 00:00:00.26
Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
107 consistent gets
0 physical reads
0 redo size
25496 bytes sent via SQL*Net to client
809 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
Still 15 times faster.
Many thanks to Padders for sharing this one.
