Oracle 11g : A deeper granularity level to SQL performance metrics
With Oracle 10g Oracle introduced a new key column spread over the dynamic performance views - the SQL_ID; The SQL_ID identified a SQL statement. The SQL_ID was needed to persist all performance metrics of a specific SQL statement in a repository. With Oracle 11g the granularity even gets deeper. With the new columns SQL_EXEC_ID and SQL_EXEX_START performance data could be collected of a specific execution of a specific SQL statement! What a quantum leap in monitoring!
Database Control 11g : Monitoring Parallel Execution
A really new step in real-time Oracle Performance Monitoring was the Database Control introduced with Oracle RDBMS 10.1; With Oracle 11.1 the concept was improved and more detailed metrics could be viewed via Database Control. The Performance page has now 4 Tabs : * Throughput * I/O * Parallel Execution * Services
IT starts with the data type
Intro
Examples
* Mathematics with Account numbers
* Timestamp as order citeria, unique history sequence
* The ultimate is_number - function
Impact of choosing the wrong datatype
* Optimizer miss-assumptions
* Breakdown of data interfaces
* Loss of service
Conclusion
From Oracle space to Tech space
My Blog changes now from Karl's Oracle Space to Karl's Tech Space.
In future i want to blog about High Performance Computing (for Dummies) ;-). A lot of CPU power is now available in the living room. We are able to participate into the development of next generation CPU design. Thus in future i blog about Oracle and Non-Oracle stuff.
Karl Reitschuster
IBM Informix Dynamic Server to Deliver Support for Mac OS X
Informix has announced to add support for Mac OS X 10.2 Leopard; That's interesting because Oracle already did with the Oracle Server 10.1 release - but then stopped their commitment for the platform for unknown reason. But if Informix with a much lower number of installations is able to support a Mac OS X release why isn't Oracle?
The number of MAC OS X user is steadily growing - specially when Apple moved from Power to Intel Architecture; Interesting hardware (64bit) is available too : the Mac Pro and the Mac Xserve are capable to run Oracle effectively.
So - Oracle - why do you hesitate?
Karl Reitschuster
Stream your data!
Supporting a CRM System with Interface data all Interface jobs log their activity into the Applications EVENTLOG table. It's interesting to know when the interface was started dependent on file delivery of other systems, how long it needs to process the incoming data and how many errors/warnings occurred during processing. The first blueprint to accomplish this I wrote the following SQL. It worked fine with about 50,000 rows. In production - we extended the maximum life cycle of eventlog rows to 90 days the SQL now needed about 280 seconds. The EVENTLOG table in production had about 450,000 rows.
Retrieving any Resultset from a weak REF cursor
Hi reader, got a call by a colleague how to display a result set of any weak REF cursor via PL/SQL? What works great via OCI driven client drivers is hard to do with PL/SQL. If you process a REF Cursor in your program returned by a PL/SQL-Method you can access easily the result set of that cursor variable. Not so in PL/SQL. Too much cursor meta information is missing.
Newton, Einstein or the Limitations of Tuning
As you know some laws which Newton detected for example of the acceleration only were valid if the velocity was low. Einstein very well known for his Relativitätstheorie which was not a complete new model but a unification of some existing theories and models described the dependency from the natural Constant the speed of light for all moving objects.
So if you tune your SQL and the retrieve of data gets faster and faster you start to need a lot of energy - more then all power plants together to increase the speed of data retrieve ;-) So there are natural limits to SQL Tuning - isn't it?
cheers ;-)
Karl Reitschuster
Oracle Performance predicatable ?
Hi reader, after Migration of an Oracle Database from AIX 5.3 to Sun Solaris 5.10 and from Oracle 10.2.0.2 to 10.2.0.3 I made some tests with Parallel query performance on the new platform. Following query was executed with parallel degree 4 and then with parallel degree 8; The query itself was generated with the DBMS_STATS package to gather statistics for an index of a partitioned table.
PGA_AGGREGATE_TARGET and _PGA_MAX_SIZE
Hi reader, due to strange behavior (Oracle 10.2.0.3, Sun Solaris 5.10, 64bit) of the SORT operation with manual PGA, independent of SORT_AREA_SIZE set occurrence of Waitevent DIRECT TEMP READ/WRITE with a lot of elapsed time did not disappear, we had to set up a batch friendly configuration with automatic PGA;

