OraNA :: Oracle News Aggregator

Welcome to the 152^nd edition of Log Buffer, the weekly review of database blogs.

PostgreSQL

Courtesy the United States PostgreSQL Association, the big news: PostgreSQL 8.4 Released!.

Josh Berkus writes, “Now that PostgreSQL 8.4 is out, I thought I’d write a little about my favorite 8.4 feature. As Mr. Performance Whack-a-Mole, what makes me happy about 8.4 is the ability to whack moles faster … which is why I’m very fond of pg_stat_statements.”

On ad’s corner, Andreas Scherbaum says, “Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem . . .  This . . . is uneloquent, error prone and does not scale well.  . . .  PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.”

David Fetter looks into WITH (so much drama in the CTE): ” By now, you’ve probably seen that PostgreSQL 8.4 can produce Mandelbrot sets . . . but what are Common Table Expressions really about? [They] are just views you create at run time. Oh, and they can refer to themselves. That latter capability is powerful and flexible (read confusing), so I’ll go over that one later. Run-time views are pretty handy, though.”

Andrew’s PostgreSQL blog introduces parallel pg_restore for PostgreSQL 8.4: “I try to complete at least one significant feature item per PostgreSQL release. This time the feature is making pg_restore run in parallel. This is quite important for many users, particularly some large enterprise users.”

SQL Server

Eric Johnson introduces SSIS 2008 and the new lookup: “SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read my mind because SQL Server 2008 has a new shiny Lookup transformation that is, in my opinion, much improved.”

Kimberly L. Tripp was thinking about looking for security vulnerabilities in database code. “I’ve always been concerned with security and I’ve always stressed the importance of auditing the REAL user context not just the current user . . .  So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters . . .  Having said that, what if I’m looking at a database for the first time . . .  I’ve come up with a quick query… And, while it’s not going to “solve” your problem . . . or even truly verify if you’re vulnerable, it gives you a ‘quick list’ of where you should look first! ”

Adam Machanic exposed the hidden costs of INSERT EXEC, beginning, “INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision–seemingly purely aesthetic–can bring to the fore.”

Martin Bell offered the reminder, after disabling TDE you still requires the certificate to restore the database.

Data Management has a first-rate HOWTO on dynamic column names and fields in SSRS. “I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Sense [sic] this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.  . . .  So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT.”

MySQL

Here on the Pythian Blog, Sheeri Cabral expressed some concerns and what does not work in XtraDB backup.

On xaprb, Baron Schwartz has a review of MySQL Administrator’s Bible. “I think that MySQL Administrator’s Bible is a very good complement to High Performance MySQL, 2nd Edition. The former is useful to people who have varying levels of knowledge, while the latter assumes a lot of experience and doesn’t cover introductory material much.  . . .  So if you’re new to databases, or if you’re new to MySQL, you might do well to start with Sheeri’s book . . . ”

Over on the MySQL Performance Blog, Baron looked into gathering queries from a server with Maatkit and tcpdump: “For the last couple of months, we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.”

Ronald Bradford looked at verifying MySQL replication in action, with “ . . . a very simple test to show MySQL replication in action and to also better understand the basics of MySQL Replication. With a configured MySQL environment we can run the following test on your MySQL master, and monitor the MySQL slave.”

The MySQL Workbench Team Blog says, Time To Upgrade - MySQL Workbench 5.1 Is Here on Win, Mac & Linux. Perty pictures follow.

Oracle

H. Tonguç Yilmaz asserted, Instrumented Code is Better: An Example on How to Generate Session Level ASH Reports. ” After 10g if you have its license ASH based performance monitoring is an alternative to the traditional SQL Tracing in my opinion. You may either use EM to generate ASH reports or directly query ASH dictionary. The handy additional hint here is that you can set filters to ASH data in order to focus on a specific session, the filter options at ASH report screen at EM can be used for this purpose or below is an example to monitor the waits from v$active_session_history view.”

