Usn's IT Blog

Oracle finally desupported the rule-based optimizer with version 10g. Since 9i, users are good adviced to make themself familiar with the cost-based-optimizer (CBO). In order to understand what happens with your statement after parsing with CBO involved, and to be able to tune your queries, displaying, understanding and optimizing explain- or execution plans is essential.

First of all, what’s a explain- or execution plan of a query? It’s a set of operations that the DB has to perform in order to deliver the correct result set. The plan is computed by the (cost-based) optimizer based on several conditions in the “environment” of the queried object (usually a table): It considers indexes, optimizer statistics, histograms, load, memory, and so on, and looks for an optimal path to achieve the goal. Thus, this plan dictates if the query runs in a efficient or wasteful way. The result set itself is – hopefully – always the same.

I don’t like to elaborate the whole tuning chapter here, because you can find real pro’s on this topic on the net, just to mention two: Karen Morton, Richard Foote and many more. This post is just a quick sheet on displaying plans with Oracle’ own toolset.

But initially you should understand a basic difference:

Read more…

Sometimes you have to recreate a user in a DB, and you like it to be the same as before again. To have its DDL at hand is usually rather convenient. DBMS_METADATA is a great toolbox to get it, my example is just a short but hopefully useful excerpt of its possibilities:

set long 200000 pages 0 lines 131
column meta format a121 word_wrapped
select dbms_metadata.get_ddl('USER', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from dual;

Take care
Usn

EDIT: Thanks to Ask Tom for the settings to use in SQL*PLUS.

EDIT2: Changed Username to input variable

On a headless (=without console) network server, the 11g JDBC driver used for (java) application connect may cause trouble. In my case, it refused to connect to the DB without any error, trace or log entry. It simply hung. After several hours, it connected one time, and freezed again. Remote debugging done by the development clarified that it locks after calling SeedGenerator() and SecureRandom().
Read more…

For comparing, classifying, benchmarking and forecasting databases, Oracle’s Automatic Workload Repository (AWR) is neat. (But remember! You will need a diagnostics pack license for that as described here and here – if you need a free technology for performance monitoring, consider using Craig Shallahamer’s OSM toolkit.)

This post simply describes the package calls to create a AWR report, for details please see the Oracle documentation or oracle-base.com.

If you need to create a snapshot manually, because you don’t like the one-hour interval, or if you disabled taking snapshots at all:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Create a report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

This script will ask you for the format of the report (html or plain text), the snapshot ID for start and end of the report (an overview of the last n day’s snapshots is given) and for a report name, that’s used for the report file name (.html or .lst).

Stay happy with your databases,
Usn