This topic describes the views of PolarDB for PostgreSQL(Compatible with Oracle). You can query views to obtain information about SQL statements. This way, you can identify performance issues.

SQL

To obtain information about SQL statements, you can query the views that are described in this topic.

  • pg_stat_statements
    Note To use the pg_stat_statements view, you must create the pg_stat_statements plug-in by executing the create extension pg_stat_statements statement.
    The following table describes the parameters in this view.
    ParameterTypeDescription
    useridoidThe object identifier (OID) of the user who executes the SQL statement.
    dbidoidThe OID of the database in which the SQL statement is executed.
    queryidbigintThe internal hash code calculated based on the parse tree of the SQL statement.
    querytextThe text of the SQL statement.
    callsbigintThe number of SQL statements that are executed.
    total_timedouble precisionThe total time consumed to execute the SQL statement. Unit: milliseconds.
    min_timedouble precisionThe shortest time consumed to execute the SQL statement. Unit: milliseconds.
    max_timedouble precisionThe longest time consumed to execute the SQL statement. Unit: milliseconds.
    mean_timedouble precisionThe average time consumed to execute the SQL statement. Unit: milliseconds.
    stddev_timedouble precisionThe population standard deviation of the time consumed to execute the SQL statement. Unit: milliseconds.
    rowsbigintThe total number of rows retrieved or affected by the SQL statement.
    shared_blks_hitbigintThe total number of shared-block cache hits by the SQL statement.
    shared_blks_readbigintThe total number of shared blocks read by the SQL statement.
    shared_blks_dirtiedbigintThe total number of shared blocks dirtied by the SQL statement.
    shared_blks_writtenbigintThe total number of shared blocks written by the SQL statement.
    local_blks_hitbigintThe total number of local-block cache hits by the SQL statement.
    local_blks_readbigintThe total number of local blocks read by the SQL statement.
    local_blks_dirtiedbigintThe total number of local blocks dirtied by the SQL statement.
    local_blks_writtenbigintThe total number of local blocks written by the SQL statement.
    temp_blks_readbigintThe total number of temporary blocks read by the SQL statement.
    temp_blks_writtenbigintThe total number of temporary blocks written by the SQL statement.
    blk_read_timedouble precisionThe total time consumed to read blocks. Unit: milliseconds. This parameter is valid only if the track_io_timing parameter is set to on. Otherwise, zero is returned.
    blk_write_timedouble precisionThe total time consumed to write blocks. Unit: milliseconds. This parameter is valid only if the track_io_timing parameter is set to on. Otherwise, zero is returned.
  • polar_stat_sql
    Note To use the polar_stat_sql view, you must create the polar_stat_sql plug-in by executing the create extension polar_stat_sql statement. The polar_stat_sql view can be used as an extension of the pg_stat_statements view.
    The following table describes the parameters in this view.
    ParameterTypeDescription
    queryidbigintThe ID of a query.
    datnamenameThe name of the database.
    rolnamenameThe username.
    user_timedoubleThe period when the user mode is used.
    system_timedoubleThe period when the system mode is used.
    minfltsbigintThe number of recycled pages or minor faults.
    majfltsbigintThe number of major page faults.
    nswapsbigintThe number of page swaps.
    readsbigintThe number of bytes read from disks.
    reads_blksbigintThe number of blocks read from disks.
    writesbigintThe number of bytes written to disks.
    writes_blksbigintThe number of blocks written to disks.
    msgsndsbigintThe number of IPC messages that are sent.
    msgrcvsbigintThe number IPC messages that are received.
    nsignalsbigintThe number of semaphores that are received.
    nvcswsbigintThe number of voluntary context switches.
    nivcswsbigintThe number of involuntary context switches.
    scan_rowsdoubleThe number of rows read by scan node operations.
    scan_timedoubleThe time consumed to perform scan node operations.
    scan_countbigintThe number of scan node operations.
    join_rowsdoubleThe number of rows read by join node operations.
    join_timedoubleThe time consumed to perform join node operations.
    join_countbigintThe number of join node operations.
    sort_rowsdoubleThe number of rows read by sort node operations.
    sort_timedoubleThe time consumed to perform sort node operations.
    sort_countbigintThe number of sort node operations.
    group_rowsdoubleThe number of rows read by group node operations.
    group_timedoubleThe time consumed to perform group node operations.
    group_countbigintThe number of group node operations.
    hash_rowsdoubleThe number of rows read by hash node operations.
    hash_memorybigintThe memory used by hash node operations. Unit: bytes.
    hash_countbigintThe number of hash node operations.
    parse_timedoubleThe time consumed to parse the SQL statement.
    analyze_timedoubleThe time consumed to analyze the SQL statement.
    rewrite_timedoubleThe time consumed to rewrite the SQL statement.
    plan_timedoubleThe time consumed to generate the execution plan.
    execute_timedoubleThe time at which the recorded SQL statement was executed.
    lwlock_waitdoubleThe lwlock wait time.
    rel_lock_waitdoubleThe wait time for table locks.
    xact_lock_waitdoubleThe wait time for transaction locks.
    page_lock_waitdoubleThe wait time for page locks.
    tuple_lock_waitdoubleThe wait time for row locks.
    shared_read_psbigintThe read IOPS.
    shared_write_psbigintThe write IOPS.
    shared_read_throughputbigintThe read throughput. Unit: bytes.
    shared_write_throughputbigintThe write throughput. Unit: bytes.
    shared_read_latencydoubleThe read latency. Unit: microseconds.
    shared_write_latencydoubleThe write latency. Unit: microseconds.
    io_open_numbigintThe number of open file operations.
    io_seek_countbigintThe number of seek file operations.
    io_open_timedoubleThe time consumed to perform open file operations. Unit: microseconds.
    io_seek_timedoubleThe time consumed to perform seek file operations. Unit: microseconds.
  • polar_stat_query_count
    Note To use the polar_stat_query_count view, you must create the polar_stat_sql plug-in by executing the create extension polar_stat_sql statement.
    The following table describes the parameters in this view.
    ParameterTypeDescription
    sqltypetextThe SQL type. Valid values: DQL, DML, DDL, and DCL.
    cmdtypetextThe SQL statement type. Examples: SELECT, INSERT, and UPDATE.
    countbigintThe total number of executions.