Doug Burns responded with his item on session-level ASH reports: “I think [Tonguç's] post is really showing two different things, one more successfully than the other.”

Randolf Geist reports a Dynamic sampling and set current_schema anomaly: “If I’m now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command . . .  This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn’t do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.”

On the Oramoss Blog, Jeff Moss looks at the case of no pruning for MIN/MAX of partition key column: “Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all the partitions behind the current date, i.e. January through mid June were populated. There were no indexes on the table.” Jeff and his readers examine the evidence and discuss some workarounds to the problem.

Oracle, MySQL, PostgreSQL, SQL Server. How’bout . . . 

NoSQL

(No SQL?!? Edwards, you’re mad!) Well, it’s not me. Here’s Curt Monash on NoSQL: “Eric Lai emailed today to ask what I thought about the NoSQL folks, and especially whether I thought their ideas were useful for enterprises in general, as opposed to just Web 2.0 companies. That was the first I heard of NoSQL, which seems to be a community discussing SQL alternatives popular among the cloud/big-web-company set, such as BigTable, Hadoop, Cassandra and so on.”

PostgreSQL stuff also had some thoughts on those who say No to SQL. “Every time I see something or hear something like this I sigh a little bit. Not only when it’s related to SQL but in the world of computer professionals in general. ‘The right tool for the job’ seems to be a hard concept to understand sometimes. I wonder why?”

Back to Oracle for a moment. Tanel Poder has a secret preview of Oracle 12g CBO leaked from Oracle labs. Would you like fries with your cost-based optimizer?

That’s all for now. See you in a week’s time!

There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up.
These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because
the new object is created as a temporary segment in the target tablespace and when the DDL action finishes it will be changed to permanent type.
These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments.
Following query finds out these segments:

 SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments
where segment_type = 'TEMPORARY' group by tablespace_name, owner;

TABLESPACE_NAME     OWNER          SEGMENT_NAME     SUM(BYTES/1024/1024)
------------------- ------------   ------------     --------------------
xxxx_DATA           SYS              123.8365          137706
BDEPST_INDEX        SYS              345.8756            8910
KMRPT_DATA          SYS                345.87       25284.875
BILL_INDEX          SYS                                   .25
DSS_DATA            SYS                                   798
MRKT_INDEX          SYS                                   208
SPCT_DATA           SYS                              69642.25
SPCT_INDEX          SYS                              956.4375

Here we can see that tablespace KMRPT_DATA, SPCT_INDEX and SPCT_DATA have large temporary segments.

To know if any DDL is active which can create temporary segments we can use the following:

SQL> conn / as sysdba
SQL> select owner FROM dba_segments WHERE segment_name='345.87';
SQL> select pid from v$process where username='owner from above query';
SQL> alter session set tracefile_identifier='TEMPORARY_SEGMENTS';
SQL> oradebug setorapid <pid obtained>
SQL> oradebug dump errorstack 3
SQL > oradebug tracefile_name

It will give you the tracefile name, open that file and check for the “current sql”
If it is a DDL like CTAS or index rebuild, then wait for the operation to complete. If there is no pid
returned then these segments are “stray segements” and needs to cleaned up manually.

There are two ways to force the drop of temporary segments:

1. Using event DROP_SEGMENTS
2. Corrupting the segments and dropping these corrupted segments.

1. Using DROP_segments:

Find out the tablespace number (ts#) which contains temporary segments:
SQL> select ts# from sys.ts$ where name = 'tablespace name';

Suppose it comes out to be 10, use the following command to cleanup temporary segments:

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 11';

level is ts#+1 i.e 10+1=11 in this case.

2. Corrupting temporary segments for drop:
For this following procedures are used:
- DBMS_SPACE_ADMIN.TABLESPACE_VERIFY
- DBMS_SPACE_ADMIN.SEGMENT_CORRUPT
- DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT

– Verify the tablespace that contains temporary segments (In this case it is KMRPT_DATA)

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('KMRPT_DATA');

– Corrupt the temporary segments in tablespace KMRPT_DATA

SQL> select 'exec DBMS_SPACE_ADMIN.SEGMENT_CORRUPT(' || chr(39) || tablespace_name || chr(39) || ',' || HEADER_FILE || ',' || HEADER_BLOCK || ');'  from dba_segments where SEGMENT_TYPE like 'TEMP%' and tablespace_name = 'KMRPT_DATA';

– Drop the corrupted temporary segments

SQL> select 'exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT (' || chr(39) || tablespace_name || chr(39) || ',' || HEADER_FILE || ',' || HEADER_BLOCK || ');' from dba_segments where SEGMENT_TYPE like 'TEMP%' and tablespace_name = 'KMRPT_DATA';

– Verify the tablespace again to update the new dictionary information:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('KMRPT_DATA');

This will remove temporary segments from permanent tablespace.

Cheers!!!

Saurabh Sood

Related Posts

• Silent Install :OUI-10133:Invalid staging area Error (0)
• You Don’t need SysDBA privilege to run Awrrpt.. (0)
• Grid Control Fails to Start (2)
• Creating ASM devices on AIX (0)
• Recreating Database Link (4)

This is asked over and over in the forums, but why not proposing an 11g solution here

create table t(description varchar2(12) primary key, 
  numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION	NUMBERS
PRIME	2,3,5,7
ODD	1,3,5,7,9

Now I want to unpivot numbers in rows

select description,(column_value).getnumberval()  
from t,xmltable(numbers)

DESCRIPTION	(COLUMN_VALUE).GETNUMBERVAL()
PRIME	2
PRIME	3
PRIME	5
PRIME	7
ODD	1
ODD	3
ODD	5
ODD	7
ODD	9

It is that simple

Works also with strings :

select (column_value).getstringval() 
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c

One of my old friends who is an Oracle DBA for at least 15 years always argued with me that “every new feature of Oracle is an evil!” :) He advised that it is not secure to upgrade a production environment to 10gR2 for example unless 11gR2 is released on your platform. Personally I always [...]

Cary Millsap public ate a new paper about software performance - Fundamentals of Software Performance Quick Reference Card. Check a other brilliant paper too - Making Friends with the Oracle Database.

regards,
Marcin

A colleague of mine in Oracle’s Real-World Performance Group just pointed out to me that the link (on my Papers, Webcasts, etc page) to the archived webcast of Part IV in my Oracle Exadata Storage Server Technical Deep Dive Series was stale. Actually, the problem turns out that I mistakenly set the file to expire [...]

I just posted "Fundamentals of Software Performance Quick Reference Card" at the Method R company website:

This two-page quick reference card written by Cary Millsap sums up computer software performance the Method R way. The first page lists definitions of the terms you need to know: efficiency, knee, load, response time, and so on. The second page lists ten principles that are vital to your ability to think clearly about software performance. This document contains meaningful insight in a format that's compact enough to hang on your wall.

It's free, and there's no sign-up required. I hope you will enjoy it.

Recently, I tested a switchover on Oracle 11g SE1.

As you know, Oracle Database Standard Edition One—as well as Standard Edition—does not have the Data Guard feature. Therefore, I had to do everything manually.

The whole process took less than 15 minutes. This includes less than five minutes of full downtime to restart the database in READ-ONLY mode, and less than 10 minutes of READ-ONLY downtime.

Of course, it depends on the size of Redo logs and the network speed to move Redo logs from the primary server to standby.

Here is what I had. The primary database and one physical standby database:

• OS - SUSE Linux ES10 (SP2) x86_64
• Oracle - Release 11.1.0.7.0 64bit SE1

First of all, I switched the standby database to the primary role.

Step 1. Shutdown the primary database

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2. Make a copy of the control file, the spfile, and the redo logs:

SQL> !cp control01.ctl copy/control01.ctl.primary
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.primary
SQL> !cp *.log copy/

Step 3. Startup the primary database in READ-ONLY mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

Step 4. Copy all needed archive logs, the copy of the control file, the spfile, and the redo logs to the standby server ora2:

oracle@ora1 /u01/app/oracle/testdb/oradata> scp ../archivelogs/1_152_333215132.dbf oracle@ora2:/u01/app/oracle/testdb/archivelogs/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/control01.ctl.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/spfilepb.ora.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo01.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo02.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo03.log oracle@ora2:/u01/app/oracle/testdb/oradata/

Step 5. Apply all needed archive logs on the standby database:

oracle@ora2 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> recover standby database until cancel;
ORA-00279: change 2244877 generated at 10/07/2008 14:40:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf
ORA-00280: change 2244877 for thread 1 is in sequence #152

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2245162 generated at 10/07/2008 14:53:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_153_333215132.dbf
ORA-00280: change 2245162 for thread 1 is in sequence #153
ORA-00278: log file '/u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Step 6. Shutdown the standby database:

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Step 7. Make a copy of the controlfile and the spfile.

To make it easier I just switched spfiles.

SQL> !cp control01.ctl copy/control01.ctl.stndby
SQL> !scp copy/control01.ctl.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.stndby
SQL> !scp copy/spfilepb.ora.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/

Step 8. Replace the standby controlfile and spfile with the copy of the primary control file and spfile:

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/spfiletestdb.ora.primary /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.primary control01.ctl
SQL> !cp copy/control01.ctl.primary control02.ctl
SQL> !cp copy/control01.ctl.primary control03.ctl

Step 9. Switch the standby database to the primary role:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' size 100m reuse autoextend on next 100m maxsize 2048m;

Tablespace altered.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE, STANDBY_BECAME_PRIMARY_SCN from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE    STANDBY_BECAME_PRIMARY_SCN
------- ---------- ---------------- --------------------------
CURRENT READ WRITE PRIMARY                             2244877

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

And finally, I switched the old primary database to the standby role.

Step 10. Shutdown the old primary database:

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 11. Replace the old primary control file and spfile with the copy of the standby control file and spfile

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !rm temp01.dbf
SQL> !rm *.log
SQL> !cp copy/spfiletestdb.ora.stndby /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.stndby control01.ctl
SQL> !cp copy/control01.ctl.stndby control02.ctl
SQL> !cp copy/control01.ctl.stndby control03.ctl

Step 12. Switch the old primary database to the standby role:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE
------- ---------- ----------------
STANDBY MOUNTED    PHYSICAL STANDBY

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

So you can see that Oracle 11g SE1 (SE) switchover of primary database and one physical database located on different servers is a very simple process. But if you have the physical standby on the same server and use OMF, you should consider two things.

1. How to change the path to the datafiles and redo logs in the control files;

   Solution:In Step 9, after the database is mounted and before it is opened, all datafiles and redo logs should be renamed using the ALTER DATABASE RENAME FILE command.

2. How not to remove the old datafiles. OMF will remove the old files after the RENAME command.

   Workaround: Move the files to a temporary directory and move them back after the RENAME command. The old primary database should be down.

The same process can be done with a RAC database.

And do not forget to make a database backup in case something goes wrong.

BANK005
BJSTM
BJS_ORG
CHAINED_ROWS
CORR_BAM
CORR_CAM
CORR_EIT
CORR_GAC
CORR_GAM
CORR_ITC
CORR_LDT
CORR_LHT

('BANK005','BJSTM','BJS_ORG','CHAINED_ROWS','CORR_BAM','CORR_CAM','CORR_EIT','CORR_GAC','CORR_GAM','CORR_ITC','CORR_LDT','CORR_LHT')

Related Posts

• Connections to DataBase Hang Including “/ as sysdba” (0)
• Scheduling Job through Cron?? (0)
• Creating ASM devices on AIX (0)
• CRS Fails to Start – 10.2.0.1 RAC Install on AIX (0)

Today marks 2 anniversaries for me. It’s my 40th birthday, and it’s exactly 1 year since I gave up eating chocolate.

They say life begins at 40. I’m staying in all day today because I don’t to be out in case I miss it…

Cheers

Tim…

If you haven't seen 11g's Real Time SQL Monitoring feature, you need to. It's one of the most useful Oracle performance troubleshooting tools I've seen since I started working with Oracle too long ago. I was first aware of it via Greg Rahn's blog post.

To date I've used it via DB Control for demos and it's sweet, but one of the problems with any demo based on DB/Grid Control is that it's use is likely to be limited to those who have DBA access. Yes, you can set up view access to GC so that people can monitor performance of targets that they have sufficient account privileges on, but a lot of sites won't want the overhead of setting that up for developers and app support teams.

So I was intrigued when I noticed Tom Kyte mention that Oracle have been working on making more of these tools available to developers. After a quick email, a look at his slides and some further help from Sue Harper, I was able to try out RTSM in SQL Developer 1.5.4

Once you have selected Tools->Monitor SQL



you'll see a grid table of records. This will include all monitored statements, including this example parallel query that I'm running just now.




If I right-click any of the statements and select 'Show SQL Details' I'll see the Real Time SQL Monitoring screen, which is deeply cool.



One criticism, though. On my dinky laptop screen, the execution plan steps don't display properly as that column's too narrow. I can resize it




but then it just annoyingly sets it back to it's original width every time the screen refreshes. Hopefully that's something that can be fixed, for those of us with dinky monitors Even so, it saved me during my last course teach because 11g DB Control started to play up on my laptop, so I was able to fall back on the SQL Developer option. It's definitely worth a look.

Thanks to Sue and Tom for their help ...

Please vote for the following Oracle OpenWorld 2009 session on Oracle Mix.

S308732 Dell IT's Implementation of Oracle Enterprise Manager Patch
Provisioning

In order to do that, you must create an account on Mix. Go to:
https://mix?oracle.com/oow/proposals/10416-dell-it-s-implementation-of-oracle-enterprise-manager-patch-provisioning
Voting ends on midnight, July 3?

Oracle Technology Network(OTN) has released the offer where both Oracle employees and OTN members can join forces and work on the sample projects, codes and other stuff. I guess that would be a cool idea though not sure that what kind of applications would be written and how the development will proceed? But nonetheless, this is a great initiative so I would recommend all , at least the developers to be a part of it. Check the sample code site of Oracle at,
https://www.samplecode.oracle.com

Let the code writing begin .

I noticed a post on H.Tonguç Y?lmaz's blog about filtering ASH data to look at the actions of a specific instrumented query. There are a few strange things that I was going to comment on but the blog requires me to use a Wordpress account before I can post. I remember this has stopped me from commenting on a few interesting posts there in the past so I've decided to post some comments here and hopefully they'll appear as a trackback or pingback or some such modern thing

I think the post is really showing two different things, one more successfully than the other.

1) Using DBMS_APPLICATION_INFO to instrument code so that we can analyse what it's doing. It is incredibly useful and Oracle's tools are all geared up to use the info if it's there. But that's not really about ASH as such, because the information would also be written to trace files too and would prove just as useful there. You could use trcsess with module or client_id, service, action or module and you would have a consolidated trace file with the same application-aware view of things, without the sampling gaps inherent in ASH data. Of course, you'd need to know about the problem in advance or be able to recreate it.

2) Filtering ASH data to see what a specific user or application is doing. In this case it's a parallel query but if I wanted to look at pq activity with ASH, I think I'd want to include the QC_SESSION_ID and possibly QC_INSTANCE_ID to tie things together. I don't think it's necessary for what the example's trying to show, but it's worth knowing about if you didn't already.

