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 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, 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
I recently upgraded my OS to Windows Vista Home Premium 64-bit system. I downloaded the beta softwares (webcam driver & cam center) for my Creative Live! Cam Voice & everything looks fine until I run the Creative Live! Cam Center. Right after runn [More]
Apple ipad This question was solved. View Solution.I just looked into both that app and into AirPrint (to see if that was a viable work-around), but it does not appear to be the case that you will be able to alter the size that text prints. It is dir [More]
Hello all, I am finally getting around to replacing my failed SuperDrive, a Panasonic UJ-857E. Reputable online resellers say the Panasonic UJ-867A will work.  I bought one.  It will not work, because the ribbon cable connector is a different shape t [More]
I updated from 10.8.1 to 10.8.2 and after restarting lots of apps just crash immediately upon opening, including: iPhoto Chrome Eclipse PgAdmin MS Office Pandora etc the list goes on and on. Apps that work: Safari Mail FireFox iTunes Calendar App Sto [More]
Ok, this is my big big problem.. One day, i was just doing nothing on my dear computer, starting up firefox or something, then suddenly everything stopped. I couldn't do anything. I forced-restarted the computer with the boot-button. When im trying t [More]
Hello I have been told that BAPIs can be used to load multiple csv files at once for which some BAPIs exists (BAPI_IPAK_CREATE_FROM_REF , BAPI_IPAK_CREATE , etc) I have users who will be dropping their standard files in a directory (this should be a [More]
Hi All, I have a view cluster with two tables. I should provide F4 help for field in item table based on the selected row of the header table. F4 should have only values fetched based on the header table entry. How can i achieve this. Please suggest. [More]
Hello. I have just put in a weeks worth of events into my iPhone calendar and none of it has synched with the cloud and is not visible on any of my devices. Anybody any ideas? Sorry if this has been asked elsewhere.Have you logged into icloud on the [More]
Since I updated to the latest version of ATT email from the classic email version, I can't get Firefox to open the mail. And I can't find a link to go back to classic. I need to know if there is a change that I need to make in my Firefox settings to [More]
iTunes no longer recognizes my iPod nano 5th gen.  When plugged into macbook usb it appears as my iPhone.  Previously both devices had individual identities. Have reset iPod and turned macbook off/on.Hello BPeery, I would go through ALL the steps her [More]