PL/SQL quickie returning results with RETURNING clause

Oh how I love a (good) pun.

In depth blog posts are great, but sometimes little quick examples are all we feel like consuming.

Those out there new(ish) to PL/SQL, familiarise yourselves with the RETURNING clause, it can provide a useful efficiency. See this example here which shows the potential - grab extra information while updating a table instead of requiring another SELECT. You can also use it in conjunction to BULK COLLECT.

CREATE TABLE sw_temp (a NUMBER ,b NUMBER);

 

DECLARE

  l_a  sw_temp.a%TYPE;

  l_b  sw_temp.b%TYPE;

BEGIN

  INSERT INTO sw_temp VALUES (1, 1);

 

  UPDATE sw_temp

  SET a = 2

  RETURNING a, b

  INTO l_a, l_b;

   

  dbms_output.put_line('a:'||l_a);

  dbms_output.put_line('b:'||l_b);

END quickie;

/

 

a:2

b:1

 

PL/SQL procedure successfully completed.

 

DROP TABLE sw_temp;

Source: http://www.grassroots-oracle.com/2011/02/plsql-quickie-returning-results.html

On wrapping & obfuscating PL/SQL

The Oracle database provides the ability to obfuscate PL/SQL code using the wrap utility.

Many moons ago when I was working on a 9i database, I encountered an issue where my information wasn't completely obfuscated. I had a package that was performing some encryption, and I wanted to ensure the seed to my encryption method was hidden. Take the following example:

create or replace procedure seeder is

  vc varchar2(20) := 'This string';

begin

  null;

end;

/

I would expect this information to be wrapped, just like the rest of my code.

exec  DBMS_DDL.CREATE_WRAPPED(dbms_metadata.get_ddl(object_type => 'PROCEDURE', name => 'SEEDER'));

And the resulting code had a different feel about it:

select dbms_metadata.get_ddl('PROCEDURE' ,'SEEDER') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','SEEDER')

--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE "SAGE"."SEEDER" wrapped

a000000

b2

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

7

4f 92

t5QJKAdccmuGzbpujO65PNpHmLQwg5nnm7+fMr2ywFxpnp8yMlKyCQmldIsJaefHK+fHdMAz

uHRlJXxlUMNLwlxpKPqFVisW0T6XRwzqxIvAwDL+0h3l0pmBCC2LwIHHLYsJcKamqIi2Mg==

1 row selected.

 
So it seems that the algorithm has improved and being able to "see" strings in the wrapped code is no longer a problem.

Once again another demonstration of how "known knowns" can change over time, and you must always test behaviour on your version; your infrastructure.

Source: http://www.grassroots-oracle.com/2009/10/on-wrapping-obfuscating-plsql.html

Tuning query over database link

We all are know about Oracle Data Base Link and I called it dblink in short throughout this article. Usisg dblink you can easily access remote Database Object. Now Please think the following situation:

Situation:

Suppose you have 2 tables: 1. table1 in local DB 2. table2 in remote DB.The table1 size is 10 MB and table2 size is 100 MB. We need to join those two tables and we access only in local DB.

If you now think about performance, it is important to where the sql query executed and the join perform. For above situation, it is better to bring table1 from local DB to Remote DB and execute the sql in remote server and finally bring back the result in local DB.

Lets do the job

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:

/*+DRIVING_SITE(table)*/

where table is the name or alias for the table at which site the execution should take place.

Example:

SELECT /*+DRIVING_SITE(table2)*/ * FROM tabl1, table2@remote_db
WHERE table1.DEPTNO = table2.DEPTNO;

If this query is executed without the hint, rows from table2 will be sent to the local site and the join will be executed there. With the hint, the rows from table1 will be sent to the remote site and the query will be executed there, returning the result to the local DB.

Source:  http://shaharear.blogspot.com/2009/02/drivingsite-sql-hint.html

OLTP vs. OLAP

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).

- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).


The following table summarizes the major differences between OLTP and OLAP system design.

	OLTP System Online Transaction Processing (Operational System)	OLAP System Online Analytical Processing (Data Warehouse)
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method
source: www.rainmakerworks.com

Bind variables - The key to application performance

If you've been developing applications on Oracle for a while, you've no doubt come across the concept of 