However, I have a couple of real problems with the ASH query shown. (Recreated here with some of the white space removed from the results to make it fit the width of this template.)

SELECT session_id,
       client_id,
       event,
       SUM(wait_time + time_waited) total_wait_time
  FROM v$active_session_history
 WHERE client_id = 'your_identifier'
   AND sample_time BETWEEN SYSDATE - 30 / 1440 AND SYSDATE
 GROUP BY session_id,
          client_id,
          event
 ORDER BY 2;

SESSION_ID CLIENT_ID                EVENT               TOTAL_WAIT_TIME
---------- ------------------------ ------------------- ---------------
       223 your_identifier          latch free                  3969275
       292 your_identifier          direct path read            5304169
       241 your_identifier          direct path read            1133055
       273 your_identifier                                    111542310
       235 your_identifier          direct path read            1052545
       235 your_identifier          latch free                  3969283
       223 your_identifier          direct path read            1003455
       241 your_identifier          latch free                  3969486

8 rows selected

I'll summarise what I think this query is meant to be returning as 'All activity for a given client id in the last 30 minutes, showing the total time waited for each event by each session used'.

I see more problems every time I look at this, but a few off the top of my head ...

1) Probably the most important is the SUM(wait_time + time_waited) as a measure of total wait time. It's not total wait time. First, ASH is sampled data so it doesn't contain all of the events. SUM-ing the data might give the illusion of something useful, but we have no idea what happened within the one second sample points! That's why the Top Sessions section of the Oracle-supplied ASH report doesn't report the amount of time spent on various events, but the percentage of samples over the period. Here's an example from one of my course slides.



