Instant SQL Formatter (beautify)

I quite often have a need to reformat RDBMS SQL statements into something readable and consistent. The advantage of doing this is the statements are easier on the eye to read.

Two web sites I find useful in this respect are:

http://www.dpriver.com/pp/sqlformat.htm

http://sqlformat.org/

Keywords: sql format beautify

Oracle ODP.NET and Windows PowerShell

Use Oracle ODP.NET and PowerShell to Simplify Data Access

The above link is an interesting article on the subject of using Oracle ODP.NET and Windows PowerShell to access to Oracle databases.

In order to do this, a complete package called ODAC ( Oracle Data Access Components ) from Oracle can be downloaded to provide the components in order to achieve this.

The site 'Data Provider for .NET Developer's Guide' (http://docs.oracle.com/cd/E51173_01/win.122/e17732/client.htm#ODPNT0008) describes the Oracle Data Provider for .NET classes which will be helpful in developing any Oracle ODP.NET application or script.

The Data Provider for .NET Developer's Guide can be found at http://docs.oracle.com/cd/E51173_01/win.122/e17732/toc.htm

Keywords: oracle powershell odp .net

Oracle SQL Plan Stability

Tanel Poder has written an article on his blog on the subject of Oracle SQL Plan Stability. The article contains a number of useful links to other pages on the subject.

Click here for the article.


See also

Tanel Poder's blog.

Oracle performance and O/S Statistics

Operating system statistics provide information on the usage and performance of the main hardware components of the system, as well as the performance of the operating system itself.

Operating System Data Gathering Tools:

• CPU - sar, vmstat, mpstat, iostat
• Memory - sar, vmstat
• Disk - sar, iostat
• Network - netstat

Reference
Oracle Database Performance Tuning Guide

Gather Statistics for the Cost Based Optimizer

The following article describes gathering statistics for the CBO (Cost Based Optimizer).

Goal

Provide pointers to the various articles outlining the recommended methods for gathering statistics for the Cost Based Optimizer for use by Database Administrators

See also

How To: Gather Statistics for the Cost Based Optimizer (Doc ID 1226841.1)

Keywords: oracle cbo stats statistics cost based optimizer

Explain Plans and DBMS_XPLAN

This post gives an overview of using DBMS_XPLAN and selecting a SQL_ID to use.


Run the following SQL query to obtain some information regarding the SQL_ID of interest..

=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2014.12.17 18:32:47 =~=~=~=~=~=~=~=~=~=~=~=
run
  1  SELECT DISTINCT sql_id,
  2     address,
  3     hash_value,
  4     child_number,
  5     plan_hash_value
  6  FROM   v$sql_plan
  7  WHERE  sql_id = '2x409g450hfyc'
  8* ORDER  BY child_number

SQL_ID      ADDRESS       HASH_VALUE CHILD_NUMBER      PLAN_HASH_VALUE
------------- ---------------- ---------- ------------ ---------------
2x409g450hfyc 00000002DCF6C808  168311756          0      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          1      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          2      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          3      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          4       240831378
2x409g450hfyc 00000002DCF6C808  168311756          5      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          6      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          7      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          8      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          9      1748903202
2x409g450hfyc 00000002DCF6C808  168311756         10      1748903202

11 rows selected.

Or even:

SELECT sql_id,
       sql_text,
       hash_value,
       plan_hash_value,
       child_number
FROM   v$sql
WHERE  Regexp_like (sql_text, 'fred', 'i');

Now use the supplied Oracle package DBMS_XPLAN to obtain the explain plan.


SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('2x409g450hfyc',7,'ALL'));


where 2x409g450hfyc is the SQL_ID of interest and
7 is the child number.

By using different child numbers, you can select which plan hash value you wish to display. Child number 2, for example, displays the plan with a hash value of 1748903202 whilst child 4 gives you 240831378.


Keywords: oracle xplan explain plan sql_id

Tuning Inter-Instance Performance in RAC

Tuning Inter-Instance Performance in RAC and OPS (Doc ID 181489.1)


This note was written to help DBAs and Support Analysts understand Inter-Instance Performance and Tuning in RAC.

Real Application Clusters (RAC) uses the interconnect to transfer blocks and messages between instances. If inter-instance performance is bad, almost all database operations can be delayed. This note describes methods of identifying and resolving inter-instance performance issues.

See Oracle Support Doc ID 181489.1 for details.

RAC interconnect traffic

Summary


