I have been profiling a number of functions and procedures and this has highlighted a big difference (in the wrong direction, unfortunately) in performance of some individual statements within a number of functions after moving from v10.0.1 to 12.0.1.
Some of these statements are not straight selects or updates, but are in the form of a Watcom IF connstruct eg:
This is just one of half a dozen similarly constructed statements in the function.
The application profiling / tracing viewer treats everything between the initial BEGIN and the final END as a single statement, so no plan is available. Obviously I could rewrite the statement as something like
then run that from isql and get a plan, but the query isn't quite the same and the context is quite different.
Is there some way of getting the plans for such statements as the are actually executed, in the context of the function / procedure?
Screen shot showing how the whole procedure is treated as a single statement, with no plan tab
There is some functionality to obtain these plans, Breck walks through it fairly well here:
Edit: You won't be able to diagnose exactly as in 9.0.2. In later versions, you'll be able to specify plan logging with -zr ALL or -zr ...+PLAN and direct the output with -zo
Of course, you can always wrap a query in a GRAPHICAL_PLAN call to get the necessary plan details from within the stored procedure or function:
CALL xp_write_file('my_plan.saplan', // write plan to a file GRAPHICAL_PLAN('SELECT * FROM my_table',2) // 2 for detailed statistics );
Documentation for GRAPHICAL_PLAN is at:
I really don't understand, why only SA still don't has convenient mechanism for getting plans of any sql statements, without voodoo. Using Tracing/LogExpensiveQueries is not that thing that all wants today. For some statement 1min is very fast, for another -1ms - very slow. So how to use LogExpensiveQueries in that case? Please, just give us simple mechanism getting any plans without voodoo, like any other RDBMS has.
answered 26 Jan '12, 07:45
I see the[/an] issue now.
The database server is not tracing the value of the expression tested in the IF statement. It's hard to say exactly why this is, but it could be that the EXISTS keyword is usually treated as a predicate for a query which creates a subquery. Since the IF statement isn't quite a query, the EXISTS subquery is not easily tied to the IF statement.
But, there is in fact request logging for these statements (in my test using "dbsrv12 -zr ALL -zp -zo rll.log ...").
For example, the following code:
CREATE OR REPLACE PROCEDURE myProc() RESULT ( ret LONG VARCHAR ) BEGIN DECLARE ret LONG VARCHAR; IF EXISTS(SELECT * FROM SYS.SYSTAB WHERE table_id = 3408) THEN SET ret = '1'; END IF; IF EXISTS(SELECT * FROM SYS.SYSTAB WHERE table_name = 'Robert') THEN SET ret = '2'; END IF; IF EXISTS(SELECT * FROM SYS.SYSPROCEDURE WHERE proc_name = 'myProc') THEN SELECT COUNT(*) INTO ret FROM SYS.SYSPROCEDURE WHERE proc_name = 'myProc'; END IF; SET ret = '0'; SELECT ret; END; CALL myProc();
Produces the following plan information in the request log:
+1,P,1,[R]tab<ISYSTAB(IO)> =,P,1,[R]tab<table_name(IO)> +1,P,1,[R]b<procedure_name(IO)> =,[,1,myProc,15,select COUNT() into ret from SYS.SYSPROCEDURE where proc_name = 'myProc' +12,P,1,[R]GrByS[ b<procedure_name(IO)> ]
As you can see, the output is fairly obscure. Also, the SELECT COUNT(*)... plan is pretty clearly tied to its query. This output can get better if the query can be executed apart from the if statement (although not ideal) as in:
DECLARE test INTEGER; SELECT 1 INTO test FROM SYS.DUMMY WHERE EXISTS(...); // or SELECT TOP 1 1 INTO test FROM ... IF test = 1 THEN //it exists! END IF;
Again, not ideal, but I hope this can provide some more ammo to tackle the problem.
answered 26 Jan '12, 20:39