Second, why WAIT_TIME + TIME_WAITED?

2) There's no filtering on session_state so it's not obvious that the group with no data returned in the EVENT column is ON CPU.

3) If I was going to ORDER BY something here, I suppose CLIENT_ID might be in there, but wouldn't I be interested in 'Most Active'? In which case, I would order by that TIME_WAITED column, if it wasn't flawed. In fact, the smart thing to do here would be to COUNT the number of samples as a proxy for time. That's what the supplied reports do and there are other examples over at ashmasters.com.

My final tip, though, would be this. If you run $ORACLE_HOME/rdbms/admin/ashrpti.sql (note the i, it's important), not only will it allow you to specify instance in a RAC cluster, but you can also limit the scope of the report in many interesting ways like this. (Although why anyone would want to report on WATI_CLASS is beyond me )

Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
Enter value for target_session_id:
SESSION report target specified:

Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_sql_id:
SQL report target specified:

Specify WATI_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_wait_class:
WAIT_CLASS report target specified:

Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
Enter value for target_service_hash:
SERVICE report target specified:

Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_module_name:
MODULE report target specified:

Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_action_name:

In other words, Oracle already supply a report that does everything that query is trying to do and in my opinion, much better. I hope I don't seem too critical but I'm trying to help people here and SUMs of timing information in ASH data has become a particular bug-bear of mine.

After 10g if you have its license ASH based performance monitoring is an alternative to the traditional SQL Tracing in my opinion. You may either use EM to generate ASH reports or directly query ASH dictionary. The handy additional hint here is that you can set filters to ASH data in order to focus on [...]

Here are a couple of posts that describe the download monitor/dashboard which is giving up-to-the-second statistics for downloads by country of the latest Mozilla release 3.5 (just about to top 5.5 million downloads since yesterday's launch). The dashboard has been put together with the help of my friends at SQLstream. Just don't try looking at this with Internet Explorer, as it doesn't support HTML5.

Julian Hyde on Open Source OLAP. And stuff.: SQLstream powers ... By Julian Hyde SQLstream gathers data from Mozilla's download centers around the world, assigns each record a latitude and longitude, and summarizes the information in a continuously executing SQL query. Data is read with sub-second latencies, ... Julian Hyde on Open Source OLAP.... - http://julianhyde.blogspot.com/

SQLstream the Sequel - RealTime Intelligence for Mozilla BI in Action
From ebizQ Presents BI in Action Virtual Conference ...

Ftp is seen as an old-time unsecure protocol. Many shops nowadays have switched or are switching to sftp. I will try to point out some differences :

Compatibility: none. the protocol is completly different. Multiple graphical clients however do support both mode. But the basic “ftp” client will not work with sftp.

Ascii mode: only in ftp. In sftp, it is always binary so there will be no conversion. Also no blocksize, recordlength or primary/secondary space for your OS/390 connections.

Interactive mode: similar. you enter your username and password, do cd, put and get. But to quit, by will not work in sftp Use quit or exit instead

Batch mode: different. Most probably you will end up setting a private/public key infrastructure for your ssh connection and use scp (secure copy). If you are using a ssh client like putty, it is possible to do something like pscp -l user -pw password server:file .

Security: sftp is secure, ftp is not.

Speed: ftp is fast, sftp is slow !

Oh NOOOOOOO!!!!! What’s the point is bringing something new if it is slower !!!

Ok, let’s try to download a 100m file:

$ time (echo "open dbsrv01
user oracle secret
bin
get 100m"|ftp -n )

real    0m24.673s
user    0m0.030s
sys     0m0.016s
$ time scp -q oracle@dbsrv01:100m .

real    1m46.978s
user    0m0.108s
sys     0m0.202s

it is about 4x slower! Is there anything we could do about it?

Well, maybe :

$ time scp -q -o Compression=yes oracle@dbsrv01:100m .

real    0m18.634s
user    0m0.748s
sys     0m0.452s

ssh/scp/sftp have a compression mode. If you are transferring your large files across a slow network, this may be an interesting option to consider !

OpenSSH homepage : http://www.openssh.org