I was looking for RAC cluster interconnect throughput and so accessed to Oracle Database Reference 11g Release 2 (11.2) documentation and surprisingly, at least for me, I was not able to find a V$ view simply displaying it.


This is a nice article on the subject of RAC interconnect traffic.

Click here to read the article.

ADD_COLORED_SQL Procedure

From the Oracle supplied documentation:

This procedure adds a colored SQL ID. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities (so that it does not have to be a TOP SQL). Capture occurs if the SQL is found in the cursor cache at snapshot time.

This can be very useful in the interests of fault finding or diagnosing problems.


SQL> execute dbms_workload_repository.add_colored_sql('6rj1tt5s6n497');

To confirm the SQL_ID has been colored, run the following SQL statement:
SQL> select * from sys.WRM$_COLORED_SQL

This will give you:

fred SQL> select * from sys.WRM$_COLORED_SQL
  2  /
      DBID SQL_ID        OWNER CREATE_TIME
---------- ------------- ----- --------------------
 413071501 6rj1tt5s6n497     1 11-Dec-2014 19:02:51
1 row selected.


and the following SQL will remove the color:
SQL> execute dbms_workload_repository.remove_colored_sql('6rj1tt5s6n497');

See also

AWR Colored SQL 11g

Oracle Support document
How to Determine the Execution Plan for a SQL Statement for a Range of AWR snapshots? (Doc ID 795204.1)

Oracle Support document
Information Center: SQL Query Performance Overview (Doc ID 1516494.2)



Keywords: oracle sql_id

11g Interactive Quick Reference

Oracle Database 11g: Interactive Quick Reference

Your Essential Guide to Oracle Database 11g Release 2

Oracle Database 11g Release 2 Enterprise Edition provides comprehensive features to easily manage the most demanding transaction processing, business intelligence, and content management applications.

Now you have easy access to information that you need to administer your Oracle Database with our downloadable Interactive Quick Reference. We have taken our traditional printed poster for Database Administrators, and created an electronic format packed with even more information.

Use this helpful reference as a cheat sheet for writing custom data dictionary scripts, locating views pertinent to a specific database component, and more.


Click here for the article.



Keywords: oracle 11g cheat sheet reference

Looking at DBA_AUDIT_TRAIL

The view DBA_AUDIT_TRAIL displays all standard audit trail entries in an Oracle database. In my case, I was using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

The following SQL can be used to look at entries in this view.

SELECT os_username,
       username,
       terminal,
       To_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS tstamp,
       returncode,
       action_name
FROM   dba_audit_trail
order by tstamp asc;

Sample output:

OS_USERNAME  USERNAME  TERMINAL     TSTAMP   RETURNCODE ACTION_NAME

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

oracle     DBSNMP  ~       2014-10-17 18:32:10 1017 LOGON

root     RLBUSY  unknown      2014-10-17 18:32:15    0 LOGOFF

abc082     IMPREP  MACH-42    2014-10-17 18:32:35    0 LOGOFF

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGOFF

FRED       DUMMY  ~       2014-10-17 18:33:00 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:33:29 1017 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  ~       2014-10-17 18:34:39 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGOFF

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGON

root     RLBUSY  unknown      2014-10-17 18:35:00    0 LOGON

The RETURNCODE column is the number associated with the ORA-????? error code that was returned to the client computer:

RETURNCODE=0 indicates success

RETURNCODE=1017 indicates bad password

RETURNCODE=28000 indicates account is locked out

For confirmation of the meaning of the returncode value, execute the following code:

SQL> execute dbms_output.put_line(sqlerrm(-1017)) ;

and you should see:

ORA-01017: invalid username/password; logon denied

or from the O/S prompt:

[oracle@host ~]$ oerr ora 01017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

See also

Oracle Support Note "Master Note For Oracle Database Auditing (Doc ID 1299033.1)"

Keywords: oracle audit trail failed login

RMAN things

I've put together some Oracle RMAN information in a Google document.


Click here to read the document.



Keywords: oracle rman ianm blog

Temporary Tablespaces

If you have reason to do some work on your temporary tablespaces, the following code may help.


-- Create the new temp tablespace.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/oradata/temp02.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Drop the original temp tablespace.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- Create the original tablespace again.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Drop the unwanted second temp tablespace.
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

-- If you have a temporary tablespace without a datafile as
-- sometimes happens with an RMAN restore, this will add
-- a datafile to the tablespace.
ALTER TABLESPACE TEMP
ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10000M
AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED;



Keywords: oracle temp tablespace database