«Bind Variables». Bind variables are one of those Oracle concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and how you need to alter your programming style to use them.

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example: SELECT fname, lname, pcode FROM cust WHERE id = 674; SELECT fname, lname, pcode FROM cust WHERE id = 234; SELECT fname, lname, pcode FROM cust WHERE id = 332; Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself. When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas, which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time. The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set. The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit SELECT fname, lname, pcode FROM cust WHERE id = :cust_no; and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset. Bind Variables in SQL*Plus In SQL*Plus you can use bind variables as follows: SQL> variable deptno number SQL> exec :deptno := 10 SQL> select * from emp where deptno = :deptno; What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement) Bind Variables in PL/SQL Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the issues to do with bind variables, to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL: create or replace procedure dsal(p_empno in number) as   begin     update emp     set sal=sal*2     where empno = p_empno;     commit;   end; / Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable. Dynamic SQL In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL. Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted: create or replace procedure dsal(p_empno in number) as   begin     execute immediate      'update emp set sal = sal*2 where empno = '||p_empno;   commit;   end; / The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows: create or replace procedure dsal(p_empno in number) as   begin     execute immediate      'update emp set      sal = sal*2 where empno = :x' using p_empno;   commit;   end; / And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on. The Performance Killer Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test: Here is the Performance Killer .... SQL> alter system flush shared_pool; SQL> set serveroutput on; declare       type rc is ref cursor;       l_rc rc;       l_dummy all_objects.object_name%type;       l_start number default dbms_utility.get_time;   begin       for i in 1 .. 1000       loop           open l_rc for           'select object_name              from all_objects             where object_id = ' || i;           fetch l_rc into l_dummy;           close l_rc;           -- dbms_output.put_line(l_dummy);       end loop;       dbms_output.put_line        (round((dbms_utility.get_time-l_start)/100, 2) ||         ' Seconds...' );   end; / 101.71 Seconds... ... and here is the Performance Winner: declare       type rc is ref cursor;       l_rc rc;       l_dummy all_objects.object_name%type;       l_start number default dbms_utility.get_time;   begin       for i in 1 .. 1000       loop           open l_rc for           'select object_name              from all_objects             where object_id = :x'           using i;           fetch l_rc into l_dummy;           close l_rc;           -- dbms_output.put_line(l_dummy);       end loop;       dbms_output.put_line        (round((dbms_utility.get_time-l_start)/100, 2) ||         ' Seconds...' ); end; / 1.9 Seconds... That is pretty dramatic.  The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously. Bind Variables in VB, Java and other applications The next question is though, what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself? In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database. For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature. Conclusion Lastly, it's worth bearing in mind that there are some instances where bind variables are probably not appropriate, usually where instead of your query being executed many times a second (as with OLTP systems) your query in fact actually takes several seconds, or minutes, or hours to execute - a situation you get in decision support and data warehousing. In this instance, the time taken to hard parse your query is only a small proportion of the total query execution time, and the benefit of avoiding a hard parse is probably outweighed by the reduction in important information you're making available to the query optimizer - by substituting the actual predicate with a bind variable, you're removing the ability for the optimiser to compare your value with the data distribution in the column, which might make it opt for a full table scan or an index when this isn't appropriate. Oracle 9i helps deal with this using a feature known as bind variable peeking, which allows Oracle to look at the value behind a bind variable to help choose the best execution plan. Another potential drawback with bind variables and data warehousing queries is that the use of bind variables disallows the potential for star transformations, taking away this powerful option for efficiently joining fact and dimension tables in a star schema. Source: http://www.akadia.com/services/ora_bind_variables.html

            

Common Problems with Indexes

Indexes

There are several types of indexes. The oldest and most popular type of Oracle indexing is a standard b-tree index, which excels at servicing simple queries. Another useful type is the function-based index. In all likelihood, the indexes you are dealing with will be b-tree indexes, or you might have a few function-based indexes. Indexes are great when they work as you’d expect them to, but things can go wrong.
Note: indexes cause a performance hit in DML operations, and should only exist when they’re actually useful!

1. Common Problems with Indexes

The following describes a few scenarios where Oracle may not behave as you would expect.

1.1. Case A

You’re executing a query, but Oracle seems to be choosing some odd execution path and you’re sure there’s a better way to do it (i.e., use your index). Your statistics may be out-of-date or not accurate enough. That is, Oracle thinks using the index is the wrong way to go. See below section on generating statistics.

1.2. Case B

You’re doing SELECT COUNT(*) FROM T — and you have an index on T, but you notice that Oracle is doing a full table scan, rather than using the index. This is most likely because the column being indexed is nullable, meaning the index count could be less than the true row count, so it has to go the the table to get the true count. If the column was non-nullable then Oracle would have used the index instead — this is one of the reasons why you should only allow null values in a column when there is a good case for it. See below for a way to take advantage of nullable columns.

1.3. Case C

You have an index on X and you’re doing SELECT * FROM T WHERE f(X) = VALUE. You find the index is not being used; this is because of the use of the function. This may be a candidate for a function index instead.

1.4. Case D

You have a table T with a compound index (i.e., an index based on more than one column) on columns (X,Y). You do SELECT * FROM T WHERE Y = 5. This index will not be used since the query predicate did not involve column X. However, the index would be used for a query like SELECT X, Y FROM T WHERE Y = 10, as Oracle realises it does not need to go to the table to get X and Y as the values are in the index itself.

1.5. Case E

Suppose you have a table with primary key X and nullable column Y. Suppose there are 100 rows in it, where X ranges from 0 to 100. Doing SELECT COUNT(Y) FROM T WHERE X < 50 will result in a full table scan rather than an index access. Why? Because Oracle realised it would be retrieving 50% of the rows. If it used the index it would have to read an index block and process each of the rows on it. Then for every other row it would have do a database block get to get the row data. So it is more efficient to just read in the database block and find the 50 rows we’re interested in.
Consider the same query, and the same table, but with many more rows, and X ranging from 0 to 5000, say. In this case the index would be used, as it would be faster than a full table scan.

1.6. Case F

You have a column which has highly-skewed data (i.e., 98% of the values in this column are X, while the other 2% are Y and Z). This type of a column will gain little benefit from an index when querying for X, and Oracle will not use it. Histograms, described in a later section, are extra column statistics which greatly aid Oracle in making decisions on columns like these.

2. Nullable Columns & Indexes

B-tree indexes will not store completely null entries, so, typically, nullable columns will make any indexes on the column(s) slightly less effective — but it can also be used to an advantage.

Say you have a table with a column that takes exactly two values. Say the values are skewed; 90% of rows take one value, the other 10% take another. This can be indexed efficiently to gain access to the minority rows. The solution is to use a null for the majority rows and whatever other value you want for the minority rows. Say you have a JOB table with a nullable column called TASK_PENDING (which is a timestamp). When the a row is inserted, this column is given a timestamp. Now whatever processes the JOB table can just do a SELECT * FROM JOB WHERE TASK_PENDING IS NOT NULL. When it processes the tasks it can mark them as null, effectively removing it from the index. Therefore the index remains very small and efficient.

Seee more: http://www.credmond.net/oracle/tuning/