Entries in Tuning (7)
Tuning Leading '%' Queries - a simple approach
Karl Reitschuster
in Development, Tuning
|
Post a Comment
| 
Email
Hi reader,
usually queries using LIKE and leading '%' cannot be indexed. So an idea would be to mirror the string content with the string reverse function to be able to put the '%' operator at the end :
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.
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.
Oracle Performance predicatable ?
Karl Reitschuster
in Tuning
|
Post a Comment
| Email
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.
Successfully launch of a new data interface or The egg preceded the chicken
Karl Reitschuster
in Development, Tuning
|
Post a Comment
|
1 Reference
| Email
Dear reader, i created a data interface for new demand of incoming data exchange from another system. Finally before the new Interface was released to production i took some tests on a production clone database. Usually in development the interface processed 6m rows in about 15 minutes. But on the clone database the processing did not end after hours. After some analysis i found out that the interface related tables had statistics gathered before processing and resulted in 0 rows per interface table.
Explain Plan - What you see is sometimes not what you get
Karl Reitschuster
in Tuning
|
4 Comments
|
3 References
| Email
Updated on Wednesday, August 1, 2007 at 12:58PM by
Karl Reitschuster
Dear reader, today support called me because of user complained about a slow system. This time I took a look into the V$SESSION_LONGOPS view which helps to find sessions running SQL lasting longer then 6 secs. Very soon I found a candidate scanning the application customers table with 6.6m rows. With the SQL_ID I found the SQL in V$SQL and pasted the SQL into an explain plan tool.
The LIKE Bind Parameter Dilemma (LBPD)
Karl Reitschuster
in Tuning
|
Post a Comment
| Email
Updated on Thursday, October 30, 2008 at 07:50AM by
Karl Reitschuster
hi reader, everybody tells you to use bind variables (reduced parses) and that’s ok. But in some circumstances you get a lot of problems with it. Using bind variables with the LIKE operator could cause horrible performance even you index that column and you do not use a ‘%’ at the start of the searched string. The Optimizer has to calculate the cardinality of an operation. If cardinality changes the execution plan could change dramatically too.
