Sitemap

Amicuk Programming Answers

Performance problem with report query

2015-10-11   Views:0

Advertisement

Hi, I am encountering a performance issue with a page returning a report. I have a page that has a region which joins 2 tables. One table has about 220,00 rows, while the other contains roughly 60,000 rows. In the region source of the report region,

Hi,
I am encountering a performance issue with a page returning a report.
I have a page that has a region which joins 2 tables. One table has about 220,00 rows, while the other contains roughly 60,000 rows. In the region source of the report region, the query includes join condition with local variables. For example, the page is page 70, and some join conditions are:
and a.id=:P70_ID
and a.name like :P70_NAME
I run the query that returns a large number of rows from sqlplus, and it takes less than 30 sec to complete.
When I run the page, the report took about 3 minutes to return.
In this case, :P70_NAME is initialized to '%' on the page.
I then tried to substitute variable value directly in the query:
and a.id=1000
and a.name like '%'
this time the report returned in about 30 sec.
I then tried another thing which specified the region as "PL/SQL Function returning sql query", and modified the region as follows:
l_sql := '.......';
l_sql := l_sql || 'and a.id=' || v('P70_ID')
and similar substituting :P70_NAME to v('P70_NAME') and append its value to the l_sql string.
The report query page also returned in 30 sec.
Is there any known performance issue with using the bind variable (:PXX_XXX) in the report region?

The replay answer
Advertisement
If you are able.. flush the shared_pool, run your
report then query the v$sql_area or v$sql_text tables.
Or do a google query and look up Cary Milsap's piece on enabling extended trace .. there is your sure fire way of finding the problem sql. I am still learning htmldb but is there a way to alter session enable trace in some pre-query block?

Go to See the other 6 answers

Performance problem with report query

Category:DefaultRelease time:2015-10-11Views:130

Hi, I am encountering a performance issue with a page returning a report. I have a page that has a region which joins 2 tables. One table has about 220,00 rows, while the other contains roughly 60,000 rows. In the region source of the report region,[More]

Performance Problem in Select query

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have performance Problem in following Select Query : SELECT VBELN POSNR LFIMG VRKME VGBEL VGPOS   FROM LIPS INTO CORRESPONDING FIELDS OF TABLE GT_LIPS    FOR ALL ENTRIES IN GT_EKPO1    WHERE VGBEL = GT_EKPO1-EBELN      AND VGPOS = GT_EKPO1-EBEL[More]

Performance problem in delete query

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have to delete nearly 900 records from a table having 10 million records. Each time I am deleting 100 of these records and calling this query from my application many times. If I increase the rownum to 1000 then I get rollback segment error due[More]

Problem with Report Query

Category:DefaultRelease time:-0001-11-30Views:130

I have tested the integration of Apex3.0 and BI Publisher via Report Query. This works very well, however I want to create a hierarchical XML document via the following Query: select a.id, a.persoonsnr, a.volgnr_dvb, a.jaar, a.naam, a.code_sagitta, a[More]

Performance Problem in Report built on a DSO

Category:DefaultRelease time:2015-10-11Views:130

Dear All, we had a report created on the top of the ODS(0sd_o06) with selection criterion as profit center,now when we click the profit center varible entry to see the selection, a window pop up and it is there for 30 mins after that only we can make[More]

Performance problem in Report

Category:DefaultRelease time:2015-10-11Views:130

Hi Guys, I have a problem with performance in report, not exactly in report but in one of filter of it, I have a vision that the name is "Input a cliente", i can surf in everthing, i can filter other visions in report but when I try to make a fi[More]

Performance problem at Select Query

Category:DefaultRelease time:-0001-11-30Views:130

Hello all, I have a  performance question. Which code can i use instead of this code " select * from t001a into table it_tp_auth where bukrs in kd_bukrs ". thus the program runs faster ? Regards HeidiHi all, that was my estimation. But I go into[More]

Performance problem using distributed query

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Can anyone provide an explanation/solution to the following problem encountered on an 8.1.7 database: SELECT /*+ DRIVING_SITE(vs) */ vs.col2 ,user_defined_function FROM v_remote_table_1 vs ,v_remote_table_2 vc ,local_table_1 s WHERE vp.col1 = vs.[More]

Performance Problem with SQL Query

Category:DefaultRelease time:-0001-11-30Views:130

Hi I have a SQL Query (say, SSS) which runs quite fast when I run it from TOAD. But when the same query is being used as INSERT INTO <table> VALUES <SSS>; It is taking hours to complete. The Original Query(OSQ) was changed to this SQL query(SS[More]

Performance problem, statspack report included.

Category:DefaultRelease time:2015-10-11Views:130

HI, on my 9.2.0.8 cpu_count=12 EE I've got job which takes 5h , there is strange top sql reported: STATSPACK report for DB Name         DB Id    Instance     Inst Num Release     Cluster Host E1        2020101010                E1            1 9.2.0.[More]

Performance problems with Reports 6i

Category:DefaultRelease time:2015-10-11Views:130

Hello, We have reports server installed on Windows NT, generating reports in html format, requested through Oracle 9iAS. When we have approx. 20 reports being generated simultaneously, RWCGI60.exe processes start to accumulate in the task manager and[More]

Hot
Hi Experts , We  are in the process of UCCHECK in an upgrade program and come across an issue with read statement using repeated key fields which is not allowed in a unicode compatable environment.             READ TABLE it_vbpa WITH KEY              [More]
Hi All, I am hoping someone out there can help me (and I am sure many others) with this problem. I was using Premiere Pro CS6 and was half way through a project when I upgraded to Premiere Pro CC. This was fine and I finished the project, however soo [More]
Many times I get a .pdf from online & when I open it, there are many pages attached with it & I can scroll through it. Is there a way on the macbook to compile pdf pages and make them into a pdf document like this in preview. I've looked & don [More]
Hi, Group. This seems like the type of question that has been asked / answered a million times before but a quick Google and a search in this forum didn't return what I was hoping for. I have a Java application that issues SQL statements against an O [More]
After installing WLS 8.1 and the AIX supplement patch plus fixes to java1.4 I am still unable to bring up my server. The following error occurs: * To start WebLogic Server, use a username and * * password assigned to an admin-level user. For * * serv [More]
Hello Experts, I am trying to create an ABAP Proxy based on my Service Interface. Before I do that I need to create a package using SE80/SE21. But, I am unable to create a package because it keeps asking me for a superpackage and there are no values [More]
Using iTunes 10.4, MBP I'm able to connect to the App Store, can see the text descriptions/reviews for apps -- but no screen images.  It's just a blank white area where they should be. Anyone else?  Is it perhaps related to launch day? Thanks, BAn "u [More]
I used JBuilder 3 and 4 before and packaged some java classes together but now when I want to use earlier version of the JBuilder like 2007, the import can't resolve some of the packages. Any suggestions please.I think it doesn't know them. Do I need [More]
Hi All, I fail to use Jeditorpane to display a text with big5 encoding correctly. It only show machine code. I am using jre1.6.0_17. Yet it works well with earlier version of jre like jre 1.6.0_15, and jre 1.6.0_16 bcpscsinbcpscsin wrote: ..I can fin [More]
i was wondering if y'all have any suggestions as to how to set up a web cam as a security cam. i'd like to record the footage to an older machine and view it when time permits at the end of the day. tia !try if you find pointers here: http://discussi [More]