Forum Stats

  • 3,759,914 Users
  • 2,251,613 Discussions
  • 7,870,866 Comments

Discussions

select count query hangs

User_BH2ZQ
User_BH2ZQ Member Posts: 2 Blue Ribbon
edited Jun 20, 2021 7:48PM in Python

hi,

I'm running below code to find count(*) with big logic in python cx_Oracle from AWS EC2. In oracle client and aqua studio returns result in 14 mins but python code taking so much time(1 hr) still not returning output nor throwing any error.

Note: small query returning very properly.

conn = cx_Oracle.connect(user=user, password=password, dsn=dsn_tns)  

cursor.execute(query)

result = cursor.fetchall();

If you give any clue or idea that would be great help. I'm struggling past one week


Installed cx_Oracle

python3 -m pip install cx_Oracle --upgrade

Installed cx_Oracle-8.2.1-cp37-cp37m-manylinux1_x86_64.whl

Regards

Suresh

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee

    A "select count(*)" will only return one row. If this is slow, check your DB. The query is executed in the DB, so the issue won't be with cx_Oracle.

    If you mean "select *" is slow, then you need to tune arraysize and prefetchsize. Refer to the cx_Oracle documentation https://cx-oracle.readthedocs.io/en/latest/user_guide/tuning.html#tuning-fetch-performance

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,573 Red Diamond
    edited Jul 12, 2021 6:08AM

    You are giving the database a WHAT-to-do with your SQL statement. It parses that statement, determines HOW to do it, creating a cursor with the how-to instructions.

    This how-to determines the workload that needs to be done. As I/O is the slowest of all operations in the database, the more I/O there is, the slower the cursor execution. The response (output) of the cursor (e.g. 1 row) does not determine performance.

    Nor does the underlying number of rows determine performance.

    Performance is directly related to the size of the cursor's workload.

    For example, this select needs access to numerous objects via full scans - so counting 141,840 rows took 4.16 seconds:

    SQL> set timing on 
    SQL> select count(*) from all_objects; 
     
           COUNT(*) 
    ---------------- 
            141,840 
     
    1 row selected. 
     
    Elapsed: 00:00:04.16 
    

    This example scans a bitmap index in parallel. Bitmap indexes are relatively small, and consumes typically less I/O than a b+tree index to process. So counting over 33 billion rows took less than 3 seconds:

    SQL> select /*+parallel(12)*/ count(*) from daily_summary; 
     
           COUNT(*) 
    ---------------- 
     33,104,208,965 
     
    1 row selected. 
     
    Elapsed: 00:00:02.97 
    

    Bottom line is that the cursor workload is the major determining factor of performance. There can also be concurrency issues and the like impacting performance.

    Thus when a cursor execution is slow you need to determine its execution plan (detailing the how-to), and its event and wait states for starters to figure out why it is slow.