0

I have two different users however one user is taking more than 2 mins to get the results from the view and have only 47 records and second in taking 0.48 sec to get the records from the same view and have records in thousands. How is this possible? Both are running in same view, same environment. Please guide what steps need to be follow to get this analysis done.

Jaspreet Singh
  • 155
  • 1
  • 10
  • same view and different number of records ? something is missing here. – JagaSrik Jul 24 '20 at 09:00
  • Show us real queries and their real execution plans with statistics – Sayan Malakshinov Jul 24 '20 at 09:03
  • As they get different data, I strongly guess they use different queries (on the same view). By using different where parameters, joins and accessing other data, oracle builds different execution plans thus using different indices, accessing different partitions etc etc. Please post the 2 whole queries and, if possible, an execution plan. See [How To Execution Plan](https://stackoverflow.com/questions/11799344/how-can-i-see-the-sql-execution-plan-in-oracle) – Chrᴉz remembers Monica Jul 24 '20 at 09:07
  • If the view has a where or join criteria with the current user, all is possible – Turo Jul 24 '20 at 09:13
  • Simply... Running this view.... Select * from vw_alc_case where agent_id ='50308949' and select * from vw_alc_case where agent_id ='50308994'... Second one is taking 0.48 sec while having records in thousands rather than first one – Jaspreet Singh Jul 24 '20 at 09:18
  • Is the second one taking 0.48 seconds to fetch all of the data, or just the first block of rows (e.g. first 50)? Compare the execution plans, but I'd guess there's a full scan happening, the first has to complete (or nearly) that before it can return anything, while the second finds some data to return much faster - but will take just as long to return *all* data. If you're looking at this in e.g. SQL Developer grid, scroll right to the end of the result set and see long that takes. – Alex Poole Jul 24 '20 at 09:46
  • 1
    The queries are different because of different literals for agent_id. So different execution plans may be possible. – oratom Jul 24 '20 at 22:10

0 Answers0