Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamic SQL runs slow at first time...

Posted on 2008-06-11
11
Medium Priority
?
1,748 Views
Last Modified: 2012-05-05
Dear Experts -

The PL/SQL function with dynamic SQL runs slow( 10 - 30 Secs)  at first time. It runs faster during the next successive calls.  Ive attached the function and the TKPROF output.

Any help would be appreciated!!!

Thanks.

Sv.

tkprof-ss.txt
Dynamic-SQL-Fn-Code.txt
0
Comment
Question by:sventhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 400 total points
ID: 21768797
yes, this is the expected behaviur. There are two things that are additionally done by the first call:

1. Hard parse of the statement. It includes syntactical and semantic analysys, optimization and execution.
    By next execution if you use biond variables only syntactical analysys will be done. the cached in the shared pool
    execution plan will be used.
2. All involved data blocks are read in the Data Buffer Cache of the SGA
    By next execution the data blocks that remain in the SGA will not be read from the disk.
    they will be used as is from the RAm, If they are not changed on the disk.
0
 
LVL 18

Author Comment

by:sventhan
ID: 21769201
Thanks Schwertner.

I've noticed from TKPROF the most of the time is spent on "db file sequential read". The purpose of the query is to get the TOP N (15) rows.
Eventhough its a expected behavior I'm just looking for some tuning options.
1) can i force a full table scan instead of using index because this query suppose to do a full table scan to retrieve the top 15 rows
2) how can I make sure the dynamic sql is using the bind variable by looking at TKPROF output?

Thanks again for the kind help and your time.


0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 400 total points
ID: 21769390
Every time you use in the WHERE clause variable instead of hardcoded value you make use of bind variable.
It just saw the execution plan. It uses index scans, so it uses the indexes.
You can also to try to compute the statistics to refresh the data.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 21769578
also,  I hope you know your query won't "reliably" return the top N rows based on your order by.

you are querying with a rownum filter that will be applied BEFORE the order by.'

So,  what your query does is,  return N random rows
then sorts them.


Here's a simple example to illustrate the problem...
given this set of data (n in 1..9)
(select level n from dual connect by level < 10)
return the top 4 rows


select n from
(select level n from dual connect by level < 10)
where rownum <= 4
order by n desc

Note, this returns 1,2,3,4  then sorts them 4,3,2,1  


the proper way to get the "top N" is to wrap the ordered query in an inline view and THEN apply the rownum restriction on the outer query.

SELECT n
  FROM (SELECT   n
            FROM (SELECT     LEVEL n
                        FROM DUAL
                  CONNECT BY LEVEL < 10)
        ORDER BY n DESC)
 WHERE ROWNUM <= 4


0
 
LVL 18

Author Comment

by:sventhan
ID: 21771009
Thanks SD.

I know the query is not perfect. It was written by someone on 2004 and I'm cleaning up now. I'll make the changes as you've suggessted.

Is the any other thoughts about "db file seq read" wait event.  
I mean should cache, pining objects into shared pool, using cursor sharing hints helpful?
If I run this for whole year the only thing DYNAMIC is going to be the month end date. So I think it should not parse for the next month end. Accoring to the TKPROF its getting parsed for every month end dates.
I'll attach the whole year tkprof soon.

Thanks again for your time.





0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 21771129
take all the order by's out except the last two.  You "might" be able to get better optimization with some predicate pushing on the unsorted inner queries.  But more immediately,  the ordering is simply an extra processing step that has no utility at all.


I take back my earlier comment about the order by/rownum  I completely overlooked the 2nd to last order by...

 ORDER    BY tab_acct.rank_value DESC ) allrecs
  <<<<<<<<<--------- I didn't notice this one earlier.
                  WHERE ROWNUM <=  15

                  ORDER by allrecs.rank_value DESC

that does give you what you're looking for.

I'm still looking for ways to get reduce the reads.
0
 
LVL 18

Author Comment

by:sventhan
ID: 21771750
Thanks SD.

I've attached the TKPROF output when I ran the query for the whole year.


dmtt-ora-12173560-ARULSS-IQT.txt
0
 
LVL 18

Author Comment

by:sventhan
ID: 21771862
The init.ora  parameter at the DB level is
cursor_sharing= 'EXACT'
When I set this to session level
ALTER SESSION SET cursor_sharing='SIMILAR'
It works runs faster.
I've to do more testing and let you know the final result...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21772102
well, that means you're not reparsing the statement each time.
also, you are probably generating a different plan with pseudo-binds than with your literals
and the pseudo-bind plan is apparently better.

either that, or you're simply getting caching benefits and it's not the cursor_sharing at all.

check the plan generated by the similar vs exact for the same query
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21772124
if using similar is helpful, then you have to ask just how "dynamic" your queries really are.

If the sharing is helpful, it must be because your queries aren't really changing very much.  (that is, only in the values,  not in columns, tables, where clauses, etc.)

So, if your queries aren't really changing,  then I strongly suggest you de-dynamic your code.  Put the static sql in the procedure instead with real binds.
0
 
LVL 18

Author Comment

by:sventhan
ID: 21772447
Thanks SD for the order by suggestion. That helps me out a lot.

I am all set for now.

Thanks to you both for helping me to find out the issue.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question