Cardinality and Pipelined Functions - Ugly!
Karl Reitschuster
in Development, Tuning
|
Post a Comment
| 
Email
Dear reader,
pipelined functions are very comfortable to process incoming data and to return it as structured result set. For this reason they are frequently used in SQL; But be carefully if you join result sets from pipelined function with other tables.
The Oracle optimizer usually uses gathered table statistics to calculate the cardinality of a join operation; For a result set originated in a pipeline function it has no statistics to evaluate and hence could try to get another path to the data.
Following SQL retrieves internal employee GUID's from external Id's passed as comma delimited string.
When enough is enough? - how to estimate I/O Performance
Karl Reitschuster
in Troubleshooting
|
Post a Comment
| Email
Hello reader,
Last week we faced performance problems also shown on the Oracle 10g Db-Console as 'SQL found consuming ...'; The users of the application mourned everything is going slow. Hm ...
The strange thing was that the same kind of SQL run without problems before. I started to monitor I/O with iostat on the Sun Solaris Database Server. The Storage was based on SAN. The I/O rates were from 5m/s - 10m/s. The devices were about 95%-100% busy and this with an I/O rate with only about 10M/second? Usually 100m/s and more were possible.
Oracle 11g : A deeper granularity level to SQL performance metrics
Karl Reitschuster
in Oracle Server
|
Post a Comment
|
3 References
| Email
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
Karl Reitschuster
in Database Control
|
Post a Comment
| Email
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
Karl Reitschuster
in Design, Development
|
2 Comments
|
2 References
| Email
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
Karl Reitschuster
in Blog
|
Post a Comment
| Email
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
Karl Reitschuster
in MAC OS X, News
|
Post a Comment
|
5 References
| Email
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!
Karl Reitschuster
in Design, SQL, Tuning
|
Post a Comment
| Email
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
Karl Reitschuster
in PL/SQL
|
Post a Comment
|
2 References
| Email
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
