Solved

How to create a sql profile

Posted on 2014-07-30
8
226 Views
Last Modified: 2014-08-12
I have a long running sql which uses a view and was taking more than 20 minutes to  get the result.
I can able to fix this sql using a Parallel hint.(It is now giving the result in seconds). The issue is we can not change the code in the background.
How can I create a sql profile so that we need not to change the code?

The below sql is modified sql (Working) :-

select /*+ PARALLEL*/ * from ps_wklst_vw where order_num<250;

The Original sql was as follows:-
select  * from ps_wklst_vw where order_num<250;
0
Comment
Question by:Oranew
[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
  • 3
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230540
Are you sure it was the parallel hint that made it faster or that the blocks were cached in memory from previous runs?

To get a true idea you need to flush the shared_pool, buffer_cache and depending on your version the result cache:

alter system flush shared_pool;
alter system flush buffer_cache;
exec dbms_result_cache.flush;

You need to think twice before doing this on a production system.

That said it looks like this might be what you are after but I cannot say for sure since I have never personally done it:
Example 17-4 Parallel Query Recommendation
http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF95163
0
 

Author Comment

by:Oranew
ID: 40230609
Yes, I checked using the explain plan and I just need help with creating the sql profile using the above
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230630
So are you saying the explain plan shows the same amount of disk/io parallel versus not parallel?
0
Technology Partners: 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!

 

Author Comment

by:Oranew
ID: 40230731
I did not check the io but the cost is 2500 vs 35000
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230745
The COST in an explain plan is worthless when it comes to performance.  It is a made up number.  Lower cost does not mean faster performance.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40230749
0
 

Author Closing Comment

by:Oranew
ID: 40255603
This is NOT a solution. Please close
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40255881
For a solution to the question asked, you need to continue working with the Experts.

I would have gladly continued working on this with you but you never posted back.
0

Featured Post

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!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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