




Remote DBA services Remote DBA Support Remote DBA RAC Remote DBA Reasons Remote Oracle Tuning Remote DBA Links Oracle DBA Support Oracle DBA Forum Oracle Disaster Oracle Training Oracle Tuning Oracle Training Remote DBA SQL Server Remote MSSQL Consulting Oracle DBA Hosting Oracle License Negotiation

GET Method

Oracle Tips by Burleson Consulting

So far, all of the forms have been using the post method, which sends forms elements in the header; the preferred method for security reasons. There is also a way to emulate command line arguments by encoding the arguments in the URL itself. This method of argument passing does not necessarily need HTML forms; it can be used by any link.

The next example demonstrates the “GET” method by doing something useful. It explains the plan for running a SQL statement by querying the v$sql_plan table. This method works on Oracle RDBMS v9.2 and newer databases only, as it uses the table that did not exist prior to the version 9.2. This example also establishes login by using the same script from Example 13.

The well known line where the header function is invoked will be changed to call the script named “queries_elapsed.php”.

The file “queries_elapsed.php” is not unusual at all. It is just another implementation of the displayQueryAsTable() function, following to the letter, the example already shown in this chapter.

The query in the “queries elapsed” is a query against V$SQL table sorting them by elapsed time, in descending order. Such queries are taught in any book dealing with the performance tuning of an Oracle instance.

The twist is in the query itself. To understand the query, another incursion into the Oracle PL/SQL territory has to be made. For the query to work, the following PL/SQL function is utilized:

Example 19

create or replace function
     expl_link(addr in raw default null ,
               hsh in number default null )
     return varchar2 deterministic
as
anch varchar2(512):='<A href="sql_plan.php?address=';
begin
   if ((addr is not null) and (hsh is not null))
      then anch:=anch||addr||'&hash='||hsh||'">Plan</A>';
      return(anch);
   else return(NULL);
   end if;
end;
/

So, why is this function so vital? What does it do? Peering into SQL*Plus may reveal some answers:

SQL> select expl_link('FF',1) from dual;
EXPL_LINK('FF',1)
---------------------------------------------
<A href="sql_plan.php?address=FF&hash=1">Plan</A>
SQL>

This function takes the address and hash value and returns a URL formed like this:

filename?ARG1=val1&ARG2=val2&...&ARGN=valn

This is a demonstration of the GET method for transferring data. The ARG1,ARG2,...,ARGN are accessible from within a PHP script as $_GET['ARG1'],$_GET['ARG2'],...,$_GET['ARGN'].

This method makes it possible to pass arguments from PL/SQL functions. So, one should review “queries_elapsed.php”:

Example 19 (Cont.)

<html>
<head>
<title>Queries, Sorted by Elapsed Time</title>
</head>
<body>
<center>
<?php
require_once('OCI_Session.php');
require_once('displayQueryAsTable.php');
session_start();
$dbh=$_SESSION['dbh'];
$SQL='select EXECUTIONS,PARSE_CALLS,DISK_READS,
      BUFFER_GETS,CPU_TIME, ELAPSED_TIME,
      expl_link(address,hash_value) EXECUTION_PLAN,SQL_TEXT
      from V$SQL
      order by ELAPSED_TIME desc';
try {
      $dbh->refresh();
      if (!$dbh->db) {
         $e=$dbh->err;
         throw new Exception ('CONN:'.$e['message']);
      }
      $sth=oci_parse($dbh->db,$SQL);
      if (!$sth) {
         $e=oci_error($sth);
         throw new Exception ('PARSE:'.$e['message']);
      }
      if (!oci_execute($sth)) {
          $e=oci_error($sth);
          throw new Exception ('EXEC:'.$e['message']);
      }
}
catch (Exception $e) {
       print $e->getMessage();
}
      displayQueryAsTable($sth,'frame="border" border="3"
rules="rows,cols"');
?>

When invoked from example19.php (example13a.php with the changed header('Location:....') line), the resulting page looks like the following:

The column “EXECUTION_PLAN” is shown in a different color because each entry in that column is a HTTP link, formed by the expl_link PL/SQL function in the query. Each of those links looks like this:

http://localhost/work/sql_plan.php?address=60E5D9E0&hash=426038

When following any of those links, the result looks like what is shown in the following picture. The usual way of showing an execution plan is in tree format, but the same thing is perfectly recognizable and useful in the tabular format as well.

This is an easily recognizable Oracle execution plan for a SQL statement. Everything is here: level, operation, and cost - only in tabular format instead of the tree format. The script performing the magic is, of course, sql_plan.php from the expl_link PL/SQL function. The following example shows what the sql_plan.php script looks like:

Example 19 (Cont.)

<?php
require_once('OCI_Session.php');
require_once('displayQueryAsTable.php');
session_start();
$dbh=$_SESSION['dbh'];
$SQL='select level,operation,options,object_name,object_type,
             optimizer,cost,cardinality
      from (
             select * from   V$SQL_PLAN
             where address=:addr and hash_value=:hash_val
           )
      connect by prior id = parent_id
      start with id=0';
try {
    $dbh->refresh();
    if (!$dbh->db) {
       $e=$dbh->err;
       throw new Exception ('CONN:'.$e['message']);
    }
    $sth=oci_parse($dbh->db,$SQL);
    if (!$sth) {
         $e=oci_error($sth);
         throw new Exception ('PARSE:'.$e['message']);
    }
    if (!oci_bind_by_name($sth,":addr",$_GET['address'],64)) {
         $e=oci_error($sth);
         throw new Exception ('BIND ADDR:'.$e['message']);
    }
    if (!oci_bind_by_name($sth,":hash_val",$_GET['hash'],64)) {
         $e=oci_error($sth);
         throw new Exception ('BIND HASH:'.$e['message']);
      }
      if (!oci_execute($sth)) {
          $e=oci_error($sth);
          throw new Exception ('EXEC:'.$e['message']);
      }
}
catch (Exception $e) {
       print $e->getMessage();
}
      displayQueryAsTable($sth,
     'frame="border" border="3" rules="rows,cols"');
?>

The arguments are fetched using the $_GET “super-global” PHP array, bound to the placeholders within the SQL statement, and the statement is executed. The results are then shown as a HTML table using the well known function, displayQueryAsTable(). So, except using the “GET” method and helping with a PL/SQL function, there is really nothing special in this script.

The weirdest and certainly the most interesting thing in the script is the SQL statement used to retrieve the execution plan itself. It is written in this way because of what looks like an optimizer bug in Oracle 10.1.0.3.0. If the query is written as the following, it will work for an extensive amount of time because the database will attempt to perform a “connect by” on the whole table and then filter out the results.

select level,operation,options,object_name,object_type,
optimizer,cost,cardinality
from V$SQL_PLAN
where address=:addr and hash_value=:hash_val
connect by prior id = parent_id
start with id=0;

v$sql_plan contains a record for every SQL in the library cache, which can be a large amount of records. This is especially true when it is known that the plans for recursive SQL statements, executed by the instance itself for maintenance purposes are also in the v$sql_plan table.

The only way to have one of those queries finish before in a timely manner is to make sure that Oracle first filters out the interesting records and performs a “connect by” operation on the significantly smaller set of rows.

Since this is not a book about tuning SQL statements, this chapter ends here. Those interested in SQL tuning can consult books by Cary Millsap, Jonathan Lewis or Tom Kyte.

SEE CODE DEPOT FOR FULL SCRIPTS